Skip to content

[known issue] Incorrect reflection of columns' "nullable" status

Jump to bottom
Gord Thompson edited this page Jan 3, 2022 · 1 revision

A defect in the Access ODBC driver prevents the "nullable" status of columns (called "Required" in the Access UI) from being reflected correctly. This dialect calls pyodbc's .columns() method, which in turn calls the ODBC SQLColumnsW function, and the Access ODBC driver returns incorrect values for that function.

pyodbc_cnxn = engine.raw_connection()
pyodbc_crsr = pyodbc_cnxn.cursor()
pyodbc_crsr.execute("""
CREATE TABLE t (
    id COUNTER PRIMARY KEY,
    required_yes LONG NOT NULL,
    required_no LONG NULL
)
""")
for row in pyodbc_crsr.columns("t"):
    print(f"{row.column_name} (nullable: {row.nullable})")

The columns are defined correctly …

required

not required

… but the ODBC driver returns

id (nullable: 0)
required_yes (nullable: 1)
required_no (nullable: 1)
Clone this wiki locally