5

We have an application which uses sqlalchemy as ORM. Apart from mapping tables, we also use it to map existing database views, which works fine.

However, we cannot find an easy way to map custom queries to Models. For example, let's say we have a report like:

q = session.query(
    Table1.field1, Table2.field2, func.sum(Table2.field3).label('sum')
).join(
    Table2, Table1.id == Table2.id
).filter(
    Table1.field1 == param1, Table1.field2 == param2
).group_by(
    Table1.field1, Table2.field2
)

We would like to map this query to a class like

class Report(CustomBaseModel):

    field1 = Column(Integer, primary_key=True)
    field2 = Column(Integer, primary_key=True)
    sum = Column(Numeric)

Our CustomBaseModel has a lot of functionality already implemented, so being able to use it here would lead to less code duplication. For instance, using the declarative base this way would make it possible to define relationships with to other tables.

Of course this example doesn't work because there is no selectable "report" in the database.

I tried setting __table__ = q.subquery() in the Report class. This way the query works but it is not possible to define columns, relationships or other properties. Perhaps there is a better way to achieve this.

M.Void
  • 2,764
  • 2
  • 29
  • 44
mathiascg
  • 550
  • 1
  • 5
  • 15
  • 2
    Please take a look at [this answer](https://stackoverflow.com/a/53253105/99594) using [`sqlalchemy-utils`](https://github.com/kvesteri/sqlalchemy-utils) undocumented features. _It will still require you to create views on the database, but these should be taken care of by (alembic) migrations as well._ – van Apr 15 '20 at 11:39
  • Thank you. However, since these reports involve complex aggregations, a views-only approach would not be enough. I thought about database functions as well, although there is no easy way to map them, but I'll have a look at ```sqlalchemy-utils``` to see what other features may be helpful. – mathiascg Apr 15 '20 at 12:43
  • You code example is perfectly `view`-able though. If you needed to add filters to the views, you could do that. Then you could even have `relationship`s using link to the view with the explicitly specified FK. Would be very curious to learn the solution you end up with. – van Apr 17 '20 at 12:59
  • 1
    I'm not sure what you are asking here; what exactly is the problem? *This way the query works but it is not possible to define columns, relationships, or other properties*: The columns come from the query named by `__table__`. You can add relationships and other properties _just fine_. I've done exactly this. Our model, populated from a subquery on another model, has hybrid properties, several relationships (defined both on the model and on other models pointing to this specific model with back references), plus regular methods and properties. – Martijn Pieters Jul 23 '21 at 14:42
  • 1
    If you wanted to add columns to the model, you'll have to add them to the _query_. You can't add columns on the model itself, because there is no table to add those columns to. – Martijn Pieters Jul 23 '21 at 14:45
  • You can build model based on a query `__table__ = q.subquery()` but you have to add `primary_key=True` attribute for one of the columns because at least one column must be as a primary key. So you can do that with `column_property()`. ``` lang-python q = session.query( column_property(Column("field1", Integer, primary_key=True), Table1.field1), func.sum(Table2.field3).label('sum') ).group_by(Table1.field1) class Report(CustomBaseModel): __tablename__ = "report" __table__ = q __mapper_args__ = { 'primary_key': [query.c. field1] } ``` – M.Void Dec 10 '21 at 09:46
  • in addition to my previous comment, I would like share links on SQLAchemy docs: [How do I map a table that has no primary key?](https://docs.sqlalchemy.org/en/14/faq/ormconfiguration.html#how-do-i-map-a-table-that-has-no-primary-key) and [I’m getting a warning or error about “Implicitly combining column X under attribute Y”](https://docs.sqlalchemy.org/en/14/faq/ormconfiguration.html#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y) – M.Void Dec 10 '21 at 09:52

1 Answers1

-2

I dont quite see what kind of behavior are you want to achive, in this example the table Report exist but dosen't needs to, it's to have some data to query in a simplified way, my_query() and my_query_session() make use of a wraper that treats it as a column in the idea that you call the function name in the query and it outputs a result.

@hybrid_property can't not accept *args neither **kwargs and @hybrid_method will.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method
from sqlalchemy.orm import sessionmaker
import sqlalchemy as sql

Base = declarative_base()
db_uri = 'sqlite:///test.db'
engine = sql.create_engine(db_uri)
Session = sessionmaker(bind=engine)
session = Session()

class Report(Base):
    __tablename__ = 'report'
    id = sql.Column(sql.Integer, primary_key=True)
    name = sql.Column(sql.String(30), default='Unknown')

    @hybrid_property
    def my_query(self):
        return self.name

    @hybrid_method
    def my_query_session(self, _session):
        return _session.query(Report).order_by(
            Report.id.desc()).first().name


if __name__ == '__main__':
    Report.__table__.create(engine)
    session.add(Report(name='Alex'))
    session.add(Report(name='Nata'))
    session.commit()

    query_data = session.query(Report).first()
    print(query_data.my_query)
    print(query_data.my_query_session(session))
SrPanda
  • 854
  • 1
  • 5
  • 9