常见的sqlalcemy查询

绝地灬酷狼 2022-02-26 19:14 63阅读 0赞

转自其他博客，另外附上找到一个将sqlalchemy的orm基本用法和增删改查的文章  
[https://www.cnblogs.com/Keep-Ambition/p/8083387.html][https_www.cnblogs.com_Keep-Ambition_p_8083387.html]

几种常见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][])))

[https_www.cnblogs.com_Keep-Ambition_p_8083387.html]: https://www.cnblogs.com/Keep-Ambition/p/8083387.html
[User.name]: http://User.name
[User.id]: http://User.id
[Person.name]: http://Person.name