Escape string Python for MySQL

user_2203413 0
Viewed 148273

I use Python and MySQLdb to download web pages and store them into database. The problem I have is that I can't save complicated strings in the database because they are not properly escaped.

Is there a function in Python that I can use to escape a string for MySQL? I tried with ''' (triple simple quotes) and """, but it didn't work. I know that PHP has mysql_escape_string(), is something similar in Python?

Thanks.

escaping mysql python
7 Answers

One other way to work around this is using something like this when using mysqlclient in python.

suppose the data you want to enter is like this <ol><li><strong style="background-color: rgb(255, 255, 0);">Saurav\'s List</strong></li></ol>. It contains both double qoute and single quote.

You can use the following method to escape the quotes:

statement = """ Update chats set html='{}' """.format(html_string.replace("'","\\\'"))

Note: three \ characters are needed to escape the single quote which is there in unformatted python string.

user_89392580

install sqlescapy package:

pip install sqlescapy

then you can escape variables in you raw query

from sqlescapy import sqlescape

query = """
    SELECT * FROM "bar_table" WHERE id='%s'
""" % sqlescape(user_input)
user_91279280

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

cursor.execute("UPDATE skcript set author='{!a}',Count='{:d}' where url='{!s}'".format(authors),leng,url))

Python3 docs

user_55995870
Related