denfromufa denfromufa - 2 years ago 173
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'}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download