SQLAlchemy的查询语法有哪些

这期内容当中小编将会给大家带来有关 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()))

以下为映射类：

class User(Base):

__tablename__ = "users"

id = Column("id", Integer, primary_key=True)

name = Column("name", String)

fullname = Column("fullname", String)

password = Column("password", String)

addresses = relation("Address", order_by="Address.id", backref="user")

def __init__(self, id=None, name=None, fullname=None, password=None, addresses=[]):

self.id = id

self.name = name

self.fullname = fullname

self.password = password

self.addresses = addresses

def __repr__(self):

return "".format(name=self.name, fullname=self.fullname, password=self.password, addresses=self.addresses)

class Address(Base):

__tablename__ = "address"

id = Column(Integer, primary_key=True)

email_address = Column(String, nullable=False)

user_id = Column(Integer, ForeignKey("users.id"))

#user = relation("User", backref="addresses", order_by="Address.id")

def __init__(self, email_address=None):

self.email_address = email_address

def __repr__(self):

return "