1. ORM

Object-relation mapping, which implements relational mapping, implements mapping of Python model objects to relational databases

• advantages
  • Use Python only for object-oriented operations, not SQL
  • The decoupling of data model and database is realized, and the same operation of different databases is realized
• disadvantages

  • The conversion between Python and SQL language is slow

  • Different ORMs operate differently from one another

2. Flask-SQLAlchemy

Is a relational database framework that provides both high-level ORM and low-level native database operations.

2.1 installation

Flask – SqlAlchemy installation

pip install flask-sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple --user
Copy the code

If you use the MySQL database, you need to install the MySQLdb driver

pip install flask-mysqldb -i https://pypi.tuna.tsinghua.edu.cn/simple
Copy the code

2.2 Database Connection Settings

class Config(object) :
    DEBUG = True
    Database connection configuration
    # SQLALCHEMY_DATABASE_URI = "database type :// database account: password @database address: port/database name? charset=utf8mb4"
    SQLALCHEMY_DATABASE_URI = "Mysql: / / root: 123 @127.0.0.1:3306 / students? charset=utf8mb4"
    # Dynamic tracking change Settings, if not set will only prompt a warning
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    The original SQL statement will be displayed when the query is performed
    SQLALCHEMY_ECHO = True

app.config.from_object(Config)

Copy the code

Creating a database

mysql -uroot -p123
create database students charset=utf8mb4;
Copy the code

2.3 SQLAlchemy Field types and constraint types

The field type

• Integer Indicates a common integer
• smallinteger
• biginteger
• float
• numeric
• string
• text
• unicode
• unicodetext
• boolean
• date
• time
• largebinary

Constraint type

• primary_key
• unique
• index
• nullable
• default

2.4 Operation of data tables

1 create a table

from flask import Flask
app = Flask(__name__)

class Config(object) :
    DEBUG = True
    SQLALCHEMY_DATABASE_URI = "Mysql: / / root: 123 @127.0.0.1:3306 / students? charset=utf8mb4"
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    The original SQL statement will be displayed when the query is performed
    SQLALCHEMY_ECHO = True

app.config.from_object(Config)


"" model class definition ""
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app=app)

All models must directly or indirectly inherit from db.model
class Student(db.Model) :
    """ Student information Model ""
    Declare the name of the data table bound to the current model
    __tablename__ = "db_students"
    # field definition
    id = db.Column(db.Integer, primary_key=True,comment="Primary key")
    name = db.Column(db.String(15), comment="Name")
    age = db.Column(db.SmallInteger, comment="Age")
    sex = db.Column(db.Boolean, default=True, comment="Gender")
    email = db.Column(db.String(128), unique=True, comment="Email address")
    money = db.Column(db.Numeric(10.2), default=0.0, comment="Wallet")

    def __repr__(self) :
        return f"{self.name}<Student>"

class Course(db.Model) :
    "" course Data Model ""
    __tablename__ = "db_course"
    id = db.Column(db.Integer, primary_key=True, comment="Primary key")
    name = db.Column(db.String(64), unique=True, comment="Course")
    price = db.Column(db.Numeric(7.2))
    The # repr() method is similar to Django's __str__ and is used to print the string information displayed on model objects
    def __repr__(self) :
        return f'{self.name}<Course>'

class Teacher(db.Model) :
    """ Teacher data Model """
    __tablename__ = "db_teacher"
    id = db.Column(db.Integer, primary_key=True, comment="Primary key")
    name = db.Column(db.String(64), unique=True, comment="Name")
    option = db.Column(db.Enum("Lecturer"."助教"."Head teacher"), default="Lecturer")

    def __repr__(self) :
        return f"{self.name}< Teacher >"

@app.route("/")
def index() :
    return "ok！"

if __name__ == '__main__':
    with app.app_context():
        Check if there is a table in the database that matches the model.
        If not, the table is built according to the table construction clause of model transformation.
        # If found, no additional processing is performed
        db.create_all()
    app.run(debug=True)
Copy the code

 with app.app_context():
    The # create_all() method needs to be placed after the model when executed
    Check if there is a table in the database that matches the model.
    If not, the table is built according to the table construction clause of model transformation.
    # If found, no additional processing is performed
    db.create_all()
Copy the code

Delete table 2

db.drop_all()

if __name__ == '__main__':
    with app.app_context():
        # db.drop_all()
        Check if there is a table in the database that matches the model.
        If not, the table is built according to the table construction clause of model transformation.
        # If found, no additional processing is performed
        db.create_all()
    app.run(debug=True)
Copy the code

2.5 Data Operations

1 Create a data entry

Method 1:

# execute in student class
 @classmethod
    def add(cls) :
        student = cls(name="Xiao Ming", sex=True, age=17, email="[email protected]", money=100)
        db.session.add(student)
        db.session.commit()
        return student
Copy the code

Method 2: student.add ()

@app.route("/add")
def add() :
    """ Add a data """
    student = Student.add()
    print(student)

    student = Student(name="Little red",age=18,sex=False, email="[email protected]", money=1000)
    db.session.add(student)
    db.session.commit()
    return "ok！"
Copy the code

2 Create multiple sets of data

• Add_all () method

@app.route("/add_all")
def add_all() :
    """ Add multiple pieces of data ""
    st1 = Student(name='wang', email='[email protected]', age=22, money=1000, sex=True)
    st2 = Student(name='zhang', email='[email protected]', age=22, money=1000, sex=True)
    st3 = Student(name='chen', email='[email protected]', age=22, money=1000, sex=True)
    st4 = Student(name='zhou', email='[email protected]', age=22, money=1000, sex=True)
    st5 = Student(name='tang', email='[email protected]', age=22, money=1000, sex=True)
    st6 = Student(name='wu', email='[email protected]', age=22, money=1000, sex=True)
    st7 = Student(name='qian', email='[email protected]', age=22, money=1000, sex=True)
    st8 = Student(name='liu', email='[email protected]', age=22, money=1000, sex=True)
    st9 = Student(name='li', email='[email protected]', age=22, money=1000, sex=True)
    st10 = Student(name='sun', email='[email protected]', age=22, money=1000, sex=True)
    db.session.add_all([st1,st2,st3,st4,st5,st6,st7,st8,st9,st10])
    db.session.commit()

    return "ok"
Copy the code

3 Deleting Data

@app.route("/delete")
def delete() :
    """ Delete data """
    # query first
    student = Student.query.first()
    print(student)
    # delete
    db.session.delete(student)
    db.session.commit()

    # Direct conditional delete [better performance!!]
    Student.query.filter(Student.id > 5).delete()
    db.session.commit()
    return "ok"
Copy the code

4 Updating Data

@app.route("/update")
def update() :
    """ Update data """
    SQL > query data, then update, 2 statements
    stu = Student.query.first()
    stu.name = 'dong'
    db.session.commit()

    # Direct conditional update, one statement [optimistic locking]
    Student.query.filter(Student.name == 'chen').update({'money': 1998})
    db.session.commit()

    # field reference [use the dictionary value of the current data as an aid to achieve a similar effect to the F function in Django]
    Student.query.filter(Student.name == "zhang").update({"money":Student.money+1000 * Student.age})
    db.session.commit()
    return "ok"
Copy the code

The total code

from flask import Flask
app = Flask(__name__)

class Config(object) :
    DEBUG = True
    SQLALCHEMY_DATABASE_URI = "Mysql: / / root: 123 @127.0.0.1:3306 / students? charset=utf8mb4 SQLALCHEMY_TRACK_MODIFICATIONS = True SQLALCHEMY_ECHO = True app.config.from_object(Config) """Model class definition""" from flask_sqlalchemy import SQLAlchemy db = SQLAlchemy(app=app) class Student(db.Model): __tablename__ =" db_Students "ID = db.Column(db.Integer, primary_key=True,comment=" primary key ") Name = db.Column(db.string (15), Column(db.SmallInteger, comment=" age ") sex = db.Column(db.Boolean, default=True, Comment =" Numeric ") email = db.column (db.string (128), unique=True, comment=" email ") money = db.column (db.numeric (10,2), Default =0.0, comment=" ") def __repr__(self): return f"{self.name}
      
       " @classmethod def add(CLS): Student = CLS (name="小 小 ", sex=True, age=17, email="[email protected]", Add (student) db.session.com MIT () return student # All models must be directly or indirectly derived from db.model class Course(db.model): "" "
      Course data model""" __tablename__ = "db_course" id = db.Column(db.Integer, primary_key=True, Name = db.column (db.string (64), unique=True, comment=" class ") price = db.column (db.numeric (7, 2)) # repr() is similar to Django's __str__, which is used to print the string information displayed on the model object def __repr__(self): return f'{self.name}
      
       ' class Teacher(db.Model): """
      Teacher data model""" __tablename__ = "db_teacher" id = db.Column(db.Integer, primary_key=True, Column(db.string (64), unique=True, comment=" 新 ") option = db.column (db.enum (" 新 ", "新 "," 新 "), Default = "lecturer") def __repr__ (self) : return f "{self. The name} < the Teacher >" @ app. The route ("/add ") def add () : "" "Add a piece of data# print(student) # print(student) # print(student) # print(student) # print(student) # print(student) Money =1000) # db.session.add(student) # db.session.com MIT () return "ok!" @app.route("/add_all") def add_all(): """Adding Multiple Pieces of Data""" st1 = Student(name='wang', email='[email protected]', age=22, money=1000, sex=True) st2 = Student(name='zhang', email='[email protected]', age=22, money=1000, sex=True) st3 = Student(name='chen', email='[email protected]', age=22, money=1000, sex=True) st4 = Student(name='zhou', email='[email protected]', age=22, money=1000, sex=True) st5 = Student(name='tang', email='[email protected]', age=22, money=1000, sex=True) st6 = Student(name='wu', email='[email protected]', age=22, money=1000, sex=True) st7 = Student(name='qian', email='[email protected]', age=22, money=1000, sex=True) st8 = Student(name='liu', email='[email protected]', age=22, money=1000, sex=True) st9 = Student(name='li', email='[email protected]', age=22, money=1000, sex=True) st10 = Student(name='sun', email='[email protected]', age=22, money=1000, sex=True) db.session.add_all([st1,st2,st3,st4,st5,st6,st7,st8,st9,st10]) db.session.commit() return "ok" @app.route("/delete") def delete(): """Delete the dataStudent = student.query.first () print(student) db.session.delete(student) db.session.mit ( Direct condition delete [better performance!!]  Student.query.filter(Student.id > 5).delete() db.session.commit() return "ok" @app.route("/update") def update(): """Update the data"" # query data and update it Stu = student.query.first () stu. Name = 'dong' db.session.com MIT () Student.query.filter(Student.name == 'chen').update({'money': MIT () # reference to student.query.filter (student.name ==) "zhang").update({"money":Student.money+1000 * Student.age}) db.session.commit() return "ok" @app.route("/get") def get(): Student = student.query.get (4) print(student) # student_list = student.query.all () # student_list = student.query.all () # First_student = student.query.filter (student.id <5).first() # print(first_student) # Ret = student.query.filter (student.id <5).count() print(ret) return "OK" if __name__ == '__main__': With app.app_context(): # check if there are tables in the database that match the model. If not, the table is built according to the table construction clause of model transformation. Db.create_all () app.run(debug=True)Copy the code

2.6 Data Query

Query filter

Method of querying results

@app.route("/get")
def get() :
    Get data based on primary key
    student = Student.query.get(4)
    print(student)

    Return all result data data
    student_list = Student.query.all(a)print(student_list)

    Return the first result data
    first_student = Student.query.filter(Student.id<5).first()
    print(first_student)

    # Number of results returned
    ret = Student.query.filter(Student.id<5).count()
    print(ret)

    return "ok"
Copy the code

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

class Config(object) :
    DEBUG = True
    Database connection configuration
    # SQLALCHEMY_DATABASE_URI = "database type :// database account: password @database address: port/database name? charset=utf8mb4"
    SQLALCHEMY_DATABASE_URI = "Mysql: / / root: 123 @127.0.0.1:3306 / students? charset=utf8mb4"
    # Dynamic tracking change Settings, if not set will only prompt a warning
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    The original SQL statement will be displayed when the query is performed
    SQLALCHEMY_ECHO = True

app.config.from_object(Config)


"" model class definition ""
db = SQLAlchemy(app=app)
# is equal to
# db = SQLAlchemy()
# db.init_app(app)

class Student(db.Model) :
    """ Student information Model ""
    Declare the name of the data table bound to the current model
    __tablename__ = "db_students"
    # field definition
    """ create table db_student(id int primary key auto_increment comment=" ", name varchar(15) comment=" """
    id = db.Column(db.Integer, primary_key=True,comment="Primary key")
    name = db.Column(db.String(15), comment="Name")
    age = db.Column(db.SmallInteger, comment="Age")
    sex = db.Column(db.Boolean, default=True, comment="Gender")
    email = db.Column(db.String(128), unique=True, comment="Email address")
    money = db.Column(db.Numeric(10.2), default=0.0, comment="Wallet")

    def __repr__(self) :
        return f"{self.name}<Student>"

    @classmethod
    def add(cls) :
        student = cls(name="Xiao Ming", sex=True, age=17, email="[email protected]", money=100)
        db.session.add(student)
        db.session.commit()
        return student

All models must directly or indirectly inherit from db.model
class Course(db.Model) :
    "" course Data Model ""
    __tablename__ = "db_course"
    id = db.Column(db.Integer, primary_key=True, comment="Primary key")
    name = db.Column(db.String(64), unique=True, comment="Course")
    price = db.Column(db.Numeric(7.2))
    The # repr() method is similar to Django's __str__ and is used to print the string information displayed on model objects
    def __repr__(self) :
        return f'{self.name}<Course>'

class Teacher(db.Model) :
    """ Teacher data Model """
    __tablename__ = "db_teacher"
    id = db.Column(db.Integer, primary_key=True, comment="Primary key")
    name = db.Column(db.String(64), unique=True, comment="Name")
    option = db.Column(db.Enum("Lecturer"."助教"."Head teacher"), default="Lecturer")

    def __repr__(self) :
        return f"{self.name}< Teacher >"

@app.route("/filter")
def filter() :
    # fuzzy query
    # all users using mailbox 163
    student_list = Student.query.filter(Student.email.endswith("@163.com")).all(a)print(student_list)

    # names start with "zh"
    student_list = Student.query.filter(Student.name.startswith("zh")).all(a)print(student_list)

    # Data with "A" in the name
    student_list = Student.query.filter(Student.name.contains("a")).all(a)print(student_list)


    # Condition comparison
    student_list = Student.query.filter(Student.age>18).all(a)print(student_list)

    # Multi-conditional comparison
    # Require that more than one condition be met
    student_list = Student.query.filter(Student.age>18, Student.sex==True).all(a)print(student_list)

    return "ok"

@app.route("/filter_by")
def filter_by() :
    """filter_by determines value equality """
    # single condition
    student_list = Student.query.filter_by(age=22).all(a)print(student_list)

    # many conditions
    student_list = Student.query.filter_by(age=22,sex=True).all(a)print(student_list)
    return "ok"


from sqlalchemy import and_,or_,not_
@app.route("/")
def multi() :
    "" Filter Multi-condition operation method ""
    """and_ and, and all conditions must be met to return the result """
    # and_(Condition 1, condition 2,....) Equivalent to filter(Condition 1, condition 2,.....)
    # age > 18 and email like "%163.com"
    # student_list = Student.query.filter(Student.age > 18, Student.email.endswith("163.com")).all()

    student_list = Student.query.filter(
        and_(
            Student.age > 18,
            Student.email.endswith("163.com"))).all(a)"""or_ or, or, returns a result if only one of the conditions is met.
    Select True for gender or age > 18
    sex = 1 or age > 18
    student_list = Student.query.filter(
        or_(
            Student.sex==True,
            Student.age>18)).all(a)print(student_list)



    # query for compound conditions
    Select * from female where age 18 or male where age 22
    # (age=18 and sex=0) or (age = 22 and sex=1)
    student_list = Student.query.filter(
        or_(
            and_(Student.age==18, Student.sex==False),
            and_(Student.age==22, Student.sex==True))).all(a)print( student_list )

    # not_ not, the result is negative
    Age = 22
    student_list = Student.query.filter(Student.age ! =22).all(a)print(student_list)
    student_list = Student.query.filter(not_(Student.age==22)).all(a)print(student_list)

    return "ok"

def filter() :
    """ Range query """

    Select * from student where id = 1, 3, 5
    student_list = Student.query.filter(Student.id.in_([1.3.5])).all(a)print(student_list)

    Select * from student where id not 1, 3, 5
    student_list = Student.query.filter(not_(Student.id.in_([1.3.5))).all(a)print( student_list )

    "" "order "" "
    # reverse order [value from large to small]
    student_list = Student.query.order_by(Student.id.desc()).all(a)# ascending [value from small to large]
    student_list = Student.query.order_by(Student.id.asc()).all(a)# select * from (select * from (select * from (select * from (select * from (select * from (select * from))))
    student_list = Student.query.order_by(Student.age.desc(), Student.id.asc() ).all(a)print(student_list)

    """ Limit the number of results ""
    student_list = Student.query.limit(2).all(a)print(student_list)

    """ Result returns the starting subscript position, starting at 0 ""
    student_list = Student.query.offset(0).limit(2).all(a)print(student_list)
    student_list = Student.query.limit(2).offset(2).all(a)print(student_list)
    return "ok"

if __name__ == '__main__':
    with app.app_context():
        Check if there is a table in the database that matches the model.
        If not, the table is built according to the table construction clause of model transformation.
        # If found, no additional processing is performed

        db.create_all()
    app.run(debug=True)

Copy the code