unicode encode error

[Jason Wang]

Hi all, i have the following error:

File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 151, in execute
query = query % db.literal(args)
File "/var/lib/python-support/python2.5/MySQLdb/connections.py", line 247, in literal
return self.escape(o, self.encoders)
File "/var/lib/python-support/python2.5/MySQLdb/connections.py", line 180, in string_literal
return db.string_literal(obj)
UnicodeEncodeError: 'ascii' codec can't encode character u'\xe7' in position 4: ordinal not in range(128)

I looked online and found that apparently changing db connect string would work, so i tried the following:

db = MySQLdb.connect(user="root", ... , use_unicode=True)
and
db = MySQLdb.connect(user="root", ... , use_unicode=True, charset="utf8")

Both of them doesn't work and i still get the above error, can someone tell me what's wrong?

Thanks a lot!

[Andy Dustman]

You have a traceback and your connection call, but you don't have your query, so impossible to say. But I will guess you aren't using execute() correctly.

use_unicode only applies to result sets, and not query parameters, so it doesn't matter for this.

[Jason Wang]

Well, here is my query:
c.execute("""SELECT aid, name FROM authors WHERE name=%s""", (author, ))

and
author = u'Fran\xe7oise Tellier-Loumagne'

So if nothing else is wrong, perhaps i'm using %s incorrectly?

Thank you very much for your time!

[Andy Dustman]

Hmmm, that actually looks OK.

What happens if you leave off use_unicode=True, charset="utf8" from connect()?

Also, what platform and versions are you using? I can see you're using Python 2.5 and I'm guessing Debian or a derivative (like Ubuntu). But what version of MySQLdb, and how was it installed?

[Andy Dustman]

Also what version of MySQL for both client and server?

Also examine the value of db.character_set_name() after you connect and see if it is really utf8.

[Jason Wang]

Hi Andy,

Here are my info:

>>> db = MySQLdb.connect(user="root", db="prices", use_unicode=True, charset="utf8")
>>> db.character_set_name()
'utf8'
>>> MySQLdb.version
'1.2.2'

The development machine i have runs Mac OS X leopard (10.5), which have the latest mysql installed (5.1.31).

if leave off the two options, i have the following:

>>> db = MySQLdb.connect(user="root", db="prices")
>>> db.character_set_name()
'latin1'
>>>

Which gives me the same error.

Turning on unicode and off doesn't seem to make a difference.

Thanks a lot for any help on this matter!

Jason

[Andy Dustman]

I have not done any testing with MySQL-5.1, but it ought to work the same as 5.0 (and 4.1) with respect to character sets.

I can't replicate this:

>>> import MySQLdb
db=MySQLdb.connect(charset="utf8")
>>> db.character_set_name()
'utf8'
>>> c=db.cursor()
>>> c.execute("select %s", (u'\xe7',))
1L
>>> c.fetchall()
((u'\xe7',),)
>>> MySQLdb.version
'1.2.2'
>>>

[Jason Wang]

Well, I have tried just using that particular string as insert, it appears to not have a problem at all either.

After digging deeper, it appears that the string i'm trying to insert into the database came out of BeautifulSoup. For some reason, it has an alternative representation of a string called NavigableString which is a subclass of Unicode.

If i cast it to unicode via unicode(str), it works fine now.

Thank you very much for all your help!

Jason