如何使用SQLAlchemy创建SQL视图？

Question

如何使用SQLAlchemy创建SQL视图？

pythonpostgresqlsqlalchemy

110

有没有“Pythonic”的方法（我是说，不使用“纯SQL”查询）在SQLAlchemy中定义SQL视图？

- Thibaut D.

7个回答

35

Stephan的答案很好，覆盖了大部分内容，但是我不满意的是缺乏与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__)

创建视图：

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

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

时间真是太巧了，我自己也在处理这个问题。对于py 2.7和SQLa 1.1.2（别问为什么...），唯一需要更改的是super(CreateView, self).__init__以及拥有class SampleView(object)。 - Steven Dickinson

1

@Steven Dickinson 是的，听起来很对！是啊，我想这是一个非常常见的任务，所以我很惊讶它的文档如此贫乏/过时/浅显。但嘿，一步一步来吧。 - fgblomqvist

2

对于那些想要以声明方式完成此操作的人，我使用了不同的元数据实例在一个单独的文件中定义了我的视图和表：`Base = declarative_base(metadata=db.MetaData())class ViewSample(Base): tablename = 'view_sample'`我仍然包括了 __definition__ 属性，并调用 CreateView 来按原始帖子中建议的方式创建它。最后，我必须修改修饰方法：if element.element.name.startswith('view_'): return compiler.visit_drop_view(element)因为我找不到一种方法将该属性添加到嵌入式表中。 - Casey

32

这些日子出现了一个适用于此的PyPI软件包：SQLAlchemy Views。

从它的PyPI页面来看：

>>> from sqlalchemy import Table, MetaData
>>> from sqlalchemy.sql import text
>>> from sqlalchemy_views import CreateView, DropView

>>> view = Table('my_view', metadata)
>>> definition = text("SELECT * FROM my_table")

>>> create_view = CreateView(view, definition, or_replace=True)
>>> print(str(create_view.compile()).strip())
CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_table

然而，你要求一个没有"pure SQL"查询的问题，所以你可能想用SQLAlchemy查询对象创建上面的definition。

幸运的是，上面的例子中的text()清楚地表明CreateView的definition参数是这样一个查询对象。所以像这样做应该可以：

>>> from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
>>> from sqlalchemy.sql import select
>>> from sqlalchemy_views import CreateView, DropView

>>> metadata = MetaData()

>>> users = Table('users', metadata,
...     Column('id', Integer, primary_key=True),
...     Column('name', String),
...     Column('fullname', String),
... )

>>> addresses = Table('addresses', metadata,
...   Column('id', Integer, primary_key=True),
...   Column('user_id', None, ForeignKey('users.id')),
...   Column('email_address', String, nullable=False)
...  )

这里有趣的部分：

>>> view = Table('my_view', metadata)
>>> definition = select([users, addresses]).where(
...     users.c.id == addresses.c.user_id
... )
>>> create_view = CreateView(view, definition, or_replace=True)
>>> print(str(create_view.compile()).strip())
CREATE OR REPLACE VIEW my_view AS SELECT users.id, users.name,
users.fullname, addresses.id, addresses.user_id, addresses.email_address 
FROM users, addresses 
WHERE users.id = addresses.user_id

- LeoRochael

24

SQLAlchemy-utils在0.33.6版本中新增了这个功能（可在pypi上获取）。它具有视图、物化视图，并与ORM集成。尚未记录，但我已成功地使用了视图+ORM。

您可以以其测试为例，同时使用ORM创建常规视图和物化视图。

要创建一个视图，在安装包后，请使用上述测试中的以下代码作为视图的基础：

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
    )

在这里，Base是declarative_base，sa是SQLAlchemy包，create_view是sqlalchemy_utils.view中的一个函数。

- bustawin

1

你找到了一种与alembic一起使用的方法吗？ - Jorge Leitao

@JorgeLeitao 我也在想同样的问题。我已经在这里打开了一个工单（https://github.com/sqlalchemy/alembic/issues/785#issuecomment-767128851），并包含了一个我现在正在尝试的解决方法。希望作者能提供更好的解决方案，他通常非常乐于助人。 - totalhack

4

该内容基于https://github.com/sqlalchemy/sqlalchemy/wiki/Views

这是一个只使用sqlalchemy的完整可执行示例，希望您不要花费数小时来使其运行。

import sqlalchemy as sa
import sqlalchemy.schema
import sqlalchemy.ext.compiler


engine = sa.create_engine('postgresql://localhost/postgres')
meta = sa.MetaData()

Session = sa.orm.sessionmaker(bind=engine)
session = Session()


class Drop(sa.schema.DDLElement):
    def __init__(self, name, schema):
        self.name   = name
        self.schema = schema


class Create(sa.schema.DDLElement):
    def __init__(self, name, select, schema='public'):
        self.name   = name
        self.schema = schema
        self.select = select

        sa.event.listen(meta, 'after_create', self)
        sa.event.listen(meta, 'before_drop', Drop(name, schema))


@sa.ext.compiler.compiles(Create)
def createGen(element, compiler, **kwargs):
    return 'CREATE OR REPLACE VIEW {schema}."{name}" AS {select}'.format(
        name   = element.name,
        schema = element.schema,
        select = compiler.sql_compiler.process(
            element.select,
            literal_binds = True
        ),
    )


@sa.ext.compiler.compiles(Drop)
def dropGen(element, compiler, **kw):
    return 'DROP VIEW {schema}."{name}"'.format(
        name   = element.name,
        schema = element.schema,
    )


if __name__ == '__main__':
    view = Create(
        name   = 'myview',
        select = sa.select(sa.literal_column('1 AS col'))
    )
    meta.create_all(bind=engine, checkfirst=True)
    print(session.execute('SELECT * FROM myview').all())
    session.close()

- rho

1

感谢可定制的模式名称。这确实节省了很多时间。另一个小的可能添加 - 使用ORM来查看。 class myview(Base): table = sa.Table('myview', Base.metadata, sa.Column('id', sa.Integer, primary_key=True), schema='myschema' ) - heyzling

1

感谢可定制的模式名称。这确实节省了很多时间。另一个小的可能添加 - 使用ORM来查看。类myview(Base): table = sa.Table('myview', Base.metadata, sa.Column('id', sa.Integer, primary_key=True), schema='myschema' ) - undefined

@heyzling 是的，我使用它们时也是这样做的。 - rho

@heyzling 是的，我使用它们的时候也是这样做的。 - undefined

0

我找不到一个简短而方便的答案。

我不需要视图的额外功能（如果有的话），所以我将视图视为其他表定义的普通表。

因此，基本上我有一个a.py文件，其中定义了所有表和视图、与sql相关的内容，以及main.py文件，我从a.py中导入这些类并使用它们。

这是我在a.py中添加的内容，并且可以正常工作：

class A_View_From_Your_DataBase(Base):
    __tablename__ = 'View_Name'
    keyword = Column(String(100), nullable=False, primary_key=True)

值得注意的是，即使视图中没有主键，您仍需要添加primary_key属性。

- Rick

这个解决方案不起作用，它显示 sqlalchemy.exc.NoSuchTableError。 - Sufiyan Ansari

@user:3443247 在使用之前需要通过 Base.metadata.reflect(views=True) 反映出视图。 - frost-nzcr4

-11

没有纯SQL的SQL视图？您可以创建一个类或函数来实现定义的视图。

function get_view(con):
  return Table.query.filter(Table.name==con.name).first()

- focusheart

3

抱歉，那不是我所问的。我的英语不完美，如果您误解了，我很抱歉 :) - Thibaut D.

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- stephan · Accepted Answer

更新：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)
         )