从MS Access提取外键

Question

我试图从MS Access表中获取所有外键。当尝试使用cursor.foreignKeys("Table")时，我得到错误:

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

这是一个类似的问题，但有主键:pyodbc -从MS Access (MDB)数据库读取主键

Answer 1

虽然Access ODBC驱动程序不支持SQLForeignKeys函数，但您仍然可以通过Jet/ACE DAO获得该信息:

import win32com.client  # needs `pip install pywin32`

def get_access_foreign_keys(db_path, table_name):
db_engine = win32com.client.Dispatch("DAO.DBEngine.120")
db = db_engine.OpenDatabase(db_path)
fk_list = []
for rel in db.Relations:
if rel.ForeignTable == table_name:
fk_dict = {
"constrained_columns": [],
"referred_table": rel.Table,
"referred_columns": [],
"name": rel.Name,
}
for fld in rel.Fields:
fk_dict["constrained_columns"].append(fld.ForeignName)
fk_dict["referred_columns"].append(fld.Name)
fk_list.append(fk_dict)
return fk_list

if __name__ == "__main__":
print(get_access_foreign_keys(r"C:UsersPublicDatabase1.accdb", "child"))
"""
[{'constrained_columns': ['parent_id'], 
'referred_table': 'parent', 
'referred_columns': ['id'], 
'name': 'parentchild'}]
"""