从MS Access中提取外键

问题描述

我正在尝试从MS Access中的表中获取所有外键。当尝试使用cursor.ForeignKeys(&Q；表&Q；)时，我收到错误：

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

这里有一个类似的问题，但主键：pyodbc - read primary keys from MS Access (MDB) database

---

解决方案

尽管Access ODBC驱动程序不支持SQLForeignKeys函数，但您仍然可以通过Jet/ACEDAO：

获取该信息

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'}]
    """