Escaping Quotes For Mysql In Python

Following the advice on this thread, I am storing my list as string type in MySQL database, but, I'm facing this error: _mysql_exceptions.ProgrammingError: (1064, 'You have an err

Solution 1:

Do not use string formatting to interpolate SQL values. Use SQL parameters:

SQL='INSERT INTO test_table (col1, col2, col3, col4) VALUES (%s, %s, %s, %s)'
cursor.execute(SQL, (var1, var2, var3, var4))

Here the %s are SQL parameters; the database then takes care of escaping the values (passed in as the 2nd argument to `cursor.execute) for you.

Exactly what syntax you need to use depends on your database adapter; some use %s, others use ? for the placeholders.

You can't otherwise use Python containers, like a list, for these parameters. You'd have to serialise that to a string format first; you could use JSON for that, but then you'd also have to remember to decode the JSON into a Python string again when you query the database. That's what the answers to the other question tried to convey.

For example, if var4 is the list, you could use:

cursor.execute(SQL, (var1, var2, var3, json.dumps(var4)))

Solution 2:

SQL = 'INSERT INTO test_table (col1, col2, col3, col4) VALUES ("{!a}", "{!a}", "{!a}", "{!a}")'.format(var1, var2, var3, str(var4))
cursor.execute(SQL)

{!a} applies ascii() and hence escapes non-ASCII characters like quotes and even emoticons.

Check out Python3 docs