Flask Operation Mysql database – flask-SQLalchemy extension

Official document

Flask-sqlalchemy.palletsprojects.com/en/master/q…

Database setup

Relational database is widely used in Web applications. Relational database stores all data in tables, which are used to model the entities of applications. The number of columns in the table is fixed, and the number of rows is variable. It uses a structured query language. Columns in a relational database define the data attributes of the entities represented in the table. For example, there are name, price, number, etc. Flask itself is not limited to a selection of databases, you can choose either SQL or NOSQL. You can also opt for the more convenient SQLALchemy, similar to Django’s ORM. SQLALchemy is actually an abstraction of the database, allowing developers to manipulate the database through Python objects instead of directly dealing with SQL statements, while sacrificing some performance costs, in exchange for a big increase in development efficiency.

SQLAlchemy is a relational database framework that provides both high-level ORM and low-level native database operations. Flask-sqlalchemy is a Flask extension that simplifies sqlAlchemy operations.

Mysql is used as an example.

Create mysql database

1. Log in to the database

mysql -u root -p password
Copy the code

2. Create a database and set the encoding

Create database < database name > charset=utf8;Copy the code

3. All databases are displayed

show databases;
Copy the code

4. Perform the following operations

mysql> create database flask_ex charset=utf8;
Query OK, 1 row affected (0.06 sec)
Copy the code

Install flask-SQLAlchemy extension

pip install -U Flask-SQLAlchemy
Copy the code

Python2: To connect to the mysql database, you still need to install flask-mysqlDB

pip install flask-mysqldb
Copy the code

Python3: To connect to the mysql database, you still need to install Pymysql

pip install pymysql
Copy the code

This chapter starts with python3.

Connect to mysql database using flask-SQLalchemy

To operate the database with the flask-SQLAlchemy extension, you first need to establish a database connection. Database connections are specified by URL, and the database used by the program must be saved into the SQLALCHEMY_DATABASE_URI key of the Flask configuration object.

Compare the database Settings in Django and Flask:

Django database setup:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql'.# change backend database to use mysql
        'NAME': 'mydb'.Set access database name
        'USER': 'root'.Mysql > access the mysql user name
        'PASSWORD': 'password'.Set access password
        'HOST': 'localhost'.Set the access IP address
        'PORT': 3306, Set the access port number}}Copy the code

Flask database setup:

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql: / / root: [email protected]:3306 / flask_ex'
Copy the code

The commonly used SQLAlchemy field type

Having seen how to set up a connection to a database, the basic field types used to create a data model using SQLAlchemy are as follows:

Type name	Python types	instructions
Integer	int	A common integer, usually 32 bits
SmallInteger	int	The value is an integer with a small range of 16 bits
BigInteger	Int or long	An integer that does not limit accuracy
Float	float	Floating point Numbers
Numeric	decimal.Decimal	A common integer, usually 32 bits
String	str	Variable length string
Text	str	Variable length strings, optimized for longer or unlimited strings
Unicode	unicode	Variable length Unicode string
UnicodeText	unicode	Variable-length Unicode strings, optimized for longer or unlimited strings
Boolean	bool	Boolean value
Date	datetime.date	time
Time	datetime.datetime	Date and time
LargeBinary	str	Binary file

Common SQLAlchemy column options

Option of	instructions
primary_key	If True, represents the primary key of the table
unique	If True, duplicate values are not allowed in this column
index	If True, create an index for this column to improve query efficiency
nullable	Null values are allowed if True, and not if False
default	Define default values for this column

Common SQLAlchemy relationship options

Option of	instructions
backref	Add a backreference to another model of the relationship
primary join	Explicitly specify join conditions to be used between the two models
uselist	If False, use a scalar value instead of a list
order_by	Specifies how the records in the relationship are sorted
secondary	Specifies how many-to-many records are sorted
secondary join	Specify secondary join conditions in many-to-many relationships when this cannot be decided by SQLAlchemy itself

There are a lot of basic options above the description of the database, the following basic add, delete and change operations to strengthen the understanding.

Basic Database operations

In Flask-SQLAlchemy, insert, modify, and delete operations are managed by a database session. Sessions are represented by db.session. Before you are ready to write data to the database, you add the data to the session and call commit() to commit the session.

Database sessions are used to ensure data consistency and avoid data inconsistency caused by partial updates. Commit writes the entire session object to the database. If something goes wrong during the write, the entire session is invalidated.

Database sessions can also be rolled back, using the db.session.rollback() method to achieve the state before the session submitted data.

In flask-SQLAlchemy, the query operation manipulates data through query objects. The most basic query returns all the data in the table, allowing for more precise database queries through filters.

Let’s start by creating a data model for two tables: the user table and the role table.

Define model classes in view functions

Having looked at the above concepts, let’s see how to create a data model and create a data table, as follows:

1. Create User and Role data models in script 15_sqlalchemy. py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import pymysql
pymysql.install_as_MySQLdb()

app = Flask(__name__)

class Config(object):
    """Configuration Parameters"""
    Set the URL to connect to the database
    user = 'root'
    password = '* * * * * * * *'
    database = 'flask_ex'
    app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql: / / % s: % [email protected]:3306 / % s' % (user,password,database)

    # set up SQLAlchemy to automatically track the database
    SQLALCHEMY_TRACK_MODIFICATIONS = True

    The original SQL statement will be displayed when the query is performed
    app.config['SQLALCHEMY_ECHO'] = True

    Disable automatic submission of data processing
    app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = False

# read config
app.config.from_object(Config)

Sqlalchemy tool object
db = SQLAlchemy(app)

class Role(db.Model):
    Define table name
    __tablename__ = 'roles'
    # define field
    id = db.Column(db.Integer, primary_key=True,autoincrement=True)
    name = db.Column(db.String(64), unique=True)
    users = db.relationship('User',backref='role') # push back multiple User model objects associated with roles

class User(db.Model):
    Define table name
    __tablename__ = 'users'
    # define fieldid = 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)) role_id = db.Column(db.Integer,  db.ForeignKey('roles.id')) Set the foreign key

if __name__ == '__main__':

    Drop all tables
    db.drop_all()

    Create all tables
    db.create_all()
Copy the code

2. Execute the script to create the database

python3 15_SQLAlchemy.py
Copy the code

3. View the created table structure in mysql

mysql> show tables;
+--------------------+
| Tables_in_flask_ex |
+--------------------+
| roles              |
| users              |
+--------------------+
2 rows in set (0.00 sec)

mysql> 
mysql> desc users;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(64) | YES  | UNI | NULL    |                |
| email   | varchar(64) | YES  | UNI | NULL    |                |
| pswd    | varchar(64) | YES  |     | NULL    |                |
| role_id | int(11)     | YES  | MUL | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> 
mysql> desc roles;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(64) | YES  | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set(0.00 SEC) mysql >Copy the code

Now that you have created the table, let’s look at how to add, delete, and modify the table.

Commonly used SQLAlchemy query filters

The filter	instructions
filter()	Returns a new query by adding a filter to the original query
filter_by()	Returns a new query by adding an equivalence filter to the original query
limit	Qualifies the result returned by the original query with the specified value
offset()	Offset the result returned by the original query to return a new query
order_by()	Returns a new query by sorting the results of the original query according to the specified criteria
group_by()	Returns a new query by grouping original query results according to specified criteria

The commonly used SQLAlchemy query executor

methods	instructions
all()	Returns all the results of the query as a list
first()	Returns the first result of the query, or None if not found
first_or_404()	Returns the first result of the query, or 404 if not found
get()	Returns the row corresponding to the specified primary key, or None if None exists
get_or_404()	Returns the row corresponding to the specified primary key, or 404 if none exists
count()	The number of query results returned
paginate()	Returns a Paginate object that contains the results in the specified range

Create a table:

db.create_all()
Copy the code

Delete table

db.drop_all()
Copy the code

Insert single data at a time

if __name__ == '__main__':

    Insert a role data
    role1 = Role(name='admin')
    db.session.add(role1)
    db.session.commit()

    Insert data again
    role2 = Role(name='user')
    db.session.add(role2)
    db.session.commit()
Copy the code

Execute the script:

python3 15_SQLAlchemy.py
Copy the code

Mysql > insert data into mysql

mysql> select * from roles \G
*************************** 1. row ***************************
  id: 1
name: admin
*************************** 2. row ***************************
  id: 2
name: user
2 rows in set (0.00 sec)
Copy the code

Insert multiple pieces of data at once

    Insert more data at once
    user1 = User(name='wang',email='[email protected]',pswd='123456',role_id=role1.id)
    user2 = User(name='zhang',email='[email protected]',pswd='201512',role_id=role2.id)
    user3 = User(name='chen',email='[email protected]',pswd='987654',role_id=role2.id)
    user4 = User(name='zhou',email='[email protected]',pswd='456789',role_id=role1.id)
    db.session.add_all([user1,user2,user3,user4])
    db.session.commit()
Copy the code

Perform insert data as follows:

python3 15_SQLAlchemy.py
Copy the code

Mysql > insert into mysql;

mysql> select * from users \G
*************************** 1. row ***************************
     id: 1
   name: wang
  email: [email protected]
   pswd: 123456
role_id: 1
*************************** 2. row ***************************
     id: 2
   name: zhang
  email: [email protected]
   pswd: 201512
role_id: 2
*************************** 3. row ***************************
     id: 3
   name: chen
  email: [email protected]
   pswd: 987654
role_id: 2
*************************** 4. row ***************************
     id: 4
   name: zhou
  email: [email protected]
   pswd: 456789
role_id: 1
4 rows in set(0.00 SEC) mysql >Copy the code

Mysql > insert row (s); insert row (s); insert row (s)

2019-11-23T16:48:56.984459Z	 9061 Query	INSERT INTO users (name, email, pswd, role_id) VALUES ('wang'.'[email protected]'.'123456', 1)
2019-11-23T16:48:56.997132Z	 9061 Query	INSERT INTO users (name, email, pswd, role_id) VALUES ('zhang'.'[email protected]'.'201512', 2)
2019-11-23T16:48:57.010175Z	 9061 Query	INSERT INTO users (name, email, pswd, role_id) VALUES ('chen'.'[email protected]'.'987654', 2)
2019-11-23T16:48:57.024134Z	 9061 Query	INSERT INTO users (name, email, pswd, role_id) VALUES ('zhou'.'[email protected]'.'456789'1),Copy the code

Values are not actually merged into an INSERT statement, but are inserted one at a time by multiple INSERT statements.

Query :filter_by Exact query

Return all users whose names are equal to wang

User.query.filter_by(name='wang').all()
Copy the code

Execute the following in the interaction model:

>python3 db_demo.py shell
In [1]: from db_demo import User

In [2]: User.query.filter_by(name='wang').all()
Out[2]: [<User 1>]

In [3]:
Copy the code

First () returns the first object queried

User.query.first()
Copy the code

Execute as follows:

In [3]: User.query.first()
Out[3]: <User 1>
Copy the code

All () returns all queried objects

User.query.all()
Copy the code

Execute as follows:

In [4]: User.query.all()
Out[4]: [<User 1>, <User 2>, <User 3>, <User 4>]

In [5]:
Copy the code

Filter Fuzzy query: returns all data whose names end with g.

User.query.filter(User.name.endswith('g')).all()
Copy the code

Execute as follows:

In [5]: User.query.filter(User.name.endswith('g')).all()
Out[5]: [<User 1>, <User 2>]
Copy the code

Get (), argument primary key, returns nothing if primary key does not exist

User.query.get()
Copy the code

Execute as follows:

In [6]: User.query.get(2)
Out[6]: <User 2>

In [7]: user2 = User.query.get(2)

In [8]: user2.name
Out[8]: 'zhang'
Copy the code

Logical no, returns all data whose name is not equal to wang.

User.query.filter(User.name! ='wang').all()
Copy the code

Execute as follows:

In [9]: User.query.filter(User.name! ='wang').all()
Out[9]: [<User 2>, <User 3>, <User 4>]
Copy the code

The logic and, which needs to import and*, returns all data that meets the and*() condition.

from sqlalchemy importand_ User.query.filter(and_(User.name! ='wang',User.email.endswith('163.com'))).all()
Copy the code

Execute as follows:

In [10]: from sqlalchemy import and_ In [15]: users = User.query.filter(and_(User.name! ='wang',User.email.endswith('163.com'))).all()

In [16]: for user inusers: ... :print(user.email) ... : [email protected]Copy the code

Logic or, need to import or_

from sqlalchemy importor_ User.query.filter(or_(User.name! ='wang',User.email.endswith('163.com'))).all()
Copy the code

Execute as follows:

In [17]: from sqlalchemy import or_ In [18]: users = User.query.filter(or_(User.name! ='wang',User.email.endswith('163.com'))).all()

In [19]: for user inusers: ... :print(user.name, user.email) ... : wang [email protected] zhang [email protected] chen [email protected] zhou [email protected] In [20]:Copy the code

Not_ is the same thing as taking negative

from sqlalchemy import not_
User.query.filter(not_(User.name=='chen')).all()
Copy the code

Execute as follows:

In [22]: from sqlalchemy import not_

In [25]: users = User.query.filter(not_(User.name=='chen')).all()

In [26]: for user inusers: ... :print(user.name, user.email) ... : wang [email protected] zhang [email protected] zhou [email protected]Copy the code

Query data and delete it

user = User.query.first()
db.session.delete(user)
db.session.commit()
User.query.all()
Copy the code

Execute as follows:

(venv) $ >python3 db_demo.py shell

In [1]: from db_demo import User

In [3]: user = User.query.first()

In [5]: from db_demo import db

In [6]: db.session.delete(user)

In [7]: db.session.commit()

In [8]: User.query.all()
Out[8]: [<User 2>, <User 3>, <User 4>]
Copy the code

Update the data

user = User.query.first()
user.name = 'dong'
db.session.commit()
User.query.first()
Copy the code

Execute as follows:

In [1]: from db_demo import User

In [5]: from db_demo import db

In [9]: user = User.query.first()

In [10]: user
Out[10]: <User 2>

In [11]: user.name
Out[11]: 'zhang'

In [12]: user.name = 'dong'

In [13]: db.session.commit()

In [14]: user = User.query.first()

In [15]: user.name
Out[15]: 'dong'
Copy the code

Use the update

User.query.filter_by(name='zhang').update({'name':'li'})
Copy the code

Execute as follows:

In [21]: User.query.filter_by(name='dong').update({'name':'li'})
Out[21]: 0

In [22]: User.query.get(2)
Out[22]: <User 2>

In [23]: user = User.query.get(2)

In [24]: user.name
Out[24]: 'li'
Copy the code

Associative query example: The relationship between roles and users is one-to-many. A role can have multiple users, and a user can belong to only one role.

Implementing associated query of all users of a role:

Select * from roles where id = 1
role1 = Role.query.get(1)
# Query all users of this role
role1.users
Copy the code

Execute as follows:

In [25]: from db_demo import Role

In [26]: role1 = Role.query.get(1)

In [27]: role1.users
Out[27]: [<User 4>]

In [28]: role2 = Role.query.get(2)

In [29]: role2.users
Out[29]: [<User 2>, <User 3>]
Copy the code

Implementing associated query of user roles:

Select * from users where id = 3
user1 = User.query.get(3)
What role does the user belong to
user1.role
Copy the code

Execute as follows:

In [30]: user1 = User.query.get(3)

In [31]: user1.role
Out[31]: <Role 2>
Copy the code