0

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...

Improve this question

3 Answers 3

Reset to default
5

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...,))
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I get this result using your advice... "c.execute(sql, clientID,companyID,currentDate,TypeSent,emailMessage,company_Name) TypeError: execute() takes at most 3 arguments (8 given)" but I only gave it 6 arguments and 6 fields defined...
Apologies, the values should be in a single tuple. Changed.
How does the MySQL adapter prevent SQL injections? I'm new to this!
1

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

Improve this answer

Comments

-1

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.

Improve this answer

6 Comments

Can you give me an example on how to implement this function? How do I get rid of the %s and replace it with .encode()...??? Sorry, I'm new to all this...
The encoding in which MySQL returns data to the client will depend on the current value of the character_set_results session variable: one usually has to specify the desired character set on connecting to MySQL, or using the SET NAMES command thereafter (I don't know whether the Python connector in use here handles this for you..?).
This is not quite true: u'%s' % 'a' returns unicode. -1 for not mentioning that he should not use string formatting to generate a SQL expression.
@Dkean : you use encode on your unicode data and you keep the %s. (All the unicode strings between the brackets after the %!)
@Thomas Here is what I get. The two fields are the only ones in need of utf-8: VALUES(%s, %s, '%s', '%s', '%s', '%s') ''' % (clientID,companyID,currentDate,TypeSent,emailMessage.encode('utf-8'),company_Name.encode('utf-8')) UnicodeDecodeError: 'ascii' codec can't decode byte 0xe2 in position 520: ordinal not in range(128)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.