denfromufa denfromufa - 1 year ago 136
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.

Answer Source

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'}