sqlalchemy使用ORM创建VIEW [英] sqlalchemy creating VIEW with ORM

问题描述

我创建了以下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)

我正在尝试找出如何使用sqlalchemy将以下原始SQL view转换为ORM语法.

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;  

找不到关于如何使用ORM进行操作的良好示例.
到目前为止，我的解决方案是只运行原始sql来创建此视图.

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.

任何人都可以指出我正确的方向，还是举一个如何使用sqlalchemy orm创建视图的示例?

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

是否可以使用metadata.create_all()

推荐答案

库sqlalchemy-utils现在包含用于创建视图的功能，并将该视图与sqlalchemy的元数据相关联，以便可以使用Base.metadata.create_all创建视图

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

示例:

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

这篇关于sqlalchemy使用ORM创建VIEW的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！