Python程式中使用SQLAlchemy時出現亂碼的解決方案

今天對clubot進行了升級, 但是匯入資料後中文亂碼, 一開是找資料說是在建立引擎的時候添加編碼資訊:

engine = create_engine("mysql://root:@localhost:3306/clubot?charset=utf8")

但是這並不行, 然後查看錶資訊:

> 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;

發現原來建立表的時候用的latin1編碼, 而老的表是用utf-8編碼建立的, SQLAlchemy中並沒有發現有建立表時指定指定編碼的方法. 所以只能在MySQL本身來找:

> 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)

發現 MySQL預設的和資料庫都是latin1的編碼, 所以更改資料庫配置

複製代碼 代碼如下:

vi /etc/mysql/my.cnf # MySQL設定檔在Ubuntu上的位置, 其他系統可能有差異

分別在[client] [mysqld]下添加

複製代碼 代碼如下:

default-character-set = utf8

這時重啟MySQL居然起不來, 說default-character-set是無效的變數, 查看MySQL版本發現是5.5, 找資料說5.5的服務端編碼設定變數是character-set-server, 所以將[mysqld]上的default-character-set = utf8改為 character-set-server = utf8, 並重啟MySQL

然後更改資料庫編碼:

複製代碼 代碼如下:

alter database clubot character set utf8;

刪除建立的表, 並重新匯入資料中文就正常了

複製代碼 代碼如下:

> use clubot;
> drop table clubot_status;
> drop table clubot_infos;
> drop table clubot_history;
> drop table clubot_members;

