SQL ↔ Python types
| SQL type | Python type | Notes |
|---|---|---|
SMALLINT / INT / BIGINT / SERIAL | int | Arbitrary precision on the Python side. |
FLOAT / SMALLFLOAT | float | IEEE 754 double / single. |
DECIMAL(p,s) / MONEY | decimal.Decimal | Exact precision preserved. |
CHAR / VARCHAR / NCHAR / NVCHAR / LVARCHAR | str | Decoded using client_locale. |
BOOLEAN | bool | |
DATE | datetime.date | |
DATETIME YEAR TO … | datetime.datetime / datetime.time / datetime.date | The Python type depends on the field range. |
INTERVAL DAY TO FRACTION | datetime.timedelta | |
INTERVAL YEAR TO MONTH | informix_db.IntervalYM | Custom type — datetime.timedelta can’t represent year-month intervals. |
BYTE / TEXT (legacy in-row blobs) | bytes / str | |
BLOB / CLOB (smart-LOBs) | informix_db.BlobLocator / informix_db.ClobLocator | Read via cursor.read_blob_column, write via cursor.write_blob_column. |
ROW(…) | informix_db.RowValue | Named-tuple-like with .name-accessible fields. |
SET(…) / MULTISET(…) / LIST(…) | informix_db.CollectionValue | Iterable; preserves duplicates only for MULTISET / LIST. |
NULL | None |
DATETIME field ranges
Section titled “DATETIME field ranges”Informix’s DATETIME YEAR TO X is field-range typed. The Python type returned depends on which fields are present:
| Range | Python type |
|---|---|
YEAR TO YEAR through YEAR TO DAY | datetime.date |
YEAR TO HOUR through YEAR TO FRACTION(5) | datetime.datetime |
HOUR TO HOUR through HOUR TO FRACTION(5) | datetime.time |
Decimal precision
Section titled “Decimal precision”DECIMAL columns preserve their declared precision and scale through the codec. A DECIMAL(10,2) column with value 123.45 decodes to Decimal("123.45") exactly — no float intermediate.
For binding Decimal values into INSERT/UPDATE, the driver uses the column’s declared scale. Pass Decimal for exact values; float works but may cause rounding at the column scale.
NULL is None in both directions. Use IS NULL / IS NOT NULL in SQL — WHERE x = ? with None returns no rows even where x IS NULL.
Type extensions
Section titled “Type extensions”informix_db.IntervalYM(years, months) represents INTERVAL YEAR TO MONTH:
from informix_db import IntervalYM
ym = IntervalYM(years=2, months=3)cur.execute("INSERT INTO contracts (term) VALUES (?)", (ym,))
cur.execute("SELECT term FROM contracts WHERE id = ?", (1,))result = cur.fetchone()[0] # IntervalYM(years=2, months=3)informix_db.RowValue and informix_db.CollectionValue are read-only types returned for ROW, SET, MULTISET, LIST columns. Both expose Python iteration and indexed access.