Import data to MS ACCESS via python error

Question

Through xlrd I am reading the value of few cells in an excel file. I would like to import these values into a MS Access database using the attached code by assigning the values to some variables. The code doesn't work if I use variables to feed the table, for example a=2, b=4.

Code:

import pyodbc

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path;') cursor = conn.cursor()

cursor.execute(''' INSERT INTO table (field1, field2) VALUES(a, b) ''') conn.commit()

I got the following error: Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2. (-3010) (SQLExecDirectW)')

The code works if I directly input the values instead of variables.

Could you please advise?

Thank you

nacho nacho · Accepted Answer · 2019-09-10T13:56:51

You need to use placeholders (?) and do something like this and pass the values as a parameter:

import pyodbc
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path;')
cursor = conn.cursor()
a=2 #some value
b=3 #other value
cursor.execute('INSERT INTO table (field1, field2) VALUES(?, ?)',(a,b))
conn.commit()

Import data to MS ACCESS via python error

1 Answers