如何使用 SQLAlchemy 创建 SQL 视图？

Question

是否有一种“Pythonic”方式（我的意思是，没有“纯 SQL”查询）来使用 SQLAlchemy 定义 SQL 视图？

原文由 Thibaut D. 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

更新： SQLAlchemy 现在 在这个主题上有一个很好的使用方法，我推荐。它涵盖了最新的不同 SQL Alchemy 版本，并具有 ORM 集成（请参阅此答案下方的评论和其他答案）。如果您查看版本历史记录，您还可以了解为什么使用 literal_binds 是不确定的（简而言之：绑定参数应该留给数据库），但仍然可以说任何其他解决方案会让大多数用户食谱不开心。我主要出于历史原因留下以下答案。

原始答案： 据我所知，不支持开箱即用地创建（只读非物化）视图。但是在 SQLAlchemy 0.7 中添加此功能非常简单（类似于我在 此处 给出的示例）。您只需要编写一个 编译器扩展 CreateView 。使用此扩展，您可以编写（假设 t 是一个包含列 id 的表对象）

 createview = CreateView('viewname', t.select().where(t.c.id>5))
engine.execute(createview)

v = Table('viewname', metadata, autoload=True)
for r in engine.execute(v.select()):
    print r

这是一个工作示例：

 from sqlalchemy import Table
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Executable, ClauseElement

class CreateView(Executable, ClauseElement):
    def __init__(self, name, select):
        self.name = name
        self.select = select

@compiles(CreateView)
def visit_create_view(element, compiler, **kw):
    return "CREATE VIEW %s AS %s" % (
         element.name,
         compiler.process(element.select, literal_binds=True)
         )

# test data
from sqlalchemy import MetaData, Column, Integer
from sqlalchemy.engine import create_engine
engine = create_engine('sqlite://')
metadata = MetaData(engine)
t = Table('t',
          metadata,
          Column('id', Integer, primary_key=True),
          Column('number', Integer))
t.create()
engine.execute(t.insert().values(id=1, number=3))
engine.execute(t.insert().values(id=9, number=-3))

# create view
createview = CreateView('viewname', t.select().where(t.c.id>5))
engine.execute(createview)

# reflect view and print result
v = Table('viewname', metadata, autoload=True)
for r in engine.execute(v.select()):
    print r

如果你愿意，你也可以专门研究一种方言，例如

@compiles(CreateView, 'sqlite')
def visit_create_view(element, compiler, **kw):
    return "CREATE VIEW IF NOT EXISTS %s AS %s" % (
         element.name,
         compiler.process(element.select, literal_binds=True)
         )

原文由 stephan 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

斯蒂芬的回答很好，涵盖了大多数基础，但让我不满意的是缺乏与 SQLAlchemy 其余部分（ORM、自动删除等）的集成。经过数小时的实验并将来自互联网各个角落的知识拼凑在一起，我得出了以下结论：

 import sqlalchemy_views
from sqlalchemy import Table
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.ddl import DropTable

class View(Table):
    is_view = True

class CreateView(sqlalchemy_views.CreateView):
    def __init__(self, view):
        super().__init__(view.__view__, view.__definition__)

@compiles(DropTable, "postgresql")
def _compile_drop_table(element, compiler, **kwargs):
    if hasattr(element.element, 'is_view') and element.element.is_view:
        return compiler.visit_drop_view(element)

    # cascade seems necessary in case SQLA tries to drop
    # the table a view depends on, before dropping the view
    return compiler.visit_drop_table(element) + ' CASCADE'

请注意，我正在使用 sqlalchemy_views 包，只是为了简化事情。

定义一个视图（例如全局像你的表模型）：

 from sqlalchemy import MetaData, text, Text, Column

class SampleView:
    __view__ = View(
        'sample_view', MetaData(),
        Column('bar', Text, primary_key=True),
    )

    __definition__ = text('''select 'foo' as bar''')

# keeping track of your defined views makes things easier
views = [SampleView]

映射视图（启用 ORM 功能）：

在加载您的应用程序时，在任何查询之前和设置数据库之后执行。

 for view in views:
    if not hasattr(view, '_sa_class_manager'):
        orm.mapper(view, view.__view__)

创建视图：

_在初始化数据库时执行，例如在 createall() 调用之后。

 from sqlalchemy import orm

for view in views:
    db.engine.execute(CreateView(view))

如何查询视图：

 results = db.session.query(SomeModel, SampleView).join(
    SampleView,
    SomeModel.id == SampleView.some_model_id
).all()

这将返回您所期望的结果（一个对象列表，每个对象都有一个 SomeModel 对象和一个 SampleView 对象）。

删除视图：

 SampleView.__view__.drop(db.engine)

它还会在 drop_all() 调用期间自动删除。

这显然是一个非常 hacky 的解决方案，但在我看来，它是目前最好的、最干净的解决方案。这几天我测试了它，没有遇到任何问题。我不确定如何添加关系（在那里遇到问题），但这并不是真正必要的，如上面的查询所示。

如果有人有任何意见，发现任何意想不到的问题，或者知道更好的做事方式，请发表评论或让我知道。

这是在 SQLAlchemy 1.2.6 和 Python 3.6 上测试的。

原文由 fgblomqvist 发布，翻译遵循 CC BY-SA 3.0 许可协议