pypyodbc更新编号类型字段

Question

我已经使用下面的脚本成功地更新了Access .mdb数据库中属于文本字段的字段。我正在尝试更新一个数字类型字段，但是会出现错误。

import csv, pypyodbc
conn=pypyodbc.connect('Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=c:/MDBTest/MyReducedMdb.mdb;')
cursor=conn.cursor()
LUT = {}
with open("C:\MDBTest\order.txt") as file:
    for line in file:
        (key, val) = line.split()
        LUT [key] = val
print (LUT)
cursor.execute("select * from Components")
rows = cursor.fetchall()
for row in rows:
    warehousecode=row[6].strip()
    if warehousecode in LUT:
        cursor.execute("""
                        UPDATE Components
                        SET Order=?
                        Where [Component Key]=?;""", (int(LUT[warehousecode]), str(row[0])))
        cursor.commit()

当我运行代码时，我会得到下面的错误。

{'406-007': '2', '406-012': '4', '406-005': '1', '406-015': '5', '406-010': '3'}
Traceback (most recent call last):
  File "D:\My Python\Scripts\IrricadDatabaseOrderUpdate.py", line 18, in <module>
    Where [Component Key]=?;""", (int(LUT[warehousecode]), str(row[0])))
  File "C:\Python34\lib\pypyodbc.py", line 1596, in execute
    check_success(self, ret)
  File "C:\Python34\lib\pypyodbc.py", line 986, in check_success
    ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
  File "C:\Python34\lib\pypyodbc.py", line 954, in ctrl_err
    raise ProgrammingError(state,err_text)
pypyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.')
>>>

请告诉我你认为问题出在哪里。谢谢。

Answer 1

您的问题是，ORDER是一个保留的SQL关键字。用引号包装它，以避免SQL驱动程序混淆。

"""
    UPDATE Components
    SET "Order"=?
    Where [Component Key]=?;"""

并给您的DB设计器一个使用SQL关键字作为列名的头等大事。