Flash database related

Database type

SQL and NoSQL.

SQL database stores structured data in an efficient and compact way, which requires a lot of energy to ensure data consistency.

NoSQL database relaxes the requirements for consistency, so as to obtain performance advantages.

ORM

Database engine and database abstraction layer.

• Object relational mapper, ORM
• Object document mapper, ODM

1. Without the user's knowledge, convert the instructions of high-level face object operation into low-level database instructions
2. There is a certain performance loss to the database business, and the single productivity is greatly improved. The key to selecting the abstraction layer is:
   1. Portability, which database engine is supported
      1. SQLAlchemy ORM supports mysql, Postgres and SQLite
   2. Flask integration

Flask-SQlAlchemy

pip install flask-sqlalchemy # 是一个 Flask 扩展

to configure

1. Database URL: SQLALCHEMY_DATABASE_URL
2. Automatically submit database changes after request: SQLALCHEMY_COMMIT_ON_TEARDOWN - True

Initialize instance

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

basedir = os.path.abspath(os.path.dirname(__file__))
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'data.sqlite')
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True  # Deal FSADeprecation Warning

# 获得数据库类实例
db = SQLAlchemy(app)

Define model

The term model refers to the persistent entity used by the program. In orm, the model is generally a Python class, and the properties of the class correspond to the class of the database table. [when the properties of this class change, the database should also be migrated]

Common SQLAlchemy field types

Common SQLAlchemy column options

Common SQLAlchemy relationship options

Relational databases use relationships to connect rows in different tables. A role can belong to multiple users, and each user can only have one role.

The following shows the definition of a common one to many relationship in the model class.

class Role(db.Model):
   # 定义表名
   __tablename__ = 'roles'
   # 定义字段
   id = db.Column(db.Integer, primary_key=True, autoincrement=True)
   name = db.Column(db.String(64), unique=True)
   users = db.relationship('User', backref='role')  # 反推与role关联的多个User模型对象


class User(db.Model):
   # 定义表名
   __tablename__ = 'users'
   # 定义字段
   id = db.Column(db.Integer, primary_key=True, autoincrement=True)
   name = db.Column(db.String(64), unique=True, index=True)
   email = db.Column(db.String(64), unique=True)
   pswd = db.Column(db.String(64))
   # 设置外键，外键建立关系，传给 db.ForeignKEy() 的参数 'roles.id' 表明，这列的值是 roles 表中 id 值
   role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))

Here are some descriptions of the code:

The users attribute added to the Role model represents the face object perspective of the relationship. For an instance of the Role class, the users attribute returns a list of users associated with the Role. db. The first parameter of relationship () represents the model that the other end of the relationship refers to. If the model has not been defined, it can be specified with a string.

db. The backref parameter in relationship () adds a role attribute to the User model and specifies it as a reverse relationship. This attribute can replace role_id accesses the role model. At this time, the model object is obtained, not the value of the foreign key.

There are other relationship types besides one to many.

Database operation

Create table

The model required by the program has been defined above, but the database is only an initialization instance at present, and there is no real reading and writing (i.e. creating) for it

Here are two related functions:

1. db.create_all()
2. db.drop_all()

As the name suggests, it means creating and discarding tables. As mentioned above, the model needs to be migrated when modifying the model. This is because if the database table already exists in the database, create_ The all() function will not create or update the function. Now we can use drop_ The all() function is discarded and rebuilt, but this causes the loss of the original function of the database. Let's save this problem first and introduce a better way to realize database migration later.

db.drop_all()
db.create_all()

Insert row

Users and user roles have been defined above. Now let's really create some roles.

Create a new demo Py, let's do some meaningful operations:

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from app import Role, User  # 从刚刚定义的文件导入身份类

basedir = os.path.abspath(os.path.dirname(__file__))
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + \
   os.path.join(basedir, 'data.sqlite')
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
# Deal FSADeprecation Warning
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

# 获得数据库类实例
db = SQLAlchemy(app)

# 创造用户身份，现在这些变量只存在于内存，并没有真正写入数据库
admin_role = Role(name='admin')
user_role = Role(name='user')

# 因为没有写入数据库，所以用户身份的id都没有赋值
print(admin_role.id, user_role.id)

# 通过数据库会话管理改动，先添加再提交（和Git一样哦
db.session.add(admin_role)
db.session.add(user_role)
db.session.commit()

# 现在可以看到相关的输出了 ：）
print(admin_role.id, user_role.id)

Database session can ensure the consistency of the database, that is to say, ensure the consistency of the data when the database is called by any thread at any time. (it seems that the concepts of thread and process have not been introduced yet, but they will be introduced soon.)

If an error occurs when writing to the session again, the whole session will become invalid. You should always submit the relevant changes in the session to avoid inconsistency in the database due to partial database updates.

If the database session commits an error, it can also be rolled back. The English name of rollback is rollback, isn't it

db.session.rollback()

Modify line

# 通过add方法更新模型
print(admin_role.name)
admin_role.name = 'administrator'
db.session.add(admin_role)
db.session.commit()
print(admin_role.name)

Delete row

# delete方法删除角色
db.session.delete(user_role)
db.session.commit()

Query line

Of course, the most basic thing is to query all records (laughter), but we can also find more accurately through filters

Role.query.all() # 所有查找
Role.query.filter_by(role=user_role).all() # 查找用户角色

You can view sql query statements by converting to str.

Common SQLAlchemy query filters

Common SQLAlchemy query executors

When executing some expressions, such as user, the processing is similar_ role. Users, the implicit query callback uses all() to form a user list. Because the query object is hidden, it is impossible to specify an accurate query filter. You can add lazy='dynamic 'to prohibit automatic query.

users = db.relationship('User', backref='role')  # 反推与role关联的多个User模型对象
# 修改为
users = db.relationship('User', backref='role',
                            lazy='dynamic')  # 反推与role关联的多个User模型对象

In this way, you can customize the filter:

user_role.users.order_by(User.username).all()

It's two o'clock in the morning. It's too late (^ ^) a. I'll share how to integrate Python Shell to avoid repeated import tomorrow.

Of course, there is a sample code:

• [X]Code