denfromufa denfromufa - 1 year ago 109
SQL Question

pyodbc - read primary keys from MS Access (MDB) database

When I try to use

then exception occurs:

Error: ('IM001', '[IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function (0) (SQLPrimaryKeys)')

does not reveal primary keys either.


For Access ODBC we can get the Primary Key columns via the .statistics method of the pyodbc cursor object:

crsr = conn.cursor()
table_name = 'MyTable'
# dict comprehension: {ordinal_position: col_name}
pk_cols = {row[7]: row[8] for row in crsr.statistics(table_name) if row[5]=='PrimaryKey'}
print(pk_cols)  # e.g., {1: 'InvID', 2: 'LineItem'}