Flask uses Sqlalchemy to implement persistent data

  • The project introduces flask Sqlalchemy
  • ORM introduction and model definition
  • Table relation type and coding implementation
    • One to many relationship (many to one relationship)
    • One to one relationship
    • Many to many relationship
  • Basic database operation
    • insert
    • to update
    • query
    • delete

The project introduces flask Sqlalchemy

First, install the flash Sqlalchemy extension:

$pip install flask-sqlalchemy

Then, import the Sqlalchemy class in the project and instantiate the database used by the application (take MySQL as an example):

from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://account:[email protected]/database'
db = SQLAlchemy(app)

The DB object is an instance of the Sqlalchemy class, which represents the database used by the program, and also obtains all the functions provided by flask Sqlalchemy.

ORM introduction and model definition

In Python, you can use the corresponding packages of the database to directly operate the database, such as pymysql to operate the MySQL database. There are also some database abstraction layer code packages to choose from, such as Sqlalchemy to be discussed here. The abstract package directly handles high-level Python objects instead of database entities such as tables.
The abstraction layer is the so-called object relational mapping (ORM). Its biggest advantage is that it can convert high-level object-oriented operations into low-level database instructions without users’ perception, which greatly simplifies code writing. Sqlalchemy is a higher-level abstraction example that has been well integrated with flask. It also supports a variety of relational database engines.

Sqlalchemy based model definition:

class Roles(db.Model):
    __tablename__ = 'roles'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    users = db.relationship('Users', backref='role')

class Users(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'))

__ tablename__ Define the table name used in the database;

The first parameter of the db.column class constructor is the type of the database table column (also a model attribute). The other parameters specify the configuration options of the attribute (database table column).

2.1 common Sqlalchemy column types

Type name Python type explain
Integer int Ordinary integer, 32-bit
SmallInteger int An integer with a small value range, usually 16 bits
BigInteger Int or long Integer with unlimited precision
Float float Floating point number
String str Variable length string
Boolean bool Boolean value
Date datetime.date date
Time datetime.time time
DateTime datetime.datetime Date and time
Text str Variable length string, optimized for long or unlimited length strings
Numeric decimal.Decimal Fixed point decimal

2.2 common Sqlalchemy column options

Option name explain
primary_key If set to true, this column is the primary key of the table
unique If set to true, duplicate values are not allowed in this column
index If set to true, create an index for the column to improve query efficiency
nullable If set to true, null values are allowed in this column. If set to false, null values are not allowed
default Define default values for this column

Table relation type and coding implementation

Relational databases use relationships to connect rows in different tables. Taking the above model definition code as an example, assume that roles have a one to many relationship with users (that is, one role can belong to multiple users, and each user can only have one role).

One to many relationship (many to one relationship)

On the “many” side, use foreign keys to define relationships
For example, in the users model, define a role_ ID is listed as the foreign key, pass the DB. Foreignkey() parameter to the DB. Column constructor, and take roles.id as the parameter of DB. Foreignkey(), which indicates role_ The value of the ID column is the ID value of the row in the roles table.
On the “one” side, create attributes representing instances (records or rows) from an object-oriented perspective, such as users = dB. Relationship (‘users’, backref = ‘role’) above
The first parameter of DB. Relationship () indicates which model is at the other end of the relationship. The backref parameter adds a role attribute to the users model to define the reverse relationship. This attribute (role) can replace role_ ID to access the roles model. At this time, you will get the model object, not the value of the foreign key.
How to understand the above paragraph can be deepened from the following two sentences:

 users = inst_role.users
 user_role = user.role

Through the code in the first sentence, you can directly obtain all users objects corresponding to a specific role instance (inst_role), which are returned in the form of a list.
Through the code in the second sentence, you can directly obtain the role object corresponding to the user through the user instance (1 line of record, rather than the role_id field value defined by the users mode).

One to one relationship

To define a one-to-one relationship, just pass an additional keyword to DB. Relationship() function based on the one to many model definition to represent the relationship option:
users = db.relationship(‘Users’, backref=‘role’, userlist=False)
One to many and one to one should pay special attention when encoding: when obtaining the object of one of many through the instance of “one” (DB. Relationship defining party):

  • One to many: users = Inst_ Role.users returns a list of objects
  • One to one: users = Inst_ Role. Users returns objects, not lists

Many to many relationship


Basic database operation

In flask Sqlalchemy, changes to the database are managed through the database “session”, which is represented by dB. Session. If you need dB. Session. Commit() to submit changes to records, always submit database related changes in the session to avoid data inconsistency in the database due to partial update exceptions.


    admin_role = Roles(name='Admin')
    mod_role = Roles(name='Moderator')
    john = Users(username='liyu', role=admin_role)
    david = Users(username='liji', role=admin_role)

The first four lines of code instantiate two roles and two user objects (mapping to the database means assigning values to the recorded fields)
5 ~ 7 lines of code is to put the new roles and user operations in one session, and finally commit uniformly, which can prevent the data inconsistency caused by the update of part due to the exception of one of the statements. This operation is to form multiple atomic operations into a transaction. If an update fails, the whole session will be invalidated.
Lines 5-7 can also be abbreviated as db.session.add ([admin_role, mod_role, John, David])

to update

In flask Sqlalchemy, a record is represented as an object; The field of the record is represented as the attribute of the object. Therefore, to update the field value is actually to assign a value to the attribute of the object:

admin_role.name = 'Administrator'


Query all records in the table: schema. Query. All()

eg. Roles.query.all()

Use filters for more accurate queries

filter explain
filter Add filter to original query
filter_by Add the equivalence filter to the original query
limit Use the specified value to limit the number of results returned by the original query
offset Offset the results returned by the original query
order_by Sort the original query results according to the specified criteria
group_by Group the original query results according to the specified criteria

2.1 filter and filter_ By difference

2.1.1 grammatical differences

Filter needs to use “class name. Attribute name” and = = comparison, while filter_ By directly uses the attribute name, and comparison uses=

users = Users.query.filter(Users.id == 1).all()
users =  Users.query.filter_by(id = 1).all()

2.1.2 combined query

Filter does not support combined query, and can only be implemented continuously with filter_ By supports combined queries (the effect of the following two statements is the same)

users = Users.query.filter(Users.id == 1).filter(Users.username == 'xxx').all()
users =  Users.query.filter_by(id = 1, username='xxx').all()
# filter_ By also supports continuous use

Note: if you want to view the native SQL query statements generated by Sqlalchemy for the query, you only need to convert the query object into a string: str (users. Query. Filter_by (role = admin_role))


Execute query

After the specified filter is applied to the query, the query execution is triggered by calling all(). The common trigger execution methods are:

method explain
all() Query all results
first() The first result of the query is returned, and none is not returned
get() The row corresponding to the specified primary key is returned, and none is not returned
count() Returns the number of query results
first_or_404() Return the first result of the query. If there is no result, terminate the request and return 404 error response
get_or_404() Returns the row corresponding to the specified primary key. If the specified primary key is not found, the request is terminated and a 404 error response is returned
paginate() Returns a paginate object

4.1 one to many

4.1.1 obtain all corresponding multi terminal objects from “one”

users = inst_ Role. Users // directly obtain all user objects belonging to the role through the users attribute of the role object
role = inst_ User. Role // obtain the role object of the user directly through the role attribute of the user instance. Note that the role object obtained here is not only the role ID

Note: Inst_ When role.users gets the object, it implicitly calls the all () method to trigger execution. However, if you add some filters (such as sorting), you need to add the lazy =’dynamic ‘keyword parameter in db.relationship. Then you can introduce the filter: Inst_ role.users.order_ by(Users.username)

4.2 many to many


In flask Sqlalchemy, deleting a database record can be mapped to deleting the object representing the record:


reference material

This is the end of this article about using Sqlalchemy to implement persistent data in flask. For more information about persistent data in flask Sqlalchemy, please search previous articles of developeppaer or continue to browse the following articles. I hope you will support developeppaer in the future!