The Python program uses SQLAlchemy with garbled code.
The clubot was upgraded today, but the Chinese characters are garbled after the data is imported. The first step is to find the information and add the encoding information when creating the engine:
engine = create_engine("mysql://root:@localhost:3306/clubot?charset=utf8")
But this is not a line, and then view the table information:
> show create table clubot_members;clubot_members | CREATE TABLE `clubot_members` ( `id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(100) DEFAULT NULL, `nick` varchar(50) DEFAULT NULL, `last_say` timestamp NULL DEFAULT NULL, `last_change` timestamp NULL DEFAULT NULL, `isonline` int(11) DEFAULT NULL, `join_date` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), UNIQUE KEY `nick` (`nick`)) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;
It is found that the latin1 encoding was used when the table was created, while the old table was created using UTF-8 encoding. SQLAlchemy does not find the method that specifies the encoding when creating the table. so we can only look for it in MySQL itself:
> show VARIABLES like "character%%";+--------------------------+-----------------------------+| Variable_name | Value |+--------------------------+-----------------------------+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | latin1 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | latin1 || character_set_system | utf8 || character_sets_dir | /data/share/mysql/charsets/ |+--------------------------+-----------------------------+8 rows in set (0.00 sec)> show create database clubot;+----------+-------------------------------------------------------------------+| Database | Create Database |+----------+-------------------------------------------------------------------+| clubot | CREATE DATABASE `clubot` /*!40100 DEFAULT CHARACTER SET latin1 */ |+----------+-------------------------------------------------------------------+1 row in set (0.00 sec)
It is found that the default MySQL and database are latin1 encoding, so change the Database Configuration
Copy codeThe Code is as follows: vi/etc/mysql/my. cnf # location of the MySQL configuration file on Ubuntu. Other systems may be different.
Add them under [client] [mysqld]
Copy codeThe Code is as follows: default-character-set = utf8
At this time, the MySQL instance cannot be restarted. The default-character-set variable is invalid. Check that the MySQL version is 5.5, find information that the server encoding setting variable of 5.5 is character-set-server, so change default-character-set = utf8 on [mysqld] to character-set-server = utf8, and restart MySQL
Then change the database code:
Copy codeThe Code is as follows: alter database clubot character set utf8;
Delete the new table and re-import the data to Chinese.
Copy codeThe Code is as follows:> use clubot;
> Drop table clubot_status;
> Drop table clubot_infos;
> Drop table clubot_history;
> Drop table clubot_members;