view_test.py

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_utils import create_view

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)


class Article(Base):
    __tablename__ = 'article'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    author_id = sa.Column(sa.Integer, sa.ForeignKey(User.id))
    author = sa.orm.relationship(User)


class ArticleView(Base):
    __table__ = create_view(
        name='article_view',
        selectable=sa.select(
            [
                Article.id,
                Article.name,
                User.id.label('author_id'),
                User.name.label('author_name')
            ],
            from_obj=(
                Article.__table__
                .join(User, Article.author_id == User.id)
            )
        ),
        metadata=Base.metadata
    )

if __name__ == "__main__":
    engine = sa.create_engine('sqlite:///:memory:')
    DBSession = sa.orm.scoped_session(sa.orm.sessionmaker())
    DBSession.configure(bind=engine)
    Base.metadata.create_all(engine)

    # Add db entries
    DBSession.merge(User(name='alice'))
    DBSession.merge(User(name='bob'))
    DBSession.merge(Article(name='paper1', author_id=1))
    DBSession.merge(Article(name='paper2', author_id=2))

    # Query all
    q1 = DBSession.query(ArticleView).all()
    for x in q1:
        print('q1', x.__dict__)

    # Filter results using 'filter()'
    q2 = DBSession.query(ArticleView).filter(ArticleView.id == 2).one()
    print('q2', q2.__dict__)

    # Filter results using 'get()'
    q3 = DBSession.query(ArticleView).get(2)

    # Filter results using 'get()' with keyword
    q4 = DBSession.query(ArticleView).get({ ArticleView.id: 2 })