Python MySQL incorrect string value

Question

I am using MySQL 5.7 and pymysql and when I am trying to store some data into the database I am getting the following error:

Warning: (1366, "Incorrect string value: '\xF0\x9F\x92\x8A' for column 'description' at row 1") result = self._query(query)

My connection is set as follows:

self.__conn = pymysql.connect(host='localhost', port=3306, user=user, passwd=pass,db = 'twitter',
                                  charset='utf8mb4')
    self.__cur = self.__conn.cursor()

thanks in advance

From the warning you are getting, it looks like the connection is working because it already has column name information (db schema). For that reason, the connection code doesn't matter that much. I would share the code that you are using to insert and the values for the columns. — Sergio Pulgarin, Aug 28 '19 at 21:07
https://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc — Sergio Pulgarin, Aug 28 '19 at 21:08

Vigor · Answer 1 · 2022-05-18T07:45:14.413

I have met the exact same problem. I'll put my solution here just in case it can help somebody else.

change the MySql's database or tables charset from utf8 to utf8mb4. Command as follows:

For database:

ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

For a specific table:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

change the charset of PyMySql's connection from utf8 to utf8mb4. Codes as follows:

conn = pymysql.connect(
host='xxx', 
db='xxx', 
user='xxx', 
passwd='xxx', 
port=xxx, 
charset='utf8mb4')

After the above setting, all is well with me. ;)

Python MySQL incorrect string value

1 Answers1