CODEWITHSUNDEEP
pythonpython-2.7ms-accesspyodbc
Peter Cunningham
Peter Cunningham

Reputation: 25

Syntax error in INSERT statement with Access and pyodbc

I'm having a hard time getting an SQL insert into an Access database using pyodbc via Python. Each attempt throws an "invalid syntax error".

 import pyodbc

 ney = 'data1'
 soy = 'data2'

 MDBA = 'C:/db/dbBase.mdb'; DRVA = '{Microsoft Access Driver (*.mdb)}'; PWDA = 'pw'

 # connect to db
 cona = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRVA,MDBA,PWDA))
 cura = cona.cursor()
 SQL = ("""insert into [db1](col1, col2) values ('ney', 'soy')""") 
 #SQL = ("insert into db1(col1, col2values (?, ?)", ney, soy)

 cura.execute(SQL)
 cona.commit()

I was able to do a SELECT statement, so what would be the correct syntax for an INSERT?

Upvotes: 1

Views: 1337

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

I just copied-and-pasted your code into IDLE and it did not throw an error for me. However, it also inserted the literal values 'ney' and 'soy' into the table. In order to get the values of those variables you need to use a parameterized query, like this:

sql = "insert into [db1] (col1, col2) values (?, ?)"
params = (ney, soy)  # tuple containing parameter values
cura.execute(sql, params)

Upvotes: 1

Related Questions