Catalog

Create data table

Add data

Insert a single piece of data

Insert multiple data

Query data

All inquiries

Precise query

Fuzzy query

Primary key query

Sort

Modifying data

Delete data

Delete data table

In the last article, we learned Flask frame —— Database configuration and migration synchronization , This article is for us to learn Flask frame —— Database operation command （ Additions and deletions ）.

First let's create one Flask project , Its directory is shown below ：

The configuration file settings.py The code is as follows ：

class Configs:
    ENV='development'
    DEBUG=True
    #  Set the connection database path 
    SQLALCHEMY_DATABASE_URI='mysql+pymysql://root:[email protected]:3306/test'
    #  Automatically submit changes in the database after each request 
    SQLALCHEMY_COMMIT_ON_TEARDOWN=True
    #  Ban SQLAlchemy Modify the tracking object and send a signal 
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    #  Display the original when operating the database SQL sentence 
    SQLALCHEMY_ECHO=True

Write it well settings.py After profile , We begin to write the import configuration 、 Code such as mapping objects and data model classes , Because there is less code , It is convenient for us to view and use , I'll write these codes in app.py It's in the document , Be careful ： In actual development , The data model and mapping objects are written in other files .

from flask import Flask
import settings
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
app = Flask(__name__)

app.config.from_object(settings.Configs)  #  load flask Project configuration 

db=SQLAlchemy(app)    #  Create a mapping object , And bound to app in 

#  Create model classes 
class User(db.Model):
    # set a table name 
    __tablename__ = 'user'
    #  Create database table fields 
    # db.Column( type , constraint )
    id=db.Column(db.Integer,primary_key=True,autoincrement=True)
    username=db.Column(db.String(15),nullable=False)
    password=db.Column(db.String(15),nullable=False)
    phone=db.Column(db.String(11),unique=True)
    rdatetime=db.Column(db.DateTime,default=datetime.now)

if __name__ == '__main__':
    app.run()

Okay , The basic code has been written , Next, we will formally add, delete, modify and check the database .

Create data table

To create a data table, just write the following code ：

db.create.all()

db.create_all() Method will look for all db.Model Data model class , Create a data table according to the data model class , Here we only create a data model class . We are pycharm Through database tools Database To observe the changes in the database ,

The results are shown in the following figure ：

In the terminal command line , We can see the primitive SQL And in test A... Is created in the database user Data sheet .

Add data

Insert a single piece of data

user1=User(username='zhagsan',password='1245',phone='13700100000')  # Create instance object 
db.session.add(user1)  # insert data 
db.session.commit()   # Commit transaction 

First, create an instance object of the model class , Reuse db.session.add() Insert the parameters in the instance object into the database , Finally using db.session.comit() To save our changes to the database .

Insert multiple data

user1=User(username='huan',password='123',phone='13700000000')  # Create a model class instance object 
user2=User(username='ying',password='456',phone='13700010000')
user3=User(username='xue',password='789',phone='13700020000')
db.session.add_all([user1, user2, user3])        # insert data 
db.session.commit()            # Commit transaction 

Inserting multiple pieces of data is similar to inserting a single piece of data , Inserting multiple pieces of data means creating instance objects of multiple model classes , Then put the instance object in the list , Reuse db.session.add_all() Method is inserted into the database , Finally using db.session.comit() To save our changes to the database .

Query data

Query data , We can use Flask-SQLAlchemy in Model Class provides the query Property to query .

All inquiries

Only one code is needed to query all the data , The code is shown below ：

User.query.all() 

Precise query

User.query.filter_by(username='huan').first()  # The query name is huan The data of 

adopt User.query.filter_by() Method to query , And then through first() Method to return the first query result . Here we choose the query name huan The data of .

When there is no data in the data we want to query , Will return None.

Fuzzy query

When we need to query the data of some data features , How to query ？

User.query.filter(User.phone.endswith('3000')).first()  # Get the ending number of the mobile phone as 3000 The data of 
User.query.filter(User.name!='huan').all()       # Getting the user name is not huan All data for 

# Import and Method , Implement logic and query 
from sqlalchemy import and_
User.query.filter(and_(User.name!='huan',User.phone.endswith('3000'))).all()  # Getting the user name is not huan And the ending number of the mobile phone is 3000

# Import or Method , Implement logic or query 
from sqlalchemy import or_
User.query.filter(or_(User.name!='huan',User.email.endswith('3000'))).all()   # Getting the user name is not huan Or the tail number of the mobile phone is 3000

# Import not, Realize reverse query 
from sqlalchemy import not_
User.query.filter(not_(User.name=='huan')).all()  # Getting the user name is not huan All data for 

adopt User.phone.endswith() Method to select the data content with which the query ends , adopt User.query.filter() Method to query , Finally using first() Method returns the first query result or all() Method gets all the data of the query and returns a list .

Of course, in addition to querying the data that ends with , You can also choose what data starts with 、 Data of what content it contains , The code is shown below ：

 Model class . attribute .startswith()    # Data starting with what 
 Model class . attribute .endswith()    # Data ending with what 
 Model class . attribute .contains()    # What data contains 

When the field we query is of integer type or date type , You can also use the following code ：

 Model class . attribute .__lt__(18)                # Less than 18
 Model class . attribute .__gt__(18)        # Greater than 18
 Model class . attribute .__ge__(18)        # Greater than or equal to 18
 Model class . attribute .__le__(18)        # Less than or equal to 18
 Model class . attribute .between(18,30)       #18 To 30 Between 

Of course , It can also be used directly > 、< 、= A sign to indicate .

Primary key query

We can query the data through the primary key in the data , The code is as follows ：

User.query.get(1)

Different from other query methods , Querying data through the primary key uses .get() Method whose parameters are key values , If the primary key does not exist, no content is returned .

Of course , We can choose the number of returned data .

User.query.limit(2).all()     # Get the first two pieces of all data 
User.query.offset(2).limit(2).all()    # Skip the first two data in all data and get the first two data after skipping 

Use User.query.limit() Method to return the number of pieces of data , The parameter passed is the number of returned data .

Sort

query By default, press id Sort in ascending order , When you encounter complex situations, you need to use order_by.

# Model class .query.order_by( Parameters )
User.query.order_by(User.id).all    # For all the id Sort 

When we want to arrange data in reverse order , Just put... In the front - That's all right. .

Modifying data

When we want to modify data , You can first create a variable to receive the data to be modified , Modify the corresponding attribute value through this variable , Finally through db.session.commit() Method commit transaction , The code is as follows ：

user = User.query.first() # Select the data to modify 
user.username = 'xiu'  # Parameter value to be modified 
db.session.commit()   # Commit transaction 

Of course, you can also modify all the data , The code is as follows ：

user = User.query.all()  # Select all data to be modified 
for i in user:      # Loop traversal 
    i.name = 'dong'     # Parameter value to be modified 
    db.session.commit()    # Commit transaction 

First, through User.query.all() To get all the data and store it in user variable , Be careful all() A list is returned , So want to use for Loop to traverse , Then modify the data according to your own needs , Finally through db.session.commit() Method commit transaction .

Delete data

Deleting data is very similar to adding data , First, create variables to store the data to be deleted , Re pass db.session.delete() To delete data , And use db.session.commit() Method commit transaction .

user = User.query.first()  
db.session.delete(user)
db.session.commit()

You can find , To modify or delete data, you should first query and receive the data to be modified or deleted , So we can select the data we want to modify or delete according to the method of querying data .

Delete data table

It's easy to delete a data table , Just one piece of code , The code is shown below ：

db.drop_all()

By writing this code , The program will delete the name of the table named as the table name of the created data table in the model class .

Okay ,Flask Framework expansion pack ——flask--sqlalchemy That's all for the database operation commands , Thank you for watching. , Continue to learn in the next article Flask frame —— Model relationship （ One to many ）.

official account ： White chocolate LIN

- END -