Python's flask framework SQLAlchemy the use of garbled problem solving

first, the question

These two days in learning to use flask + SQLAlchemy customized a Web query page of the demo, in the test, found that the results of the query display garbled. The workaround is documented here.

Second, the solution of ideas

1, flask program on the positioning

Flask's documentation mentions that you can disable the use of SQLAlchemy default Unicode encoding by setting Sqlalchemy_native_unicode. It is possible that SQLALCHEMY default UNICODE encoding is not UTF-8, holding the idea, in the program specified "Sqlalchemy_native_unicode=false", execute the program, error.

Flask also mentions "Use_native_unicode" as the target encoding to specify the encoding, attempting to change "db = SQLAlchemy (APP)" to "db = SQLAlchemy (app, use_native_unicode=" UTF8 ")”。 This time although no error, but still garbled.

2, MySQL on the location

It occurred to me that it was possible to create a table without specifying a character set, which was caused by the default character set of the database. Keep looking for a while. How to specify a method to use a character set when building a table, no results.

The database is not going to use UTF-8, is it? Hold this idea, enter the database, enter "status", on the output of the information displayed by default is Latin-1. For a long while, the original problem is here.

mysql> status--------------MySQL Ver 14.14 distrib 5.1.73, for Redhat-linux-gnu (x86_64) using ReadLine 5.1Connection I D:     9Current database:   web12306current User:      Root@localhostssl: Not in          usecurrent pager:     stdoutusing outfile:     ' Using delimiter:    ; Server version:     5.1.73 Source distributionprotocol version:    10Connection:       Localhost via UNIX socketserver CharacterSet:  utf8db   characterset:  utf8client characterset:  latin1conn. CharacterSet:  Latin1unix Socket:      /var/lib/mysql/mysql.sock

3, solve the problem

Now that it is found, the problem is to add the configuration on MySQL my.cnf and restart the MySQL service:

# go to MySQL config file directory cd/etc/mysql/# edit my.cnf profile vim my.cnf# Add a line under [Mysqld] in the file Character_set_server = utf8# in [client] and [ MySQL] Add one line below each Default-character-set = utf8# save. Then restart the MySQL service and the settings will take effect for service mysqld restart

Note: It is important to note that the previously existing data, after the above modification, through the MySQL select query will be garbled, you need to re-import.

PS: Python under SQLAlchemy really is super easy to use, not too familiar with children's shoes can try the following MySQL example:

#!/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy.orm Import mapper, sessionmaker__author__ = ' tan9le ' from Sqla Lchemy import Create_engine, Table, Column, Integer, String, metadatafrom sqlalchemy.sql.expression import Castfrom Sqlal Chemy.ext.compiler import compilesfrom sqlalchemy.dialects.mysql import \ BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, DAT E, \ DATETIME, Decimal, Decimal, DOUBLE, ENUM, FLOAT, INTEGER, \ Longblob, Longtext, Mediumblob, Mediumint, Mediumte XT, NCHAR, \ NUMERIC, NVARCHAR, REAL, SET, SMALLINT, TEXT, time, TIMESTAMP, \ Tinyblob, TINYINT, Tinytext, VARBINARY , VARCHAR, year# Table Property Description Object MetaData = MetaData () usertable = Table ("Wzp_user", MetaData, Column (' user_id ', Integer, Primary_ key=true), column (' user_name ', varchar (), unique=true, Nullable=false), column (' Password ', varchar (+), nullable= True) #创建数据库连接, mysqldb connection mysql_db = Create_engine (' mysql://user name: Password @ip:port/dbname ') #创建数据库连接, using Mysql-connector-python connection Mode #mysql_db = Create_engine ("mysql+mysqlconnector://User name: Password @ip:port/dbname ") #生成表metadata. Create_all (mysql_db) #创建一个映射类class User (object): pass# Map the table to class mapper (User, usertable) #创建了一个自定义了的 Session Class session = Sessionmaker () #将创建的数据库连接关联到这个sessionSession. Configure ( BIND=MYSQL_DB) session = Session () def main (): U = User () #给映射类添加以下必要的属性 because the table created above specifies that the field cannot be empty, and the unique u.user_name= ' tan9le test ' #按 According to the code above to create the table, this field is allowed to be empty u.password= ' 123456 ' #在session中添加内容 session.add (u) #保存数据 session.flush () #数据库事务的提交, Sisson automatically expires without requiring To turn off Session.commit () #query () A simple understanding is that the ORM-enabled alternative to select () can accept any combination of class/column expressions, query = Session.query (User) #列出 All user Print List (query) #根据主键显示 print query.get (1) #类似于SQL的where, printing one of the first print query.filter_by (user_name= ' tan9le test ' ). First () U = query.filter_by (user_name= ' tan9le test '). First () #修改其密码字段 U.password = ' 654321 ' #提交事务 session.commit () #打    A new password appears in print Query.get (1). Password #根据id字段排序, print the username and password for instance in Session.query (user). Order_by (user.user_id): Print Instance.user_name, Instance.password #释放资源 session.close () if __name__ = = ' __main__ ': Main () 

