Python, MySQLdb and UTF-8

Monday, December 17, 2007

So, after waisting way too much time with UTF-8 and MySQLdb, I've finally found a solutution, which seems to work with the newest version of MySQLdb. You maybe also know this problem with the following error message:

"UnicodeEncodeError:'latin-1' codec can't encode character ..."

This is because MySQLdb normally tries to encode everythin to latin-1. This can be fixed by executing the following commands right after you've etablished the connection:
db.set_character_set('utf8')
dbc.execute('SET NAMES utf8;')
dbc.execute('SET CHARACTER SET utf8;')
dbc.execute('SET character_set_connection=utf8;')
"db" is the result of MySQLdb.connect, and "dbc" is the result of db.cursor().

I hope this will help some guys out, not waisting as much time as I did for this issue.

Comments to this article

  • Avatar of Rafael Barbolo Lopes Reply Rafael Barbolo Lopes Monday, January 21, 2008 1:24 AM

    I've also wasted a long time trying to fix this!

    Thanks

  • Avatar of Alex Reply Alex Sunday, February 24, 2008 6:23 PM

    thank you for sharing!

  • Avatar of THLopes Reply THLopes Monday, July 7, 2008 4:23 PM

    Thanks very Much!

    This tip helped me a lot in my Graduation project. Please, visit my site to see the results soon, when I publish it.

    Best regards,

  • Avatar of Miriam Ruiz Reply Miriam Ruiz Monday, April 6, 2009 5:12 PM

    Thanks! Your recipe has worked perfectly for me :)

  • Avatar of Alex Lopez Reply Alex Lopez Friday, June 19, 2009 6:45 PM

    You're the fucking master!!!!

    Our project owes you a few beers

  • Avatar of wearetherock Reply wearetherock Monday, August 24, 2009 6:26 AM

    Thanks

  • Avatar of Uriel Katz Reply Uriel Katz Wednesday, December 16, 2009 4:14 PM

    you saved me a few hours even that i get payed by the hour :)

  • Avatar of Daniel Pérez R. Reply Daniel Pérez R. Tuesday, December 22, 2009 3:55 PM

    Thanks a lot. This was the only information that really help me.

  • Avatar of Thierry Graff Reply Thierry Graff Friday, January 29, 2010 9:28 AM

    Really thank you

  • Avatar of Bojan Jovanovic Reply Bojan Jovanovic Monday, May 24, 2010 11:44 AM

    Thank you!!!

    This helped me!

  • Avatar of mclaudt Reply mclaudt Thursday, May 27, 2010 9:41 PM

    Thanks a lot! It works now!

    So, let's sum all the magic:

    import MySQLdb
    db=MySQLdb.connect(user="guest",passwd="guest",db="dbname",use_unicode=True)
    db.set_character_set('utf8')
    c=db.cursor()
    c.execute('SET NAMES utf8;')
    c.execute('SET CHARACTER SET utf8;')
    c.execute('SET character_set_connection=utf8;')

  • Avatar of warachet Reply warachet Tuesday, July 6, 2010 1:46 PM

    Perfect! this recipe saves my life... Thanks.

  • Avatar of blackghost Reply blackghost Friday, September 3, 2010 4:40 PM

    Thank you!!!

    This helped me!

  • Avatar of Henry Reply Henry Thursday, September 9, 2010 1:42 PM

    Excellent, thanks for this!

  • Avatar of Vasiliy Reply Vasiliy Thursday, November 4, 2010 12:53 PM

    Thank you! It helped me a lot!

  • Avatar of Jason Reply Jason Friday, December 3, 2010 1:13 AM

    I am still getting an error:

    File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line 146, in execute
    query = query.encode(charset)
    UnicodeDecodeError: 'ascii' codec can't decode byte 0xe2 in position 134: ordinal not in range(128)

    Am I doing something else wrong? Sorry I am new to python.
    Thanks

    • Avatar of Jason Reply Jason Friday, December 3, 2010 1:29 AM

      I updated to version 1.2.3 of MySQLdb and it worked! Thanks

  • Avatar of Zenky Reply Zenky Monday, January 10, 2011 9:50 PM

    Thank you very much for the not waisted time!

  • Avatar of Adrián Reply Adrián Saturday, May 7, 2011 12:14 AM

    Thanks! I've also wasted a lot of time trying to fix the database throught mysql when the problem was my python script.

  • Avatar of Almirot Reply Almirot Saturday, May 7, 2011 12:15 AM

    Great job mate!

  • Avatar of Maxim Reply Maxim Tuesday, May 31, 2011 6:47 AM

    You saved me, dude! Live long and prosper :)

  • Avatar of mazeLe Reply mazeLe Monday, August 29, 2011 10:17 PM

    Yay, thanks man!

  • Avatar of Paolo Reply Paolo Tuesday, October 4, 2011 6:02 PM

    Thank you so so so so so so so much!! I have tried for long time but I'm finally seeing the light, thanks!

  • Avatar of Paolo Reply Paolo Tuesday, October 4, 2011 6:04 PM

    Thank you so so so so so so so so so so so so so much, I love you!

  • Avatar of volker Reply volker Thursday, November 24, 2011 12:32 PM

    Da die Kontakte auf .de enden:

    Vielen,vielen Dank.
    Es geht mir wie all den anderen hier. Ich habe jahrelang mit einer
    Krücke gelebt. Endlich kann ich meine quellen bereinigen

  • Avatar of nasser Reply nasser Sunday, December 25, 2011 7:18 AM

    Thanks, alot

  • Avatar of lenriq Reply lenriq Tuesday, January 3, 2012 12:44 PM

    A lot of useless methods, mate. It is enough to connect with charset="utf8" param.

    db = MySQLdb.connect(host="localhost", user = "root", passwd = "", db = "testdb", use_unicode=True, charset="utf8")

    • Avatar of Ben Scholzen 'DASPRiD' Reply Ben Scholzen 'DASPRiD' Wednesday, March 7, 2012 12:47 AM

      I'm not completly sure, but I think that the charset option was not available in 2007. But you are right, now this is surely the better solution.

  • Avatar of Joey Reply Joey Thursday, February 23, 2012 3:47 PM

    Thankyou! Very useful article, it did actually save me heaps of time!

  • Avatar of Toby Reply Toby Sunday, April 1, 2012 4:38 PM

    Thank you very much, this saved me a lot of time!

Leave a comment

Please note that your email address will not be shown, it is only used to fetch your avatar image from gravatar.com and for notifications. 

                                  
 _ __   ___  __ _ _   _ _____   _ 
| '_ \ / _ \/ _` | | | |_  / | | |
| | | |  __/ (_| | |_| |/ /| |_| |
|_| |_|\___|\__, |\__, /___|\__, |
            |___/ |___/     |___/