Solution for garbled characters when using SQLAlchemy in Python

This article mainly introduces the solution for garbled characters when using SQLAlchemy in Python programs. SQLAlchemy is a commonly used tool for Python to operate MySQL databases, you can refer to the following code for upgrading clubot today, but the Chinese characters are garbled after the data is imported. when you open the document, you can 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

The 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]

The 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:

The code is as follows:

Alter database clubot character set utf8;

Delete the new table and re-import the data to Chinese.

The code is as follows:

> Use clubot;
> Drop table clubot_status;
> Drop table clubot_infos;
> Drop table clubot_history;
> Drop table clubot_members;