CODEWITHSUNDEEP
pythonencodingmysql-python
brsbilgic
brsbilgic

Reputation: 11833

python encoding problem with mysqldb

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.

Upvotes: 0

Views: 3222

Answers (1)

linuxie
linuxie

Reputation: 127

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.

Upvotes: 1

Related Questions