pypyodbc update number type field

Question

I have used the script below to successfully update fields in an Access .mdb database that are text fields. I am trying to update a number type field but am getting errors.

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()

When I run the code I get the errors below.

{'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.')
>>>

Please let me know where you think the problem might be. Thanks.

score 1 · Accepted Answer · answered Jun 09 '16 at 23:10

1

Your problem is that ORDER is a reserved SQL keyword. Wrap it in quotes, to avoid your SQL driver getting confused.

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

And give your DB designer an up-head-smack for using SQL keywords as column names.

answered Jun 09 '16 at 23:10

Travis Griggs

21,522
19
91
167

Good answer (+1), but using square brackets would be more representative of Access SQL common practice and consistent with the quoting of the `[Component Key]` field in the same query. – Gord Thompson Jun 09 '16 at 23:52

pypyodbc update number type field

1 Answers1