SQLAlchemy column_property basics

Question

I have two models:

class Report(Base):
    __tablename__ = 'report'
    id = Column(Integer, primary_key=True)

class ReportPhoto(Base):
    __tablename__ = 'report_photo'
    id = Column(Integer, primary_key=True)
    report_id = Column(Integer, ForeignKey(Report.id), nullable=False)

    report = relationship(Report, uselist=False, backref=backref('report_photo', uselist=True))

And I would like to add column to Report model which indicates is there any records within ReportPhoto. I try to use column_property this way:

class Report(Base):
    __tablename__ = 'report'
    id = Column(Integer, primary_key=True)

    has_photo = column_property(
        select(ReportPhoto.any())
    )

but get an error NameError: name 'ReportPhoto' is not defined. How I can fix this issue?

score 6 · Answer 1 · answered Aug 31 '12 at 23:23

something like that should work:

    class ReportPhoto(Base):
        __tablename__ = 'report_photo'
        id = Column(Integer, primary_key=True)
        report_id = Column(Integer, ForeignKey('report.id'), nullable=False)

    class Report(Base):
        __tablename__ = 'report'
        id = Column(Integer, primary_key=True)
        report_photos = relationship(ReportPhoto, backref='report')
        has_photo = column_property(
            exists().where(ReportPhoto.report_id==id)
        )

score 5 · Accepted Answer · answered Jan 06 '14 at 04:57

I will add to @Vladimir lliev's response with some clarification for anyone else that might not see how to do this.

Place the table that will have the 'foreign table referencing' column_property after that which it references. In this case, it means placing Report after ReportPhoto. This will solve your NameError, however, you would be left with a new error on your ReportPhoto foreign key reference. To solve this, place your foreign key table reference in quotes. You can read more by referencing the declarative documentation (e.g., declarative.py) and looking under "Configuring Relationships" --- specifically, read the portion on quoting your foreign references.

With your code, this would look like:

class ReportPhoto(Base):
    # This now goes first
    __tablename__ = 'report_photo'
    id = Column(Integer, primary_key=True)
    # Notice the quotations around Report references here
    report_id = Column(Integer, ForeignKey("Report.id"), nullable=False)

    # Notice the quotations around Report references here
    report = relationship("Report", 
           uselist=False, 
           backref=backref("report_photo", uselist=True))

class Report(Base):
    # This is now _after_ ReportPhoto
    __tablename__ = 'report'
    id = Column(Integer, primary_key=True)

    # ReportPhoto now exists and we will not trip a NameError exception
    has_photo = column_property(
        select(ReportPhoto.any())
    )

Is `column_property` accessing the joined `ReportPhoto` record or is it returning a random `ReportPhoto` record? — ChaimG, Mar 25 '18 at 14:23

SQLAlchemy column_property basics

2 Answers2