'SQL syntax Error while trying to add INT into column inside for loop

CURRENT

ERROR check the manual that corresponds to your MySQL server version for the right syntax to use near 'Long-Beach/112-E-Eldridge-St-90807/home/7571273' at line 1

new_url prints as www.redfin.com/CA/Long-Beach/112-E-Eldridge-St-90807/home/7571273 which does show up on my for_sale Table when i search it manually under the filter.

i keep getting a syntax error when i'm trying to add an estimate_price to the row in the current for loop. I've tried every variation i can think of but still no success. Please advise. The current value of estimate_price is NULL, the current setting for that field is INT. Thank You

mycursor.execute("SELECT web_url FROM for_sale WHERE estimate_price IS NULL")
for web in mycursor:
    print(web)
    url = str(web)
    new_url = url[10:-3]

    print("NEW URL",new_url)
    
    ## IN PUT IT IN THAT SAME LINE.
    mycursor.execute(f"UPDATE for_sale SET estimate_price = 120000 WHERE web_url = {new_url}")
mysqlmysql-pythonmysql-connector


Solution 1:[1]

As Barmar mentions above, you should use a query parameter instead of an f-string. This is the way to do it in Python:

mycursor.execute("UPDATE for_sale SET estimate_price = 120000 WHERE web_url = %s", [new_url])

The Python connector for MySQL will take care of quoting the value and escaping any special characters if necessary. Or if you created the cursor as the MySQLPreparedCursor subclass, it'll use proper query parameters with PREPARE and EXECUTE, so the value is never combined with the SQL syntax.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bill Karwin

Related Questions