Python PYODBC INSERT - Too Few Parameters

Question

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.

Gord Thompson · Accepted Answer · 2017-09-13T13:44:47.673

2

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

edited Sep 13 '17 at 13:44

answered Sep 12 '17 at 23:05

Gord Thompson

116,920
32
215
418

This worked, but could you tell me why in one instance it works fine, and in this one it doesn't? I'm new to writing Python code, not to connecting databases. Another insert function I wrote does the same thing on a different table and it works without issue. Obviously field and value names are different. Thank you for clarifying; obviously in my research I misunderstood how to apply parameters. – JR87 Sep 13 '17 at 12:37
The term for your original approach is "dynamic SQL", and dynamic SQL is prone to "SQL injection" vulnerabilities where the data *values* can cause problems with the SQL syntax. The most famous of those vulnerabilities are related to the security and integrity of the database (ref: "Little Bobby Tables"), but SQL injection issues can also simply cause programs to crash for certain data values. That's why it's important to use a "parametrized query" to pass the data values to the query, as shown in my answer. – Gord Thompson Sep 13 '17 at 13:41
I wanted to add to this, as I've begun working in Python more regularly now and ran into a similar issue. I was attempting to do a select query. I kept gettin a message that 4 parameters were required, but 3 were provided. I added an additional random one only to be told the query required only 3. The issue is that a table name cannot be a parameter: https://stackoverflow.com/questions/6669009/passing-table-name-as-a-parameter-in-pyodbc. Everything else can be parameters, but you'd need to write your sql and store in a string variable with the table variable passed into it. – JR87 Mar 09 '18 at 15:36

Python PYODBC INSERT - Too Few Parameters

1 Answers1