pandas to_sql 给出 unicode 解码错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32235696/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pandas to_sql gives unicode decode error
提问by lathomas64
I have a pandas dataframe I loaded via read_csv that I am trying to push to a database via to_sql when I attempt
我有一个通过 read_csv 加载的 Pandas 数据框，当我尝试时，我试图通过 to_sql 推送到数据库
df.to_sql("assessmentinfo_pivot", util.ENGINE)
I get back a unicodeDecodeError:
我得到一个 unicodeDecodeError：
UnicodeEncodeError: 'ascii' codec can't encode characters in position 83-84: ordinal not in range(128)
There is no encoding option for to_sql to specify utf-8 for the to_sql and the Engine was created with encoding set to utf-8
to_sql 没有为 to_sql 指定 utf-8 的编码选项，并且引擎是在编码设置为 utf-8 的情况下创建的
ENGINE = create_engine("mssql+pymssql://" +
config.get_local('CEDS_USERNAME') + ':' +
config.get_local('CEDS_PASSWORD') + '@' +
config.get_local('CEDS_SERVER') + '/' +
config.get_local('CEDS_DATABASE'),
encoding="utf-8")
Any pandas insight into getting this working properly? most of my searched lead me to people having a similar error for to_csv which is just resolved by adding encoding="utf-8" but that is unfortunately not an option here.
任何Pandas洞察使这个工作正常？我的大部分搜索都让我找到了对 to_csv 有类似错误的人，这只是通过添加 encoding="utf-8" 来解决的，但不幸的是，这不是这里的一个选项。
I tried paring the file down but it still gives errors even when stripped down to just the headers: http://pastebin.com/F362xGyP
我尝试将文件缩减，但即使剥离到标题，它仍然会出错：http: //pastebin.com/F362xGyP
回答by alybel
I experienced the exact same issue with the combination pymysql and pandas.to_sql
我在组合 pymysql 和 pandas.to_sql 时遇到了完全相同的问题
Update, here is what worked for me:
更新，这对我有用：
Instead of passing the charset as an argument, try attaching it directly to the connection string:
不要将字符集作为参数传递，而是尝试将其直接附加到连接字符串：
connect_string = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(DB_USER, DB_PASS, DB_HOST, DB_PORT, DATABASE)
connect_string = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(DB_USER, DB_PASS, DB_HOST, DB_PORT, DATABASE)
The problem seems to happen in pymysql and the cause for the error seemingly is that the encoding you define is not properly forwarded and set when the pymsql connection is set.
问题似乎发生在 pymysql 中，错误的原因似乎是在设置 pymsql 连接时未正确转发和设置您定义的编码。
For the sake of debugging, I harcoded
为了调试，我硬编码
encoding = 'utf-8
encoding = 'utf-8
in the pymysql _do_execute_manyfunction and that explained it to me.
在 pymysql_do_execute_many函数中，它向我解释了它。
回答by David Incio
I have solved the issue changing the character set in MySQL database (UTF-8) and adding this to the pymysql connection: charset='utf8'.
我已经解决了改变MySQL数据库的字符集（UTF-8）的问题，并添加该到pymysql连接：charset='utf8'。
回答by user13089205
I experienced a similar problem on python 3.7.: UnicodeEncodeError: 'charmap' codec can't encode character '\ufffd' in position 0: character maps to
我在 python 3.7 上遇到了类似的问题。：UnicodeEncodeError: 'charmap' codec can't encode character '\ufffd' in position 0: character maps to
It was the way I defined my engine. I had charset defined to utf-8 in my engine, yet it did not pick it up:
这是我定义引擎的方式。我在我的引擎中将字符集定义为 utf-8，但它没有选择它：
# Connecting to the database(reference for checkout_listener not added)
def MysqlConnection(DbName):
DB_TYPE = 'mysql'
DB_DRIVER = 'mysqldb'
DB_NAME = DbName
POOL_SIZE = 100
CHARSET = 'utf-8'
SQLALCHEMY_DATABASE_URI = '%s+%s://%s:%s@%s:%s/%s?%s' % (DB_TYPE, DB_DRIVER, DB_USER,
DB_PASS, DB_HOST, DB_PORT, DB_NAME, CHARSET)
ENGINE1 = create_engine(
SQLALCHEMY_DATABASE_URI, pool_size=POOL_SIZE, pool_recycle=3600, echo=False)
event.listen(ENGINE1, 'checkout', checkout_listener)
return (ENGINE1);
This worked fine on python 2 but on python 3, the charmap error would occur. The only solution I found was to write engine in a different manner, and add charset to the definition string:
这在 python 2 上运行良好，但在 python 3 上，会发生charmap 错误。我找到的唯一解决方案是以不同的方式编写引擎，并将字符集添加到定义字符串中：
connection_string = f"{mysql_user}:{mysql_password}@localhost:3306/{db_name}?charset=utf8"
engine = create_engine(f'mysql://{connection_string}')
