Pyodbc - Read Primary Keys From Ms Access (mdb) Database

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) (SQLPr

Solution 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`defget_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:\Users\Public\Database1.accdb", "team"
        )
    )
    # ['city', 'prov']

Solution 2:

Here is solution using pythonnet and Oledb Jet driver. Note that this does not preserve the order of primary keys as columns:

import clr
import System
import System.Data.OleDb
from System.Data.OleDb import OleDbSchemaGuid

defgetKeyNames(tableName, mdbname):
    conn = System.Data.OleDb.OleDbConnection()
    conn.ConnectionString = ("Provider=Microsoft.Jet.OLEDB.4.0;""Data source={}".format(mdbname))
    conn.Open()
    returnList=[]
    mySchema = (conn).GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,
        [None, None, tableName])
    columnOrdinalForName = mySchema.Columns["COLUMN_NAME"].Ordinal
    for r in mySchema.Rows:
        returnList.append(r.ItemArray[columnOrdinalForName])
        conn.Close()
    return returnList

getKeyNames(table_name,mdbname)