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!

First, fix the formatting, and include the actual traceback, not just a description of the exception. — abarnert, Jun 11 '18 at 22:56

abarnert · Accepted Answer · 2018-06-11T23:04:45.030

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))

edited Jun 11 '18 at 23:04

answered Jun 11 '18 at 23:00

abarnert

354,177
51
601
671

Thank you, but still having some issues: TypeError: not all arguments converted during string formatting – Dafni V. Jun 11 '18 at 23:03
@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. – abarnert Jun 11 '18 at 23:06
Ok no problem, that was a good boost and help. Thank you abarnert. – Dafni V. Jun 11 '18 at 23:07

Python Formatting SQL WHERE clause

1 Answers1