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 am trying to figure out how to convert the following raw SQL viewto ORM syntax using 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;  

Could not find good examples of how to do this with ORM.
So far, my solution is to just run raw sql to create this view.

can someone point me in the right direction or give an example of how to create views using sqlalchemy orm?

Is it possible to create views using metadata.create_all()

Answer 1

sqlalchemy-utils sqlalchemy, Base.metadata.create_all

:

# 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()