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

You can then connect to InterSystems IRIS as follows:

        import iris

server = "localhost"
port = 1972 
namespace = "USER"
username = "_system"
password = "SYS"

connection = iris.connect(server, port, namespace, username, password)
        
    

From the connection object, you can either create a irispy object to access globals and server-side methods, or create a cursor object to run SQL queries on a database with the DB-API.

Further Reading

Executing SQL Commands

SQL queries on the database can be run with the Python DB-API or with SQLAlchemy.

DB-API

        ## Connection from above
connection = iris.connect(server, port, namespace, username, password)

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)
        
    

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

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

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

You can use similar syntax to execute many queries:

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

params = [ 
	[ "Hello",15, "World"],
    [ "Foo", 15, "Bar"],
    [ "Alpha", 3,"Beta"],
    ["One", 2, "Three"]
]
cursor.executemany(sql_insert, 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:

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

cursor.execute(sql_select) 

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

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

# Fetch all (remaining) rows
results_set = cursor.fetchall() 
        
    

The results set is a list of lists, but 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

SQLAlchemy is a popular Python SQL Object Relational Mapper (ORM) which can be used with InterSystems IRIS. To use SQLAlchemy with IRIS, install "sqlalchemy-iris[intersystems]". This installs both the base SQLAlchemy, as well as the InterSystems-specific extensions.

pip install "sqlalchemy-iris[intersystems]"

SQLAlchemy can be used as follows:

        from sqlalchemy import create_engine 

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

DATABASE_URL = f"iris://{username}:{password}@{server}:{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_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

server = "localhost"
port = 1972 
namespace = "USER"
username = "_system"
password = "SYS"

## Create connection (as above)
connection = iris.connect(server, port, namespace, username, password)

# 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") 

##close the connection
connection.close()
        
    

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:

        # See above for connection
irispy = iris.createIRIS(connection)

number_sum = irispy.classMethodValue("sample.DemoClass", "AddNumbers", 1, 2) 
print(number_sum) # prints 3

# Sets the first integer subscript of a global to "Hello World"
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:

        # 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 function with a return
new_age = person.invoke("addYears", 5)
print(new_age) # prints 30

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