CODEWITHSUNDEEP
pythonsqlms-accesspyodbc
sunny0792
sunny0792

Reputation: 13

Use Python to insert data into a MS Access Database

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?

Upvotes: 1

Views: 3763

Answers (2)

Parfait
Parfait

Reputation: 107567

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)

Upvotes: 0

Chris
Chris

Reputation: 136880

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

Upvotes: 2

Related Questions