Emoticons from iPhone to Python/Django

Question

I'm trying to save comments from an iPhone app that may and nowadays most likely will include emoticons. No matter what I do, I can't save the emoticons to the MySQL database ... Constant Unicode errors.

Python 2.6.5
Django 1.2.1
MySQL database (set to utf8 character set for tables and rows)
Saving the data to a VARCHAR(255) field

The error I keep receiving is:

Incorrect string value: '\xF0\x9F\x97\xBC \xF0...' for column 'body' at row 1

The string I'm passing into the database is:

test_txt = u"Emoji - \U0001f5fc \U0001f60c \U0001f47b ...".encode('utf-8')

Update: Here's the model I'm using:

class ItemComment(db.Model):
  item = db.ForeignKey(Item)
  user = db.ForeignKey(Profile)
  body = db.CharField(max_length=255, blank=True, null=True)

  active = db.BooleanField(default=True)
  date_added = db.DateTimeField(auto_now_add=True)

  def __unicode__(self):
    return "%s" % (self.item)

The odd thing is, if I try and pass this to a field that I've created in MySQL and not Django models.py it works fine. But as soon as I register the field in Django models it dies. Is there another way to store these perhaps?

Any ideas would be amazing.
I could not be more stuck on this ...

Update 2: Tracking it in Terminal using the following UPDATE statement (notice the U0001f5fc)

UPDATE 'table' SET 'body' = '', WHERE 'table'.'id' = 1 ; args=(u'\U0001f5fc')

Using as hardcore as I can get to pass the value:

force_unicode(smart_str(value), encoding='utf-8', strings_only=False, errors='ignore')

But the error still throws:

_mysql_exceptions.Warning: Incorrect string value: '\xF0\x9F\x97\xBC' for column 'body' at row 1

Totally lost!!!

Cheers,

@bossylobster, cheers, just added the model to the copy above. — Danny Moss, May 01 '12 at 01:28
Check out http://stackoverflow.com/questions/2108824/mysql-incorrect-string-value-error-when-save-unicode-string-in-django, since you said it works fine in MySQL I doubt this will hope, but it's worth a try. — bossylobster, May 01 '12 at 01:37
@bossylobster, no idea why as it seems to help the other folks. But I receive the exact same error. From the look of things, the DB is set up as utf8 all the way. Thanks though ... — Danny Moss, May 01 '12 at 02:16
Did you create the tables after setting the db to utf8? I believe with postgres at least, encoding changes only apply to new tables — cberner, May 01 '12 at 03:33
@cberner, no go. Just DROPped some tables, CREATEd them again, making sure "DEFAULT CHARSET=utf8" and no dice. I'm gonna update everything to the latest and see how we go. Thanks guys. — Danny Moss, May 01 '12 at 04:22

score 5 · Answer 1 · edited May 23 '17 at 10:29

5

Change charset utf8mb4 for MySQL server (version 5.5.3 later)

In my.ini (my.cnf)

[mysqld]
character_set_server = utf8mb4
collation-server = utf8mb4_unicode_ci

or SQL query

SET NAMES 'utf8mb4';

see also http://dev.mysql.com/doc/refman/5.5/en/charset-connection.html

or deletes the character to do it.

python

import re
# emoji_text is unicode
no_emoji_text = re.sub('[\xF0-\xF7][\x80-\xBF][\x80-\xBF][\x80-\xBF]', '', str(emoji_text))

Thank you.

See also MySQL throws Incorrect string value error

edited May 23 '17 at 10:29

Community

1
1

answered Sep 04 '12 at 05:10

bindi

121
2
5

This is also the solution I used. If that might help I wrote an article where I tried to explain the problems I had, which were similar : http://pjambet.github.io/blog/emojis-and-mysql/ – pjam Aug 28 '13 at 19:30

score 0 · Answer 2 · answered Nov 06 '17 at 09:40

I use Django 1.11 and following setting.py and the create sql can store emoji well,

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db_name',
        'USER': 'db_user',
        'PASSWORD': 'your_password',
        'OPTIONS': {'charset': 'utf8mb4'},   # note here!!!
    }
}

The sql come from this answer,

CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Emoticons from iPhone to Python/Django

2 Answers2