Not all parameters were used in the SQL statement when executing executemany()

Question

I'm using the Python3 version of the mysql-connector and I try to use the executemany function.

My code looks like this:

database = mysql.connector.connect(
        host='localhost',
        user='user',
        password='test',
        database='test_database'
    )
cursor = database.cursor()
categories = ['Test1', 'Test 2']
stmt = "INSERT INTO categories (name) VALUES (%s)"
cursor.executemany(stmt, categories)

My table has just one column and this is a string column called name.

I tried to use the entries in the list as tuple, I changed the insert statement to this:

stmt = "INSERT INTO categories (name) VALUES ('%s')"

So I basically added single quotes in the %s placeholder. I also tried to replace the %s with ? but that also didn't work.

Due to the Python3 formatting conventions the single execute function is working when I use this:

stmt = "INSERT INTO categories (name) VALUES ('{}')".format('Test1')

Unfortunately the executemany function needs a second argument so I can't use the .format function.

This is my working example with the normal execute function:

database = mysql.connector.connect(
            host='localhost',
            user='user',
            password='test',
            database='test_database'
        )
cursor = database.cursor()
categories = ['Test1', 'Test2']
for category in categories:
    cursor.execute("INSERT INTO categories (name) VALUES ('{}')".format(category))

database.commit()

Edit:

I've read the official documentation. I copy and pasted the code and changed it to my parameters but it still doesn't work.

categories = [('Test1'),('Test2'),]
stmt = "INSERT INTO categories (name) VALUES (%s)"
cursor.executemany(stmt, categories)

score 0 · Answer 1 · answered Feb 10 '20 at 14:14

0

Just to rule out the basics:

I see that the for loop you pasted in the question ends with a database.commit() statement, but theexecutemany block doesn't. Did you omit this in your code or just in the question? (sorry I am putting this as an answer..can't add comments yet)

answered Feb 10 '20 at 14:14

0buz

3,443
2
8
29

Hey the code didn't even reach the commit because the exception fired beforehand but I finally found the answer – Neal Mc Beal Feb 10 '20 at 14:15

score 0 · Answer 2 · answered Feb 10 '20 at 14:18

Regarding the solution of this stackoverflow question I found a solution.

The statement was correct but my data list was wrong. If you just provide one value you need a comma inside the tuple.

This is the final solution:

categories = [('Test1',),('Test2',)]
stmt = "INSERT INTO categories (name) VALUES (%s)"
cursor.executemany(stmt, categories)

This is also documented in the MySQL documentation.

Not all parameters were used in the SQL statement when executing executemany()

2 Answers2