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.
asked Aug 10, 2011 at 13:39
brsbilgic
11.9k16 gold badges66 silver badges100 bronze badges
1 Answer 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.
Sign up to request clarification or add additional context in comments.
Comments
lang-py
xlrdis, 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.use_unicodeon 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.