Use a SQL view with a SQLAlchemy ORM class?

Question

in my Postgres DB I have one table called actions. I found myself doing the same complex query over and over, so I decided to create a view (portfolio).

CREATE TABLE actions(
    id UUID NOT NULL PRIMARY KEY,
    utc_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    action_type TEXT NOT NULL,
    coin TEXT NOT NULL,
    action_id BYTEA NOT NULL,
    amount NUMERIC(13, 8) NOT NULL,
    investment NUMERIC(7, 2) NOT NULL DEFAULT 0.00,
    wallet TEXT NOT NULL,
    FOREIGN KEY(coin) REFERENCES coins(coin_token),
    FOREIGN KEY(wallet) REFERENCES wallets(name),
    FOREIGN KEY(action_type) REFERENCES action_type(name)
);
CREATE VIEW portfolio AS
SELECT coin,
    SUM(amount) AS amount,
    SUM(investment) AS investment,
    SUM(investment) / SUM(amount) AS min_price
FROM actions
GROUP BY coin
HAVING SUM(amount) > 0.00
ORDER BY coin;
CREATE VIEW actual_investment AS
SELECT SUM(investment)
FROM actions
WHERE action_type IN ('DEPOSIT', 'WITHDRAW');

in my python code, I declared a class for actions using sqlalchemy. So far it works as expected, I can read&write to that table. But I haven't found a way to map portfolio something like a read-only table. The workaround that I have implemented is to emulate the SQL query in Python (which I don't like a bit).

is this even possible?

NOTE: I found this thread but I don't have a primary key in the view Is possible to mapping view with class using mapper in SqlAlchemy?

class Portfolio(Base):
    __table__ = Table(
        "portfolio",
        Base.metadata,
        Column("coin", String, primary_key=True),
        Column("amount", Float(precision=13), nullable=False),
        Column("investment", Float(precision=7), nullable=False),
    )

the above code produces the following error:

sqlalchemy.exc.ArgumentError: Mapper mapped class Portfolio->portfolio could not assemble any primary key columns for mapped table 'portfolio'

As always any help or hint will be appreciate it :-)

juanp_1982 · Accepted Answer · 2022-01-20 18:07:12Z

1

I found a "solution" that works in my use case. Since I know the column "coin" in my view will hold unique values, I re-defined that column as a primary key. I guess this will be true for any column that is being grouped by.

I hope this solution works for everyone who runs into this post :-)

edited Jan 20, 2022 at 18:07

answered Jan 18, 2022 at 21:29

juanp_1982

1,0272 gold badges20 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use a SQL view with a SQLAlchemy ORM class?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related