时间戳

首页 > 解决方案 > mysql 编码以允许表情符号

python - mysql 编码以允许表情符号

问题描述

在我的pipeline.py

self.conn = pymysql.connect(host='localhost', user='usr154', password=PASSWORD, db='db_v1',
                                    charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
self.cursor = self.conn.cursor()
# Table exists with all column types to "text"
my_id="a13cg"
title='hello world'
my_string = 'Top 10 Ist es ein Cityhotel? Ist es ein Schloss? Ist es ein Inselresort? '
self.cursor.execute("""insert into results values (%s,%s,%s)""",(my_id,title,my_string))


Traceback (most recent call last):
  File "/home/timmy/.local/lib/python3.8/site-packages/twisted/internet/defer.py", line 654, in _runCallbacks
    current.result = callback(current.result, *args, **kw)
  File "/home/timmy/.local/lib/python3.8/site-packages/scrapy/utils/defer.py", line 154, in f
    return deferred_from_coro(coro_f(*coro_args, **coro_kwargs))
  File "/home/timmy/t_roman/t_roman/pipelines.py", line 44, in process_item
    self.cursor.execute("""insert into results values (%s,%s,%s)""",
  File "/home/timmy/.local/lib/python3.8/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/timmy/.local/lib/python3.8/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/timmy/.local/lib/python3.8/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/timmy/.local/lib/python3.8/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/home/timmy/.local/lib/python3.8/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/home/timmy/.local/lib/python3.8/site-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/home/timmy/.local/lib/python3.8/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/timmy/.local/lib/python3.8/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1366, "Incorrect string value: '\\xF0\\x9F\\x94\\x8D' for column 'htmlSnippet' at row 1")

我尝试对值进行编码，但效果不佳

my_string = my_string.encode()

您好，我有以下管道填充了（最终）在烧瓶应用程序中使用的 MySQL，如何正确存储这些值，以便稍后显示它们。

标签： pythonmysqlpymysql

解决方案

你不需要转换为字节（我假设这是scrapy MySQL管道），因为表已经创建，你可以像这样使用它。

self.cursor.execute("""ALTER TABLE results CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin""")

您还可以将列类型从更改text为NVARCHAR(255)

二进制排序规则与 _bin 排序规则的比较

推荐阅读