Python escape special characters in INSERT INTO on mysql

Question

I want read some record from one mysql DB and write to another DB. I am doing like this:

cnx_1 = mysql.connector.connect(config_1)
cnx_2 = mysql.connector.connect(config_2)
cursor_1 = cnx_1.cursor()
cursor_2 = cnx_2.cursor()
query = "select * from...."
cursor_1.execute(query)
for res in cursor_1:
    id,msg=res
    insert_query="insert into tbl1 (id,msg) values (%d,'%s')"%(id,msg)
    cursor_2.execute(insert_query)

This code works fine in most cases,

Problem: but if the 'msg' field contains special character, i have problem.

first i replaced some special char with equivalent one:

msg=msg.replace('\'',"\\'")
msg=msg.replace('\r',"\\r")
msg=msg.replace('\n',"\\n")

and then i found a better solution from here:

msg=str(MySQLdb.escape_string(msg))

but both was not fully successful because msg can be whatever. for example for 2nd solution i got this error:

UnicodeEncodeError: 'ascii' codec can't encode character u'\xc9' in position 683: ordinal not in range(128)

Try this instead: `cursor2.execute("insert into tbl1 (id, msg) values (%d, %s)", (id, msg))`. This will have the connector bind the arguments itself and it should do so correctly. — cdhowie, Jul 15 '15 at 13:56
Is this of help : http://stackoverflow.com/questions/1342000/how-to-make-the-python-interpreter-correctly-handle-non-ascii-characters-in-stri — PaulF, Jul 15 '15 at 13:58
@cdhowie i will try it but i guess it could not help because i have more than 25 field and cursor.execute has limit in argument count. — Giac, Jul 15 '15 at 14:07
@PaulF i need whole message and i can not remove non ascii characters. — Giac, Jul 15 '15 at 14:10
escape_string(s) From MySQLdb documentation : _"Use connection.escape_string(s), if you use it at all. _mysql.escape_string(s) cannot handle character sets. You are probably better off using connection.escape(o) instead, since it will escape entire sequences as well as strings."_ : http://mysql-python.sourceforge.net/MySQLdb-1.2.2/ — PaulF, Jul 15 '15 at 14:21

score 1 · Answer 1 · edited May 23 '17 at 10:27

After a couple of weeks i returned back to solve this problem and i found it. actually 'MySQLdb.escape_string' is working perfectly and the 'UnicodeEncodeError' problem was due to concatenating unicode and str.

Based on this, i changed the 'defaultencoding' from 'ascii' to 'utf8':

sys.setdefaultencoding('utf8')
...
id,msg=res    # Type of msg is unicode
insert_query=U'insert into tbl1 (id,msg) values (%d,\'%s\')'%(id,msg)
cursor_2.execute(insert_query)

Python escape special characters in INSERT INTO on mysql

1 Answers1