统计函数
计数
session.query(User).filter(User.name.like('%ed')).count()
分组计数
from sqlalchemy import func
session.query(func.count(User.name), User.name).group_by(User.name).all()
全部计数
session.query(func.count('*')).select_from(User).scalar()
一对多关系,外键
注意ForeignKey和relationship以及backref的用法
backref的语意为User对象可以反向索引Address对象,通过绑定的addresses属性,即user.addresses
>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship, backref
>>> class Address(Base):
... __tablename__ = 'addresses'
... id = Column(Integer, primary_key=True)
... email_address = Column(String(100), nullable=False)
... #指定外键,与users表的主键关联
... user_id = Column(Integer, ForeignKey('users.id'))
... #指定反向属性
... user = relationship("User", backref=backref('addresses', order_by=id))
...
... def __repr__(self):
... return "<Address(email_address='%s')>" % self.email_address
同步数据库
Base.metadata.create_all(engine)
测试
>>> jack = User(name='jack', fullname='Jack Bean', password='gjffdd')
>>> jack.addresses
[]
>>> jack.addresses = [
... Address(email_address='jack@google.com'),
... Address(email_address='j25@yahoo.com')]
`>>> jack.addresses[1]
<Address(email_address='j25@yahoo.com')>
>>> jack.addresses[1].user
<User(name='jack', fullname='Jack Bean', password='gjffdd')>`
>>> session.add(jack)
>>> session.commit()