mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

Asked by Adam McNicol on 2013-01-10

Hi There,

I am just starting to investigate how to use python and mysql with blob data and I created a very simple script that creates a table and then opens an image file before saving the resulting data to the database.

I first tried it in SQLite3 (which is what I am used to) and it works fine:

import sqlite3
import mysql.connector as conn

db = con.connect("test3.db")
cursor = db.cursor()

sql = """create table blob_store
         (blob_id integer,
          blob_file text,
          blob_data blob,
          primary key(blob_id))"""

cursor.execute(sql)
db.commit()

image = open("adam.jpg","rb")
image_data = image.read()
image.close()

test_data = [["hello",image_data],["beth",image_data]]

sql = "insert into blob_store (blob_file, blob_data) values (?,?)"

for each in test_data:
    cursor.execute(sql,each)
    db.commit()

cursor.close()

I changed the above code to use mysql instead:

import mysql.connector as conn

db = conn.connect(host="localhost", user="root", passwd="", db="image_upload")
cursor = db.cursor()

sql = """create table blob_store
         (blob_id integer auto_increment,
          blob_file text,
          blob_data blob,
          primary key(blob_id))"""

cursor.execute(sql)
db.commit()

image = open("adam.jpg","rb")
image_data = image.read()
image.close()

test_data = [["hello",image_data],["beth",image_data]]

sql = "insert into blob_store (blob_file, blob_data) values (?,?)"

for each in test_data:
    cursor.execute(sql,each)
    db.commit()

cursor.close()

How when I run it this way I get the following error:

Traceback (most recent call last):
  File "/Users/me/Desktop/test.py", line 25, in <module>
    cursor.execute(sql,each)
  File "/Library/Frameworks/Python.framework/Versions/3.3/lib/python3.3/site-packages/mysql/connector/cursor.py", line 409, in execute
    "Not all parameters were used in the SQL statement")
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

However, if I change it to use tokens it works fine:

import mysql.connector as conn

db = conn.connect(host="localhost", user="root", passwd="", db="image_upload")
cursor = db.cursor()

sql = """create table blob_store
         (blob_id integer auto_increment,
          blob_file text,
          blob_data blob,
          primary key(blob_id))"""

cursor.execute(sql)
db.commit()

image = open("adam.jpg","rb")
image_data = image.read()
image.close()

test_data = [["hello",image_data],["beth",image_data]]

sql = "insert into blob_store (blob_file, blob_data) values (%s,%s)"

for each in test_data:
    print(each)
    cursor.execute(sql,each)
    db.commit()

cursor.close()

Is this the preferred method of adding data or am I doing something wrong with the questions marks?

Thanks for any assistance,

Kind Regards,

Adam.

Question information

Language:
English Edit question
Status:
Solved
For:
MySQL Connector/Python Edit question
Assignee:
No assignee Edit question
Solved by:
Geert JM Vanderkelen
Solved:
2013-01-10
Last query:
2013-01-10
Last reply:
2013-01-10

MySQL Connector/Python supports 'format' (%s) and 'pyformat' (%{name}s). It does not support qmark-style ('?').
(One can always create a cursor class which support qmark.)

See http://www.python.org/dev/peps/pep-0249/ for more information.

Adam McNicol (adammcnicol) said : #2

Thanks for that - been driving me crazy the past few days!

I will have a look into creating cursor classes,

Thanks again,

Adam.