Why am I getting this UnicodeEncodeError when I am inserting into the MySQL database?

Question

UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position 2: ordinal not in range(128)

I changed my database default to be utf-8, and not "latin"....but this error still occurs. why?

This is in my.cnf. Am I doing this wrong? I just want EVERYTHING TO BE UTF-8.

init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
default-character-set=utf8
character-set-server = utf8
collation-server = utf8_general_ci
default-character-set=utf8

Answer 1

If you get an exception from Python then it's nothing to do with MySQL -- the error happens before the expression is sent to MySQL. I would presume that the MySQLdb driver doesn't handle unicode.

If you are dealing with the raw MySQLdb interface this will be somewhat annoying (database wrappers like SQLAlchemy will handle this stuff for you), but you might want to create a function like this:

def exec_sql(conn_or_cursor, sql, *args, **kw):
    if hasattr(conn_or_cursor):
        cursor = conn_or_cursor.cursor()
    else:
        cursor = conn_or_cursor
    cursor.execute(_convert_utf8(sql), *(_convert_utf8(a) for a in args),
                   **dict((n, _convert_utf8(v)) for n, v in kw.iteritems()))
    return cursor

def _convert_utf8(value):
    if isinstance(value, unicode):
        return value.encode('utf8')
    else:
        return value

Answer 2

MySQLdb.connect(read_default_*) options won't set the character set from default-character-set. You will need to set this explicitly:

MySQLdb.connect(..., charset='utf8')

Or the equivalent setting in your django databases settings.