Use InterSystems IRIS as a Relational Database with DB-API
As a Python developer, you are likely familiar with querying databases using SQL. InterSystems IRIS® data platform supports this workflow seamlessly, allowing you to treat it like a high-performance relational database. However, if you truly want to work with relational data using Python’s object-oriented nature, you can use an object-relational mapping (ORM), which converts data representation between a relational database and an object-oriented programming language.
This section introduces two common Python tools that can be used with InterSystems IRIS:
-
DB-API: A lightweight, direct SQL interface.
-
SQLAlchemy: A powerful ORM and SQL toolkit.
The easiest way to get started is to download and install the InterSystems implementation of SQLAlchemy, which includes the InterSystems IRIS Python SDK Kit as a dependency. The Python SDK Kit includes both the DB-API driver and the Native SDK for Python. This gives you everything you need to start working with InterSystems IRIS from Python.
Prerequisites
This exercise and the related exercises in this section require the DB-API driver and sometimes SQLAlchemy.
The following command installs both SQLAlchemy (sqlalchemy-intersystems-iris)Opens in a new tab and the DB-API driver (intersystems-irispythonOpens in a new tab):
pip install sqlalchemy-intersystems-iris
DB-API: Direct SQL Access
The iris module provides a PEP 249-compliant interface for executing raw SQL queries. DB-API is the standard interface to interact with any relational backend. It is ideal for lightweight scripts, data access layers, and quick prototyping.
There are multiple iris modules, each with their own APIs. This section focuses on the DB-API approach, which is used for external Python applications that connect to InterSystems IRIS.
For complete documentation on the InterSystems implementation of DB-API, including InterSystems-specific extensions, see Using Python DB-API.
Establishing a DB-API Connection
To establish a connection to an InterSystems IRIS instance using DB-API, use the iris.connect() method. This code creates a connection to the InterSystems IRIS instance and opens a cursor for executing SQL commands.
import iris
# Replace with your connection details
connection_string = "localhost:1972/USER"
username = "_system"
password = "SYS"
connection = iris.connect(connection_string, username, password)
cursor = connection.cursor()
In connection_string, 1972 is the port number while USER is the namespace that you connect to. Change these to match your specific needs, as well.
Remember to close the cursor and connection when you are done:
cursor.close()
connection.close()
Executing a SQL Query
Once connected, you can execute SQL queries using the cursor object. You can then retrieve the results from the query using the methods fetchone(), fetchmany(), fetchall(), or scroll().
This example uses the Sample.Person class from the Samples-Data repository on GitHub: https://github.com/intersystems/Samples-DataOpens in a new tab.
cursor.execute("SELECT * FROM Sample.Person WHERE Age >= 50")
row = cursor.fetchone()
while row is not None:
print(row[:])
row = cursor.fetchone()
In the above example, fetchone() returns a pointer to the next row, or None if no more data is available.
Parameters
Parameters help prevent SQL injections and can make your queries more flexible. With DB-API, you can specify both positional and named parameters to extend your queries. Pass the parameters along with the SQL statement to the cursor to execute them.
The Sample.Person class on GitHub is created with randomized data. To get the parameter examples below to run, find an ID and Name from your sample data.
Positional Parameters
Positional parameters match the question marks in the SQL statement with the arguments in the parameters list by position.
sql = "SELECT * FROM Sample.Person WHERE ID = ? and Name = ?"
params = [1, 'Doe,John Q.']
cursor.execute(sql, params)
result = cursor.fetchone()
row = result[:]
print(row)
Named Parameters
Named parameters match the :argument variables in the SQL statement with the arguments in the parameters dictionary by keyword.
sql = "SELECT * FROM Sample.Person WHERE ID = :id and Name = :name"
params = {'id' : '1', 'name' : 'Doe,John Q.'}
cursor.execute(sql, params)
result = cursor.fetchone()
row = result[:]
print(row)
For more documentation on DB-API, see Using Python DB-API.
