Read fidelity with ConnectorX, Ibis and database drivers

Read role and versions

Reference versions include ConnectorX 0.4.6, Ibis 12.0.0, SQLAlchemy 2.1.4, PyMySQL 1.2.3 and mysql-connector-python 26.7.0. The read-only matrix invoked real APIs; 5000 read combinations do not certify writes, CDC or bulk loading. Complete the shared fixtures and install connectorx pandas ibis-framework[mysql] sqlalchemy pymysql packaging. On Debian, installing the Ibis MySQL extra may build mysqlclient from source; install pkg-config and libmariadb-dev first if that build reports missing MySQL/MariaDB headers. If the Ibis backend import reports a missing packaging module, install it into the same Python environment that runs Ibis.

Keep raw reads separate from explicit CAST/HEX profiles. Default inference of UInt64/BIT, exact decimals, negative TIME and wide date ranges can differ from the database. Passing an exact representation does not make raw inference pass. Historical Ibis UInt64 metadata report: matrixone#29846.

ConnectorX exact reads

Cast ID/decimal to text and binary to HEX, retaining SQL NULL as None. The URL is constructed structurally and stays out of logs. For remote TLS, use parameters supported by the selected ConnectorX backend and qualify them separately; this minimal example is local.

A raw ConnectorX query over non-UTF-8 binary such as X'00FF80' can raise pyo3_runtime.PanicException during client-side conversion. Run the raw probe in a separate process, preserve its error and exit status, then run the CAST/HEX query below as a separate adapter case. Do not record the adapter pass as a pass for the raw path. SQL NULL and zero-length binary remain distinct: HEX(NULL) is NULL while HEX(X'') is an empty string.

import connectorx as cx
from mo_common import engine

query = ("SELECT CAST(id AS CHAR) AS id_text,CAST(amount AS CHAR) AS amount_text,"
         "HEX(payload) AS payload_hex,label,rev FROM lake_demo WHERE id IN (2,9007199254740993) ORDER BY id")
e = engine()
try:
    uri = e.url.set(drivername="mysql").render_as_string(hide_password=False)
    frame = cx.read_sql(uri,query,return_type="pandas")
    rows = frame.to_dict("records")
    r = next(x for x in rows if x["id_text"]=="9007199254740993")
    assert r["amount_text"]=="12345678901234567890.123456789012345678"
    assert r["payload_hex"]=="00FF80" and r["label"]=="中文😀"
finally:
    e.dispose()

Ibis backend and SQLAlchemy reads

Reuse the same query definition for these two APIs. Building an Ibis expression is not execution: call execute and check values. For table reflection, record schema() and actual SQL and qualify unsigned/BOOL reflection separately; custom SQL success does not establish correct reflection.

import os
import ibis
import pandas as pd
import sqlalchemy as sa
from mo_common import options, engine

kw = options()
ssl_args = {}
if os.environ.get("MO_SSL_CA"):
    ssl_args = {"ssl":{"ca":os.environ["MO_SSL_CA"]},"ssl_mode":"VERIFY_IDENTITY"}
backend = ibis.mysql.connect(host=kw["host"],port=kw["port"],user=kw["user"],
                             password=kw["password"],database=kw["database"],charset="utf8mb4",**ssl_args)
try:
    expression = backend.sql(query)
    result = expression.execute()
    print(result.dtypes)
finally:
    backend.disconnect()
e = engine()
try:
    with e.connect() as c:
        result = pd.read_sql_query(sa.text(query),c,coerce_float=False)
    assert result.loc[result.id_text=="9007199254740993","payload_hex"].iloc[0]=="00FF80"
finally:
    e.dispose()

For a fixture containing a NULL payload, check it with pd.isna(value) and compare the non-NULL zero-length payload with value == "". In the tested text query, ConnectorX returned Python None for NULL, while pandas and Ibis .execute() returned floating NaN. coerce_float=False prevents decimal coercion to float; it does not force pandas to represent SQL NULL as Python None.

JDBC, ODBC, SDK and complete acceptance

Follow the real connection steps in the JDBC guide, ODBC guide and Python driver guide, then run the same typed fixtures. Use getBigDecimal/getBytes for exact JDBC values and qualify negative TIME/wide dates separately. Retain ODBC SQL type, column size and decimal digits. Check MatrixOne SDK read, write and commit/rollback APIs separately. The reflected BOOL DDL report is #29879.

Use original source values as the oracle; compare complete fresh reads, NULL/empty distinctions, multiplicity and column order. Pagination needs stable ordering keys and a consistent input range. Parallel partitions do not automatically share one SQL snapshot. Reader throughput reports need connections, partitions, bytes transferred and memory; cache/small-fixture timing is not overall lakehouse capacity certification.