The birth of a Web application (4)

The previous chapter realizes the login part function, is said to be part of the function, because the user name and password written fixed value is certainly not possible, a whole function, at least need to register, login, password modification, etc., this need to provide a value to store these values to the database capacity.

The current mainstream database is divided into two, that is, relational database and NoSQL database, for small and medium-sized systems, two database performance, ease of use are quite, is a good choice.

Basic Configuration

Here you use the SQLAlchemy database framework's flask integration package, or Flask-sqlalchemy, for database operations.

SQLAlchemy is a very good framework, simplifying the operation of the database, which provides a high level of ORM, but also provides low levels of SQL functionality, very convenient to use.

How to install with the previous type, or the PIP command:

pip3.6 install flask-sqlalchemy

After the installation is complete, modify the configuration section of default to import the package first:

from flask.ext.sqlalchemy import SQLAlchemy

Then configure the link string:

app.config["SQLALCHEMY_DATABASE_URI"]=‘mysql://root:[email protected]/cblog‘

To change autocommit after a configuration request is completed:

app.config["SQLALCHEMY_COMMIT_ON_TEARDOWN"]=True

Instantiate SQLAlchemy:

db=SQLAlchemy(app)

Model settings

After the installation is complete, continue to refine the login example, modify the default.py file, add the user model (class) and the role model (to show the Association)

Role class

class Role(db.Model):       #需继承模型    __tablename__="roles"   #db中表明，如果不设置，则会与class同的默认名    id=db.Column(db.Integer,primary_key=True)  #SQLAlchemy要求必须有主键，一般命名为id即可    name=db.Column(db.String(50),unique=True)  #表示name为字符串，不重复    users=db.relationship("User",backref=‘role‘)  #关联user模型，并在user中添加反向引用(backref)

User class

class User(db.Model):    __tablename__="users"    id=db.Column(db.Integer,primary_key=True)    username=db.Column(db.String(50),unique=True,index=True)   #此列带索引    password=db.Column(db.String(50))    role_id=db.Column(db.Integer,db.ForeignKey("roles.id"))    #外键指向roles表中的id列

The following is to consider how to execute, to be convenient, there is no intrusion into the logic code, which requires not hard-coded into the logic code, such as the determination of the DB state of the code as a parameter to App.run (), this time the shell comes in handy

Configuration scripts

To have flask support command-line scripting, you first need to install the Flask-script extension:

pip3.6 install flask-script

To modify the default.py code:

from flask.ext.script import Managermamager=Manager(app)....if __name__==‘__main__‘:    #app.run(debug=True)    mamager.run()

After you have modified it, run it again:

python default.py

The discovery did not run successfully, but prompted:

As you can see, parameters are required behind the shell (Execute script), Runserver (start service), and help

Start Service below:

python default.py runserver

Service successfully executed

Database more Configuration

But this time, visit the site (127.0.0.1:5000), there will be a 500 error, prompt no MySQL module, this is why? Obviously there is no reason to install the MySQL driver, using the PIP command to install the driver:

pip3.6 install MySQL-python

An error was found with the display (only the win system here):

Follow the prompts to install the C + + toolkit, as prompted by the

Http://landinghub.visualstudio.com/visual-cpp-build-tools

Download complete directly for EXE file, install

Restart after installing Mysql-python, found or not, after Baidu only found, mysqldb this library only support to python2.7, not support 3.x, that had to use other methods, using the Pymysql library:

pip3.6 install PyMySQL

Then modify the default.py code to add two lines:

import pymysqlpymysql.install_as_MySQLdb()

Enter the source code, note this line:

sys.modules["MySQLdb"] = sys.modules["_mysql"] = sys.modules["pymysql"]

You can successfully use and connect to MySQL.
The browser enters the connection and correctly enters the site.

Next, use the shell to create the database table and enter the default.py root directory:

python default.py shellfrom default import dbdb.create_all()

If there is no error, then the database table should be completed:

Database migration

Then the problem comes, when the model is modified, is not reflected in the DB, then what if the modification? For now, it's also simple:

db.drop_all()db.create_all()

But this is only now debugging time to use, if the DB already has the data, this certainly is unbearable, this time, is the database migration plug-in migrate debut, first or the same, need to install:

pip3.6 install flask-migrate

As before, modify the default.py file to configure it after installation:

from flask.ext.migrate import Migrate,MigrateCommandmigrate=Migrate(app,db) #配置迁移mamager.add_command("db",MigrateCommand) #配置迁移命令

Then initialize the migration warehouse with the init command

 python default.py db init

The command line displays:

Then add the Migrations directory:

Indicates that the migration file has been initialized for completion.

The Migrate framework provides some commands for migrating operations, respectively (using the default.py file example):

#根据差异创建迁移python default.py db migrate -m "说明"#改动差异 python default.py db upgrade#取消差异改动python default.py db downgrade

Back to form

Next look at how the login is associated with the database and modify the code within the login method:

@app.route("/login",methods=["POST"])def loginPost():    username=request.form.get("username","")    password=request.form.get("password","")    user=User.query.filter_by(username=username,password=password).first() #数据库查询    if user is not None:        session["user"]=username        return render_template("/index.html",name=username,site_name=‘myblog‘)    else:        flash("您输入的用户名或密码错误")        return render_template("/login.html") #返回的仍为登录页

The results are perfect.

Some summary

Here are some summary things about Python and DB connectivity

Data type

DB Type (SQLAlchemy)	Python type
Integer	Int
Smallinteger	Int
BigInteger	Long
Float	Float
Numeric	Decimal. Decimal
String	Str
Text	Str
Boolean	bool
Date	Datetime.date
Time	Datetime.time
Datetime	Datetime.datetime
Pickletype	Python object serialization
Largebinary	STR (binary)

Column Options

Primary_key	True to primary key (at least one column per object)
Unique	True to not allow duplicates
Index	True to create an index
Nullable	True to be nullable, false to NOT NULL
Default	Set default values
Backref	To add a reverse reference to another model
Primaryjoin	Explicit link conditions for two models
Lazy	Optional values: Select: Load on first access, immediate: Load immediately after load of source object, joined load immediately with join, subquery: Load immediately with subquery, Noload: Never load, dynamic: Does not load record, but provides loaded query
Uselist	If False, the list is not used, and the use of a scalar value
Order_by	Sorting method
Secondary	Specify the name of the relationship table
Secondaryjoin	Manually set two-level junction conditions in many-to-many relationships

Database operations

Db.session.add (model)	Insert or modify records (commit required)
Db.session.delete (model)	Delete Record (commit required)
Model.query	Query records

Query filter

All ()	Return all records
Filter ()	Add filter to original query, return new result
Filter_by ()	Add the equivalent filter to the original query and return the new results
Limit ()	Limit the number of results for paging
Offset ()	Offset results, paging with
Order_by ()	Sort
Group_by ()	Group
First ()	Returns the first result, none returns none
first_or_404 ()	Returns the first result, no return 404 response
Get ()	Returns the record of the primary key, none returns none
get_or_404 ()	Returns the record for the primary key, and returns a 404 response if none
Count ()	Return Full quantity
Paginate ()	Returns a Paginate object that contains the results in the specified range

After these chapters, the login function has been basically completed, in the next chapter will explain the user registration of related functions.

The birth of a Web application (4)