Flask and MySQL configuration operations

Operating Environment:
Centos6.7; Python 2.7.11;

—————————— of the Split line preparation knowledge:

The 1,roles table adds the primary key attribute for the ID column, and the other table users add the foreign key attribute for the role_id column so that the primary key corresponds to the Sisu key, resulting in the change of the role name (the row value of the Name column in the Roles table) once in the roles table, all by Role_ ID refers to the role of the user can immediately see the updated relationship, it is named relational database.
2,nosql databases generally use collections instead of tables, use documents instead of records, and the benefit of using NoSQL databases is that data duplication can improve query speed.
3, there are several ways to manipulate a database:
A, you can write SQL statements at the database command line, which is the underlying operations database.
b, in Python, you can use some database drivers such as the Mysql-python driver to operate the database, the driver of the underlying cumbersome command encapsulation.
C, further simplify the command by using the SQLAlchemy database framework to further encapsulate the database driver in flask. So SQLAlchemy is not a database, but a framework for manipulating databases.

Database management of —————————— dividing line
With the completion of the preparatory knowledge, there are several steps to managing the database:
1, configure the database.
2, define the database model, and establish relationships to the tables in the database.
3, basic operation of the database (command line operation):
Create a table, insert rows, modify rows, delete rows, query rows
4, Operation database in view function
5, integrated Python shell
6,flask-migrate Implementing a database migration. (Until now, it is not clear how the database migration to reflect the role, later understand)

—————————— the exact steps of the magical dividing line
1，安装
Installation of SQLAlchemy

pip install flask-sqlalchemy

Mysql-python driver installation: Initially in line with the Yum method

yum install MySQL-python

Although the display has been installed, but is imported with import when the module is not found, and later in the virtual environment with PIP installation can be, specific people can Google installation method. Here to provide you with a thought, yum installation can not identify and try Pip.
2，配置数据库
Flask configuration MySQL database and SQLite different, first you have to create a good database, have a data name to use, and SQLite no database run SQLAlchemy will automatically give you to create the database, so before configuring the database, You want to use other methods to build an empty database, recommend everyone with phpMyAdmin (PS: Later found a better tool: Install Simple interface is Chinese, is the activation of a little trouble, Navicat for MySQL, strong Amway beginners use), The blogger is installed according to the following link, pro-test:
Http://blog.sina.com.cn/s/blog_70545bad0101khx3.html
Here I built a database called Text1 in advance with phpMyAdmin, and then wrote the code in the hello.py file:
From Flask.ext.sqlalchemy import SQLAlchemy
App = Flask (name)
app.config[' secret_key ' = ' hard to guess '
app.config[' Sqlalchemy_database_uri ']= ' mysql://root: Password @localhost:3306/text1 ' #这里登陆的是root用户, to fill in their own password, The default port for MySQL is 3306, fill in the database name you created earlier Text1
app.config[' Sqlalchemy_commit_on_teardown ']=true #设置这一项是每次请求结束后都会自动提交数据库中的变动
db = SQLAlchemy (APP) #实例化

3，定义模型，建立关系：
In hello.py

class Role(db.Model):      __tablename__ = ‘roles‘ #定义表名     id = db.Column(db.Integer,primary_key=True)#定义列对象     name = db.Column(db.String(64),unique=True)     user = db.relationship(‘User‘,backref=‘role‘,lazy=‘dynamic‘)#建立两表之间的关系     def __repr__(self):         return ‘<Role {}> ‘.format(self.name) class User(db.Model):     __tablename__ = ‘users‘     id = db.Column(db.Integer,primary_key = True)     username = db.Column(db.String(64),unique=True,index=True)     role_id = db.Column(db.Integer,db.ForeignKey(‘roles.id‘))     def __repr__(self):         return ‘<User {}>‘.format(self.username)

3, after the above steps, the database operation can be done (command line)
Create a table

 if __name__ == ‘__main__‘:     

Add directly in the hello.py file and enter too much trouble at the python command line

Insert row:

 admin_role =Role(name = ‘Admin‘) #实例化 mod_role = Role(name = ‘Moderator‘) user_role =Role(name = ‘User‘) user_john = User(username = ‘john‘,role=admin_role)#role属性也可使用，虽然他不是真正的数据库列，但却是一对多关系的高级表示 user_susan = User(username = ‘susan‘,role= user_role) user_david = User(username = ‘david‘,role = user_role) db.session.add_all([admin_role,mod_role,user_role,user_john,user_susan,user_david])  # 准备把对象写入数据库之前，先要将其添加到会话中，数据库会话db.session和Flask session对象没有关系，数据库会话也称事物 db.session.commit()#提交会话到数据库

To run the hello.py program, open phpMyAdmin to see the roles and users tables in the database Text1, and try knocking the command line in Python:
Modify:

admin_role.name =‘Adminstrator‘db.session.add(admin_role)#添加到数据库db.session.commit()#提交到数据库

Delete:

db.session.delete(mod_role)db.session.commit()

Inquire:

User.query.filter_by(role=user_role).all() #注意过滤器的使用

4, operation in view function:
In the hello.py file:

 @app.route(‘/‘,methods=[‘GET‘,‘POST‘]) def index():     myform = NameForm()     if myform.validate_on_submit():         user = User.query.filter_by(username=myform.name.data).first()         if user is None:             user = User(username=myform.name.data)             db.session.add(user) #注意这里不接着用commit（）函数，正是因为前面设置了app.config[‘SQLALCHEMY_COMMIT_ON_TEARDOWN‘]=True ，所以会SQLAlchemy会自动帮你commit.             session[‘known‘] = False           else:             session[‘known‘] = True         session[‘name‘]= myform.name.data         myform.name.data = ‘‘         return redirect(url_for(‘index‘))     return render_template(‘formindex.html‘,form=myform,name=session.get(‘name‘),known=session.get(‘known‘,False))

In the formindex.html file, add:

    {% if not known %}    <p>please to meet you !</p>    {% else %}    <p>happy to see you again!</p>    {% endif %}

5, the integration of the Python shell, if the previous third step is to operate at the command line, you will know that each time you want to import the database instance and model in the shell, it is quite troublesome, can be added in hello.py:

from flask.ext.script import Shell def make_shell_context():    return dict(app=app,db=db,User=User,Role=Role) #右边app是指hello.py中的实例对象，左边app是自己命名，以后可以在命令行中输入，就调用了app实例manager.add_command(‘shell‘,Shell(make_context=make_shell_context)) #添加shell 命令，Shell类中make_context 参数是规定要传人的上下文环境

This allows you to use an app instance without having to import the app in the command line.
6, the last is the database migration, a bit like git in the repository, but the specific role of the performance is not understood:
No way, just copy the code again.
Installation: Pip Install Flask-migrate
Configuration (in hello.py):

from flask.ext.migrate import Migrate,MigrateCommandmigrate = Migrate(app,db) #创建实例manager.add_command(‘db‘,MigrateCommand）#将MigrateCommand类传给db

To create a migration warehouse:
(venv) $ python hello.py DB init # There's Wood there's much like git

To create a migration script automatically:
(venv) $ python hello.py db migrate-m "Initial migration" #手动是用revision command

Update:
(venv) $ pyhton hello.py DB Upgrade

At this point, the basic operation of flask and MySQL database is over, see you ~

Flask and MySQL configuration operations