Flask数据库_filter过滤器的使用

Flask数据库_filter过滤器的使用

一.filter过滤器介绍

• equal

  判断模型属性值是否等于某个值

• not equal

  判断模型属性值是否不等于某个值

• like

  判断模型属性值是否模糊等于一个值

• in

  判断模型属性值是否在一个序列中出现过

• not in

  判断模型属性值是否在一个序列中没有出现过

• null

  判断模型属性值是否为空

• not null

  判断模型属性值是否不为空

• and

  需要同时满足多个判断条件

• or

  满足多个条件中的某个一个条件即可

二.实例

db_utils.py:

#coding=utf-8


from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

USER = 'root'
PWD = 'root'
HOST = '127.0.0.1'
PORT = 3306
DATA_BASE = 'flask_db'

DB_URL = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'

# 创建一个引擎
engine = create_engine(DB_URL)
Base = declarative_base(engine)
Session = sessionmaker(engine)

main.py:

#coding=utf-8

from cgi import print_exception
from turtle import title
from db_utils import Base,Session
from sqlalchemy import Column,Integer,Float,String,Text,and_,or_
from random import randint
from uuid import uuid4

class Article(Base):
    __tablename__ = 't_article'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    price = Column(Float,nullable=False)
    content = Column(Text)

    def __repr__(self) -> str:
        return f'<Article(title:{self.title} price:{self.price} content:{self.content})>'

def create_data():
    with Session() as session:
        for i in range(10):
            if i%2 == 0:
                art = Article(title=f'title{i+1}',price=randint(0,100),content=uuid4())
            else:
                art = Article(title=f'TITLE{i+1}',price=randint(0,100))
            session.add(art)
        session.commit()

def query_data():
    with Session() as s:
        art = s.query(Article).filter_by(id = 3).first()
        print(art)

def query_data_equal():
    with Session() as s:
        art = s.query(Article).filter(Article.id == 2).first()
        print(art)

def query_data_not_equal():
    with Session() as s:
        art = s.query(Article).filter(Article.title != 'title6').all()
        for x in art:
            print(x)

def query_data_like():
    with Session() as s:
        art = s.query(Article).filter(Article.title.like('title%')).all()
        for x in art:
            print(x)

def query_data_in():
    with Session() as s:
        art = s.query(Article).filter(Article.title.in_(['title3','title6','title9'])).all()
        for x in art:
            print(x)

def query_data_not_in():
    with Session() as s:
        art = s.query(Article).filter(~Article.title.in_(['title3','title6','title9'])).all()
        for x in art:
            print(x)

def query_data_null():
    with Session() as s:
        art = s.query(Article).filter(Article.content == None).all()
        for x in art:
            print(x)

def query_data_not_null():
    with Session() as s:
        art = s.query(Article).filter(Article.content != None).all()
        for x in art:
            print(x)

def query_data_and():
    with Session() as s:
        # art = s.query(Article).filter(Article.content == None,Article.price>40).all()
        art = s.query(Article).filter(and_(Article.content == None,Article.price>40)).all()
        for x in art:
            print(x)

def query_data_or():
    with Session() as s:
        art = s.query(Article).filter(or_(Article.content == None,Article.price>40)).all()
        # art = s.query(Article).filter(Article.title == 'title5' or Article.price>40).all()
        for x in art:
            print(x)

if __name__ == '__main__':
    # Base.metadata.create_all()
    # create_data()
    # query_data_equal()
    # query_data_not_equal()
    # query_data_like()
    # query_data_in()
    # query_data_not_in()
    # query_data_null()
    # query_data_not_null()
    # query_data_and()
    query_data_or()