Each month I have to create a report from the same set of data. I just finished migrating all of that information into Access and updating my reporting code to read the data from the Access tables, which has been a lifesaver. However, at the end of the report, I need to update one of the tables with this month's "ulr" values. To play it safe, I want the code to never read in this month's ulr values, delete any that exist in the Access table while running, then add this month's ulrs to the table. That way, if I have to run the code more than once, it's always replacing this month's values with the most recent run. All of the values in the table have an "asof" field indicating which reporting month they're from, to make this easier.
I have no problem reading in from the table, and no problem writing new values to it. However, I can't seem to delete records from the table. I would like to delete all records with an "asof" value equal to the variable "reportdate" which is a pandas Timestamp for the report date. This month, it was pd.Timestamp(2021,4,30).
ProgrammingError Traceback (most recent call last)
<ipython-input-36-c6e3188cf05f> in <module>
6 cnxn = pyodbc.connect(connStr)
7 crsr = cnxn.cursor()
----> 8 crsr.execute(
9 r"DELETE FROM ulrs WHERE asof = {}".format(reportdate))
10 cnxn.commit()
ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '[asof]=2021-04-30 00:00:00'. (-3100) (SQLExecDirectW)")
I've tried variations on the delete statement, but each time I either get that error, or the code runs fine but nothing gets deleted from the table. Any insight would be brilliant, thank you.
#Delete all rows in ulrs Access table for the current month:
connStr = (
r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
r"DBQ=%s;" % ('W:\\Data for Reports\\Act vs Exp Data\\Program A v E\\AvE Databases\\ave_reference - Copy.accdb')
)
cnxn = pyodbc.connect(connStr)
crsr = cnxn.cursor()
crsr.execute(
r"DELETE FROM ulrs WHERE asof={}".format(reportdate))
cnxn.commit()
Consider parameterization which among other reasons better facilitates data types without any punctuation needs like quotes or numeral symbols. PyODBC supports parameters with qmarks.
Notice the use of the second argument in cursor.execute. Below assumes reportdate is a Python date/time variable.
crsr.execute("DELETE FROM ulrs WHERE asof = ?", reportdate)
cnxn.commit()