SQLAlchemy 几种查询方式总结

#1. 简单查询.

a =session.query(User).all()
print(11,a)
# 11 [(1,fujiz,1), (2,fu,2), (3,meng,2), (4,bin,3), (5,fujiz,1), (6,fu,2), (7,meng,2), (8,bin,3), (25,fujiz,1), (26,fu,2), (27,meng,2), (28,bin,3), (29,fujiz,1), (30,fu,2), (31,meng,2), (32,bin,3), (41,fujiz,1), (42,fu,2), (43,meng,2), (44,bin,3), (45,fujiz,1), (46,fu,2), (47,meng,2), (48,bin,3), (49,fujiz,1), (50,fu,2), (51,meng,2), (52,bin,3), (61,fujiz,1), (62,fu,2), (63,meng,2), (64,bin,3), (65,fujiz,1), (66,fu,2), (67,meng,2), (68,bin,3), (73,fujiz,1), (74,fu,2), (75,meng,2), (76,bin,3), (77,fujiz,1), (78,fu,2), (79,meng,2), (80,bin,3), (89,fujiz,1), (90,fu,2), (91,meng,2), (92,bin,3)]

b= session.query(User.name,User.role).all()
print(22,b)
# 22 [('fujiz', 1), ('fu', 2), ('meng', 2), ('bin', 3), ('fujiz', 1), ('fu', 2), ('meng', 2), ('bin', 3), ('fujiz', 1), ('fu', 2), ('meng', 2), ('bin', 3), ('fujiz', 1), ('fu', 2), ('meng', 2), ('bin', 3), ('fujiz', 1), ('fu', 2), ('meng', 2), ('bin', 3), ('fujiz', 1), ('fu', 2), ('meng', 2), ('bin', 3), ('fujiz', 1), ('fu', 2), ('meng', 2), ('bin', 3), ('fujiz', 1), ('fu', 2), ('meng', 2), ('bin', 3), ('fujiz', 1), ('fu', 2), ('meng', 2), ('bin', 3), ('fujiz', 1), ('fu', 2), ('meng', 2), ('bin', 3), ('fujiz', 1), ('fu', 2), ('meng', 2), ('bin', 3), ('fujiz', 1), ('fu', 2), ('meng', 2), ('bin', 3)]


c =session.query(User,User.name).all()
print(33,c)
# 33 [((1,fujiz,1), 'fujiz'), ((2,fu,2), 'fu'), ((3,meng,2), 'meng'), ((4,bin,3), 'bin'), ((5,fujiz,1), 'fujiz'), ((6,fu,2), 'fu'), ((7,meng,2), 'meng'), ((8,bin,3), 'bin'), ((25,fujiz,1), 'fujiz'), ((26,fu,2), 'fu'), ((27,meng,2), 'meng'), ((28,bin,3), 'bin'), ((29,fujiz,1), 'fujiz'), ((30,fu,2), 'fu'), ((31,meng,2), 'meng'), ((32,bin,3), 'bin'), ((41,fujiz,1), 'fujiz'), ((42,fu,2), 'fu'), ((43,meng,2), 'meng'), ((44,bin,3), 'bin'), ((45,fujiz,1), 'fujiz'), ((46,fu,2), 'fu'), ((47,meng,2), 'meng'), ((48,bin,3), 'bin'), ((49,fujiz,1), 'fujiz'), ((50,fu,2), 'fu'), ((51,meng,2), 'meng'), ((52,bin,3), 'bin'), ((61,fujiz,1), 'fujiz'), ((62,fu,2), 'fu'), ((63,meng,2), 'meng'), ((64,bin,3), 'bin'), ((65,fujiz,1), 'fujiz'), ((66,fu,2), 'fu'), ((67,meng,2), 'meng'), ((68,bin,3), 'bin'), ((73,fujiz,1), 'fujiz'), ((74,fu,2), 'fu'), ((75,meng,2), 'meng'), ((76,bin,3), 'bin'), ((77,fujiz,1), 'fujiz'), ((78,fu,2), 'fu'), ((79,meng,2), 'meng'), ((80,bin,3), 'bin'), ((89,fujiz,1), 'fujiz'), ((90,fu,2), 'fu'), ((91,meng,2), 'meng'), ((92,bin,3), 'bin')]


#2. 带条件查询

a =session.query(User).filter_by(name ="meng").all()
print(44,a)
# 44 [(3,meng,2), (7,meng,2), (27,meng,2), (31,meng,2), (43,meng,2), (47,meng,2), (51,meng,2), (63,meng,2), (67,meng,2), (75,meng,2), (79,meng,2), (91,meng,2)]


b =session.query(User).filter(User.name =="meng").all()
print(55,b)
# 55 [(3,meng,2), (7,meng,2), (27,meng,2), (31,meng,2), (43,meng,2), (47,meng,2), (51,meng,2), (63,meng,2), (67,meng,2), (75,meng,2), (79,meng,2), (91,meng,2)]


c =session.query(User).filter(User.name.like("m%")).all()
print(66,c)
# 66 [(3,meng,2), (7,meng,2), (27,meng,2), (31,meng,2), (43,meng,2), (47,meng,2), (51,meng,2), (63,meng,2), (67,meng,2), (75,meng,2), (79,meng,2), (91,meng,2)]



# 3.多条件查询

a = session.query(User).filter(and_(User.name.like("m%"),User.role==2)).all()
print(77,a)
# 77 [(3,meng,2), (7,meng,2), (27,meng,2), (31,meng,2), (43,meng,2), (47,meng,2), (51,meng,2), (63,meng,2), (67,meng,2), (75,meng,2), (79,meng,2), (91,meng,2)]


# 4.关联查询
a =session.query(User,Role).filter(User.role ==Role.rid).all()
print(88,a)
# 88 [((1,fujiz,1), (1,dba)), ((2,fu,2), (2,sa)), ((3,meng,2), (2,sa)), ((4,bin,3), (3,net)), ((5,fujiz,1), (1,dba)), ((6,fu,2), (2,sa)), ((7,meng,2), (2,sa)), ((8,bin,3), (3,net)), ((25,fujiz,1), (1,dba)), ((26,fu,2), (2,sa)), ((27,meng,2), (2,sa)), ((28,bin,3), (3,net)), ((29,fujiz,1), (1,dba)), ((30,fu,2), (2,sa)), ((31,meng,2), (2,sa)), ((32,bin,3), (3,net)), ((41,fujiz,1), (1,dba)), ((42,fu,2), (2,sa)), ((43,meng,2), (2,sa)), ((44,bin,3), (3,net)), ((45,fujiz,1), (1,dba)), ((46,fu,2), (2,sa)), ((47,meng,2), (2,sa)), ((48,bin,3), (3,net)), ((49,fujiz,1), (1,dba)), ((50,fu,2), (2,sa)), ((51,meng,2), (2,sa)), ((52,bin,3), (3,net)), ((61,fujiz,1), (1,dba)), ((62,fu,2), (2,sa)), ((63,meng,2), (2,sa)), ((64,bin,3), (3,net)), ((65,fujiz,1), (1,dba)), ((66,fu,2), (2,sa)), ((67,meng,2), (2,sa)), ((68,bin,3), (3,net)), ((73,fujiz,1), (1,dba)), ((74,fu,2), (2,sa)), ((75,meng,2), (2,sa)), ((76,bin,3), (3,net)), ((77,fujiz,1), (1,dba)), ((78,fu,2), (2,sa)), ((79,meng,2), (2,sa)), ((80,bin,3), (3,net)), ((89,fujiz,1), (1,dba)), ((90,fu,2), (2,sa)), ((91,meng,2), (2,sa)), ((92,bin,3), (3,net))]

#5. 聚合查询
a= session.query(User.name,func.count("*").label("user_count")).group_by(User.name).all()
print(99,a)
# 99 [('bin', 15), ('fu', 15), ('fujiz', 15), ('meng', 15)]


a =session.query(User.name,func.sum(User.nid).label("sum")).group_by(User.name).all()
print(12,a)
# 12 [('bin', Decimal('896')), ('fu', Decimal('866')), ('fujiz', Decimal('851')), ('meng', Decimal('881'))]


session.commit()
session.close()

#查询记录总查询:
from sqlalchemy import  func

a=session.query(func.count(User.nid))
print(13,a)
# 13 SELECT count(user.nid) AS count_1

View Code

几种常见sqlalchemy查询：
#简单查询
print(session.query(User).all())
print(session.query(User.name, User.fullname).all())
print(session.query(User, User.name).all())

#带条件查询
print(session.query(User).filter_by(name='user1').all())
print(session.query(User).filter(User.name == "user").all())
print(session.query(User).filter(User.name.like("user%")).all())

#多条件查询
print(session.query(User).filter(and_(User.name.like("user%"), User.fullname.like("first%"))).all())
print(session.query(User).filter(or_(User.name.like("user%"), User.password != None)).all())

#sql过滤
print(session.query(User).filter("id>:id").params(id=1).all())

#关联查询
print(session.query(User, Address).filter(User.id == Address.user_id).all())
print(session.query(User).join(User.addresses).all())
print(session.query(User).outerjoin(User.addresses).all())

#聚合查询
print(session.query(User.name, func.count('*').label("user_count")).group_by(User.name).all())
print(session.query(User.name, func.sum(User.id).label("user_id_sum")).group_by(User.name).all())

#子查询
stmt = session.query(Address.user_id, func.count('*').label("address_count")).group_by(Address.user_id).subquery()
print(session.query(User, stmt.c.address_count).outerjoin((stmt, User.id == stmt.c.user_id)).order_by(User.id).all())

#exists
print(session.query(User).filter(exists().where(Address.user_id == User.id)))
print(session.query(User).filter(User.addresses.any()))

限制返回字段查询
person = session.query(Person.name, Person.created_at,Person.updated_at).filter_by(name="zhongwei").order_by(Person.created_at).first()

记录总数查询：
from sqlalchemy import func

# count User records, without
# using a subquery.
session.query(func.count(User.id))

# return count of user "id" grouped
# by "name"
session.query(func.count(User.id)).
group_by(User.name)

from sqlalchemy import distinct
# count distinct "name" values
session.query(func.count(distinct(User.name)))

方法	orm
equals	query.filter(User.name == ‘wang’)
not equals	query.filter(User.name != ‘wang’)
LIKE	query.filter(User.name.like(‘%ed%’))
IN	query.filter(User.name.in_([‘ed’, ‘wendy’, ‘jack’]))
NOT IN	query.filter(~User.name.in_([‘ed’, ‘wendy’, ‘jack’]))
IS NULL	query.filter(User.name == None)
IS NOT NULL	query.filter(User.name != None)
AND	query.filter(and_(User.name == ‘ed’, User.fullname == ‘Ed Jones’)) 或者query.filter(User.name == ‘ed’, User.fullname == ‘Ed Jones’)
OR	query.filter(or_(User.name == ‘ed’, User.name == ‘wendy’))