Python程序中使用SQLAlchemy时出现乱码的解决方案
今天对clubot进行了升级,但是导入数据后中文乱码,一开是找资料说是在创建引擎的时候添加编码信息:
engine=create_engine("mysql://root:@localhost:3306/clubot?charset=utf8")
但是这并不行,然后查看表信息:
>showcreatetableclubot_members; clubot_members|CREATETABLE`clubot_members`( `id`int(11)NOTNULLAUTO_INCREMENT, `email`varchar(100)DEFAULTNULL, `nick`varchar(50)DEFAULTNULL, `last_say`timestampNULLDEFAULTNULL, `last_change`timestampNULLDEFAULTNULL, `isonline`int(11)DEFAULTNULL, `join_date`timestampNULLDEFAULTNULL, PRIMARYKEY(`id`), UNIQUEKEY`email`(`email`), UNIQUEKEY`nick`(`nick`) )ENGINE=InnoDBAUTO_INCREMENT=20DEFAULTCHARSET=latin1;
发现原来创建表的时候用的latin1编码,而老的表是用utf-8编码创建的,SQLAlchemy中并没有发现有创建表时指定指定编码的方法.所以只能在MySQL本身来找:
>showVARIABLESlike"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/| +--------------------------+-----------------------------+ 8rowsinset(0.00sec) >showcreatedatabaseclubot; +----------+-------------------------------------------------------------------+ |Database|CreateDatabase| +----------+-------------------------------------------------------------------+ |clubot|CREATEDATABASE`clubot`/*!40100DEFAULTCHARACTERSETlatin1*/| +----------+-------------------------------------------------------------------+ 1rowinset(0.00sec)
发现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
然后更改数据库编码:
alterdatabaseclubotcharactersetutf8;
删除新建的表,并重新导入数据中文就正常了
>useclubot; >droptableclubot_status; >droptableclubot_infos; >droptableclubot_history; >droptableclubot_members;