MySQL "Incorrect String Value" Error When Save Unicode String in Django

MySQL incorrect string value error when save unicode string in Django

I just figured out one method to avoid above errors.

Save to database

user.first_name = u'Rytis'.encode('unicode_escape')
user.last_name = u'Slatkevičius'.encode('unicode_escape')
user.save()
>>> SUCCEED

print user.last_name
>>> Slatkevi\u010dius
print user.last_name.decode('unicode_escape')
>>> Slatkevičius

Is this the only method to save strings like that into a MySQL table and decode it before rendering to templates for display?

Django unable to save unicode string in MySQL (OperationalError - 1366, Incorrect string value)

It is not the Python/Django related issue. Your MySQL table column doesn't supports the unicode format you are currently using.

Default character set used by MySQL is utf-8. If you want to change character set for any particular column, you may run the query as:

ALTER TABLE db.table MODIFY COLUMN my_column VARCHAR(255)
CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

where:

• db: your database
• table: name of the table
• my_column: name of column you want to modify

Incorrect string value error in MySql when saving Unicode

I think this works for you, try and let me know if it works:

import MySQLdb as mdb
con = mdb.connect('host', 'user', 'pass', 'dbname')
cur = con.cursor()
con.set_character_set('utf8')
cur.execute('SET NAMES utf8;')
cur.execute('SET CHARACTER SET utf8;')
cur.execute('SET character_set_connection=utf8;')

Python/Django/MySQL Incorrect string value error

I fixed it! The problem was that I wasn't parsing the email correctly with respect to the charsets. My fixed email parsing code comes from this post and this post:

#get the charset of an email
#courtesy http://ginstrom.com/scribbles/2007/11/19/parsing-multilingual-email-with-python/
def get_charset(message, default='ascii'):
    if message.get_content_charset():
        return message.get_content_charset()

    if message.get_charset():
        return message.get_charset()

    return default

#courtesy https://stackoverflow.com/questions/7166922/extracting-the-body-of-an-email-from-mbox-file-decoding-it-to-plain-text-regard
def get_body(message):
    body = None

    #Walk through the parts of the email to find the text body.
    if message.is_multipart():
        for part in message.walk():
            #If part is multipart, walk through the subparts.
            if part.is_multipart():
                for subpart in part.walk():
                    if subpart.get_content_type() == 'text/plain':
                        #Get the subpart payload (i.e., the message body).
                        charset = get_charset(subpart, get_charset(message))
                        body = unicode(subpart.get_payload(decode=True), charset)
            #Part isn't multipart so get the email body.
            elif part.get_content_type() == 'text/plain':
                charset = get_charset(subpart, get_charset(message))
                body = unicode(part.get_payload(decode=True), charset)
    #If this isn't a multi-part message then get the payload (i.e., the message body).
    elif message.get_content_type() == 'text/plain':
        charset = get_charset(subpart, get_charset(message))
        body = unicode(message.get_payload(decode=True), charset)

    return body

Thanks very much for the help!

Incorrect string value error in MySql when saving Unicode

I think this works for you, try and let me know if it works:

import MySQLdb as mdb
con = mdb.connect('host', 'user', 'pass', 'dbname')
cur = con.cursor()
con.set_character_set('utf8')
cur.execute('SET NAMES utf8;')
cur.execute('SET CHARACTER SET utf8;')
cur.execute('SET character_set_connection=utf8;')

How to convert an entire MySQL database characterset and collation to UTF-8?

Use the ALTER DATABASE and ALTER TABLE commands.

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Or if you're still on MySQL 5.5.2 or older which didn't support 4-byte UTF-8, use utf8 instead of utf8mb4:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

“Incorrect string value” when trying to insert String into MySQL via Python and Text file

The string you're trying to insert into db has an unusual character at its beginning. I just copied your string:

In [1]: a = '<'

In [2]: a
Out[2]: '\xef\xbb\xbf<'

You need to get rid of those characters. This is a good post explaining what these characters are.

---

Related Topics