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,

Answer 1

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