Flask SQLAlchemy MySQL 编码问题
我在mysql里有一个简单的数据库，想要打印结果，但编码出错了。这种情况在使用ORM模型和纯SQL模式时都会发生。
在相同的sqlalchemy配置下，直接使用是可以的，但在flask应用中就不行。我还尝试用简单的PHP测试，结果是正常的。
我到底哪里做错了呢？
Mysql变量
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
Mysql表
CREATE TABLE `dct_person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(128) DEFAULT NULL,
.
.
.
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
文件 test2.py -> 编码正常
#!/usr/bin/python
# -*- coding: utf-8 -*-
from sqlalchemy import create_engine
engine = create_engine('mysql://user:pass@localhost/db')
connection = engine.connect()
result = connection.execute("select name from dct_person limit 5")
for row in result:
print "name:", row['name']
connection.close()
输出
name: María de los Ángeles Félix Santamaría Espinosa
name: Bertahasa Bertahasa Honzca
name: Teresita Jiménez
真实的flask应用 -> 编码失败
在 config.py 文件中
SQLALCHEMY_DATABASE_URI = 'mysql://user:pass@localhost/db'
使用flask-script的命令
#-*- coding: utf-8 -*-
"""Test db command file"""
from flask import Flask
from flask.ext.script import Command
from flask.ext.sqlalchemy import SQLAlchemy
class TestDb(Command):
"test db"
def run(self):
print "recode db"
app = Flask(__name__)
app.config.from_object('config')
db = SQLAlchemy(app)
result = db.engine.execute('SELECT id,name FROM dct_person LIMIT 5')
for r in result:
print r.name
输出
MarÃa de los Ãngeles Félix SantamarÃa Espinosa
Bertahasa Bertahasa Honzca
Teresita Jiménez
谢谢
编辑：更多信息和测试
在一个文件中使用相同的配置，有三种查询数据库的方法，结果却不同。
test.py
print "\nFlask version (FAIL)"
from flask import Flask
app = Flask(__name__)
app.config.from_object('config')
from flask.ext.sqlalchemy import SQLAlchemy
db = SQLAlchemy(app)
result = db.engine.execute('SELECT id,name FROM dct_person LIMIT 5')
for r in result:
print r.name
print "\nPure version with connect (OK)"
from sqlalchemy import create_engine
engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI'])
connection = engine.connect()
result = connection.execute("select id,name from dct_person limit 5")
for row in result:
print row['name']
connection.close()
print "\nPure version without connect (FAIL)"
engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI'])
result = db.engine.execute("select id,name from dct_person limit 5")
for row in result:
print row['name']
运行命令 python test.py
Flask version
Jaume Mateu i Bullich
Margarita Llobera Llompart
MarÃa de los Ãngeles Félix SantamarÃa Espinosa
Bertahasa Bertahasa Honzca
Teresita Jiménez
Pure version with connect
Jaume Mateu i Bullich
Margarita Llobera Llompart
María de los Ángeles Félix Santamaría Espinosa
Bertahasa Bertahasa Honzca
Teresita Jiménez
Pure version without connect
Jaume Mateu i Bullich
Margarita Llobera Llompart
MarÃa de los Ãngeles Félix SantamarÃa Espinosa
Bertahasa Bertahasa Honzca
Teresita Jiménez
编辑2：其他可能的原因
如果我在每种情况下打印类型，唯一能正常工作的情况是……这是什么情况？我的数据库数据编码错了吗？
Flask version (FAIL)
MarÃa de los Ãngeles Félix SantamarÃa Espinosa
<type 'unicode'>
Pure version with connect (OK)
María de los Ángeles Félix Santamaría Espinosa
<type 'str'>
Pure version without connect (FAIL)
MarÃa de los Ãngeles Félix SantamarÃa Espinosa
<type 'unicode'>
3 个回答
0
我不太明白你们在讨论什么，但我之前遇到过一个问题，就是用 flask_mysqldb 和 utf8mb4 时，表情符号 emojjs 显示不出来，差点就放弃了。
我查看了 flask_mysqldb 的源代码，发现它使用的是
app.config['MYSQL_CHARSET'] = 'utf8mb4'
而不是
app.config['MYSQL_DATABASE_CHARSET'] = 'utf8mb4'
所以我把应用的配置从 MYSQL_DATABASE_CHARSET 改成了 MYSQL_CHARSET，现在一切都正常了。
2
我也遇到了同样的问题，不过我通过设置Flask的配置属性 MYSQL_DATABASE_CHARSET 来解决了。
app.config['MYSQL_DATABASE_CHARSET'] = 'utf8mb4'
这个解决办法可以在这里找到: https://stackoverflow.com/a/35659945
15
你可以在数据库的链接中尝试设置字符集。
SQLALCHEMY_DATABASE_URI = 'mysql://user:pass@localhost/db?charset=utf8'