Python Formatting SQL WHERE clause

Question

I'm having this function that communicates via pymysql to an SQL database stored to my localhost. I know there are similar posts about formatting an SQL section especially this one but could anyone suggest a solution? Always getting TypeError: can't concat tuple to bytes. I suppose it's sth with the WHERE clause.

def likeMovement(pID):
 print("Give a rating for the movement with #id:%s" %pID)
 rate=input("Give from 0-5: ")
 userID=str(1)
 print(rate,type(rate))
 print(pID,type(pID))
 print(userID,type(userID))
 cursor=con.cursor()
 sqlquery='''UDPATE likesartmovement SET likesartmovement.rating=%s WHERE 
 likesartmovement.artisticID=? AND likesartmovement.userID=?''' % (rate,), 
 (pID,userID)
 cursor.execute(sqlquery)

TypeError: not all arguments converted during string formatting

Thanks in advance!

• abarnert: First, fix the formatting, and include the actual traceback, not just a description of the exception.
• abarnert: Include the exception as text, not as a screenshot.

Answer 1

The problem is that you're storing (pID,userID) as part of a tuple stored in sqlquery, instead of passing them as the arguments to execute:

sqlquery='''UDPATE likesartmovement SET likesartmovement.rating=%s WHERE 
 likesartmovement.artisticID=? AND likesartmovement.userID=?''' % (rate,)
cursor.execute(sqlquery, (pID,userID))

---

It may be clearer to see why these are different if you take a simpler example:

s = 'abc'
spam(s, 2)
s = 'abc', 2
spam(s)

Obviously those two don't do the same thing.

---

While we're at it:

• You have to spell UPDATE right.
• You usually want to use query parameters for SET clauses for exactly the same reasons you want to for WHERE clauses.
• You don't need to include the table name in single-table operations, and you're not allowed to include the table name in SET clauses in single-table updates.

So:

sqlquery='''UDPATE likesartmovement SET rating=? WHERE 
 artisticID=? AND userID=?'''
cursor.execute(sqlquery, (rating, pID, userID))

• Dafni V.: Thank you, but still having some issues: TypeError: not all arguments converted during string formatting
• abarnert: @DafniV. You have at least four errors in your code, and you only asked about the first one. I've edited the answer to include more of them, but I'm not going to go step by step debugging every new problem you run into. And meanwhile, the problem you're asking about here can't actually come from the code as described, so you must have done something else wrong, which I couldn't debug without seeing it.
• Dafni V.: Ok no problem, that was a good boost and help. Thank you abarnert.