CODEWITHSUNDEEP
mysqlpython-3.x
Giac
Giac

Reputation: 465

Python escape special characters in INSERT INTO on mysql

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)

Upvotes: 1

Views: 5592

Answers (1)

Giac
Giac

Reputation: 465

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)

Upvotes: 1

Related Questions