Python全系列 教程
3567个小节阅读:5929.2k
目录
鸿蒙应用开发
C语言快速入门
JAVA全系列 教程
面向对象的程序设计语言
Python全系列 教程
Python3.x版本,未来主流的版本
人工智能 教程
顺势而为,AI创新未来
大厂算法 教程
算法,程序员自我提升必经之路
C++ 教程
一门通用计算机编程语言
微服务 教程
目前业界流行的框架组合
web前端全系列 教程
通向WEB技术世界的钥匙
大数据全系列 教程
站在云端操控万千数据
AIGC全能工具班
A A
White Night
过滤是数据提取的一个很重要的功能,以下对一些常用的过滤条件进行解释,并且这些过滤条件都是只能通过filter方法实现的:
equals
xxxxxxxxxx
news= session.query(News).filter(News.title == "title1").first()
not equals
xxxxxxxxxx
query(User).filter(User.name != 'ed')
like & ilike [不区分大小写]
xxxxxxxxxx
query(User).filter(User.name.like('%ed%'))
in
xxxxxxxxxx
query(User).filter(User.name.in_(['ed','wendy','jack']))
not in
xxxxxxxxxx
query(User).filter(~User.name.in_(['ed','wendy','jack']))
is null
xxxxxxxxxx
query(User).filter(User.name==None)
# 或者是
query(User).filter(User.name.is_(None))
is not null
xxxxxxxxxx
query(User).filter(User.name != None)
# 或者是
query(User).filter(User.name.isnot(None))
and
xxxxxxxxxx
query(User).filter(and_(User.name=='ed',User.fullname=='Ed Jones'))
# 或者是传递多个参数
query(User).filter(User.name=='ed',User.fullname=='Ed Jones')
# 或者是通过多次filter操作
query(User).filter(User.name=='ed').filter(User.fullname=='Ed Jones')
or
xxxxxxxxxx
query(User).filter(or_(User.name=='ed',User.name=='wendy'))
如果想要查看orm底层转换的sql语句,可以在filter方法后面不要再执行任何方法直接打印就可以看到了。比如:
xxxxxxxxxx
news = session.query(News).filter(or_(News.title=='abc',News.content=='abc'))
print(news)
代码演示
xxxxxxxxxx
from random import randint
from uuid import uuid4
from sqlalchemy import Column,Integer,String,Float,Text,and_,or_
from db_util import Base,Session
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):
return f"<Article(title:{self.title} price:{self.price} content:{self.content})>"
def create_data():
with Session() as ses:
for i in range(10):
if i%2 == 0:
art = Article(title = f'title{i+1}',price=randint(1,100),content = uuid4())
else:
art = Article(title = f'TITLE{i+1}',price=randint(1,100))
ses.add(art)
ses.commit()
def query_data():
with Session() as ses:
# rs = ses.query(Article).filter_by(id=1).first()
rs = ses.query(Article).filter(Article.id == 1).first()
print(rs)
def query_data_equal():
with Session() as ses:
rs = ses.query(Article).filter(Article.title == 'title2').first()
print(rs)
def query_data_not_equal():
with Session() as ses:
rs = ses.query(Article).filter(Article.title != 'title2').all()
print(rs)
def query_data_like():
with Session() as ses:
# select * from t_article where title like 'title%';
rs = ses.query(Article).filter(Article.title.like('title%')).all()
for r in rs:
print(r)
def query_data_in():
with Session() as ses:
rs = ses.query(Article).filter(Article.title.in_(['title1','title3','title6'])).all()
for r in rs:
print(r)
def query_data_not_in():
with Session() as ses:
rs = ses.query(Article).filter(~ Article.title.in_(['title1','title3','title6'])).all()
for r in rs:
print(r)
def query_data_null():
with Session() as ses:
rs = ses.query(Article).filter(Article.content == None).all()
for r in rs:
print(r)
def query_data_not_null():
with Session() as ses:
rs = ses.query(Article).filter(Article.content != None).all()
for r in rs:
print(r)
def query_data_and():
with Session() as ses:
# rs = ses.query(Article).filter(Article.title !='title4' and Article.price >8 ).all()
# rs = ses.query(Article).filter(Article.title !='title4',Article.price >50 ).all()
rs = ses.query(Article).filter(and_(Article.title !='title4',Article.price >50) ).all()
for r in rs:
print(r)
def query_data_or():
with Session() as ses:
rs = ses.query(Article).filter(or_(Article.title =='title4',Article.price >50) ).all()
for r in rs:
print(r)
if __name__ == '__main__':
# Base.metadata.create_all()
# create_data()
# query_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()
实时学习反馈
1. 在SQLAlchemy中,对数据过滤说法错误的是?
A filter过滤完数据时,需要提取数据
B filter中可使用or函数来增加多个条件
C filter中可使用and_函数来增加多个条件
D filter中可使用like函数做模糊匹配
答案
1=>B