mysql in python encoding

Question

This post is the same with my question in MySQL in Python: UnicodeEncodeError: 'ascii' this is just to clear things up.

I am trying to save a string to a MySQL database but I get an error:

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

UnicodeDecodeError: 'ascii' codec can't decode byte 0xc2 in position 25: ordinal not in range(128)

and the string is saved at m['Text']

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

Here is a snippet to the 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 out the SQL query and put print text it would print out Lala*=#&%@<>_?!:;-'"/()¥¡¿

The error is only encountered when the SQL is being processed.

MySQL encoding is set to utf8_unicode_ci. (or should i change this?)

Thanks.

i get the error stated above. I get an encoding error i think. — weyhei
– weyhei, Commented Feb 17, 2012 at 15:39
if i only do risk = m['Text'] msg = risk.encode('utf8') text = db.escape_string(msg) print text everything works fine. — weyhei
– weyhei, Commented Feb 17, 2012 at 15:40
are you using MySQLdb, if so what is your connection string?? — vikki
– vikki, Commented Feb 17, 2012 at 15:40
db = MySQLdb.connect("localhost", "root","password","application") — weyhei
– weyhei, Commented Feb 17, 2012 at 15:49

vikki · Accepted Answer · 2012-02-17 16:18:43Z

2

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

create a cursor

cur = db.cursor()

and then execute like so:

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 dont need these two lines:

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

edited Feb 17, 2012 at 16:18

answered Feb 17, 2012 at 16:00

vikki

2,8241 gold badge22 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

weyhei Over a year ago

mysql_exceptions.ProgrammingError: (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 \'\'\', \'\'Lala*=#&%@<>?!:;-\\\\\\\'\\\\\\"/()\xc2\xa5\xc2\xa1\xc2\xbf\'\', \'\'smart\'\', \'\'u\'\')\' at line 1')

vikki Over a year ago

@weyhei sorry try the sql again, i've edited it (i've removed the apostrophes surrounding the %s )

wberry · Accepted Answer · 2012-02-17 22:11:18Z

0

It is not clear whether your m['Text'] value is of type StringType or UnicodeType. My bet is that it is a byte-string (StringType). If that's true, then adding a line m['Text'] = m['Text'].decode('UTF-8') before your insert may work.

answered Feb 17, 2012 at 22:11

wberry

19.5k9 gold badges59 silver badges89 bronze badges

1 Comment

weyhei Over a year ago

I print out the array m and text is: 'Text': u'Lala*=#&%@<>_?!:;-\'"/()\xa5\xa1\xbf'

Collectives™ on Stack Overflow

mysql in python encoding

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related