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 Pablo Reply Pablo Wednesday, May 23, 2012 9:50 AM

      Thank you, that was exactly was I looking for!

    • Avatar of Lorrin Reply Lorrin Wednesday, September 26, 2012 7:12 AM

      Thanks!

  • 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!

  • Avatar of CHIT Reply CHIT Saturday, September 1, 2012 8:00 PM

    Thanks. It helped me either, saved lots of time.

  • Avatar of ALEX Reply ALEX Sunday, December 16, 2012 5:23 PM

    Thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  • Avatar of katrotz Reply katrotz Thursday, January 17, 2013 2:24 PM

    You rock

  • Avatar of ittana Reply ittana Monday, January 21, 2013 12:58 AM

    Just what I needed after spending all day looking for a solution. Thanks for posting this.

  • Avatar of Stas Reply Stas Thursday, February 14, 2013 3:43 PM

    Thanks, you saved me a lot of time :-)

  • Avatar of Maxx Reply Maxx Wednesday, February 20, 2013 4:00 PM

    Wow. I was almost giving up on this. Thank you so much!!

  • Avatar of mdde Reply mdde Tuesday, March 19, 2013 4:24 AM

    jesus. christ. thank you.

  • Avatar of jack Reply jack Friday, April 12, 2013 5:48 AM

    Thank you very much , you are so great.

  • Avatar of Problem Reply Problem Sunday, May 5, 2013 11:52 PM

    Warning: Incorrect string value : 'xe5\.....' for column .... at row 1.

    what i need to do?

  • Avatar of GXG geo Reply GXG geo Friday, May 10, 2013 6:16 PM

    worked like a charm. Thank you for the post!

  • Avatar of maxime Reply maxime Tuesday, June 11, 2013 5:03 PM

    Awesome ! Thanks a lot man

  • Avatar of Tiberius Reply Tiberius Wednesday, June 12, 2013 7:30 PM

    Been bashing my brains out on how to get this to work using Python 2.7.5 and MySQLdb 1.2.4b5 with no luck. The database is set to use UTF8 encoding:

    CREATE DATABASE `reading_list` /*!40100 DEFAULT CHARACTER SET utf8 */
    ENGINE=MyISAM AUTO_INCREMENT=142 DEFAULT CHARSET=utf8

    And I've tried many variations to get this to work with no luck:

    # -*- coding: utf-8 -*-

    import MySQLdb as mdb
    import sys

    con = mdb.connect('host', 'user', 'passwd', 'db', use_unicode='True')
    with con:
    cur = con.cursor()
    cur.execute("SELECT * FROM book WHERE isbn10 != 'NULL'")

    rows = cur.fetchall()

    for row in rows:
    print row[1]

    Any thoughts on why the return is still garble?

  • Avatar of Leo Júnior Reply Leo Júnior Friday, June 21, 2013 3:40 PM

    Thank you fot that =)

  • Avatar of Tomislav Reply Tomislav Tuesday, September 3, 2013 3:04 PM

    Thank you.

  • Avatar of Agora Reply Agora Tuesday, September 3, 2013 4:52 PM

    Thank you man!

  • Avatar of SShah Reply SShah Saturday, November 2, 2013 6:24 PM

    Thank you. Solved my problem.

  • Avatar of peterchurch67 Reply peterchurch67 Wednesday, December 11, 2013 11:44 AM

    Thanks for your article! I had the same problem and wasted so much time, now it works:
    I just needed this line:
    db.set_character_set('utf8')
    and everything else worked!

  • Avatar of Thiago Ganzarolli Reply Thiago Ganzarolli Thursday, May 22, 2014 12:58 AM

    Lifesaver!

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.

                                  
 _   _  ___  ___  _____   ___   _ 
| | | |/ _ \/ __|/ _ \ \ / / | | |
| |_| | (_) \__ \ (_) \ V /| |_| |
 \__,_|\___/|___/\___/ \_/  \__,_|