如何通过Python 3.5.1创建永久的MS Access查询? [英] How to create permanent MS Access Query by Python 3.5.1?
问题描述
我有大约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()
SO,如何从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解决此问题.
谢谢.
您可以使用 要删除该保存的查询,您只需执行 DROP VIEW 语句. 有关Access中DDL的更多信息,请参见 数据定义语言 > I have about 40 MS Access Databases and have some troubles if need to create or transfer one of MS Access Query (like object) from one db to other dbs.
So I tried to solve this problem with SO, How to create and save MS Access Query like objects from python?
I tried to search info in Google, but the answers were related with Run SQL code. On VBA this code looks like: Sorry for my English, it's not my native :) By the way, I know how to solve this by VBA, but I'm interested in solve this by python. Thank you. You can use a CREATE VIEW statement to create a saved Select Query in Access. The pyodbc equivalent to your VBA example would be To delete that saved query you could simply execute a DROP VIEW statement. For more information on DDL in Access see 这篇关于如何通过Python 3.5.1创建永久的MS Access查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!crsr = conn.cursor()
sql = """\
CREATE VIEW TestQuery AS
SELECT * FROM TestTable
"""
crsr.execute(sql)
pyodbc
but.. as I saw pyodbc
doesn't support to create new, permanent MS Access Query (object).
I can connect to db, create or delete tables/rows but can't to create and save new query.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()
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
crsr = conn.cursor()
sql = """\
CREATE VIEW TestQuery AS
SELECT * FROM TestTable
"""
crsr.execute(sql)