1

I am using 'insert...on duplicate key' as query for executemany, but it gives me error - "not all arguments converted during string formatting".

I have 5 columns in the table(col1, col2, col3, col4, col5).
col1 and col5 are the keys.
In case, a duplicate entry is found, I'd update col2, col3, col4. My query: 

q = insert into table values (%s, %s, %s, %s, %s) on duplicate key update col2 = %s, col3 = %s, col4 = %s

tuple = (val1, val2, val3, val4, val5, val2, val3, val4)
#tuple above is a list. tuple = [(val1, val2, val3, val4, val5, val2, val3, val4),  
                                 (val1, val2, val3, val4, val5, val2, val3, val4),
                                 (val1, val2, val3, val4, val5, val2, val3, val4)....]
cursor.executemany(q, tuple)

Upon printing the tuple, I get the following result(a list of tuples, as expected) :
[(1029, 4, 550.0, None, 199, 4, 550.0, None)....]

SOLUTION TO THE PROBLEM - q = insert into table (col1, col2, col3, col4, col5)values (%s, %s, %s, %s, %s) on duplicate key update col2 = values(col2), col3 = values(col3), col4 = values(col4)
No need to pass extra parameters, update values could be taken from the values passed on to the first part of the query itself.
I got that from here - MySQLdb returns not all arguments converted with "on duplicate key update"

Improve this question
4
  • 1
    check if all tuples have 8 items. It seems that some tuple has wrong number of elements and some of elements are not used in query - they are not converted during string formatting. BTW: you can get the same error with "%s" % (1,2) - string has only one %s but tuple has two values (1,2) Commented Sep 17, 2019 at 9:47
  • @furas - Checked it, Sir. Every tuple has got 8 values. Could it be possible that since first part of the query requires 5 values, and I am giving 8, it is giving error? Should I somehow break the tuple( which is supplying values) into two parts, just like the query... Commented Sep 17, 2019 at 10:03
  • query has 8 %s so every tuple should have 8 elements. I would add code which check len() for every tuple to test it. Or I would try to run query with every tuple sparatelly (and print tuple before use whith query) to catch which tuple makes problem. Commented Sep 17, 2019 at 10:19
  • @furas: Finally, got the solution. Your words - "BTW: you can get the same error with "%s" % (1,2)" helped me a lot. This SOLVED my problem - q = insert into table (col1, col2, col3, col4, col5)values (%s, %s, %s, %s, %s) on duplicate key update col2 = values(col2), col3 = values(col3), col4 = values(col4) Commented Sep 17, 2019 at 11:04
Related questions
1
MySQL + Python: Not all arguments converted during string formatting
1
TypeError: not all arguments converted during string formatting when inserting with parameters
5
MySQLdb returns not all arguments converted with "on duplicate key update"
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.