CODEWITHSUNDEEP
python-3.xms-accesspyodbc
JR87
JR87

Reputation: 95

Python PYODBC INSERT - Too Few Parameters

I've been over this multiple times, I've taken a copy of the database file for testing purposes and even renamed the field to match the Python script. So the field name I am using in Python matches the tables field name exactly.

import pyodbc

def main():
 tble="SomeTable"
 fld1="SomeField"
 val1="TestValue"
 sqlStrng = """INSERT INTO %s (%s) VALUES(%s);""" %(tble, fld1,val1)

 contStrng = (
  r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
  r'DBQ=Some\Path\File.accdb;'
  )

 cnnctn = pyodbc.connect(contStrng)
 cursr = cnnct.cursor()

 cursr.execute(sqlStrng)

 cnnctn.commit()
 cnnctn.close()

This isn't a spelling issue. I've made a testing copy of the Access file and created a table called SomeTable with a field called SomeField. I have the correct path, I've verified this by running a SELECT SQL script, which works without issue.

I've tried making fld1 a parameter and then sending that with the execute command, but then I'm informed I need 0 parameters. When I remove it, I'm informed I need 1.

I'm beginning to think perhaps it's this file? The same code works on another file I have. However I created a brand new file, and same results. I have to be missing something.

Upvotes: 3

Views: 871

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

If you print(sqlStrng) immediately after assigning it you'll see that its value is

INSERT INTO SomeTable (SomeField) VALUES(TestValue);

The Access Database Engine treats unrecognized names as parameters, so it wants to be given a parameter value for name TestValue. If you want to insert the string value 'TestValue' then you should use a pyodbc parameter placeholder (?) and pass val1 as a parameter in the .execute method, like so:

tble="SomeTable"
fld1="SomeField"
val1="TestValue"
sqlStrng = """INSERT INTO [%s] ([%s]) VALUES(?);""" %(tble, fld1)

contStrng = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
r'DBQ=Some\Path\File.accdb;'
)

cnnctn = pyodbc.connect(contStrng)
cursr = cnnct.cursor()

cursr.execute(sqlStrng, val1)

Notice that you wouldn't get an error if SomeField was a Number field and you used val1=123 because 123 would be treated as a numeric literal, not an unrecognized name (unquoted text value).

Upvotes: 2

Related Questions