CODEWITHSUNDEEP
pythonmysqlpython-2.7escapingmysql-python
Pyderman
Pyderman

Reputation: 16189

How to handle apostrophes in MySQL-Python?

A Python API is giving back u"'HOPPE'S No. 9'" as a value for a particular product attribute. I'm then looking to insert it into the DB, also using Python (python-mysqldb), with the following query:

INSERT INTO mytable (rating, Name) VALUES('5.0 (7)', 'HOPPE'S No. 9';

MySQL rejects this, and the suggested approach to handling a single quote in MySQL is to escape it first. This I need to do in Python, so I try:

In [5]: u"'HOPPE'S No. 9'".replace("'", "\'")
Out[5]: u"'HOPPE'S No. 9'"

When I incorporate this in my program, MySQL still rejects it. So I double-escape the apostrophe, and then an insert happens successfully. Thing is, it contains the escape character (so what gets written is 'HOPPE\'S No. 9').

If I need the second escape character, but when I add it gets left in, then how can I handle the escaping without having the escape character included in the string that gets inserted?

Edit: Based on theBjorn's suggestion, tried:

actualSQL = "INSERT INTO %s (%s) VALUES(%s);"

#cur.execute(queryString)
cur.execute(actualSQL,
            (configData["table"], sqlFieldMappingString, sqlFieldValuesString))

but it looks like I'm back to where I was when I was trying to escape using the single escape with .replace():

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''mytable' ('rating, Name, Image, mfg, price, URL') VALUES('\'5.0 (3)\', \'AR-1' at line 1

Upvotes: 1

Views: 5533

Answers (1)

thebjorn
thebjorn

Reputation: 27311

You should never construct sql that way. Use parameterized code instead:

cursor.execute(
    "insert into mytable (rating, name) values (%s, %s);",
    ("5.0 (7)", "HOPPE'S No. 9")
)

your latest problem is due to the misconception that this is string interpolation, which it isn't (the use of %s is confusing), thus:

actualSQL = "INSERT INTO %s (%s) VALUES(%s);"

will be wrong. It is possible to construct your sql string, but probably easier to do so in two steps so we don't trip over sql parameter markers looking like string interpolation markers. Assuming you have the values in a tuple named field_values:

params = ["%s"] * len(field_values)         # create a list with the correct number of parameter markers
sql = "insert into %s (%s) values (%s)" % ( # here we're using string interpolation, but not with the values
    configData["table"], 
    sqlFieldMappingString,
    ', '.join(params)
)

if you print sql it should look like my example above. Now you can execute it with:

cursor.execute(sql, field_values)

Upvotes: 4

Related Questions