Should execute() /executemany() auto-cast parameters to match parameter_schema from prepared statement?

[okhsunrog]

Context

We have an Arrow Flight SQL server and noticed that when users call executemany() with Python data, it fails for column types smaller than Int64 (e.g., Int32, Int16, Int8).

Observed Behavior

1. Server returns correct parameter_schema after prepare():

cursor.adbc_prepare("INSERT INTO my_table(col) VALUES (?)")
param_schema = cursor._stmt.get_parameter_schema()
# Returns: Schema([Field('$1', int32)])

2. User calls executemany() with Python integers:

cursor.executemany("INSERT INTO my_table(col) VALUES (?)", [(1,), (2,), (3,)])

3. PyArrow converts Python int to Int64 by default (in convert_executemany_parameters)

4. Server receives Int64 data but expects Int32 resulting in an error:

column types must match schema types, expected Int32 but found Int64

Workaround

Users can manually create Arrow data with correct types:

cursor.adbc_prepare("INSERT INTO my_table(col) VALUES (?)")
param_schema = pa.Schema._import_from_c_capsule(
    cursor._stmt.get_parameter_schema().__arrow_c_schema__()
)
batch = pa.record_batch([[1, 2, 3]], schema=param_schema)
cursor.executemany("INSERT INTO my_table(col) VALUES (?)", batch)
# Works!

Question

Is this behavior by design?

Looking at the code, convert_executemany_parameters() in _dbapi_backend.py doesn't have access to parameter_schema and simply infers types from Python values.

Should ADBC:

1. Current behavior: User is responsible for providing correctly typed Arrow data
2. Alternative: Auto-cast parameters to match parameter_schema when available

If (1) is intentional, it might be worth documenting this more explicitly.

[CurtHagenlocher]

I think (1) is the only realistic answer for the spec as a whole, though individual drivers might be able to do (2) under some circumstances.

[lidavidm]

Right, the conversion from Python values to Arrow is best-effort. While we could use the schema from GetParameterSchema instead, I'm hesitant since I think for many systems, the schema is not fully reflective of the actual types (e.g. I know Postgres will give VARCHAR when the type is ambiguous/multiple types are possible)