98.SQLAlchemy中filter函数的使用

util_db

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
HOST = "localhost"
PORT = 3306
DATA_BASE = 'flask_db'
USER = 'root'
PWD = 'root'
DB_URI = f"mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}"

engine = create_engine(DB_URI)
Base = declarative_base(engine)
Session = sessionmaker(engine)

filter过滤函数.py

from operator import and_, or_
from random import randint
from tkinter.messagebox import RETRY
from turtle import title
from uuid import uuid4
from sqlalchemy import Column, Integer, String, Float, Text, and_, or_
from util_db import Base, Session

# 创建数据表类型
class Article(Base):
    __tablename__ = 't_article'
    id = Column(Integer, primary_key = True, autoincrement = True)
                                #不准为空
    titile = Column(String(50), nullable = False)
    price = Column(Float, nullable = False)
    content = Column(Text)

    # 用此方法，输出格式不会再是地址, 是自己拼接的字符串格式输出
    def __repr__(self):
        return f"<Article(title:{self.titile}) price:{self.price} content:{self.content}>"

# 创建数据内容
def create_data():
    with Session() as ses:
        for i in range(10):
            if i%2 == 0:
                art = Article(titile = f'title{i+1}', price = randint(1,100), content = uuid4())
            else:
                art = Article(titile = f'TITLE{i+1}', price = randint(1,100))
            ses.add(art)
        ses.commit()


# 测试filter过滤数据：
# ==
def query_data():
    with Session() as ses:
        rs = ses.query(Article).filter(Article.id == 1).first()
        print(rs)

# !=
def query_data_not_equal():
    with Session() as ses:
        rs = ses.query(Article).filter(Article.id != 1).all()
        for i in rs:
            print(i)

# like
def query_data_like():
    with Session() as ses:
        rs = ses.query(Article).filter(Article.titile.like("title%")).all()
        for i in rs:
            print(i)

# in_
# 注意；in的写法是in_
def query_data_in():
    with Session() as ses:
        rs = ses.query(Article).filter(Article.titile.in_(['title1','title3','title5'])).all()
        for i in rs:
            print(i)

# not in_
# 注意；in的写法是   ~    in_
def query_data_in():
    with Session() as ses:
        rs = ses.query(Article).filter(~Article.titile.in_(['title1','title3','title5'])).all()
        for i in rs:
            print(i)

# None
def query_data_null():
    with Session() as ses:
        rs = ses.query(Article).filter(Article.content == None).all()
        for i in rs:
            print(i)

# not None
def query_data_null():
    with Session() as ses:
        rs = ses.query(Article).filter(Article.content != None).all()
        for i in rs:
            print(i)

# and
# 有三种方法
def query_data_null():
    with Session() as ses:
        #方法1：and
        # rs = ses.query(Article).filter(Article.titile!='title4' and Article.price >8 ).all()

        # 方法2： ,
        # rs = ses.query(Article).filter(Article.titile!='title4' , Article.price >8 ).all()

        # 方法3（推荐）：and_
        rs = ses.query(Article).filter(and_(Article.titile!='title4', Article.price > 8)).all()
        for r in rs:
            print(r)

# or_
def query_data_or():
    with Session() as ses:
        rs = ses.query(Article).filter(or_(Article.titile!='title4', Article.price > 8)).all()
        for r in rs:
            print(r)





if __name__ == "__main__":
    # Base.metadata.create_all()
    # create_data()
    # query_data()
    # query_data_not_equal()
    # query_data_like()
    # query_data_in()
    # query_data_null()
    query_data_or()