python encoding problem with mysqldb

Question

I have troubles with encoding in python while using xlrd and mysqldb. I am reading an excel file which contains Turkish characters in it.

When I print the value like that print sheet.cell(rownum,19).value it writes İstanbul to console, which is correct.(Win7 Lucida ConsoleLine,encoding is `cp1254)

However, if I want to insert that value to database like

sql = "INSERT INTO city (name) VALUES('"+sheet.cell(rownum,19).value+"')"
cursor.execute (sql)
db.commit()

gives error as

Traceback (most recent call last):
File "excel_employer.py", line 112, in <module> cursor.execute (sql_deneme)
File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 157, in execute
    query = query.encode(charset)
UnicodeEncodeError: 'latin-1' codec can't encode character u'\u0130' in position
    41: ordinal not in range(256)

If I change the sql as

sql = "INSERT INTO city (name) VALUES('"+sheet.cell(rownum,19).value.encode('utf8')+"')"

the value is inserted without any error but it becomes Ä°stanbul

Could you give me any idea how can I put the value İstanbul to database as it is.

I don't know what xlrd is, but my guess is that the problem is on MySQL's side. If someone more knowledgeable hasn't answered you by tonight when I get home maybe I can look up some code I have and actually give an answer of some sort. — Keith Pinson
– Keith Pinson, Commented Aug 10, 2011 at 15:21
Sorry, I have not worked with this since last summer, when I had a nightmare with it. Looking more closely, I am convinced your problem is with MySQL, not with xlrd. Are you settings the use_unicode on your connection? I'm suspecting you are. That's good. But you also have to set MySQL's internal encoding to UTF-8 for it to actually work right. Sorry got to run right now but see if you can find anything on that. — Keith Pinson
– Keith Pinson, Commented Aug 10, 2011 at 22:15

linuxie · Accepted Answer · 2014-09-28 03:42:31Z

1

Just as @Kazark said, maybe the encoding of your connector of mysql is not set.

conn = MySQLdb.connect(
    host="localhost",
    user="root",
    passwd="root",
    port=3306,
    db="test1",
    init_command="set names utf8"
    )

Try this, when you init your python connector of mysql. But be sure the content been inserted is utf-8.

answered Sep 28, 2014 at 3:42

linuxie

1373 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

python encoding problem with mysqldb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related