1

I've defined a model that represents the results of a SELECT statement that joins multiple tables together and performs calculations on the columns

from sqlalchemy import select

class ComplexModel(Base):
    __table__ = select([users_table.c.id, users_table.c.name, orders_table.c.total]).\
                 select_from(users_table.join(orders_table, users_table.c.id == orders_table.c.user_id))

and can use it like:

session.query(ComplexModel).filter(ComplexModel.name == "John").first()

How can I provide an interface that allows an IDE to detect the column names of a complex model is to define the columns as class-level attributes:

class ComplexModel:
    id = Column(Integer)
    name = Column(String)
    total = Column(Float)

errors:

"Can't add additional column 'id' when specifying __table__"

I tried using a view:

same as above but:

__table__ = create_view(
        name="users-orders-view",
        selectable=select(...

But still: Can't add additional column 'id' when specifying __table__

tgk
  • 3,857
  • 2
  • 27
  • 42
  • 1
    Instead of `SELECT` can you create this as an SQL view (depending on your database) - as I believe it might be able to solve the problem more neatly. – Mikko Ohtamaa Jan 10 '23 at 19:22
  • trying this: https://github.com/kvesteri/sqlalchemy-utils/blob/master/tests/test_views.py – tgk Jan 10 '23 at 21:05
  • 1
    I tried, still getting the same – tgk Jan 10 '23 at 22:51
  • I believe views are persistent in the database. So you creaet views by Alembic migrations or by hand. Then `__table__` is just the view name. I might be wrong though, but this is how I did it in the past. – Mikko Ohtamaa Jan 11 '23 at 10:25
  • See here https://stackoverflow.com/a/47514546/315168 – Mikko Ohtamaa Jan 11 '23 at 10:25

0 Answers0