Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

My statement should match syntax, but I get error "Not all parameters were used in the SQL statement") mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

stmt = "UPDATE abbcards SET carPlate = '%(values)s' WHERE carId =%(id)s"
params = [tuple(cusSurname.values()), tuple(cusSurname.keys())]
cur.executemany(stmt, params)
What's wrong? I've defined the operation and seq_params variables. 
                Your stmt strings makes it appear you would be passing a dict, but you're passing a list of tuples? Shouldn't your params be a dict with keys 'values' and 'id'?
– The Pjot
                Dec 3, 2018 at 8:46
                Nope. Only lists and tuples. e.g. mysql.connector.errors.ProgrammingError: Parameters for query must be list or tuple. Checked it more then twice. Safiest way is to use tuples.
– Rostislav Aleev
                Dec 3, 2018 at 8:53
@ThePjot is somewhat right in their comment. You are using named placeholders in your query string (%(values)s and %(id)s), so you have to pass the parameters in the form of a dict.
BUT, you also call executemany which needs a list or tuple of parameters. Many times, executemany does nothing else than calling execute multiple times while iterating over params, passing an element of parameters in every iteration.
That means, each element of params must contain a full set of parameters for the query.
Looking at your query string you need two parameters to be substituted.
stmt = "UPDATE abbcards SET carPlate = '%(values)s' WHERE carId =%(id)s"
# Side note: the quote chars around %(values)s are quite certainly wrong here
From your code examples, one could assume that cusSurname is a dict.
Let's say it looks like this:
cusSurname = {'1': 'a1b1c1',
              '2': 'a2b2c2',
              '3': 'a3b3c3'}
What you are doing in the code above, is passing all the keys and all the values of cusSurname to executemany.
params = [('a1b1c1', 'a2b2c2', 'a3b3c3'), ('1', '2', '3')]
This would lead to a final query string of:
UPDATE abbcards SET carPlate = '\'a1b1c1\'' WHERE carId ='a2b2c2'
... with one element from the first parameter tuple not being substituted, which leads to mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement, before the second statement could be formed from the string and the params, which would look like:
UPDATE abbcards SET carPlate = '\'1\'' WHERE carId ='2'
... and raise the same exception.
What you'll want to do, is end up with a list/tuple of dicts consisting of individual items of your cusSurname:
stmt = "UPDATE abbcards SET carPlate = %(values)s WHERE carId =%(id)s"
params = [dict({'id': k, 'values': v}) for k,v in cusSurname.items()]
# [{'id': '1', 'values': 'a1b1c1'}, {'id': '2', 'values': 'a2b2c2'}, {'id': '3', 'values': 'a3b3c3'}]
cur.executemany(stmt, params)
Or switch to positional params:
stmt = "UPDATE abbcards SET carPlate = %s WHERE carId =%s"
params = [tuple(i) for i in cusSurname.items()]
# [('1', 'a1b1c1'), ('2', 'a2b2c2'), ('3', 'a3b3c3')]
cur.executemany(stmt, params)
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.