SQLAlchemy操作MariaDBノートの4

統計関数

カウント

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()

1対のマルチリレーション、外部キー

注意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='[email protected]'),
...                 Address(email_address='[email protected]')]
`>>> jack.addresses[1]
<Address(email_address='[email protected]')>

>>> jack.addresses[1].user
<User(name='jack', fullname='Jack Bean', password='gjffdd')>`
>>> session.add(jack)
>>> session.commit()