烧瓶sqlalchemy mysql编码问题

Question

我有一个简单的数据库在mysql和我尝试打印结果，但编码是错误的。它发生在orm模型和纯sql模式中。

在相同的sqlalchemy conf下，纯使用工作而烧瓶应用程序不使用。我也试过用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 |
    +----------------------+-----------------+

CREATE TABLE `dct_person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
   .
   .
   .
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

#!/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

在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

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

如果我在每个大小写中打印类型，唯一有效的大小写是...wtf？我的数据库数据编码错误吗？

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

Answer 1

您可以尝试在数据库URL中设置字符集。

SQLALCHEMY_DATABASE_URI = 'mysql://user:pass@localhost/db?charset=utf8'

请参阅关于MySQL的SQLAlchemy文档unicode部分。