mysql - SQLAlchemy 插入 MySQL DB UnicodeEncodeError 用于 cyrlic 数据
问题描述
我将 Python.SQLAlchemy 与 MySQL 数据库一起使用。以下所有代码均适用于数据中的拉丁符号,但不适用于西里尔语:
UnicodeEncodeError:“charmap”编解码器无法对位置 0-17 中的字符进行编码:字符映射到
我在引擎构造函数中添加了“encoding='utf8', convert_unicode=True”,但没什么可改变的
MySQL 中表的字符集/排序规则:utf8 / utf8-bin
代码:
数据库连接
def DB_alchemy(self, category, db="mysql://user:pass@localhost/all_gid_2"):
self.sql_engine = sql.create_engine(db, echo=True, encoding='utf8', convert_unicode=True)
metadata = sql.MetaData(self.sql_engine)
sql_tbl_name_products = category+'_products'
sql_tbl_name_class = category + '_classes'
self.tbl_products = sql.Table(sql_tbl_name_products, metadata, autoload=True)
self.tbl_classes = sql.Table(sql_tbl_name_class, metadata, autoload=True)
self.connection = self.sql_engine.connect()
……
插入
def Insert_df_to_SQL(self, df, tbl):
dict_insert = df.to_dict(orient='records')
insert_qry = tbl.insert()
self.connection.execute(insert_qry, dict_insert)
西里尔数据中 SQLAlchemy 引擎的回声:
2020-08-02 22:07:05,839 INFO sqlalchemy.engine.base.Engine INSERT INTO
Nb_classes
(类型、class_subtype、文本、解释、名称)值(%s、%s、%s、%s、%s)2020- 08-02 22:07:05,840 INFO sqlalchemy.engine.base.Engine ('CL', 'Производительность', 'Расширенная функциональность', 'Стандартный процессор, внешняя графика начального уровня, мультимедиа', 'CL_discret_lite') 2020-08- 02 22:07:05,840 信息 sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
File "C:/Users/shulya403/Shulya403_works/all_gid_2/Database/db_insert_pd.py", line 259, in <module>
FillDB.Classes_to_SQL(df_new=FillDB.df_Classes.head(3))
File "C:/Users/shulya403/Shulya403_works/all_gid_2/Database/db_insert_pd.py", line 241, in Classes_to_SQL
self.Insert_df_to_SQL(df_select, self.tbl_classes)
File "C:/Users/shulya403/Shulya403_works/all_gid_2/Database/db_insert_pd.py", line 166, in Insert_df_to_SQL
self.connection.execute(insert_qry, dict_insert)
File "C:\Users\shulya403\Shulya403_works\all_gid_2\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1014, in execute
return meth(self, multiparams, params)
File "C:\Users\shulya403\Shulya403_works\all_gid_2\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Users\shulya403\Shulya403_works\all_gid_2\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1133, in _execute_clauseelement
distilled_params,
File "C:\Users\shulya403\Shulya403_works\all_gid_2\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1318, in _execute_context
e, statement, parameters, cursor, context
File "C:\Users\shulya403\Shulya403_works\all_gid_2\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1515, in _handle_dbapi_exception
util.raise_(exc_info[1], with_traceback=exc_info[2])
File "C:\Users\shulya403\Shulya403_works\all_gid_2\venv\lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
raise exception
File "C:\Users\shulya403\Shulya403_works\all_gid_2\venv\lib\site-packages\sqlalchemy\engine\base.py", line 1278, in _execute_context
cursor, statement, parameters, context
File "C:\Users\shulya403\Shulya403_works\all_gid_2\venv\lib\site-packages\sqlalchemy\engine\default.py", line 593, in do_execute
cursor.execute(statement, parameters)
File "C:\Users\shulya403\Shulya403_works\all_gid_2\venv\lib\site-packages\MySQLdb\cursors.py", line 199, in execute
args = tuple(map(db.literal, args))
File "C:\Users\shulya403\Shulya403_works\all_gid_2\venv\lib\site-packages\MySQLdb\connections.py", line 280, in literal
s = self.string_literal(o.encode(self.encoding))
File "C:\Users\shulya403\AppData\Local\Continuum\anaconda3\lib\encodings\cp1252.py", line 12, in encode
return codecs.charmap_encode(input,errors,encoding_table)
UnicodeEncodeError: 'charmap' codec can't encode characters in position 0-17: character maps to <undefined>
我能做些什么?
解决方案
尝试添加
?charset=utf8mb4
到连接 URI 的末尾。——戈德·汤普森
推荐阅读
- java - 如何在返回函数之前等待 getValue() 在 Firebase 中完成?
- python - 通过 numpy 矩阵进行索引时,mat[:3,1:2] 和 mat[:3,1] 有什么区别?
- python - PDF 文件中的超级/子脚本——编辑或提取
- f# - 泛型构造要求类型“IConditionalOperator”具有公共默认构造函数
- pine-script - 前一周 H/L 指标仅在每小时时间范围及以下可见
- python - 与 1e-1 相乘后,滚动标准为 pandas DataFrame 返回 0
- c - 是什么导致我的程序停止运行?CS50 - 实验室 1 - 人口增长
- excel - 尝试调整基于文件名中的单个关键字打开 Excel 文件的现有代码,以改为使用多个关键字
- bitbucket - Bitbucket 无法使用包含“@”符号的用户名处理 FTP 部署
- hibernate-criteria - Criteria API 排序并获取对应于最高 5 个值的行