python %s format specifier failing for utf-8 values

Question

I have the following python code which pushes data to db:

cursor.execute("INSERT INTO "+ DATA_TABLE + """ (fk_id, title, streetaddress,json) 
                 values (%(fk_id)s, %(title)s, %(address)s, %(json)s ) """ ,(
                 result))

But I get the error:

  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 185, in unicode_literal
    return db.literal(u.encode(unicode_literal.charset))
UnicodeEncodeError: 'latin-1' codec can't encode characters in position 0-5: ordinal not in range(256)

Is it because the %s expects value to be a valid latin-1 string while dict is giving it a utf-8 string? Whats a clean workaround?

Note: The json is safely created using json.dumps. It has several characters of different languages.

Answer 1

you should try to convert all your string in unicode

cursor.execute(u"INSERT INTO "+ unicode(DATA_TABLE) + u""" (fk_id, title, streetaddress,json) 
                 values (%(fk_id)s, %(title)s, %(address)s, %(json)s ) """ ,(
                 result))

and pass charset="utf-8" to connect as specified in http://mysql-python.sourceforge.net/MySQLdb.html