python:mysql error, "Incorrect string value: '\\xF0\\x9F\\x87\\xBA\\xF0\\x9F...'

Question

python program ,insert data in mysql table:

is_exist_table_sql = "SHOW TABLES LIKE 'fb_public_figure_posts'"
    if cur.execute(is_exist_table_sql) == 0:
        create_sql = """CREATE TABLE fb_public_figure_posts(Post_ID varchar(32), Permalink varchar(128), Create_time varchar(32), Updated_time varchar(32), Author varchar(32),
        Author_ID bigint, Message text, Link varchar(1024), Likes int, Comments int, pf_ID bigint, foreign key(pf_ID) references fb_public_figure_info(ID))"""
        cur.execute(create_sql)

db_data = posts
if type == "public_figure_posts":
    for item in db_data:
        if "'" in item["message"]:
            item["message"] = str(item["message"]).replace("'","\\\'")
        elif '"' in item["message"]:
            item["message"] = str(item["message"]).replace('"','\\\"')
        is_exist_id_sql = "select * from fb_public_figure_posts where Post_ID = '" + item['id'] + "'"
        if cur.execute(is_exist_id_sql) == 0:
            insert_sql = "INSERT INTO fb_public_figure_posts VALUES ('{0}','{1}','{2}','{3}','{4}',{5},'{6}','{7}',{8},{9},{10})".format(item['id'],item['permalink_url'],item['created_time'],item['updated_time'],item['from']['name'],item['from']['id'],item['message'],item['link'],item['likes']['summary']['total_count'],item['comments']['summary']['total_count'],public_figure_id)
            print(insert_sql)
            cur.execute(insert_sql)

when running , it errors:

pymysql.err.InternalError: (1366, "Incorrect string value: '\\xF0\\x9F\\x87\\xBA\\xF0\\x9F...' for column 'Message' at row 1")

it point that the sentence error:

 Traceback (most recent call last):
  File "C:/Python/PyCharmProject/FaceBookCrawl/FBCrawl.py", line 286, in <module>
    publicfigure_download.public_figure_posts_storage(public_figure_name)
INSERT INTO fb_public_figure_posts VALUES ('153080620724_10158392447835725','https://www.facebook.com/DonaldTrump/posts/10158392447835725:0','2017-01-01T04:59:07+0000','2017-01-23T19:52:49+0000','Donald J. Trump',153080620724,'‪TO ALL AMERICANS-‬
  File "C:\Python\PyCharmProject\FaceBookCrawl\publicfigure_download.py", line 103, in public_figure_posts_storage
‪#HappyNewYear & many blessings to you all! Looking forward to a wonderful & prosperous 2017 as we work together to #MAGA‬','https://www.facebook.com/DonaldTrump/photos/a.488852220724.393301.153080620724/10158392447835725/?type=3',158710,11045,153080620724)
    mysql_manage().public_figure_db_manage(type, posts, public_figure_id, public_figure_name)
  File "C:\Python\PyCharmProject\FaceBookCrawl\database_manage.py", line 47, in public_figure_db_manage
    cur.execute(insert_sql)
  File "C:\Python\Python36\lib\site-packages\pymysql\cursors.py", line 166, in execute
    result = self._query(query)
  File "C:\Python\Python36\lib\site-packages\pymysql\cursors.py", line 322, in _query
    conn.query(q)
  File "C:\Python\Python36\lib\site-packages\pymysql\connections.py", line 835, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Python\Python36\lib\site-packages\pymysql\connections.py", line 1019, in _read_query_result
    result.read()
  File "C:\Python\Python36\lib\site-packages\pymysql\connections.py", line 1302, in read
    first_packet = self.connection._read_packet()
  File "C:\Python\Python36\lib\site-packages\pymysql\connections.py", line 981, in _read_packet
    packet.check_error()
  File "C:\Python\Python36\lib\site-packages\pymysql\connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "C:\Python\Python36\lib\site-packages\pymysql\err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1366, "Incorrect string value: '\\xF0\\x9F\\x87\\xBA\\xF0\\x9F...' for column 'Message' at row 1")

Process finished with exit code 1

in conclusion, it shows:

pymysql.err.InternalError: (1366, "Incorrect string value: '\\xF0\\x9F\\x87\\xBA\\xF0\\x9F...' for column 'Message' at row 1")

could you please help me for that

Why don't you use `django` or `SQL alchemy` for ORM? They will automatically take care of escaping string so you can save them directly in the DB. — Vikash Singh, Jan 24 '17 at 02:22
I will use django or SQL alchemy, but now, could you please help me — bin, Jan 24 '17 at 02:40
remove the `.replace("'","\\\'")` it should work without that — Vikash Singh, Jan 24 '17 at 02:43
but there are many " ' " and ' " ' in the string That I want to insert into mysql table, if I do not deal with them , it also errors — bin, Jan 24 '17 at 04:41
you can use simplejson.dumps and loads. it will work for you. — Manoj Jadhav, Dec 26 '17 at 12:22

score 0 · Answer 1 · answered Mar 28 '18 at 09:58

You actually have two problems here. The first one is that you're trying to insert a string containing characters that are not supported by your database/table/field encoding - the catch is that MySQL's "utf-8" encoding is NOT really utf8 compliant (surprise, surprise...). The linked SO question gives full explanations, you can also check this for a migration procedure.

Your second issue - which AFAICT isn't responsible for the problem you describe but will cause quite a few other problems anyway - is that you're not using your db connector properly. This makes your code uselessly complicated and quite brittle - proper escaping is a pain as you might have noticed, so better to let the db connector do the job -, but also opens your app to SQL injection attacks. The cure here is simple: use prepared statements. This is actually much simpler than not using them and will kill two birds with one stone.

python:mysql error, "Incorrect string value: '\\xF0\\x9F\\x87\\xBA\\xF0\\x9F...'

1 Answers1