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

i followed https://www.blueboxcloud.com/insight/blog-article/getting-out-of-mysql-character-set-hell for solve this issue — trilopin, Aug 17 '15 at 07:31

score 15 · Accepted Answer · answered Oct 26 '14 at 20:36

15

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.

answered Oct 26 '14 at 20:36

wenzul

3,948
2
21
33

@trilopin You could also use `print row['name'].decode("utf-8")` but this will not solve the basic encoding problem... I'm pretty sure that give charset in the connection string should fix the problem. See also [here](https://stackoverflow.com/questions/9155264/how-do-i-get-sqlalchemy-to-correctly-insert-a-unicode-ellipsis-into-a-mysql-tabl) and [here](https://stackoverflow.com/questions/10819192/sqlalchemy-result-for-utf-8-column-is-of-type-str-why)... – wenzul Oct 27 '14 at 09:44
@trilopin Maybe look into [that](https://stackoverflow.com/questions/19015098/why-does-sqlalchemy-create-engine-with-charset-utf8-return-python-type-str-and) issue and check your MySql version. – wenzul Oct 27 '14 at 09:45

score 2 · Answer 2 · answered Aug 09 '18 at 09:12

2

I had the same issue, but I fixed it by setting the Flask configuration property MYSQL_DATABASE_CHARSET.

app.config['MYSQL_DATABASE_CHARSET'] = 'utf8mb4'

The solution for this found at: https://stackoverflow.com/a/35659945

answered Aug 09 '18 at 09:12

C. Damoc

476
4
9

At a glance, it looks like this is for Flask-MySQL not flask-sqlalchemy – Charles L. Dec 18 '20 at 04:04

score 0 · Answer 3 · edited Aug 15 '20 at 17:52

0

I'm not sure what are you guys talking about but I had a problem with flask_mysqldb and utf8mb4 not showing emojjs after almost giving up on it

I look at flask_mysqldb source code and it was using

app.config['MYSQL_CHARSET'] = 'utf8mb4'

not

app.config['MYSQL_DATABASE_CHARSET'] = 'utf8mb4'

so I changed the app config from MYSQL_DATABASE_CHARSET to MYSQL_CHARSET and it works perfectly now

edited Aug 15 '20 at 17:52

Adhun Thalekkara

713
10
23

answered Aug 15 '20 at 16:20

Yousef GG

1

flask sqlalchemy mysql encoding problems

EDIT: more info and tests

EDIT 2: other posible causes

3 Answers3