MySQL/python字符集问题

Question

我使用pymysql（0.9.3）通过python连接和清理MySQL服务器（8.0.19）表，并使用sqlalchemy将表写入数据库。但是继续产生错误的字符串值错误

InternalError: (pymysql.err.InternalError) (1366, "Incorrect string value: '\\xC5\\x81asis...' for column 'client_last_name' at row 726")

我曾尝试在数据库连接和引擎创建者中使用latin1、utf8和utf8mb4字符集，但错误相同

db = pymysql.connect(host='', 
                          user='', 
                          password='', 
                          db='',
                          charset="utf8mb4")

engine = create_engine('mysql+pymysql://user:pass@port/schema'+'?charset=utf8mb4')

con = engine.connect()

这是生成故障的代码行： full_email_stats.to_sql("email_stats", con, if_exists="replace")

回溯是：

  File "<ipython-input-33-8abe82be8923>", line 128, in <module>
    full_email_stats.to_sql("email_stats", con, if_exists="replace")

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pandas/core/generic.py", line 2663, in to_sql
    method=method,

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py", line 521, in to_sql
    method=method,

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py", line 1317, in to_sql
    table.insert(chunksize, method=method)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py", line 755, in insert
    exec_insert(conn, keys, chunk_iter)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py", line 669, in _execute_insert
    conn.execute(self.table.insert(), data)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 982, in execute
    return meth(self, multiparams, params)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement
    distilled_params,

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1250, in _execute_context
    e, statement, parameters, cursor, context

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1226, in _execute_context
    cursor, statement, parameters, context

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 148, in do_executemany
    rowcount = cursor.executemany(statement, parameters)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pymysql/cursors.py", line 197, in executemany
    self._get_db().encoding)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pymysql/cursors.py", line 229, in _do_execute_many
    rows += self.execute(sql + postfix)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)

  File "/Users/AdrianAlvarez/opt/anaconda3/lib/python3.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)

InternalError: (pymysql.err.InternalError) (1366, "Incorrect string value: '\\xC5\\x81asis...' for column 'client_last_name' at row 726")

在MySQL中，数据帧具有不同的字符集。有人知道如何解决这个问题吗

这可能有点牵强，但我在r中执行了相同的过程，并且对字符集没有任何问题。有人知道为什么吗