Connecting to InterSystems IRIS from Client-Side Python Applications

Introduction

Much of the functionality of InterSystems IRIS can be accessed from Python programs that run externally to the InterSystems IRIS instance. This is distinct from Embedded Python, which exists with classes and is run within InterSystems IRIS.

This guide refers to connecting to instances of InterSystems IRIS using applications run on the client-side, i.e. not on the same server as InterSystems IRIS. This connection can be made to allow access to InterSystems IRIS databases, or to run scripts or functions on the InterSystems IRIS server.

Installing the Python Software Development Kit

The SDK can be installed using pip:

pip  install intersystems-irispython

Executing SQL Commands

To run SQL commands on a database, we can either use the DB-API or SQLAlchemy. These are common Python frameworks for connecting to SQL databases from Python,. DB-API is a Python standard (PEP 249) which is supported by the InterSystems Python SDK, while SQLAlchemy is a popular toolkit and Object Relational Mapper.

        import iris

connection_args = {
    "hostname":"localhost",
    "port":1972,
    "namespace":"USER",
    "username":"_system",
    "password":"SYS"
} 

# Connect
with iris.dbapi.connect(**connection_args) as connection:

    # Create cursor object
    cursor = connection.cursor() 

    # Basic SQL command to create a table
    sql_create ="""CREATE TABLE package.TableName (
                Col1 VARCHAR(50),
                Col2 INTEGER,
                Col3 VARCHAR(200)
                )
                """
    # Execute the Command
    cursor.execute(sql_create)

        
    

To add parameter to a query at run-time, leave a ? in place of a parameter. Here is an example of using this to insert values:

            sql = "INSERT INTO package.TableName ( Col1, Col2, Col3) values (?,?,?)"

    cursor.execute(sql, ["Hello", 1, "World!"])

Use similar syntax to execute many queries:

            params = [ 
        ["Hello", 2, "World"],
        ["Foo", 23, "Baz"],
        ["Alpha", 56, "Gamma"],
        ["One", 2, "Three"]
    ]
    cursor.executemany(sql, params)
        
    

To retrieve data from the database, we can use SELECT queries. The queries are executed in the same way, but require an additional method call to fetch the data. There are a few options here:

        with iris.dbapi.connect(**connection_args) as connection:
    
    # Create Cursor
    cursor = connection.cursor()

    # Define query
    sql_select = "SELECT ID, Col1, Col2, Col3 FROM package.TableName"

    # Execute query
    cursor.execute(sql_select) 

    # Fetch next row of query results
    cursor.execute(sql_select) 
    results_set = cursor.fetchone() 

    # Fetch the next 5 rows
    results_set = cursor.fetchmany(5)

    # Fetch all rows
    cursor.execute(sql_select) # Re-execute query
    results_set = cursor.fetchall() 

    print(results_set)  # ((1, 'Hello', 1, 'World!'), (2, 'Hello', 2, 'World'), (3, 'Foo', 23, 'Baz'), (4, 'Alpha', 56, 'Gamma'), (5, 'One', 2, 'Three'))
        
    

The results set is a tuple of tuples, and can be easily converted to a pandas DataFrame as follows:

        import pandas as pd

df = pd.DataFrame(results_set, columns=["ID", "Col1", "Col2", "Col3"])

SQL Alchemy

To use SQLAlchemy with IRIS, install the official InterSystems SQLAlchemy dialect from PyPI. This installs both the base SQLAlchemy, as well as the InterSystems-specific extensions.

pip install sqlalchemy-intersystems-iris

SQLAlchemy can be used as follows:

        from sqlalchemy import create_engine 

# Credentials: 
username = "_system" 
password = "SYS" 
namespace = "USER" 
hostname = "localhost" 
port = 1972 

DATABASE_URL = f"iris://{username}:{password}@{hostname}:{port}/{namespace}" 

engine = create_engine(DATABASE_URL, echo=False)
        
    

You can use SQLAlchemy to create a table:

        from sqlalchemy import Column, MetaData, Table, select
from sqlalchemy.sql.sqltypes import Integer, VARCHAR
from sqlalchemy_intersystems_iris import IRISVector

## Create a table, adding column objects to create the schema

demo_table = Table("DemoTableName",  # Give the table a name
                    
                    ## Create metadata for the table
                    MetaData(), 
                    
                    ## Create an ID as the index and autoincrement it
                    Column("ID", Integer, primary_key=True, autoincrement = True),
                    
                    ## Create a column for the item name
                    Column("Name", VARCHAR(1024)),
                    
                    ## Use the special IRIS Vector datatype to embed vectors 
                    Column("EmbeddingVectors", IRISVector(item_type=float, max_items=3)) 
                  )

# Drop existing table with same name
demo_table.drop(engine, checkfirst=True) # Uses the engine object created in the block above

# Create table
demo_table.create(engine, checkfirst=True)
        
    

and insert data:

        with engine.connect() as conn: 
    conn.execute( demo_table.insert(), ## the action being executed
                [
                    {"Name": "Cat","EmbeddingVectors":[4,4,2]},
                    {"Name": "Dog","EmbeddingVectors":[3.5,3.5,2]},
                    {"Name": "Espresso", "EmbeddingVectors":[-2, -2, 1]},
                    {"Name": "Tea", "EmbeddingVectors":[-1, -5, 0]}
                ]
    )
    conn.commit()
        
    

The SQLAlchemy engine can be combined with the data manipulation tool, pandas, to read data directly into a DataFrame:

        import pandas as pd 

query = "SELECT ID, Name, EmbeddingVectors FROM DemoTableName"

# Read SQL output Directly to Pandas Dataframe
df = pd.read_sql(query, engine)

print(df.head())

# Outputs: 
#    ID      Name EmbeddingVectors
# 0   1       Cat            4,4,2
# 1   2       Dog        3.5,3.5,2
# 2   3  Espresso          -2,-2,1
# 3   4       Tea          -1,-5,0
        
    

Native Connection (Globals and Methods)

Globals and InterSystems IRIS classes can be accessed using an iris.createIRIS connection:

        import iris

connection_args = {
    "hostname":"localhost",
    "port":1972,
    "namespace":"USER",
    "username":"_system",
    "password":"SYS"
} 
## Create connection

with iris.connect(**connection_args) as connection:
    
    # Create an iris object
    irispy = iris.createIRIS(connection)

    # Create a global array in the USER namespace on the server
    irispy.set("Value", "myGlobal", "subscript") 

    # Read the value from the database and print it
    # usage: irispy.get(Global, subscripts1, subscript2, subscript...)

    print(irispy.get("myGlobal", "subscript")) # prints "Value"

    # Delete the global array and terminate
    irispy.kill("myGlobal") 
        
    

You can also use the irispy object with server-side classes and methods. You call class method using irispy.classMethodValue or irispy.classMethodVoid, where value and void specify whether anything is returned from the class method. If you need specific return types, you also used irispy.classMethodString, or replace String with another datatype.

So we could use the following InterSystems IRIS class:

        Class sample.DemoClass 
{
ClassMethod AddNumbers(a As %Integer, b As %Integer) As %Integer
{
	return a+b	
}
ClassMethod IncrementGlobal(value As %String) As %Status
{
	set ^DemoGlobal($INCREMENT(^DemoGlobal)) = value
}
}
        
    

With:

        with iris.connect(**connection_args) as connection:
    
    # Create irispy object
    irispy = iris.createIRIS(connection)

    # Call the AddNumbers class method with return value
    number_sum = irispy.classMethodValue("sample.DemoClass", "AddNumbers", 1, 2) 
    print(number_sum) # prints 3

    # Calls the IncrementGlobal class method without return value
    irispy.classMethodVoid("sample.DemoClass", "IncrementGlobal", "Hello World")

    print(irispy.get("DemoGlobal")) # prints 1 (or the number of times IncrementGlobal has been run)

    print(irispy.get("DemoGlobal", 1)) # Prints "Hello World"

You can also create a class object, for example, say you had the IRIS class:

        Class sample.Person Extends %Persistent
{

	Property Name As %String;
	
	Property Age As %Integer;
	
	Method setName(name As %String) As %Status{
		set ..Name = name
		quit $$$OK
	}
	
	Method addYears(num as %Integer) As %Integer {
		return ..Age + num
	}
}
        
    

You could use this in an external Python application with:

        with iris.connect(**connection_args) as connection:
        
    # Create new class object 
    person = irispy.classMethodObject("sample.Person", "%New")

    # Set a property
    person.set("Age", 25)

    # Get a property
    age = person.get("Age")

    print(age) # 25

    # Call a method without a return with .invokeVoid(methodName, parameter)
    person.invokeVoid("setName", "George")
    print(person.get("Name")) # prints "George"

    # Call a method with a return
    new_age = person.invoke("addYears", 5)
    print(new_age) # prints 30

    # Call the inherited method to save the object to a database
    person.invokeVoid("%Save")
        
    

Further Reading