Flash operation Mysql database - Flash Sqlalchemy extension

Posted by samscripts on Sat, 29 Jan 2022 08:51:48 +0100

Official website document

https://flask-sqlalchemy.palletsprojects.com/en/master/quickstart/

Database settings

The relational model database is widely used in Web applications. The relational database stores all data in the table. The table is used to model the entity of the application. The number of columns in the table is fixed and the number of rows is variable. It uses a structured query language. The columns of a relational database define the data attributes of the entities represented in the table. For example, there are name, price, number, etc. in the product list. Flash itself does not limit the choice of database. You can choose either SQL or NOSQL. You can also choose a more convenient SQLALchemy, similar to Django's ORM. SQLALchemy is actually the database www.meimeimeitu8 Com abstraction allows developers to operate the database through Python objects instead of directly dealing with SQL statements. While discarding some performance overhead, it is in exchange for a great improvement in development efficiency.

SQLAlchemy is a relational database framework, which provides the operation of high-level ORM and low-level native database. Flash SQLAlchemy is a flash extension that simplifies SQL alchemy operations.

The following uses mysql as an example.

Create mysql database

1. Log in to the database

mysql -u root -p password

2. Create a database and set the code

create database <Database name> charset=utf8;

3. Display all databases

show databases;

4. The implementation is as follows:

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

Install the extension of flash Sqlalchemy

pip install -U Flask-SQLAlchemy

python2: to connect to mysql database, you still need to install flash mysql dB

pip install flask-mysqldb

Python 3: to connect to mysql database www.rsxedu.com COM, you still need to install pymysql

pip install pymysql

This chapter begins with Python 3.

Use flask Sqlalchemy to connect to mysql database

To use Flask Sqlalchemy to expand the operation database, you first need to establish a database connection. The database connection is specified by URL, and the database used by the program must be saved to Sqlalchemy of the flash configuration object_ DATABASE_ Uri key.

Compare the database settings in Django and Flask:

Django's database settings:

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql', # Modify the backend database and use mysql
'NAME': 'mydb', # Set access database name
'USER': 'root', # Access mysql user name
'PASSWORD': 'password', # Set access password
'HOST': 'localhost', # Set access ip address
'PORT': 3306, # Set access port number
}
}

Database settings for flash:

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@127.0.0.1:3306/flask_ex'

Common SQLAlchemy field types

After reading how to set up the connection database, let's take a look. When using SQLAlchemy to create a data model, the basic field types are as follows:

Type name	Types in python	explain
Integer	int	Ordinary integer, usually 32 bits
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
Numeric	decimal.Decimal	Ordinary integer, usually 32 bits
String	str	Variable length string
Text	str	Variable length string, which is optimized for long or unlimited length strings
Unicode	unicode	Variable length Unicode string
UnicodeText	unicode	Variable length Unicode string, optimized for long or unlimited length strings
Boolean	bool	Boolean value
Date	datetime.date	time
Time	datetime.datetime	Date and time
LargeBinary	str	Binary file

Common SQLAlchemy column options

Option name	explain
primary_key	If True, it represents the primary key of the table
unique	If True, it means that duplicate values are not allowed in this column
index	If True, create an index for this column to improve query efficiency
nullable	If True, null values are allowed; if False, null values are not allowed
default	Define default values for this column

Common SQLAlchemy relationship options

Option name	explain
backref	Add reverse references to the first mock exam in another model of the relationship.
primary join	Explicitly specify the join conditions used between the two models
uselist	If False, scalar values are used instead of lists
order_by	Specifies how records in the relationship are sorted
secondary	Specifies how records in many to many are sorted
secondary join	Specify secondary join conditions in many to many relationships when you cannot decide for yourself in SQLAlchemy

There are descriptions of many basic options above. Let's add, delete and modify the database to enhance understanding.

Basic operation of database

In flask Sqlalchemy, insert, modify and delete operations are managed by the database session. Session dB Session representation. Before you write the data to the database, you first add the data to the session and then call the commit() method to submit the session.

Database session is to ensure data consistency and avoid data inconsistency caused by partial update. The commit operation writes all session objects into the database. If an error occurs in the writing process, the whole session will become invalid.

The database session can also be rolled back through dB session. The rollback () method implements the state before the session submits data.

In flask Sqlalchemy, query operation is to manipulate data through query object. The most basic query is to return all the data in the table. You can query the database more accurately through the filter.

Let's create the data model of two tables: user table and role table.

Define model classes in view functions

After reading so many concept descriptions above, let's take a look at how to create a data model and create a data table, as follows:

1. In script 15_SQLAlchemy.py writing to create User and Role data models

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

app = Flask(__name__)

class Config(object):
"""configuration parameter"""
# Set the URL to connect to the database
user = 'root'
password = '********'
database = 'flask_ex'
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://%s:%s@127.0.0.1:3306/%s' % (user,password,database)

# Set sqlalchemy to automatically track the database
SQLALCHEMY_TRACK_MODIFICATIONS = True

# The original SQL statement is displayed when querying
app.config['SQLALCHEMY_ECHO'] = True

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

# Read configuration
app.config.from_object(Config)

# Create database 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') # Reverses multiple User model objects associated with role s

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

if __name__ == '__main__':

# Delete all tables
db.drop_all()

# Create all tables
db.create_all()

Execute the script and create the database

python3 15_SQLAlchemy.py

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>

After creating the data table, let's see how to add, delete, query and modify the data.

Common SQLAlchemy query filters

filter	explain
filter()	Add the filter to the original query and return a new query
filter_by()	Add the equivalent filter to the original query and return a new query
limit	Use the specified value to limit the results returned by the original query
offset()	Offset the result returned by the original query and return a new query
order_by()	Sort the original query results according to the specified criteria and return a new query
group_by()	Group the original query results according to the specified criteria and return a new query

Common SQLAlchemy query executors

method	explain
all()	Returns all the results of the query as a list
first()	Returns the first result of the query. If it is not found, it returns None
first_or_404()	Returns the first result of the query. If it is not found, it returns 404
get()	Returns the row corresponding to the specified primary key. If it does not exist, returns None
get_or_404()	Returns the row corresponding to the specified primary key. If it does not exist, it returns 404
count()	Number of returned query results
paginate()	Returns a Paginate object that contains the results within the specified range

Create table:

db.create_all()

Delete table

db.drop_all()

Insert a single piece of data at a time

if __name__ == '__main__':

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

# Insert another piece of data
role2 = Role(name='user')
db.session.add(role2)
db.session.commit()

Execute script:

python3 15_SQLAlchemy.py

View the inserted data in mysql as follows:

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

Insert multiple pieces of data at one time

# Insert multiple pieces of data at one time
user1 = User(name='wang',email='wang@163.com',pswd='123456',role_id=role1.id)
user2 = User(name='zhang',email='zhang@189.com',pswd='201512',role_id=role2.id)
user3 = User(name='chen',email='chen@126.com',pswd='987654',role_id=role2.id)
user4 = User(name='zhou',email='zhou@163.com',pswd='456789',role_id=role1.id)
db.session.add_all([user1,user2,user3,user4])
db.session.commit()

Insert data as follows:

python3 15_SQLAlchemy.py

Query the inserted data in mysql as follows:

mysql> select * from users \G
*************************** 1. row ***************************
id: 1
name: wang
email: wang@163.com
pswd: 123456
role_id: 1
*************************** 2. row ***************************
id: 2
name: zhang
email: zhang@189.com
pswd: 201512
role_id: 2
*************************** 3. row ***************************
id: 3
name: chen
email: chen@126.com
pswd: 987654
role_id: 2
*************************** 4. row ***************************
id: 4
name: zhou
email: zhou@163.com
pswd: 456789
role_id: 1
4 rows in set (0.00 sec)

mysql>

Although it seems that multiple pieces of data are inserted at one time in python, in fact, multiple line insertion statements are also executed in mysql. You can see the following from the mysql log:

2019-11-23T16:48:56.984459Z 9061 Query INSERT INTO users (name, email, pswd, role_id) VALUES ('wang', 'wang@163.com', '123456', 1)
2019-11-23T16:48:56.997132Z 9061 Query INSERT INTO users (name, email, pswd, role_id) VALUES ('zhang', 'zhang@189.com', '201512', 2)
2019-11-23T16:48:57.010175Z 9061 Query INSERT INTO users (name, email, pswd, role_id) VALUES ('chen', 'chen@126.com', '987654', 2)
2019-11-23T16:48:57.024134Z 9061 Query INSERT INTO users (name, email, pswd, role_id) VALUES ('zhou', 'zhou@163.com', '456789', 1)

In fact, multiple values are not merged into one insert statement, and multiple insert statements are still inserted one by one.

Query: filter_by exact query

Returns all user s whose name is equal to wang

User.query.filter_by(name='wang').all()

In the interaction model, execute the following:

>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]: