The use of flask SQLAlchemy encapsulates and optimizes SQLAlchemy:

• Flask Sqlalchemy is a plug-in of the flask framework
• Flask SQLAlchemy is a plug-in that simply encapsulates SQLAlchemy
• It makes it easier for us to use sqlalchemy in Flask.

1. Installation:

pip install flask-sqlalchemy

2. Key points of using flask Sqlalchemy:

2.1 database connection

Database initialization is no longer through create_engine.

1. Like sqlalchemy, define the database connection string DB_URI.
2. The defined database connection string DB_URI, via Sqlalchemy_ DATABASE_ The key name URI is configured to app Config.
   code:

app.config["SQLALCHEMY_DATABASE_URI"] = DB_URI

1. Using flash_ sqlalchemy. Sqlalchemy class defines an object and passes in the app.

db = SQLAlchemy(app)

2.2 create ORM model class

Previously, it was through Base = declarative_base() to initialize a base class and then inherit. It's easier in flask Sqlalchemy.

1. It is the same as using sqlalchemy to define the model. It is no longer necessary to use delarative_base to create a base class. Instead, use dB Model as the base class.
2. In the model class, columns, strings, integers and relationship s do not need to be imported. You can directly use the corresponding attribute names under db.

from flask_sqlalchemy import SQLAlchemy

1. When defining the model, you can not write__ tablename__， So flash_ Sqlalchemy will convert the name of the current model to lowercase by default as the name of the table,
2. And if the name of the model uses multiple words and uses the hump naming method, underline will be used between multiple words, although flash_ Sqlalchemy provides us with this feature, but it is not recommended. (enhance code readability and improve teamwork efficiency) app config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

code:

#2. Create model class
class User(db.Model):   #db.Model: fixed writing
    __tablename__='t_user'
    id=db.Column(db.Integer,primary_key=True,autoincrement=True)
    uname=db.Column(db.String(50))
    pwd=db.Column(db.String(50))

    def __repr__(self):
        return f'user name:{self.uname}~~password:{self.pwd}'

2.3 mapping ORM model to database table

After writing the model class, to map the model to the table in the database, use the following code

1. Delete database table: dB drop_ all()
2. Create database table: dB create_ all()

Execution code: the data table was created successfully

db.create_all()

However, a warning appears when you start the project

The solution is to add a line of code:

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

2.4 use of session

There is no need to use sessionmaker to create sessions in the future,
Direct use of DB Session is OK,
The operation of this session is the same as the previous sqlalchemy session.

2.5 adding data

The added data is no different from the previous one, except that session has become a db attribute

@app.route('/create')
def create():
    name=request.args.get('name')
    pwd=request.args.get('pwd')
    print(name,pwd)
    #Insert into database table t_ In user
    user=User(uname=name,pwd=pwd)
    db.session.add(user)
    db.session.commit()
    return 'Added successfully'

2.6 query data

1. Single table query
The query data is no longer the previous session Instead of the query method, the query attribute is placed in dB Model, so the query is carried out through "model name. Query". Query is the same as the query method in sqlalchemy before.
2. Multi table query
If the lookup data involves multiple models, you can only use dB session. Query (model name) all() this way

@app.route('/query')
def query():
    id=request.args.get('id')
    user=db.session.query(User).filter(User.id==id).first()
    print(user)
    return "The query is successful. The found user name is{}".format(user.uname)

2.7 data modification:

There is no difference between the modified data and the previous one, except that session has become a db attribute

@app.route('/update')
def update():
    id=request.args.get('id')
    name=request.args.get('name')
    pwd=request.args.get('pwd')
    #Get data with id=id
    res=db.session.query(User).filter(User.id==id).first()
    print(res)
    res.uname=name
    res.pwd=pwd
    db.session.add(res)
    db.session.commit()
    return "Modified successfully"

2.8 delete data:

Deleting data is similar to adding and modifying data, except that session is an attribute of db

@app.route('/delete')
def delete_user():
    name=request.args.get('name')
    user=User.query.filter(User.uname==name).first()
    if user:
        db.session.delete(user)
        db.session.commit()
    return "Deleted successfully"