SQLAchemy-iris with the latest version Python driver

After so many years of waiting, we finally got an official driver available on Pypi

Additionally, found JDBC driver finally available on Maven already for 3 months,  and .Net driver on Nuget more than a month.

 As an author of so many implementations of IRIS support for various Python libraries, I wanted to check it. Implementation of DB-API means that it should be replaceable and at least functions defined in the standard. The only difference should be in SQL.

And the beauty of using already existing libraries, that they already implemented other databases by using DB-API standard, and these libraries already expect how driver should work.

I decided to test InterSystems official driver by implementing its support in SQLAlchemy-iris library.

executemany

Prepare a database operation (query or command) and then execute it against all parameter sequences or mappings found in the sequence seq_of_parameters.

Very helpful function, whish let insert multiple rows at once. Let's start with a simple example

import iris

host = "localhost"
port = 1972
namespace = "USER"
username = "_SYSTEM"
password = "SYS"
conn = iris.connect(
    host,
    port,
    namespace,
    username,
    password,
)

with conn.cursor() as cursor:
    cursor = conn.cursor()

    res = cursor.execute("DROP TABLE IF EXISTS test")
    res = cursor.execute(
        """
    CREATE TABLE test (
            id IDENTITY NOT NULL,
            value VARCHAR(50)
    ) WITH %CLASSPARAMETER ALLOWIDENTITYINSERT = 1
    """
    )

    cursor = conn.cursor()
    res = cursor.executemany(
        "INSERT INTO test (id, value) VALUES (?, ?)", [
            (1, 'val1'),
            (2, 'val2'),
            (3, 'val3'),
            (4, 'val4'),
        ]
    )
Python
Python

This is working fine, but what if we need to insert only one value per row.

    res = cursor.executemany(
        "INSERT INTO test (value) VALUES (?)", [
            ('val1', ),
            ('val2', ),
            ('val3', ),
            ('val4', ),
        ]
    )
Python
Python

This unfortunately leads to an unexpected exception

RuntimeError: Cannot use list/tuple for single values

By some reason, one value per row is allowed, and InterSystems requires using a different way

    res = cursor.executemany(
        "INSERT INTO test (value) VALUES (?)", [
            'val1',
            'val2',
            'val3',
            'val4',
        ]
    )
Python
Python

This way it's working fine

fetchone

Fetch the next row of a query result set, returning a single sequence, or None when no more data is available.

For instance simple example on sqlite

import sqlite3
con = sqlite3.connect(":memory:")

cur = con.cursor()
cur.execute("SELECT 1 one, 2 two")
onerow = cur.fetchone()
print('onerow', type(onerow), onerow)
cur.execute("SELECT 1 one, 2 two union all select '01' as one, '02' as two")
allrows = cur.fetchall()
print('allrows', type(allrows), allrows)
Python
Python

gives

onerow <class 'tuple'> (1, 2)
allrows <class 'list'> [(1, 2), ('01', '02')]

And with InterSystems driver

import iris

con = iris.connect(
    hostname="localhost",
    port=1972,
    namespace="USER",
    username="_SYSTEM",
    password="SYS",
)

cur = con.cursor()
cur.execute("SELECT 1 one, 2 two")
onerow = cur.fetchone()
print("onerow", type(onerow), onerow)
cur.execute("SELECT 1 one, 2 two union all select '01' as one, '02' as two")
allrows = cur.fetchall()
print("allrows", type(allrows), allrows)
Python
Python

by some reasons gives

onerow <class 'iris.dbapi.DataRow'> <iris.dbapi.DataRow object at 0x104ca4e10>
allrows <class 'tuple'> ((1, 2), ('01', '02'))

What is DataRow, why not tuple or at least a list

Exceptions

Standard describes a variety of exception classes that the driver is supposed to use, in case if something is wrong. And the InterSystems driver does not use it at all, just raising RunTime error for any reason, which is not part of the standard anyway.

Application may rely on the exception type happening, and behave accordingly. But InterSystems driver does not provide any difference. And another issue, SQLCODE would help, but it needs to be parsed out of error message

Conclusion

So, during testing I found multiple bugs

• Random errors happening at any time <LIST ERROR> Incorrect list format, unsupported type for IRISList; Details: type detected : 32
  • will work ok, if you try again right after the error
• Caught some segmentation faults, don't even know how it happens
• Unexpected result from fetchone function
• Unexpected way of working of executemany function, for one value rows
• Exceptions not implemented at all, different errors should raise different exceptions, and applications rely on it
• Can break Embedded Python if installed next to IRIS
  • due to the same name used by Embedded Python and this driver, it overrides what's already installed with IRIS and may break it

SQLAlchemy-iris now supports the official InterSystems driver, but due to incompatibility with Embedded Python and several bugs discovered during testing. Install with this command, with the defined extra

pip install sqlalchemy-iris[intersystems]

And simple usage, URL should be iris+intersystems://

from sqlalchemy import Column, MetaData, Table
from sqlalchemy.sql.sqltypes import Integer, VARCHAR
from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase


DATABASE_URL = "iris+intersystems://_SYSTEM:SYS@localhost:1972/USER"
engine = create_engine(DATABASE_URL, echo=True)

# Create a table metadata
metadata = MetaData()


class Base(DeclarativeBase):
    pass
def main():
    demo_table = Table(
        "demo_table",
        metadata,
        Column("id", Integer, primary_key=True, autoincrement=True),
        Column("value", VARCHAR(50)),
    )

    demo_table.drop(engine, checkfirst=True)
    demo_table.create(engine, checkfirst=True)
    with engine.connect() as conn:
        conn.execute(
            demo_table.insert(),
            [
                {"id": 1, "value": "Test"},
                {"id": 2, "value": "More"},
            ],
        )
        conn.commit()
        result = conn.execute(demo_table.select()).fetchall()
        print("result", result)


main()
Python
Python

Due to bugs in InterSystems driver, some features may not work as expected. And I hope it will be fixed in the future