PFRF SQLAlchemy One to Many Relationship

Let’s assume that we are going to make relationship between Consumer has many address, such as Billing, Shipping etc, address.

  • Consumer : This is the paren entity / model

  • ConsumerAddress : This is the child entity / model


from pf_sqlalchemy.db.orm import Base, database

class Consumer(Base):
    identifier = database.Column("identifier", database.String(200), nullable=False, unique=True)
    email = database.Column("email", database.String(200), unique=True)
    password = database.Column("password", database.String(500), nullable=False)
    addresses = database.relationship(
        primaryjoin="and_(, ConsumerAddress.isDeleted==False)"
  • backref='consumer': Point the reference.

  • lazy=True: This means it will load the data on demand, Lazy has various implementation, please see those.

  • primaryjoin : Telling the joining condition, here we seeing if the address is not deleted then will join by Consumer id with ConsumerAddress consumerId.


from import AddressType
from pf_sqlalchemy.db.orm import Base, database

class ConsumerAddress(Base):
    addressType = database.Column("address_type", database.Enum(AddressType), default=AddressType.REGULAR, nullable=False)
    consumerId = database.Column("consumer_id", database.BigInteger, database.ForeignKey(''), nullable=False)
  • database.ForeignKey('') : It’s indicate database relational pointer or foreign key of the column consumer_id