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, Commented Jan 26, 2020 at 1:38
@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
– Parfait, Commented Jan 26, 2020 at 2:32
Please show data so we can see what you are attempting to insert. Right now only one scalar value appears to be attempted. — Parfait
– Parfait, Commented Jan 26, 2020 at 2:38
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
– Gord Thompson, Commented Jan 26, 2020 at 12:19

Chris · Accepted Answer · 2020-01-26 03:33:47Z

2

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,))

answered Jan 26, 2020 at 3:33

Chris

139k139 gold badges316 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parfait · Accepted Answer · 2020-01-26 04:37:27Z

Consider a pure SQL query as MS Access Jet/ACE engine can directly query Excel workbooks. Specifically, run an insert-select UNION query, avoiding the use of pandas, looping, or parameters.

Below assumes Serial column header exists in each Excel sheet and is the column you intended to loop on. Adjust accordingly.

sql = """INSERT INTO ABCD (Serial)
         SELECT t1.Serial 
         FROM [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\Excel\Workbook.xlsx].[Sheet1$] t1

         UNION ALL

         SELECT t2.Serial 
         FROM [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\Excel\Workbook.xlsx].[Sheet2$] t2

         UNION ALL

         SELECT t3.Serial 
         FROM [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\Excel\Workbook.xlsx].[Sheet3$] t3

         ...
      """

cur.execute(sql)

Collectives™ on Stack Overflow

Use Python to insert data into a MS Access Database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related