Python 基础教程

Python 高级教程

Python 相关应用

Python 笔记

pymysql 报错 UnicodeEncodeError: 'latin-1' codec can't encode character '\u5e02' in position 116: ordinal not in range(256)

Python 笔记 Python 笔记


python 通过 pymysql 连接操作 mysql 库时,出现 UnicodeEncodeError: 'latin-1' codec can't encode character '\u5e02' in position 116: ordinal not in range(256) 报错信息,具体是什么原因,如何解决?

具体错误

具体错误信息如下:

Traceback (most recent call last):
  File "xxx.py", line 61, in <module>
    op_mysql()
  File "xxx.py", line 46, in op_mysql
    cur.execute(sql)
  File "/usr/lib/anaconda3/lib/python3.6/site-packages/pymysql/cursors.py", line 166, in execute
    result = self._query(query)
  File "/usr/lib/anaconda3/lib/python3.6/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/lib/anaconda3/lib/python3.6/site-packages/pymysql/connections.py", line 833, in query
    sql = sql.encode(self.encoding, 'surrogateescape')
UnicodeEncodeError: 'latin-1' codec can't encode character '\u5e02' in position 116: ordinal not in range(256)

原因

出现如上错误是因为数据库的编码和数据源(如上使用 pymysql 连接)的编码不一致导致,pymysql 的 mysql 连接默认采用 latin-1 编码,而我们常用 utf8 编码形式的业务数据库。

解决方案

将 pymysql 连接的 db 对象设置和数据库一致的编码形式即可,如下所示,指定 charset 参数即可:

import pymysql


def connect_db():
    return pymysql.connect(
        host='xxx.xx.xx.xxx',
        port=3306,
        user='xxxx',
        password='xxxxxx',
        database='xxxxx',
        charset='utf8'
    )