欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

sqlalchemy带条件查询

程序员文章站 2022-03-11 19:02:23
...

源出处:点击传送门
filter_by 与filter的区别: 
1. filter_by只能取值= filter可以==,!=,>=,<=等多种取值方式 
2. filter带参数必须表名.字段名 User.username

#带条件查询
# print(session.query(User).filter_by(username='111').all())
# row = session.query(User).filter(User.username=='dandan').all()
# print(row)


结果

[<User(id='14' ,username='111',password='111',createtime='2018-03-09 20:29:18',_locked ='False',)>]
[<User(id='1' ,username='dandan',password='123asd',createtime='2018-03-06 22:52:51',_locked ='False',)>]



如何查看sqlalchemey的sql语句

去掉all()就可以看到

row = session.query(User).filter(User.username!='aaa')
print(row)


结果

SELECT user.id AS user_id, user.username AS user_username, user.password AS user_password, user.createtime AS user_createtime, user._locked AS user__locked 
FROM user 
WHERE user.username != %(username_1)s
1


表中单个字段查询

print(session.query(User.username).filter(User.username!='dandan').all())


结果

[('tobee',), ('tree',), ('aaa',), ('coding',), ('choupi',), ('111',), ('choupidan',), ('youku',)]


first()

仅查询显示第一个

print(session.query(User.username).filter(User.username!='dandan').first())


只查询出来第一个,有且只有一个 
如果有两个符合条件的,会报错

print(session.query(User).filter(User.username=='choupi').one())


结果

<User(id='12' ,username='choupi',password='q1',createtime='2018-03-09 20:27:54',_locked ='False',)>


get 根据主键查询

主键在表中只有一个 
例如ID为主键,查询id=3的元素

print(session.query(User).get(3))
<User(id='3' ,username='tree',password='zzz111',createtime='2018-03-09 14:44:21',_locked ='False',)>


limit 限制查询结果


limit(3) 仅查出3条结果

print(session.query(User).filter(User.username!='dandan').limit(3).all())
[<User(id='2' ,username='tobee',password='234qwe',createtime='2018-03-07 16:16:05',_locked ='False',)>, <User(id='3' ,username='tree',password='zzz111',createtime='2018-03-09 14:44:21',_locked ='False',)>, <User(id='4' ,username='aaa',password='111',createtime='2018-03-09 17:57:15',_locked ='False',)>]


offset() 限制前面n个,显示后面n+1个


显示第三个以后

print(session.query(User.username).filter(User.username!='dandan').all())
print(session.query(User.username).filter(User.username!='dandan').limit(3).all())

print(session.query(User.username).filter(User.username!='dandan').offset(3).all())


结果:

[('tobee',), ('tree',), ('aaa',), ('coding',), ('choupi',), ('111',), ('choupidan',), ('youku',)]
[('tobee',), ('tree',), ('aaa',)]
[('coding',), ('choupi',), ('111',), ('choupidan',), ('youku',)]


slice() 切片


slice(1,3) 与python的slice一致,从0开始 左闭右开,显示1,2两个元素

print(session.query(User.username).filter(User.username!='dandan').slice(1,3).all())

 

[('tree',), ('aaa',)]


order_by() 元素排序 顺序

 

print(session.query(User.username).filter(User.username!='dandan').order_by(User.username).all())


按数字字符顺序排序

[('111',), ('aaa',), ('choupi',), ('choupidan',), ('coding',), ('tobee',), ('tree',), ('youku',)]


desc() 逆序排序
from sqlalchemy import desc
print(session.query(User.username).filter(User.username!='dandan').order_by(desc(User.username)).all())

逆序排序

[('youku',), ('tree',), ('tobee',), ('coding',), ('choupidan',), ('choupi',), ('aaa',), ('111',)]


like()
结合%使用 与原生sql一致

print(session.query(User.username).filter(User.username.like('%e')).all())

 

[('tobee',), ('tree',)]


notlike()

print(session.query(User.username).filter(User.username.notlike('%e')).all())

 

[('dandan',), ('aaa',), ('coding',), ('choupi',), ('111',), ('choupidan',), ('youku',)]


in_()

print(session.query(User.username).filter(User.username.in_(['dandan','aaa'])).all())

 

print(session.query(User.username).filter(User.username.in_(['dandan','aaa'])).all())


notin_

print(session.query(User.username).filter(User.username.notin_(['dandan','aaa'])).all())

 

[('tobee',), ('tree',), ('coding',), ('choupi',), ('111',), ('choupidan',), ('youku',)]


is_
两种表达方式 None

print(session.query(User.username).filter(User.username==None).all())
print(session.query(User.username).filter(User.username.is_(None)).all())


isnot
filter支持多条件查询

print(session.query(User.username).filter(User.username.isnot(None),User.password=='111').all())

 

[('aaa',), ('111',)]


or_

from sqlalchemy import or_
print(session.query(User.username).filter(or_(User.username.isnot(None),User.password=='111')).all())

 

[('dandan',), ('tobee',), ('tree',), ('aaa',), ('coding',), ('choupi',), ('111',), ('choupidan',), ('youku',)]


聚合函数


count group_by

from sqlalchemy import func
print(session.query(User.password,func.count(User.id)).group_by(User.password).all())


查询原生sql

SELECT user.password AS user_password, count(user.id) AS count_1 
FROM user GROUP BY user.password
[('111', 2), ('123asd', 1), ('234qwe', 1), ('333', 1), ('choupidan', 1), ('q1', 1), ('qwer', 1), ('zzz111', 1)]


having的用法 


having字句可以让我们筛选成组后的各种数据,where字句在聚合前先筛选记录,也就是说作用在group by和having字句前。 
而having子句在聚合后对组记录进行筛选。真实表中没有此数据,这些数据是通过一些函数生存。

print(session.query(User.password,func.count(User.id)).group_by(User.password).\
      having(func.count(User.id)>1).all())
[('111', 2)]


sum

 

print(session.query(User.password,func.sum(User.id)).group_by(User.password).all())


[(‘111’, Decimal(‘18’)), (‘123asd’, Decimal(‘1’)), (‘234qwe’, Decimal(‘2’)), (‘333’, Decimal(‘16’)), (‘choupidan’, Decimal(‘15’)), (‘q1’, Decimal(‘12’)), (‘qwer’, Decimal(‘5’)), (‘zzz111’, Decimal(‘3’))]

max

print(session.query(User.password,func.max(User.id)).group_by(User.password).all())


[(‘111’, 14), (‘123asd’, 1), (‘234qwe’, 2), (‘333’, 16), (‘choupidan’, 15), (‘q1’, 12), (‘qwer’, 5), (‘zzz111’, 3)]

min

print(session.query(User.password,func.min(User.id)).group_by(User.password).all())


[(‘111’, 4), (‘123asd’, 1), (‘234qwe’, 2), (‘333’, 16), (‘choupidan’, 15), (‘q1’, 12), (‘qwer’, 5), (‘zzz111’, 3)]

lable 别名


lable别名不能用在having中

extract


提取时间元素

from sqlalchemy import extract
print(session.query(extract('minute',User.createtime).label('minute'),func.count(User.id)).group_by('minute').all())


分钟排序

[(16, 1), (27, 1), (29, 3), (44, 1), (52, 1), (57, 2)]

天数排序

print(session.query(extract('day',User.createtime).label('day'),func.count(User.id)).group_by('day').all())

[(6, 1), (7, 1), (9, 7)]

 

相关标签: Sqlalchemy条件查询