如何使用python从MS Access数据库中检索所有保存的查询?

Question

基本上，我正在使用python比较2个访问数据库。

我无权手动打开任何访问文件，它必须完全在 Python 中完成！

我需要检索完整的列表

• 查询名称
• 关联的查询代码

我不会提前知道查询的名称是什么。

我已经尝试了许多几乎有效的解决方案，我在下面概述了 3 个封闭的解决方案。

---

部分解决方案 1

我几乎让它使用win32com和CurrentDb.QueryDefs方法来检索每个查询的代码。

但是，联接的顺序似乎没有确定性地存储在 2 个数据库之间。 (它似乎取决于 MSysQuery 中条目的顺序)

即在一个数据库中，连接的文本可以是

on Table1.ColumnA = Table2.ColumnA & Table1.ColumnB = Table2.ColumnB

而在另一个

on Table1.ColumnB = Table2.ColumnB & Table1.ColumnA = Table2.ColumnA

显然，这些将导致相同类型的联接，但不是完全相同的查询文本。

如果我直接比较文本，它们不匹配。在比较之前处理文本似乎是一个坏主意，有很多极端情况。

示例代码

objAccess = Dispatch("Access.Application")
objAccess.Visible = False
counter = 0
query_dicts = {}
for database_path in (new_database_path, old_database_path):
# Open New DB and pull stored queries into dict
objAccess.OpenCurrentDatabase(database_path)
objDB = objAccess.CurrentDb()
db_query_dict = {}
for stored_query in objDB.QueryDefs:
db_query_dict[stored_query.name] = stored_query.sql
query_dicts[("New" if counter == 0 else 'Old')] = db_query_dict
objAccess.CloseCurrentDatabase()
counter += 1

---

部分解决方案 2

第一个解决方案失败后，我尝试在 MSysQuery 上编写查询并强制排序。但是，pyodbc 没有对表的读取访问权限！

看来您无法从python本身授予读取访问权限，这是一个问题，在这里可能是错误的。

查询：

SELECT MSysObjects.Name
, MSysQueries.Attribute
, MSysQueries.Expression
, MSysQueries.Flag
, MSysQueries.Name1
, MSysQueries.Name2
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId
order by MSysObjects.Name
, MSysQueries.Attribute
, MSysQueries.Expression
, MSysQueries.Flag
, MSysQueries.Name1
, MSysQueries.Name2

部分解决方案 3

我尝试的另一件事是让python将VBA模块存储到数据库中，该模块会将元信息写入表，然后通过pyodbc读出该表。

我可以添加模块，但 Access 数据库不断提示输入模块的名称。我找不到有关如何使用方法调用命名模块的文档

示例代码：

import win32com.client as win32
import comtypes, comtypes.client
import win32api, time
from win32com.client import Dispatch
strDbName = r'C:UsersUsernameSampleDatabase.mdb'
objAccess = Dispatch("Access.Application")
# objAccess.Visible = False
objAccess.OpenCurrentDatabase(strDbName)
objDB = objAccess.CurrentDb()

xlmodule = objAccess.VBE.VbProjects(1).VBComponents.Add(1)  # vbext_ct_StdModule
xlmodule.CodeModule.AddFromString(Constants.ACCESS_QUERY_META_INFO_MACRO)
objAccess.Run("CreateQueryMetaInfoTable")
objAccess.CloseCurrentDatabase()

objAccess.Quit()

我试图添加的宏。

Sub CreateQueryMetaInfoTable()
Dim sql_string As String
# Create empty table
CurrentDb.Execute ("Create Table QueryMetaInfoTable (QueryName text, SqlCode text)")
Dim qd As QueryDef
For Each qd In CurrentDb.QueryDefs
# insert values
sql_string = "Insert into QueryMetaInfoTable (QueryName, SqlCode) values ('" & qd.Name & "', '" & qd.SQL & "')"
CurrentDb.Execute sql_string
Next

End Sub

Answer 1

在@Gord Thompson的帮助下，我现在有一个可行的解决方案。

我需要与 OLEDB 连接以授予读取访问权限 1st，使用所需信息生成一个非系统表，然后通过 pandas 使用 ODBC 读回该表。

CONNECTION_STRING_OLEDB = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE={};Jet OLEDB:System Database={};" 
ACCESS_QUERY_META_INFO_CREATE = """SELECT MSysObjects.Name
, MSysQueries.Attribute
, MSysQueries.Expression
, MSysQueries.Flag
, MSysQueries.Name1
, MSysQueries.Name2
INTO QueryMetaInfo       
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId
order by MSysObjects.Name
, MSysQueries.Attribute
, MSysQueries.Expression
, MSysQueries.Flag
, MSysQueries.Name1
, MSysQueries.Name2"""

ACCESS_QUERY_META_INFO_READ = """select * from QueryMetaInfo
order by Name
, Attribute
, Expression
, Flag
, Name1
, Name2;"""
ACCESS_QUERY_META_INFO_DROP = "DROP TABLE QueryMetaInfo"
connection = win32com.client.Dispatch(r'ADODB.Connection')
DSN = CONNECTION_STRING_OLEDB.format(database_path, r"C:UsersC218AppDataRoamingMicrosoftAccessSystem.mdw")
connection.Open(DSN)
cmd = win32com.client.Dispatch(r'ADODB.Command')
cmd.ActiveConnection = connection
cmd.CommandText = "GRANT SELECT ON MSysObjects TO Admin;"
cmd.Execute()
connection.Execute(ACCESS_QUERY_META_INFO_CREATE)
connection.Close()
# connect with odbc to read the query meta info into pandas
connection_string = Constants.CONNECTION_STRING_ACCESS.format(database_path)
access_con = pyodbc.connect(connection_string)
access_cursor = access_con.cursor()
df = pd.read_sql(ACCESS_QUERY_META_INFO_READ, access_con)
# drop table after read
access_cursor.execute(ACCESS_QUERY_META_INFO_DROP)
access_cursor.commit