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']
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…