sqlalchemy复杂查询
这章节要操作的数据
多条件查询，可以在表达式中各种and，ro方式进行查询
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from s1 import modelsengine = create_engine('sqlite:///test.db')
Session = sessionmaker(bind=engine)
session = Session()row1 = session.query(models.Users).filter(text("nid>:value or age>:age")).params(value=9,age=30).all()
row_list = [row.name for row in row1]
print(row_list)try:session.commit()
except Exception as e:print(e)session.close()#结果
['警皇', '猪皇', '沙加特', '春丽', '桑切尔夫', '隆', '肯', '豪鬼', '骨裂']
between函数，查询某一列和某一列之间的数据
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from s1 import modelsengine = create_engine('sqlite:///test.db')
Session = sessionmaker(bind=engine)
session = Session()row1 = session.query(models.Users).filter(models.Users.name.between('春丽','骨裂')).all()
row_list = [row.name for row in row1]
print(row_list)
try:session.commit()
except Exception as e:print(e)session.close()#结果
['春丽', '桑切尔夫', '沙加特', '猪皇', '肯', '警皇', '豪鬼', '阿比盖尔', '隆', '骨裂']
查询包含与不包含
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from s1 import modelsengine = create_engine('sqlite:///test.db')
Session = sessionmaker(bind=engine)
session = Session()#包含nid为8,9
row1 = session.query(models.Users).filter(models.Users.nid.in_([8,9])).all()
row_list = [row.name for row in row1]
print(row_list)#不包含nid为8,9
row1 = session.query(models.Users).filter(~models.Users.nid.in_([8,9])).all()
row_list = [row.name for row in row1]
print(row_list)try:session.commit()
except Exception as e:print(e)
session.close()#结果['警皇', '猪皇']
['阿比盖尔', '嘉米', '小樱02', '劳拉02', '沙加特', '春丽', '桑切尔夫', '隆', '肯', '豪鬼', '骨裂']
查询嵌套
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from s1 import modelsengine = create_engine('sqlite:///test.db')
Session = sessionmaker(bind=engine)
session = Session()
#获取年龄等于35的用户id，再通过包含查询，查询到这两个id的数据
row1 = session.query(models.Users).filter(models.Users.nid.in_(session.query(models.Users.nid).filter(models.Users.age==35))).all()
row_list = [row.name for row in row1]
print(row_list)try:session.commit()
except Exception as e:print(e)session.close()#结果
['桑切尔夫', '骨裂']
上面说到了使用or，and的用法，但只是编写表达式，并不是面向对象
下面示例是使用面向对象的方式
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from s1 import models
from sqlalchemy import and_,or_engine = create_engine('sqlite:///test.db')
Session = sessionmaker(bind=engine)
session = Session()#查询nid大于9,年龄小于30的用户
row1 = session.query(models.Users).filter(and_(models.Users.nid>9,models.Users.age<30))
row_list = [row.name for row in row1]
print(row_list)#查询gender是真的用户,或者年龄小于30的用户
row1 = session.query(models.Users).filter(or_(models.Users.gender==True,models.Users.age<30))
row_list = [row.name for row in row1]
print(row_list)#and_和or_可以互相嵌套,查询gender是真的用户,或者id>1而且年龄在20到30之间
row1 = session.query(models.Users).filter(or_(models.Users.gender==True,and_(models.Users.nid>1,models.Users.age.between(20,30)))
).all()
row_list = [row.name for row in row1]
print(row_list)try:session.commit()
except Exception as e:print(e)session.close()#结果
['春丽', '肯']
['阿比盖尔', '嘉米', '小樱02', '劳拉02', '春丽', '肯']
['嘉米', '小樱02', '劳拉02', '春丽', '隆', '肯']
分页
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from s1 import models
from sqlalchemy import and_,or_engine = create_engine('sqlite:///test.db')
Session = sessionmaker(bind=engine)
session = Session()#offset：起始位置
#limit：数量
row1 = session.query(models.Users).offset(2).limit(3)
row_list = [row.name for row in row1]
print(row_list)try:session.commit()
except Exception as e:print(e)session.close()#结果
['小樱02', '劳拉02', '警皇']
模糊查询
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from s1 import models
from sqlalchemy import and_,or_engine = create_engine('sqlite:///test.db')
Session = sessionmaker(bind=engine)
session = Session()#查询年龄3字头的用户
row1 = session.query(models.Users).filter(models.Users.age.like("3%")).all()
row_list = [row.name for row in row1]
print(row_list)try:session.commit()
except Exception as e:print(e)session.close()#结果
['劳拉02', '猪皇', '桑切尔夫', '隆', '骨裂']
排序
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.sql import text
from s1 import models
from sqlalchemy import and_,or_engine = create_engine('sqlite:///test.db')
Session = sessionmaker(bind=engine)
session = Session()#按照年龄排序
#升序
row1 = session.query(models.Users).order_by(models.Users.age.asc())
row_list = [row.name for row in row1]
print(row_list)
#降序
row1 = session.query(models.Users).order_by(models.Users.age.desc())
row_list = [row.name for row in row1]
print(row_list)
try:session.commit()
except Exception as e:print(e)session.close()#结果
['阿比盖尔', '嘉米', '小樱02', '春丽', '肯', '劳拉02', '隆', '桑切尔夫', '骨裂', '猪皇', '沙加特', '豪鬼', '警皇']
['警皇', '沙加特', '豪鬼', '猪皇', '桑切尔夫', '骨裂', '劳拉02', '隆', '肯', '春丽', '小樱02', '嘉米', '阿比盖尔']
聚合函数
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.sql import text, func
from s1 import models
from sqlalchemy import and_, or_engine = create_engine('sqlite:///test.db')
Session = sessionmaker(bind=engine)
session = Session()# 聚合
ret = session.query(func.sum(models.Users.age), # 所有数相加的值func.max(models.Users.age), #最大值func.min(models.Users.age) #最小值
).first()
print(ret)try:session.commit()
except Exception as e:print(e)session.close()
分组
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.sql import text, func
from s1 import models
from sqlalchemy import and_, or_engine = create_engine('sqlite:///test.db')
Session = sessionmaker(bind=engine)
session = Session()#分组
ret = session.query(func.count(models.Users.name)).group_by(models.Users.gender).all()
print('男:',ret[0][0],'个')
print('女:',ret[1][0],'个')
try:session.commit()
except Exception as e:print(e)session.close()#结果
男: 9 个
女: 4 个
