Thursday 12 May 2016

Using Python, MySQL and UTF-8

I have been spending way to long time figuring out how I can encode my data to utf8 when fetching records from MySQL database. All the time I was focusing on encoding the output, resulting in error like this one.
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe1 in position 1: 
ordinal not in range(128)
The solution I finally found is to set the character set when I prepare the MySQL connection
con.set_character_set('utf8')
Ending up in my code like this.
!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb as mdb

def getname(p):
    con = mdb.connect('hostname', 'username', ' password', 'databasname')
    con.set_character_set('utf8')
    sql = "SELECT cellname1 from tablename where cellname2='%s'"%(str(p))
    cur = con.cursor()
    cur.execute(sql)
    row = cur.fetchone()
    if not row:
        return 'n/a'
    else:
        return str(row[0])
    cur.close()
Now everything works like a charm.