Python newspaper Operationalerror: (1366, "Incorrect string value ..." Problem solving

I. Environment and problem description 1. Environment

Operating system: Win10,64bit.

Python version: 2.7.15

MySQL version: 5.7.23

2. Description of the problem

Using Python to read data from a data file, after processing, using MYSQLDB to connect to the database table and inserting data, the error occurs:

Operationalerror: (1366, "Incorrect string value ..."

Online can be found to be a coding problem:

This exception is a MySQL problem, not a python problem, because MySQL's field type is utf-xxx, while in MySQL these utf-8 data types can store up to three bytes of characters, but not four bytes.

Because the character set of the previous database is UTF8, you need to convert to UTF8MB4 encoding.

Unfortunately, I installed the MySQL version is very old, is 5.4 version, and UTF8MB4 code after 5.5.3 support, so can only reinstall MySQL, re-loaded is 5.7.23 version.

Second, about UTF8MB4 code

MySQL added this UTF8MB4 code after version 5.5.3, Mb4 is the most bytes 4 meaning, specifically for compatibility with four-byte Unicode. In fact, UTF8MB4 is a superset of UTF8, theoretically using UTF8, and then modifying the character set to UTF8MB4, will not have any problems with the existing UTF8 encoded reads. Of course, in order to save space, the general use of UTF8 is enough.

Since UTF8 has no problem coping with daily use, why use UTF8MB4? The low version of MySQL supports the UTF8 encoding, with a maximum character length of 3 bytes, and an error occurs if you encounter a 4-byte character.

The three-byte UTF-8 maximum encoded Unicode character is 0xFFFF, which is the basic multiple plane (BMP) in Unicode. In other words, any Unicode character that is not in the base multiple plane cannot be stored using MySQL's original UTF8 character set.

Iii. Problem solving 1. Database Encoding Modification

To modify the contents of the My.ini:

[Client] default-character-Set =UTF8MB4[MySQL] default-character-Set =UTF8MB4[mysqld] character-Set-Client-Handshake=FALSEcharacter-Set-Server=Utf8mb4 Collation-Server=utf8mb4_unicode_ci Init_connect='SET NAMES utf8mb4'

Convert the database and the already built tables into UTF8MB4:

ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

To change the table encoding:

ALTER TABLE CONVERT  to CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Restart the MySQL service.

If this time has solved the problem, can not look down.

Otherwise, you can see whether the following ideas will give you a little idea.

My situation is still not solving the problem.

2. Start by reading the encoding of the file

The encoding of the discovery data file is gb2312, so you need to convert to Unicode and then to UTF8 after reading a line of content:

line = Line.decode ('gbk'). Encode ('utf-8')

(It is strange to use ' gb2312 ')

This solves the problem.

So it can be seen that the problem I actually encountered is that the read encoding is not recognized when it is written to MySQL, I create a new table, the default encoding is UTF8, and can be inserted normally, which also validates the idea.

Iv. references

1. Python insert data to MySQL times wrong: MySQL

2. The difference between UTF8MB4 and UTF8

3. Change the MySQL database encoding to UTF8MB4

Finish

Python newspaper Operationalerror: (1366, "Incorrect string value ..." Problem solving