Solve the garbled problem when using SQLAlchemy In the Python Flask framework.

Solve the garbled problem when using SQLAlchemy In the Python Flask framework.

I. Problems

In the past two days, I learned how to use flask + SQLAlchemy to customize a web query page demo. During the test, I found that the query results were garbled. Here we will record the solution.

Ii. Solution

1. Locate on the flask Program

As mentioned in the flask document, you can set SQLALCHEMY_NATIVE_UNICODE to disable the default Unicode encoding of SQLAlchemy. It is possible that SQLAlchemy's default Unicode encoding is not a UTF-8, with the idea of specifying "SQLALCHEMY_NATIVE_UNICODE = False" in the program, the execution program, an error is reported.

"Use_native_unicode" is also mentioned in flask to specify the encoding method for the target encoding. Change "db = SQLAlchemy (app)" to "db = SQLAlchemy (app, use_native_unicode =" utf8 ") ". Although no error is reported this time, it is still garbled.

2. Locate on mysql

It is possible that the character set is not specified when the table is created, and the default Character Set of the database is used. Looking for a period of time to specify how to use the character set when creating a table, failed.

The database should not be used, isn't it a UTF-8? With this idea, enter the database and enter "status". The default value of latin-1 is displayed on the output information. After a long time, the problem was solved.

mysql> status--------------mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1Connection id:     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, the problem is solved by adding related configuration on mysql's my. cnf and restarting the mysql service:

# Enter the mysql configuration file directory cd/etc/mysql/# edit my. cnf configuration file vim my. cnf # Add a row of character_set_server = utf8 # in the file under [mysqld] and add a row of content under [client] and [mysql] respectively. default-character-set = utf8 # Save. Then restart the MySQL service and the setting takes effect. service mysqld restart

Note: The data that already exists is garbled during mysql select query after modification and needs to be re-imported.

PS: SQLAlchemy in Python is really super easy to use. If you are not familiar with it, you can try the following MySQL example:

#! /Usr/bin/env python #-*-coding: UTF-8-*-from sqlalchemy. orm import mapper, sessionmaker _ author _ = 'tan9le' from sqlalchemy import create_engine, Table, Column, Integer, String, MetaDatafrom sqlalchemy. SQL. expression import Castfrom sqlalchemy. ext. compiler import compilesfrom sqlalchemy. dialects. mysql import \ BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, DATE, \ DATETIME, DECIMAL, DECIMAL, DOUBLE, ENUM, FLOAT, INTEGER, \ LONGBLOB, LONGTEXT, MEDIUMBLOB, MEDIUMINT, MEDIUMTEXT, NCHAR, \ NUMERIC, NVARCHAR, REAL, SET, SMALLINT, TEXT, TIME, TIMESTAMP, \ TINYBLOB, TINYINT, TINYTEXT, VARBINARY, VARCHAR, YEAR # attribute description object of the Table metadata = MetaData () userTable = Table ("wzp_user", metadata, Column ('user _ id', Integer, primary_key = True ), column ('user _ name', VARCHAR (50), unique = True, nullable = False), Column ('Password', VARCHAR (40), nullable = True )) # create a database connection. MySQLdb connection method: mysql_db = create_engine ('mysql: // username: password @ ip: port/dbname') # create a database connection, use the mysql-connector-python connection method # mysql_db = create_engine ("mysql + mysqlconnector: // username: password @ ip: port/dbname") # generate the table metadata. create_all (mysql_db) # create a ing class User (object): pass # map the table to the class mapper (User, userTable) # create a custom Session class Session = sessionmaker () # associate the created database connection with this sessionSession. configure (bind = mysql_db) session = Session () def main (): u = User () # Add the following attributes to the ing class, because the field specified in the table created above cannot be blank and unique u. user_name = 'tan9le test' # follow the code above to create a table. This field allows null u. password = '000000' # Add the content session to the session. add (u) # Save the data session. flush () # commit database transactions. sisson automatically expires without closing the session. A simple understanding of commit () # query () Is that select () supports an ORM alternative method and can accept any combination of class/column expressions query = session. query (User) # list all user print lists (queries) # display print query based on the primary key. get (1) # similar to SQL where, print the first print query. filter_by (user_name = 'tan9le test '). first () u = query. filter_by (user_name = 'tan9le test '). first () # modify its password field u. password = '000000' # submit the transaction session. commit () # print the new password print query. get (1 ). password # sort by id field and print the username and password for instance in session. query (User ). order_by (User. user_id): print instance. user_name, instance. password # Release the resource session. close () if _ name _ = '_ main _': main ()