sqlalchemy creating VIEW with ORM

Question

I created the following ORM:

from sqlalchemy import Column, Integer, String, UniqueConstraint
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()


class TableA(Base):
    __tablename__ = 'table_a'

    id = Column(Integer, primary_key=True, nullable=False)
    identifier = Column(String(320))
    internal_id = Column(Integer)
    type = Column(String(32))
    time = Column(DateTime(timezone=True))
    success = Column(Boolean())
    parameters = Column(JSONB())



class TableB(Base):
    __tablename__ = 'table_b'
    __table_args__ = (UniqueConstraint('generate_action',
                                       'print_action',
                                        name='my_action_key'),)

    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    generate_action = Column(Integer)
    print_action = Column(Integer)
    generate_action = Column(Integer)
    coupon_code = Column(String(300))
    number_of_rebought_items = Column(Integer)
    seconds_between_rebuy = Column(Integer)

I'm trying to figure out how to convert the following raw SQL view to ORM syntax with sqlalchemy.

CREATE VIEW my_view AS
    SELECT table_b.id as table_b_id,
        tb.coupon_code as coupon_code,
        tb.number_of_rebought_items as number_of_rebought_items,
        ta.id as table_a_action_id,
        ta.time as time,
        ta.parameters as parameters,
    FROM table_b tb
    LEFT JOIN table_a ta on
        ta.id = tb.generate_action;

Couldn't find any good examples out there of how to do it with ORM.
So far, my solution is to just run raw sql to create this view.

can anyone point me to the right direction, or give an example of how to create views with sqlalchemy orm?

Is it possible to create the views with metadata.create_all()

Possible duplicate of [How to create an SQL View with SQLAlchemy?](http://stackoverflow.com/questions/9766940/how-to-create-an-sql-view-with-sqlalchemy) — univerio, Oct 17 '16 at 16:14
No, that question and answer focuses on using the sqlalchemy Table class, whereas this question is focused on a data model set up with declarative_base. — Elaine Hale, Feb 02 '18 at 18:32
For example, all of the tables declared in the question can be created with one line of code: Base.metadata.create_all(engine). Is there a similar way to declare select statements as views that will be instantiated with the create_all call or something similar? — Elaine Hale, Feb 02 '18 at 18:39

Haleemur Ali · Accepted Answer · 2019-05-19T06:04:54.517

the library sqlalchemy-utils now includes functionality for creating views, and it associates the view with sqlalchemy's metadata so that it is possible to create the view using Base.metadata.create_all

example:

# installation: pip install sqlalchemy-utils
from sqlalchemy_utils import create_view
from sqlalchemy import select, func

# engine Base & Table declaration elided for brevity

stmt = select([
    TableB.id.label('table_b_id'),
    TableB.coupon_code,
    TableB.number_of_rebought_items,
    TableA.id.label('table_a_action_id'),
    TableA.time,
    TableA.parameters
]).select_from(TableB.__table__.outerjoin(TableA, TableB.generate_action == TableA.id))

# attaches the view to the metadata using the select statement
view = create_view('my_view', stmt, Base.metadata)

# provides an ORM interface to the view
class MyView(Base):
    __table__ = view

# will create all tables & views defined with ``create_view``
Base.metadata.create_all()

# At this point running the following yields 0, as expected,
# indicating that the view has been constructed on the server 
engine.execute(select([func.count('*')], from_obj=MyView)).scalar()

I was unable to make this work with alembic's autogenerate, though. — Jorge Leitao, Nov 06 '20 at 14:26

sqlalchemy creating VIEW with ORM

1 Answers1

Linked