Use Python to insert data into a MS Access Database

Question

I'm trying to insert values into a Microsoft Access Database using Python.

I'm able to insert values as follows:

df = pd.read_excel(xlsx, sheets[0])

for i in range(1, len(sheets)):
    data = []
    data = pd.read_excel(xlsx, sheets[i])
    df = df.append(data)

k = (df.iat[3,0])
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ= \\.....\Stat_tracker.accdb;')
cursor = conn.cursor()
cursor.execute("INSERT INTO ABCD (Serial) VALUES ('xxxx')")

cursor.execute('select * from ABCD')
for row in cursor.fetchall():
    print(row)

I want to use a loop to iterate over multiple records. Is there a better way?

When I replace the cursor.execute code with

cursor.execute("INSERT INTO ABCD (Serial) VALUES (%s)",(k,))

It throws back the following error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver]
Syntax error in query expression '%s'. (-3100) (SQLPrepare)")

Clearly, I'm missing something with the use of %s. Perhaps there's another placeholder?

It should be a simple fix, I'm just too unfamiliar with the syntax.

Help a newbie out?

Perhaps comma after k is an issue because there is only one parameter. — June7
@June7, OP actually has it correct as it is a tuple of one item. OP is using the wrong placeholder. For pyodbc, it should be qmark, ?. — Parfait
Please show data so we can see what you are attempting to insert. Right now only one scalar value appears to be attempted. — Parfait
If you really do need to use pandas then you might be interested in sqlalchemy-access to facilitate writing the DataFrame values to Access. — Gord Thompson

Chris Chris · Accepted Answer · 2020-01-26T03:33:47

Python's Database API Specification lists several ways of doing parametrization:

qmark: Question mark style, e.g. ...WHERE name=?
numeric: Numeric, positional style, e.g. ...WHERE name=:1
named: Named style, e.g. ...WHERE name=:name
format: ANSI C printf format codes, e.g. ...WHERE name=%s
pyformat: Python extended format codes, e.g. ...WHERE name=%(name)s

Most implementations only support one or maybe two. It looks like pyodbc uses qmark-style parameters, not format-style.

Try this instead:

cursor.execute("INSERT INTO ABCD (Serial) VALUES (?)", (k,))

Use Python to insert data into a MS Access Database

2 Answers