Escape quotes in MySQL query on python

Question

I am trying solve problem with quotes in my sql queries. I know that many questions were asked on stackoverflow. But no one worked for me. May be because I am using other framework (pyMySQL) for sql connection. I tried parameterized mysql query:

    conn = pymysql.connect(host ='host', user='user', passwd='user', db='db', charset='utf8mb4')
    cur = conn.cursor()
    cur.execute("USE db")
    cur.execute("UPDATE table SET callback_data = %s, message = %s, photo = %s, location = %s, first_name_last_name = %s WHERE user_id=%s",
(callback_data, message, photo, location, first_name_last_name, user_id))
    cur.connection.commit()
    cur.close()
    conn.close()

But I always get error with %s (error message: expected, got %s). My python version is 3.6.1. How I can escape quotes in my sql query? Thanks.

"table" is a [reserved word](https://dev.mysql.com/doc/refman/5.7/en/keywords.html), so you'll have to quote it with backticks in order to use it as an identifier. Otherwise your call to execute and the query string seem fine. There are 6 placeholders and 6 arguments in the tuple. — Ilja Everilä, Jul 04 '17 at 09:21
Check if this ans helps you: https://stackoverflow.com/a/61042304/8939258 — Saurav Panda, Apr 05 '20 at 12:10

score 0 · Answer 1 · answered Sep 15 '17 at 07:59

First, as Ilja sayed you have to use backticks for mysql reserved words.

Then for text/varchar fields you can use json dumps:

import json

[...]

cur.execute("UPDATE `table` SET callback_data = %s, message = %s, photo = %s, location = %s, first_name_last_name = %s WHERE user_id=%s", % (
  json.dumps(callback_data),
  json.dumps(message),
  json.dumps(photo),
  json.dumps(location),
  json.dumps(first_name_last_name),
  json.dumps(user_id)
  )

Escape quotes in MySQL query on python

1 Answers1