Access insert into values

Question

I created a table in Access and the connection works, but my loop through my dataframe using iterrows() is throwing a syntax error.

I have tried removing spaces and altering the syntax.

for index, row in futures_table.iterrows():
    cursor.execute('''
                    INSERT INTO cme_oil_futures (MONTH,SETTLE,DATE,REPORT_TYPE)
                    VALUES (?,?,?,?)
                ''',(row['MONTH'],row['SETTLE'],row['DATE'],row['REPORT_TYPE']))
    conn.commit()

Expected result is to insert the values for each row in the "MONTH", "SETTLE", "DATE", and "REPORT_TYPE" columns from my dataframe.

The error I get is "

Exception has occurred: pyodbc.ProgrammingError ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. (-3502) (SQLExecDirectW)') File "E:\Python\DDWebsraping\cme_oil.py", line 83, in ''',(row['MONTH'],row['SETTLE'],row['DATE'],row['REPORT_TYPE']))

how do I connect to the access database, just futures_table.to_sql("table", engine, if_exists='append')? — Michael Wentz
@JohnGordon I was wondering that, I checked other questions and it seems to be correct. — Barmar

LetEpsilonBeLessThanZero LetEpsilonBeLessThanZero · Accepted Answer · 2019-02-14T18:44:05

Try the following code. I think you just needed brackets around the fields. MS Access is a strange and mysterious beast.

for index, row in futures_table.iterrows():
    cursor.execute('''
                    INSERT INTO cme_oil_futures ([MONTH],[SETTLE],[DATE],[REPORT_TYPE])
                    VALUES (?,?,?,?)
                ''',(row['MONTH'],row['SETTLE'],row['DATE'],row['REPORT_TYPE']))
    conn.commit()

Access insert into values

1 Answers