Python, convert 4-byte char to avoid MySQL error "Incorrect string value:"

Question

I need to convert (in Python) a 4-byte char into some other character. This is to insert it into my utf-8 mysql database without getting an error such as: "Incorrect string value: '\xF0\x9F\x94\x8E' for column 'line' at row 1"

Warning raised by inserting 4-byte unicode to mysql shows to do it this way:

>>> import re
>>> highpoints = re.compile(u'[\U00010000-\U0010ffff]')
>>> example = u'Some example text with a sleepy face: \U0001f62a'
>>> highpoints.sub(u'', example)
u'Some example text with a sleepy face: '

However, I get the same error as the user in the comment, "...bad character range.." This is apparently because my Python is a UCS-2 (not UCS-4) build. But then I am not clear on what to do instead?

Is it still a problem if you use the utf8mb4 charset in MySql? — Janne Karila
– Janne Karila, Commented Sep 28, 2012 at 8:57
Not sure. I unfortunately don't get to change the charset of the database. — user984003
– user984003, Commented Sep 28, 2012 at 9:02

Martijn Pieters · Accepted Answer · 2016-09-28 21:46:39Z

In a UCS-2 build, python uses 2 code units internally for each unicode character over the \U0000ffff code point. Regular expressions need to work with those, so you'd need to use the following regular expression to match these:

highpoints = re.compile(u'[\uD800-\uDBFF][\uDC00-\uDFFF]')

This regular expression matches any code point encoded with a UTF-16 surrogate pair (see UTF-16 Code points U+10000 to U+10FFFF.

To make this compatible across Python UCS-2 and UCS-4 versions, you could use a try:/except to use one or the other:

try:
    highpoints = re.compile(u'[\U00010000-\U0010ffff]')
except re.error:
    # UCS-2 build
    highpoints = re.compile(u'[\uD800-\uDBFF][\uDC00-\uDFFF]')

Demonstration on a UCS-2 python build:

>>> import re
>>> highpoints = re.compile(u'[\uD800-\uDBFF][\uDC00-\uDFFF]')
>>> example = u'Some example text with a sleepy face: \U0001f62a'
>>> highpoints.sub(u'', example)
u'Some example text with a sleepy face: '

Collectives™ on Stack Overflow

Python, convert 4-byte char to avoid MySQL error "Incorrect string value:"

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related