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.