Flask Starter series of database integration!

This is the 18th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021.

A database is the infrastructure of most Web applications, and you need to store data in a database. Here we will learn how to add database support for Flask applications.

Flask-sqlalchemy to manage the database

Flask-sqlalchemy integrates SQLAlchemy, which simplifies the tasks of connecting to the database server, managing the database and operating sessions, and makes it easier for Flask to store and process data. You can easily add, delete, change, and query database tables using Python classes, and the plugin supports multiple database types such as MySQL, PostgreSQL, and SQLite.

This can be done using PIP Install Flask-SQLalchemy.

Configuration Flask_SQLAlchemy

Flask-sqlalchemy = SQLite; flask-SQLAlchemy = SQLite; flask-SQLAlchemy = SQLite; Flask’s instance app is passed through the SQLAlchemy class provided by Flask-SQLAlchemy to create a DB instance representing the database used by the application. This DB object can use all the functionality of Flask-SQLAlchemy.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

basedir = os.path.abspath(app.root_path)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(basedir, 'data.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)
Copy the code

The SQLALCHEMY_TRACK_ MODIFICATIONS configuration variable represents whether to track changes to an object, which is set to False here.

Defining the database model

A data model is a Python class used to map database tables. A data model class corresponds to a table in the database, and the attributes of the class represent the fields of the database table. All Model classes need to inherit from flask-SQLAlchemy which provides the db.model base class.

Create a new model.py file and define the User class as follows:

from app import db


class User(db.Model) :
    id = db.Column(db.Integer, primary_key=True)
    user_name = db.Column(db.String)
    password = db.Column(db.String)
Copy the code

Create databases and tables

Run the Flask application, and then enter the Flask shell command in the terminal to enter the Python interactive environment, as shown below:

>>> from app import db  # import db instance from app.py
>>> from model import *  Import all model classes from model.py
>>> db.create_all()  Create table with db.create_all()
Copy the code

After execution, a data.db library file is generated in the project root directory. Note: Once the database and table are created, subsequent changes to the model class do not automatically apply to the actual table. For example, adding or deleting fields in the model class, changing the name and type of the field, and calling db.create_all() again does not recreate or update the table. Db.drop_all () can only be used to drop all tables in the database and then call db.create_all().

In this way, the database table was deleted and rebuilt, and the original data in the table is not there, which is certainly not good, then there is the concept of database migration, first leave a pit, introduced in the next article.

Database operations

Now that we have created the model and generated the database and tables, it’s time to learn about common database operations. Database operations are mainly CRUD (Create Create, Read Read/query, Update Update, Delete Delete).

Create

To add a record to the database, there are three main steps:

• Use Python model classes to create objects as a record
• Adds the newly created object to the database session
• Commit database session

Create a new user in the interaction environment above:

>>> from app import db  # import db instance from app.py
>>> from model import User   # import model class User
>>> user1=User(user_name='tigeriaf', password='123456')  Create user 1
>>> user2=User(user_name='admin', password='123456')  Create user 2
Add the new object to the database session
>>> db.session.add(user1)
>>> db.session.add(user2)
Commit database session and write data to data.db file
>>> db.session.commit()
# test
>>> print(user1.id)
1
>>> print(user2.id)
2
Copy the code

Also, in addition to calling the add() method in turn to add records, you can use add_all() to add a list of multiple model class objects at once.

Read

Using model classes

Various filtering methods can be invoked to query the data of a database table by attaching the query attribute provided by the model class. The query pattern is as follows:

< model class >.query.< filter method >.< query method >

Starting from a model class, filtering methods and query functions are added to query objects corresponding to Query attributes to filter the entries in the tables corresponding to model classes, etc. Finally, model class instances containing corresponding database record data are returned, and corresponding field data can be obtained by calling the attributes of the returned instances.

# select * from all
>>> User.query.all()
[<User 1>, <User 2>]
Select * from record where id = 0
>>> user1=User.query.get(1)
>>> user1.user_name
'tigeriaf'
# query the number of entries
>>> User.query.count()
2
Select * from user_name where user_name is admin
>>> User.query.filter_by(user_name='admin').all()
[<User 2>]
Copy the code

SQLAlcherny provides a number of filtering methods that can be used to obtain more accurate queries, which will not be expanded here. For a complete Query and filtering method, see The Query Object.

Update

Updating a record is as simple as attaching new values to the properties of the model class, and then calling commit() to commit the session. Change the user name user_name of user 2 as follows:

>>> user2=User.query.get(2)
>>> user2.user_name
'admin'
>>> user2.user_name='Joe'
>>> db.session.commit()
>>> user2.user_name
'Joe'
Copy the code

Delete

Use delete() to delete the data record as follows:

>>> db.session.delete(user2)
>>> db.session.commit()
>>> user2=User.query.get(2)
>>> print(user2)
None
Copy the code

Manipulate the database in view functions

The way you manipulate a database in a view function is pretty much the same in a Python Shell interaction, except that there are more steps to get data from the request object and validate the data, as shown in the following example:

@app.route('/user', methods=['GET'.'POST'])
def user() :
    if request.method == 'POST':
        user_name = request.form['user_name']
        password = request.form['password']
        user = User(user_name=user_name, password=password)
        db.session.add(user)
        db.session.commit()
        return 'user:{} add success! id:{}'.format(user_name, user.id)

    else:
        user_id = request.args.get('user_id')
        user = User.query.get(user_id)
        if user:
            return 'Hello user:{}! '.format(user.user_name)
        else:
            return 'failed'
Copy the code

In the above code, the view function user accepts the requests of the two ways, completes the functions of adding users and querying users respectively, and stores the received data in the database.

The test for sending a request is as follows:

Original is not easy, if small partners feel helpful, please click a “like” and then go ~

Finally, thank my girlfriend for her tolerance, understanding and support in work and life!