我试图重写一些旧的Access VBA代码到Python,但我坚持以下错误:name_error: name 'ERTZ6635' is not defined
旧VBA代码
Set ConsTable = DB.OpenRecordset("SELECT * FROM table1")
ConsCount = 87404
If ConsCount > 0 Then
ConsTable.MoveFirst
For I = 1 To ConsCount
Set ConsBlendTable = DB.OpenRecordset("SELECT * FROM table2 WHERE CONS_BATCH = " & Char34(ConsTable!Batch))
Python代码:
进口win32com.client
dbe = win32com.client.Dispatch("DAO.DBEngine.120")
db = dbe.OpenDatabase(r"C:UsersxyzDesktopacess.accdb")
ConsTable = db.OpenRecordset("select * from table1")
ConsCount = 87404
if ConsCount>0:
ConsTable.MoveFirst()
for i in range(1, ConsCount):
ConsBlendTable = db.OpenRecordset("SELECT * FROM table2 WHERE CONS_BATCH = " & eval(ConsTable.Fields["Batch"].Value))
ERTZ6635值是ConsTable.Fields["Batch"].Value中的值
在VBA代码中,Char34()
可能是用户定义的函数,因为它不是VBA内置方法。然而,对于双引号,ASCII表中的第34个字符有一个常量Chr34
。因此,根据其名称,该方法可以在输入参数值周围使用双引号。这一点很重要,因为您尝试在Python中使用eval
进行翻译。
所以简单的答案是包含双引号,你可以在Python中插入f字符串。
sql = f"""SELECT * FROM table2
WHERE CONS_BATCH = "{ConsTable.Fields["Batch"].Value}"
"""
ConsBlendTable = db.OpenRecordset(sql)
但是,在SQL查询中插入参数的字符串是不可取的中的任何语言,包括VBA和Python。除了安全和效率问题,如果value本身包含双引号,这段代码仍然可以破坏。
相反,考虑MS Access SQL引擎通过QueryDefs支持的参数化。
VBA(从以前的SQL连接调整)
Dim qdef As QueryDef
...
Set ConsTable = DB.OpenRecordset("SELECT * FROM table1")
' PREPARED STATEMENT (NO DATA)
sql = "PARAMETERS batch_prm TEXT(255);" _
& "SELECT * FROM table2 WHERE CONS_BATCH = batch_prm"
ConsTable.MoveFirst
Do While Not ConsTable.EOF
Set qdef = DB.CreateQueryDef("", sql)
qdef!batchprm = ConsTable!Batch ' BIND PARAMETER
Set ConsBlendTable = qdef.OpenRecordset() ' OPEN RECORDSET VIA QUERYDEF
...
ConsBlendTable.Close
ConsTable.MoveNext
Loop
ConsTable.Close
'RELEASE RESOURCES
Set ConsBlendTable = Nothing: Set ConsTable = Nothing
Set qdef = Nothing: Set DB = Nothing
Python(采用try/except
处理COM)
QueryDef
对象接口。下面用传统的DAO循环遍历recordset中的每条记录(即Do While Not rst.EOF
的翻译)。
import win32com.client
try:
dbe = win32com.client.Dispatch("DAO.DBEngine.120")
db = dbe.OpenDatabase(r"C:UsersxyzDesktopacess.accdb")
ConsTable = db.OpenRecordset("SELECT * FROM table1")
# PREPARED STATEMENT
sql = """PARAMETERS batch_prm TEXT(255);
SELECT * FROM table2 WHERE CONS_BATCH = batch_prm
"""
ConsTable.MoveFirst()
while ConsTable.EOF == False:
qdef = db.CreateQueryDef("", sql)
# BIND PARAMETER
qdef.Parameters["batch_prm"].Value = ConsTable.Fields["Batch"].Value
# OPEN RECORDSET VIA QUERYDEF
ConsBlendTable = qdef.OpenRecordset()
...
ConsBlendTable.Close()
ConsTable.MoveNext()
ConsTable.Close()
except Exception as e:
print(e)
finally:
# RELEASE RESOURCES
ConsBlendTable = None; ConsTable = None
qdef = None; db = None; dbe = None
del ConsBlendTable; del ConsTable; del qdef; del db; del dbe
最后,我必须说明。而不是直接翻译VBA,使用Python的DB-API,因为它可以直接查询MS Access数据库,而不需要DAO或COM对象,特别是维护良好的:pyodbc
。使用JOIN
运行,而不是通过WHERE
进行迭代循环。是的,pyodbc
支持带有?
qmarks的参数。
import pyodbc
dbname = r"C:UsersxyzDesktopacess.accdb"
constr = f"DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={dbname};"
conn = pyodbc.connect(constr)
cur = conn.cursor()
sql = """SELECT t1.Batch, t2.CONS_BATCH, ...
FROM table1 t1
INNER JOIN tbale2 t2
ON t1.Batch = t2.CONS_BATCH
"""
cur.execute(sql)
for row in cur.fetchall():
...
cur.close()
conn.close()