Python encoded mysql

Question

This post matches my question in MySQL in Python: UnicodeEncodeError: 'ascii' , this is just to clarify the situation.

I try to save a string in a MySQL database, but I get an error:

File .smart.py, line 51, in (number, text, smart, u)

UnicodeDecodeError: codec 'ascii' cannot decode byte 0xc2 at position 25: serial number not in range (128)

and the string is stored in m ['Text']

Lala * = # &% @ <> _:;?! - '"/ () ¥ ¡¿

Here is a snippet of code

risk = m['Text'] msg = risk.encode('utf8') text = db.escape_string(msg) sql = "INSERT INTO posts(nmbr, \ msg, tel, sts) \ VALUES ('%s', '%s', '%s', '%s')" % \ (number, text, 'smart', 'u') 

If I try to comment on the SQL query and set the print text , it prints Lala * = # &% @ <> _?! ;; - '"/ () ¥ ¡¿

The error only occurs when processing SQL.

MySQL encoding is set to utf8_unicode_ci. (or should I change that?)

Thanks.

Answer 1

add these parameters MySQLdb.connect(..., use_unicode=1,charset="utf8") .

create cursor

 cur = db.cursor() 

and then do this:

 risk = m['Text'] sql = """INSERT INTO posts(nmbr, msg, tel, sts) \ VALUES (%s, %s, %s, %s)""" values = (number, risk, 'smart', 'u') cur.execute(sql,values) #use comma to separate sql and values, this will ensure values are escaped/sanitized cur.commit() 

now you do not need these two lines:

 msg = risk.encode('utf8') text = db.escape_string(msg) 

Answer 2

It is unclear whether m['Text'] type StringType or UnicodeType . My bet is that it is a byte string ( StringType ). If so, add the line m['Text'] = m['Text'].decode('UTF-8') before your insert can work.