Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLAlchemy column_property basics

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?

like image 618
drnextgis Avatar asked Aug 29 '12 05:08

drnextgis


People also ask

How can I learn SQLAlchemy?

It is an open source and cross-platform software released under MIT license. SQLAlchemy is famous for its object-relational mapper (ORM), using which classes can be mapped to the database, thereby allowing the object model and database schema to develop in a cleanly decoupled way from the beginning.

How does SQLAlchemy ORM work?

SQLAlchemy is a library that facilitates the communication between Python programs and databases. Most of the times, this library is used as an Object Relational Mapper (ORM) tool that translates Python classes to tables on relational databases and automatically converts function calls to SQL statements.

Should I use SQLAlchemy core or ORM?

If you want to view your data in a more schema-centric view (as used in SQL), use Core. If you have data for which business objects are not needed, use Core. If you view your data as business objects, use ORM. If you are building a quick prototype, use ORM.

Is SQLAlchemy good for ETL?

One of the key aspects of any data science workflow is the sourcing, cleaning, and storing of raw data in a form that can be used upstream. This process is commonly referred to as “Extract-Transform-Load,” or ETL for short.


2 Answers

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)
        )
like image 53
Vladimir Iliev Avatar answered Sep 23 '22 09:09

Vladimir Iliev


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())
    )
like image 31
Matthew Avatar answered Sep 21 '22 09:09

Matthew