SQLAlchemy操作MariaDB笔记之四

统计函数

计数

session.query(User).filter(User.name.like('%ed')).count()

分组计数

from sqlalchemy import funcsession.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='[e-mail]'),...                 Address(email_address='[e-mail]')]`>>> jack.addresses[1]<Address(email_address='[e-mail]')>>>> jack.addresses[1].user<User(name='jack', fullname='Jack Bean', password='gjffdd')>`>>> session.add(jack)>>> session.commit()