0

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.

brsbilgic
  • 11,613
  • 16
  • 64
  • 94
  • I agree but is this totally about MySQL ? – brsbilgic Aug 10 '11 at 14:52
  • 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 Aug 10 '11 at 15:21
  • I really need any help urgently ! – brsbilgic Aug 10 '11 at 22:03
  • 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 Aug 10 '11 at 22:15

1 Answers1

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.

linuxie
  • 127
  • 3
  • 14