Python equivalent of mysql_real_escape_string, for getting strings safely into MySQL?

Question

Is there a Python equivalent of PHP's mysql_real_escape_string?

I'm trying to insert some strings into a MySQL db direct from Python, and keep getting tripped up by quotes in the strings.

mysql_string = "INSERT INTO candidate (name, address) VALUES  " 
for k, v in v_dict.iteritems():
    mysql_string += " ('" + v_dict['name'] + "', '" + v_dict['address'] + "'), "
mysql_string += ";"
cursor.execute(mysql_string)

I've tried re.escape() but that escapes every non-alphanumeric character in the strings, which isn't what I need - I just need to escape single quotes in this instance (plus more generally anything else that might trip up MySQL).

Could do this manually I guess, but is there a smarter way to do it in Python?

Answer 1

If you are using mysql-python, just try

MySQLdb.escape_string(SQL)

Example

>>> import MySQLdb
>>> MySQLdb.escape_string("'")
"\\'"

Answer 2

cursor.executemany('INSERT INTO candidate (name, address) VALUES (%s, %s)',
                   [(v_dict['name'], v_dict['address'])] * len(v_dict))

should do what your code appears to attempt -- inserting the same identical values N times (you're looping on v_dict.iteritems() but completely ignoring the loop variables, and instad just reusing those specific two values from v_dict each and every time). Of course if you mean something completely different the second line will need to be changed accordingly, but the key idea anyway is to use placeholders, not string formatting, for such tasks.