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

Question

When I try to use cursor.primaryKeys("tablename") then exception occurs:

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

list(cursor.columns(table='tablename')) does not reveal primary keys either.

Answer 1

For Access ODBC we can usually^* 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'}

^*EDIT: This approach assumes that the primary key index for the table is named PrimaryKey. That is true if the table is created using the MS Access table builder (GUI) but is not true if the table is created using DDL (i.e., CREATE TABLE …). In those cases the primary key index will have a name like Index_EA5344E1_0942_445C so the above method won't work, but we can use ACE DAO instead:

import win32com.client  # needs `pip install pywin32`


def get_access_primary_key_columns(db_path, table_name):
    db_engine = win32com.client.Dispatch("DAO.DBEngine.120")
    db = db_engine.OpenDatabase(db_path)
    tbd = db.TableDefs(table_name)
    for idx in tbd.Indexes:
        if idx.Primary:
            return [fld.Name for fld in idx.Fields]


if __name__ == "__main__":
    print(
        get_access_primary_key_columns(
            r"C:UsersPublicDatabase1.accdb", "team"
        )
    )
    # ['city', 'prov']