CODEWITHSUNDEEP
pythonms-accesspyodbc
Bert Geenen
Bert Geenen

Reputation: 5

Python: pyodbc, Microsoft Access: update dates in table, using # date qualifier

I am trying to update some records in a Microsoft Access database, using pyodbc in Python. I am struggling in updating date/time fields in Access, which would require a # date qualifier symbol in the sql statement.

A sample of a statement could be

sql = '''UPDATE [TABLE] SET[STARTDATE]=#2018-10-25# WHERE[KEYCOLUMN]=12345;'''

Using a parameter query, that would look like

sql = '''UPDATE [TABLE] SET[STARTDATE]=? WHERE[KEYCOLUMN]=?;'''
params = (#2018-10-25#, 12345)
cursor.execute(sql, params)

However, the execution crashes because of the required # symbols for the data qualifier. Also using the datetime library is not helpful, as the following parameters are also resulting in a failed execution:

params = (datetime.date(2018,10,25), 12345)
cursor.execute(sql, params)

What would be the correct syntax/workaround for updating DateTime fields, including blanking a DateTime field in an Access database?

Error codes when trying different attempts (with the actual table names and field names, instead of the sample names used in above example):

When passing the date as a string

SQL: UPDATE [Status Scorecard] [IN:Start Execute] = ? WHERE [IN:ID]=?;
Params: ('2015-09-14', '47977')
Error:(<class 'pyodbc.ProgrammingError'>, ProgrammingError('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. (-3503) (SQLExecDirectW)'), <traceback object at 0x07E24120>)

When passing the data with # date delimiter symbol:

sql = '''UPDATE [Status Scorecard] [IN:Start Execute] = ? WHERE [IN:ID]=?;'''
params = (#2015-09-14#, '47977')
print(params)
r = axSCORECARD.updateSQL(sql, params)

The code does not run, as the # symbol is treated as a comment, resulting in a code syntax error.

When passing the data as a string, using the # date delimiter inside the string:

SQL: UPDATE [Status Scorecard] [IN:Start Execute] = ? WHERE [IN:ID]=?;
Params: ('#2015-09-14#', '47977')
Error:(<class 'pyodbc.ProgrammingError'>, ProgrammingError('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. (-3503) (SQLExecDirectW)'), <traceback object at 0x07E24148>)

When passing the date as a datetime.date call (after having added 'import datatime' to the script):

SQL: UPDATE [Status Scorecard] [IN:Start Execute] = ? WHERE [IN:ID]=?;
Params: (datetime.date(2015, 9, 14), '47977')
Error:(<class 'pyodbc.ProgrammingError'>, ProgrammingError('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. (-3503) (SQLExecDirectW)'), <traceback object at 0x07E240D0>)

Upvotes: 0

Views: 1785

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

Hash mark (#) delimiters are only required for date literals in the SQL command text itself. They are not required (and are in fact invalid) in a parameterized query.

This works, verified with pyodbc 4.0.24:

sql = "UPDATE [Status Scorecard] SET [IN:Start Execute] = ? WHERE [IN:ID] = ?"
params = (datetime.date(2015, 9, 14), '47977')
crsr.execute(sql, params)
cnxn.commit()

result:

IN:ID  IN:Start Execute
-----  ----------------
47977  2015-09-14

To "blank out" the date, use None

params = (None, '47977')

Upvotes: 2

Related Questions