sqlalchemy带条件查询相关应用

sqlalchemy带条件查询

filter_by与filter

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

表中单个字段查询

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())

('tobee',)

one()

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

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())

[('dandan',), ('aaa',)]

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的用法
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)]