PyMySQL警告:(1366，“字符串值不正确:'\\ xF0 \\ x9F \\ x98 \\ x8D t ...") [英] PyMySQL Warning: (1366, "Incorrect string value: '\\xF0\\x9F\\x98\\x8D t...')

问题描述

我正在尝试使用Pandas和MySQL将数据(推文和其他Twitter文本信息)导入数据库.我收到以下错误:

I'm attempting to import data (tweets and other twitter text information) into a database using Pandas and MySQL. I received the following error:

166:警告:(1366，第3行的'text'的字符串值不正确:'\ xF0 \ x9F \ x92 \ x9C \ xF0 \ x9F ...") 结果= self._query(查询)

166: Warning: (1366, "Incorrect string value: '\xF0\x9F\x92\x9C\xF0\x9F...' for column 'text' at row 3") result = self._query(query)

166:警告:(1366，第5行的'text'列的字符串值不正确:'\ xF0 \ x9F \ x98 \ x8D t ...") 结果= self._query(查询)

166: Warning: (1366, "Incorrect string value: '\xF0\x9F\x98\x8D t...' for column 'text' at row 5") result = self._query(query)

经过彻底的搜索后，我的数据库列的设置似乎出现了问题.我尝试将数据库字符集设置为UTF8并将其整理为utf_unicode_ci，但仍然收到相同的错误.

After a thorough search it seems as if there's something wrong in the way my database columns are set up. I've tried setting the database charset to UTF8 and collating it to utf_unicode_ci but I still receive the same error.

以下是将数据导入数据库的代码:

The following is the code that imports the data to the database:

#To create connection and write table into MySQL

engine = create_engine("mysql+pymysql://{user}:{pw}@{lh}/{db}?charset=utf8"
                       .format(user="user",
                               pw="pass",
                               db="blahDB",
                               lh="bla.com/aald/"))

df.to_sql(con=engine, name='US_tweets', if_exists='replace')

我要导入的数据包含以下数据类型:"int64"，对象"和"datetime64 [ns]".通过使用

The data I'm importing consist of the following data types: 'int64', 'object' and 'datetime64[ns]'. I found out these data types by printing the data to the console with

print(df['tweett']) >>> returns dtype 'object'

感谢您的帮助，谢谢！

推荐答案

在连接到MySQL以及相关列时，您需要utf8mb4，而不是utf8.

You need utf8mb4, not utf8, when connecting to MySQL and in the columns involved.

更多python技巧: http://mysql.rjweb.org/doc.php /charcoll#python (除了使用utf8mb4代替utf8.UTF-8不应更改.)

More python tips: http://mysql.rjweb.org/doc.php/charcoll#python (Except use utf8mb4 in place of utf8. UTF-8 should not be changed.)

对此的更详细说明可以在此处找到.

A more detailed explanation to this can be found here.

这篇关于PyMySQL警告:(1366，“字符串值不正确:'\\ xF0 \\ x9F \\ x98 \\ x8D t ...")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！