如何使用Python 3.5.1创建永久的MS Access查询？

Question

我拥有大约40个MS Access数据库，如果需要从一个数据库创建或转移一个MS Access查询（如对象）到其他数据库，会遇到一些问题。 因此，我尝试使用pyodbc解决这个问题，但是.. 我发现pyodbc不支持创建新的、永久的MS Access查询（对象）。 我可以连接到数据库，创建或删除表/行，但无法创建并保存新查询。

import pyodbc

odbc_driver = r"{Microsoft Access Driver (*.mdb, *.accdb)}"

db_test1 = r'''..\Test #1.accdb'''
db_test2 = r'''..\Test #2.accdb'''
db_test3 = r'''..\Test #3.accdb'''
db_test4 = r'''..\Test #4.accdb'''

db_test_objects = [db_test1, db_test2, db_test3, db_test4]

odbc_conn_str = "Driver=%s;DBQ=%s;" % (odbc_driver, db_file)
print (odbc_conn_str)

conn = pyodbc.connect(odbc_conn_str)
odbc_cursor = conn.cursor()

NewQuery = "CREATE TABLE TestTable(symbol varchar(15), leverage double)"

odbc_cursor.execute(NewQuery)
conn.commit()
conn.close()

那么，如何从Python创建并保存类似于MS Access查询的对象呢？我尝试在Google上搜索相关信息，但是答案都与运行SQL代码有关。

在VBA中，此代码看起来像：

Public Sub CreateQueryDefX()

   Dim base(1 To 4) As String
   base(1) = "..\Test #1.accdb"
   base(2) = "..\Test #2.accdb"
   base(3) = "..\Test #3.accdb"
   base(4) = "..\Test #4.accdb"

   For i = LBound(base) To UBound(base)
    CurrentBase = base(i)
    Set dbo = OpenDatabase(CurrentBase)
        With dbo
        Set QueryNew = .CreateQueryDef("TestQuery", _
         "SELECT * FROM TestTable")
         RefreshDatabaseWindow
        .Close
        End With
   Next i

RefreshDatabaseWindow

End Sub

抱歉我的英语不是母语。

顺便说一下，我知道如何通过VBA解决这个问题，但我对用Python解决它很感兴趣。

谢谢。

- kn9ka

Answer 1

您可以使用创建视图语句在Access中创建一个保存的Select查询。相当于您的VBA示例的pyodbc等效语句是：

crsr = conn.cursor()
sql = """\
CREATE VIEW TestQuery AS
SELECT * FROM TestTable
"""
crsr.execute(sql)

要删除保存的查询，您可以执行DROP VIEW语句。

有关Access中DDL的更多信息，请参见 数据定义语言。

- Gord Thompson

---

Gord，谢谢。我能再问一个问题吗？如何删除已创建的视图？如果您有关于pyodbc语句（例如CREATE VIEW等）的链接，我将不胜感激。我在文档中找不到相关信息。 - kn9ka

Answer 2

考虑到 Python 等价于 VBA，使用 COM 接口调用 Access 对象库。通过 Python 的第三方模块 win32com，您可以调用 CreateQueryDef 方法。请注意：此 COM 接口可适用于其他语言，如 PHP 和 R！下面使用 try/except/finally 代码块以确保 Access 应用程序进程关闭且不论代码成功或失败（类似于 VBA 中的 On Error 处理）。

import win32com.client

# OPEN ACCESS APP AND DATABASE
dbases = ["..\Test #1.accdb", "..\Test #2.accdb", "..\Test #3.accdb", "..\Test #4.accdb"]

try:
    oApp = win32com.client.Dispatch("Access.Application")

    # CREATE QUERYDEF
    for db in dbases:
        oApp.OpenCurrentDatabase(db)
        currentdb = oApp.CurrentDb()
        currentdb.CreateQueryDef("TestQuery", "SELECT * FROM TestTable")
        currentdb = None
        oApp.DoCmd.CloseDatabase

except Exception as e:
    print(e)

finally:
    currentdb = None
    oApp.Quit
    oApp = None

---

此外，如果您需要通过pyodbc而不是COM接口运行DML语句，请考虑使用分布式查询，因为Access可以直接在SQL中查询其他数据库。下面的代码应该适用于Python（请确保转义反斜杠）：

SELECT t.* FROM [C:\Path\To\Other\Database.accdb].TestTable t

- Parfait

---

非常好，谢谢，它可以工作，但是COM对象有一个大问题，那就是安全选项。在我的情况下，并非每个人都有更改它们的特权。 - kn9ka