Flask-ORM

Abstract

In the previous chapter, we learned about basic database concepts and common table operations, and I want you to realize that it is best not to introduce database dependency in back-end applications. However, if introduced, it is a good idea to know some basic database knowledge before using, such as: start from manually writing SQL statements to create tables.

With the tables in place, today’s chapter begins by explaining how to build the ability to operate on databases in back-end applications.

ORM

Object Relational Mapping (ORM)

The name of the ORM makes its function and purpose very clear:

Object: objects, in this case objects in programming languages, such as classes in Python/Java, or structs in Golang

A Relational database such as MySQL, Oracle, and PostgreSQL

Mapping refers to the association between programming language objects and relational databases. For example, object names represent table names, object attributes represent table column names, and data types.

The mapping is shown in the figure

It is because of the above characteristics of ORM, so in the backend applications that inherit ORM framework, such as Django, Flask, you can define the Model class (object Model), and create the corresponding data table structure in the database with one key.

Flask – SQLAlchemy framework

The installation

Flask as a microframework, almost all of its integrated capabilities come from plug-ins, and ORM framework is one of the plug-ins that provide database operation capabilities.

SQLAlchemy is a popular ORM framework in Python. It provides high-level ORM and low-level native database operations, allowing developers to manipulate databases through Python objects instead of directly dealing with SQL statements.

Flask-sqlalchemy is a Flask application extension designed to simplify the use of SQLAlchemy in Flask applications by providing useful defaults and additional helpers to make common tasks easier to complete.

Run the following command to install the extension package

pip install pymysql
pip install flask-sqlalchemy
Copy the code

If the installation speed is slow, specify the source address of the mirror

pip install flask-sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple 
Copy the code

The introduction of

Introducing flask-SQLalchemy into the Flask application is simple as follows:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "Mysql + pymysql: / / root: [email protected]:3306 / ops? charset=utf8"
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)
Copy the code

Using flask-SQLalchemy to extend to simplicity is obvious, just set up a SQLALCHEMY_DATABASE_URI variable of the following format:

mysql+pymysql://username:password@host:port/db_name? charset=utf8Copy the code

You can replace it according to your database Settings.

SQLALCHEMY_ECHO = True SQLALCHEMY_ECHO = True SQLALCHEMY_ECHO = True SQLALCHEMY_ECHO = True On the other hand, it’s good for troubleshooting.

Model

After the introduction of DB, we can start to define the Model Model. According to the data table structure in the previous chapter, we can define the corresponding object Model as follows:

class Devices(db.Model) :
    __tablename__ = 'devices'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="Autoincrement primary key")
    ip = db.Column(db.String(16), nullable=False, comment="IP address")
    hostname = db.Column(db.String(128), nullable=False, comment="Host name")
    idc = db.Column(db.String(32), comment="Room")
    row = db.Column(db.String(8), comment="Cabinet row")
    column = db.Column(db.String(8), comment="Cabinet row")
    vendor = db.Column(db.String(16), comment="Vendor")
    model = db.Column(db.String(16), comment="Model")
    role = db.Column(db.String(8), comment="Role")
    created_at = db.Column(db.DateTime(), nullable=False, server_default=text('NOW()'), comment="Creation time")
    updated_at = db.Column(db.DateTime(), nullable=False, server_default=text('NOW()'), server_onupdate=text('NOW()'), comment="Modification time")
Copy the code

Two new columns, created_AT and updated_AT, are added from the previous section. When a new row is added or a row is modified, the time when the data was created or modified is updated.

You can refer to the DATA table SQL statement to compare, ORM framework model and database mapping,

CREATE TABLE IF NOT EXISTS `devices` (
    `id` INT AUTO_INCREMENT COMMENT 'Increment primary key',
    `ip` VARCHAR(16) NOT NULL COMMENT 'IP address',
    `hostname` VARCHAR(128) COMMENT 'Host name',
    `idc` VARCHAR(32) COMMENT 'room',
    `row` VARCHAR(8) COMMENT 'Cabinet row',
    `column` VARCHAR(8) COMMENT 'Cabinet row',
    `vendor` VARCHAR(16) COMMENT 'manufacturers',
    `model` VARCHAR(16) COMMENT 'model',
    `role` VARCHAR(8) COMMENT The 'role',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time'.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

Data type and column options

From the comparison of Model creation and SQL statements above, there is a specific mapping between flask-SQLAlchemy and database data types. Since flask-SQLAlchemy is implemented based on SQLAlchemy, its data types are the same as SQLAlchemy.

Common data types used in SQLAlchemy:

SQLAlchemy types	instructions
Integer	Integer that maps to the database as an int
Float	Floating point type, which maps to the database as float. He occupied the 32nd position
Double	A double-precision floating-point type, mapped to the database as a double, occupying 64 bits
String	The variable character type, mapped to the database, is the VARCHAR type.
Boolean	Boolean, mapped to the database is tinyInt
Date	Storage time. Only year, month and day can be stored. The mapping to the database is of type date
DateTime	Storage time, can store year, month, day, minute, second, millisecond and so on. Mapping to the database is also of datetime type
Timestamp	Store time, you can store minutes and seconds. Mapping to the database is also of timestamp type
Text	Stores long strings. Generally can store more than 6W characters, mapped to the database is the text type
LongText	Longtext type, mapped to the database as longtext type

In addition to the usual data types, there are a number of attributes that are specified in our SQL statement when we define columns, which are also defined in SQLAlchemy.

Column options commonly used in SQLAlchemy

SQLAlchemy column attributes	instructions
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, this column is indexed to improve query efficiency
nullable	If set to True, this column allows null values; If set to False, null values are not allowed in this column
comment	The description of the column
autoincrement	When the value is an int primary key, the value can be incremented
server_default	Set the default values for the remote database
server_onupdate	Set the default values in the remote database when a column is updated

Add and delete

After the Model is defined, the most important step is to add, delete, change and check the data table through the operation of the Model.

The code is sorted as follows:

# /models.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = "Mysql + pymysql: / / root: YfyH98333498. @ 127.0.0.1:3306 / ops? charset=utf8"
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)


class Devices(db.Model) :
    __tablename__ = 'devices'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, comment="Autoincrement primary key")
    ip = db.Column(db.String(16), nullable=False, comment="IP address")
    hostname = db.Column(db.String(128), nullable=False, comment="Host name")
    idc = db.Column(db.String(32), comment="Room")
    row = db.Column(db.String(8), comment="Cabinet row")
    column = db.Column(db.String(8), comment="Cabinet row")
    vendor = db.Column(db.String(16), comment="Vendor")
    model = db.Column(db.String(16), comment="Model")
    role = db.Column(db.String(8), comment="Role")
    created_at = db.Column(db.DateTime(), nullable=False, server_default=text('NOW()'), comment="Creation time")
    updated_at = db.Column(db.DateTime(), nullable=False, server_default=text('NOW()'), server_onupdate=text('NOW()'), comment="Modification time")


if __name__ == '__main__':
    # increase
    # delete
    # change
    # check
Copy the code

increase

# /models.py
if __name__ == '__main__':
    # increase
    device = Devices(ip="10.0.0.1", hostname="BJ-R01-C01-N9K-00-00-01", idc="Beijing", row="R01", column="C01", vendor="Cisco", model="Nexus9000", role="CSW")
    db.session.add(device)
    db.session.commit()
Copy the code

Db.session is used for data manipulation, and db.session.com MIT () is executed after the statement is executed to commit the operation. This is because flask-SQLalchemy uses the session mechanism to ensure that the multi-thread operation of the database is not affected. The specific principles will be discussed separately in the introduction.

When you execute Python models.py, you can see the following console output:

2022-03-06 20:03:35,775 INFO SQLalchemy.engine.Engine SELECT @@sql_mode 2022-03-06 20:03:35,776 INFO Sqlalchemy.engine.Engine SELECT @@lower_case_table_names 2022-03-06 20:03:35,777 INFO SQLalchemy.engine DATABASE() 2022-03-06 20:03:35.778 INFO SQLalchemy.engine.Engine BEGIN (Implicit) 2022-03-06 20:03:35.780 INFO sqlalchemy.engine.Engine INSERT INTO devices (ip, hostname, idc, `row`, `column`, vendor, model, `role`) VALUES (%(ip)s, %(hostname)s, %(idc)s, %(row)s, %(column)s, %(vendor)s, %(model)s, %(role)s) 2022-03-06 20:03:35,781 INFO SQLalchemy. engine.Engine [generated in 0.00023s] {' IP ': '10.0.0.1', 'hostname': 'BJ-R01-C01-N9K-00-00-01', 'idc': 'Beijing', 'row': 'R01', 'column': 'C01', 'vendor': 'Cisco', 'model': 2022-03-06 20:03:35,783 INFO SQLalchemy.engine.Engine COMMITCopy the code

Select * from devices\G; The database query result is as follows:

mysql> select * from devices\G; * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 row * * * * * * * * * * * * * * * * * * * * * * * * * * * id: 1 IP: 10.0.0.1 hostname: BJ-R01-C01-N9K-00-00-01 idc: Beijing row: R01 column: C01 vendor: Cisco model: Nexus9000 role: CSW created_at: 2022-03-06 20:03:35 updated_AT: 2022-03-06 20:03:35 1 row in Set (0.00 SEC)Copy the code

You can see that a data entry was successfully inserted and that the database automatically inserted the ID, CREATED_AT, and updated_AT fields.

Batch additions are as follows:

# /models.py
if __name__ == '__main__':
    # increase
    device1 = Devices(ip="10.0.0.1", hostname="BJ-R01-C01-N9K-00-00-01", idc="Beijing", row="R01", column="C01", vendor="Cisco", model="Nexus9000", role="CSW")
    device2 = Devices(ip="10.0.0.2", hostname="BJ-R01-C01-N9K-00-00-02", idc="Beijing", row="R01", column="C02", vendor="Cisco", model="Nexus9000", role="CSW")
    device3 = Devices(ip="10.0.0.3", hostname="BJ-R01-C01-N9K-00-00-03", idc="Beijing", row="R01", column="C03", vendor="Cisco", model="Nexus9000", role="CSW")
    db.session.add_all(device1, device2, device3)
    db.session.commit()
Copy the code

The database query result is as follows:

mysql> select * from devices\G; * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 row * * * * * * * * * * * * * * * * * * * * * * * * * * * id: 5 IP: 10.0.0.1 hostname: BJ-R01-C01-N9K-00-00-01 idc: Beijing row: R01 column: C01 vendor: Cisco model: Nexus9000 role: CSW created_at: 2022-03-06 20:18:52 updated_at: 2022-03-06 20:18:52 *************************** 2. row *************************** id: 6 IP: 10.0.0.2 hostname: BJ-R01-C01-N9K-00-00-02 IDC: Beijing Row: R01 Column: C02 Vendor: Cisco Model: Nexus9000 Role: CSW created_at: 2022-03-06 20:18:52 updated_at: 2022-03-06 20:18:52 * * * * * * * * * * * * * * * * * * * * * * * * * * * 3. The row * * * * * * * * * * * * * * * * * * * * * * * * * * * id: 7 IP: 10.0.0.3 hostname: BJ-R01-C01-N9K-00-00-03 idc: Beijing row: R01 column: C03 vendor: Cisco model: Nexus9000 role: CSW created_at: 2022-03-06 20:18:52 UPDATed_AT: 2022-03-06 20:18:52 3 Rows in Set (0.00 SEC)Copy the code

Delete, change, look up

Both deletion and modification need to be based on queries. Since queries are too long, we will explain them in the next chapter.

conclusion

This chapter is an important one. We introduce the database in the back-end application for the first time, and realize the operation of the database through ORM framework. Therefore, we hope that you must practice the operation by yourself, otherwise the following learning content can not be carried out smoothly.