SQLite Recipes

Change the batch size of the result set

Recipe source: sqlite_batch_rows.py

The ADBC SQLite driver allows control over the size of batches in result sets. Because the driver performs type inference, this also controls how many rows the driver will look at to figure out the type. If you know your result set has many NULL rows up front, you may consider increasing the batch size so that the driver can infer the correct types.

import adbc_driver_sqlite.dbapi

conn = adbc_driver_sqlite.dbapi.connect()

First we’ll set up a demo table with 1024 NULL values.

with conn.cursor() as cur:
    cur.execute("CREATE TABLE demo (val TEXT)")

    cur.execute(
        """
    WITH RECURSIVE series(n) AS (
        SELECT 1
        UNION ALL
        SELECT n + 1
        FROM series
        WHERE n + 1 <= 1024
    )
    INSERT INTO demo (val)
    SELECT NULL
    FROM series
    """
    )

    cur.execute("INSERT INTO demo VALUES ('foo'), ('bar'), ('baz')")

If we query the table naively, we’ll get an error, because the driver first looks at the first 1024 values to determine the column type. But since every value is NULL, it falls back to the default type of int64, which poses a problem when it then encounters a string in the next batch.

with conn.cursor() as cur:
    try:
        cur.execute("SELECT * FROM demo")
        print(cur.fetchallarrow().schema)
    except OSError as e:
        print(e)
        # Output:
        # [SQLite] Type mismatch in column 0: expected INT64 but got STRING/BINARY
    else:
        raise RuntimeError("Expected an error")

We can tell the driver to increase the batch size (and hence look at more rows).

with conn.cursor() as cur:
    cur.adbc_statement.set_options(
        **{
            adbc_driver_sqlite.StatementOptions.BATCH_ROWS.value: 2048,
        }
    )
    cur.execute("SELECT * FROM demo")
    print(cur.fetchallarrow().schema)

stdout

[SQLite] Type mismatch in column 0: expected INT64 but got STRING/BINARY
val: string