SQLAlchemy Adapter for IBM i

DBAPI Connection

This dialect uses the pyodbc DBAPI and the IBM i Access ODBC Driver.

Connection string:

engine = create_engine("ibmi://user:password@host/rdbname[?key=value&key=value...]")

Connection Arguments

The sqlalchemy-ibmi dialect supports multiple connection arguments that are passed in the URL to the create_engine function.

Connection string keywords:

  • current_schema - Define the default schema to use for unqualified names.

  • library_list - Specify which IBM i libraries to add to the server job’s library list. Can be specified in the URL as a comma separated list, or as a keyword argument to the create_engine function as a list of strings

  • autocommit - If False, Connection.commit must be called; otherwise each statement is automatically committed. Defaults to False.

  • readonly - If True, the connection is set to read-only. Defaults to False.

  • timeout - The login timeout for the connection, in seconds.

  • use_system_naming - If True, the connection is set to use the System naming convention, otherwise it will use the SQL naming convention. Defaults to False.

  • trim_char_fields - If True, all character fields will be returned with trailing spaces truncated. Defaults to False.

create-engine arguments:

  • fast_executemany - Enables PyODBC’s fast_executemany option. Conversion between input and target types is mostly unsupported when this feature is enabled. eg. Inserting a Decimal object into a Float column will produce the error “Converting decimal loses precision”. Defaults to False.

Transaction Isolation Level / Autocommit

Db2 for i supports 5 isolation levels:

  • SERIALIZABLE: *RR

  • READ COMMITTED: *CS

  • READ UNCOMMITTED: *CHG

  • REPEATABLE READ: *ALL

  • NO COMMIT: *NC

At this time, sqlalchemy-ibmi supports all of these isolation levels except NO COMMIT.

Autocommit is supported on all available isolation levels.

To set isolation level globally:

engine = create_engine("ibmi://user:pass@host/", isolation_level='REPEATABLE_READ')

To set using per-connection execution options:

connection = engine.connect()
connection = connection.execution_options(
    isolation_level="SERIALIZABLE"
)

Table Creation String Size

When creating a table with SQLAlchemy, Db2 for i requires that the size of a String column be provided.

Provide the length for a String column as follows:

 class User(Base):
     __tablename__ = 'users'
     id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
     name = Column(String(50))

 users = Table('users', metadata,
     Column('id', Integer, Sequence('user_id_seq'), primary_key=True),
     Column('name', String(50)),
 )

Literal Values and Untyped Parameters

SQLAlchemy will try to use parameter markers as much as possible, even for values specified with the literal, null, func sql expression functions. Because Db2 for i doesn’t support untyped parameter markers, in places where the type is unknown, a CAST expression must be placed around it to give it a type. sqlalchemy-ibmi will automatically do this based on the type object provided to SQLAlchemy.

In some cases, SQLAlchemy allows specifying a Python object directly without a type object. In this case, SQLAlchemy will deduce the type object based on the Python type:

Python type

SQLAlchemy type

bool

Boolean

int

Integer

float

Float

str

Unicode

bytes

LargeBinary

decimal.Decimal

Numeric

datetime.datetime

DateTime

datetime.date

DateTime

datetime.time

DateTime

The deduced SQLAlchemy type will be generic however, having no length, precision, or scale defined. This causes problems when generating these CAST expressions. To support handling the majority of cases, some types will be adjusted:

Python type

SQLAlchemy type

int

BigInteger

str

Unicode(32739)

In addition, Numeric types will be rendered as inline literals. On SQLAlchemy 1.4 and up, this will be done using render_literal_execute to support statement caching.

If the type used is not appropriate (eg. when specifying a >32k string), you must specify the type (or use a cast):

too_big_for_varchar = 'a' * 32768
connection.execute(
    select(literal(too_big_for_varchar, UnicodeText()))
).scalar()

Text search support

The ColumnOperators.match function is implemented using a basic LIKE operation by default. However, when OmniFind Text Search Server for Db2 for i is installed, match will take advantage of the CONTAINS function that it provides.