flask sqlalchemy mysql encoding problems

Question

I have a simple database in mysql and i try to print results but encoding is wrong. It happens with orm models and pure sql modes.

With same sqlalchemy conf, pure use works and flask app dont. I also have tried with simple test in php and it works ok.

What am i doing wrong?

Mysql variables

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 table

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

File test2.py -> ENCODING WORKING

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

output

name: María de los Ángeles Félix Santamaría Espinosa
name: Bertahasa Bertahasa Honzca
name: Teresita Jiménez

real flask application -> ENCODING FAIL

in config.py

SQLALCHEMY_DATABASE_URI = 'mysql://user:pass@localhost/db'

Command with 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

output

María de los Ãngeles Félix Santamaría Espinosa
Bertahasa Bertahasa Honzca
Teresita Jiménez

Thanks

EDIT: more info and tests

same conf in one file, thre ways of querying db and diferent results

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

EDIT 2: other posible causes

if I print types in each case, the only case that works is ... wtf? is my database data wrong encoded?

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

You can try to set the charset in the database url.

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

See the SQLAlchemy documentation on MySQL, unicode section.