CODEWITHSUNDEEP
pythonmysql
DKean
DKean

Reputation: 2099

how to save utf-8 characters from python to mySQL

I use python on a server to communicate with mySQL. If a string with non-ascii characters is given to python to convey into a mySQL table field I get this error from the server.

UnicodeEncodeError: 'ascii' codec can't encode characters in position 251-256: ordinal not in range(128)

How can I pass through utf-8 data. I do have the comment:

"# -*- coding: utf-8 -*-"

...included in the python main code page, as well as all the code pages.

Strangely enough I am able to fetch data from mySQL which contains UTF-8 characters and it transfers well down to JavaScript.

The line of code which attempts to transfer the data is as follows:

sql = '''INSERT INTO clientMail (clientID,coID,MessageDate,TypeSent,Comments,FName)
        VALUES(%s, %s, '%s', '%s', '%s', '%s') ''' % (clientID,companyID,currentDate,TypeSent,emailMessage,company_Name)
print "===>>>>>>>>>>>>>",sql

The UTF-8 characters occur in the Comments field

Any help would be appreciated...

Upvotes: 0

Views: 2123

Answers (3)

Daniel Roseman
Daniel Roseman

Reputation: 599620

A very quick fix should be to ensure that your string is Unicode to start with:

sql = u"INSERT..."

ie prefix the string with a u.

However, you should not be using string formatting to interpolate values into SQL statements. You should rely on the MySQL adapter to do that for you, to ensure that you are protected against SQL injection:

sql = u"INSERT... VALUES (%s, %s..)"
cursor.execute(sql, (clientID, companyID...,))

Upvotes: 5

DKean
DKean

Reputation: 2099

I would like to say that someone here did help, but I was not able to get it going, no matter how hard I tried... Nevertheless, thank you all for trying. I learned something from all anyway.

The way to get this admittedly difficult problem solved is as follows:
The connection parameters must include the following items 

... charset="utf8", use_unicode=True"

Without these nothing you do will work, as I have concluded. But I am a novice, so don't take me too seriously.

Nevertheless, thank you all so much for pitching in.... You guys rock.

Dennis

Upvotes: 1

Thomas Orozco
Thomas Orozco

Reputation: 55207

Your data ought to be Unicode and string formatting (%s) is forcing it to string type using default encoding (ASCII).

Use .encode('utf-8') on your Unicode strings and you'll be all set.

Upvotes: -1

Related Questions