如何使用 SQLAlchemy 创建 SQL 视图? [英] How to create an SQL View with SQLAlchemy?

问题描述

是否有一种Pythonic"方式(我的意思是，没有纯 SQL"查询)使用 SQLAlchemy 定义 SQL 视图?

Is there a "Pythonic" way (I mean, no "pure SQL" query) to define an SQL view with SQLAlchemy?

推荐答案

更新: 另见 SQLAlchemy 使用配方 这里

Update: See also the SQLAlchemy usage recipe here

据我所知，不支持开箱即用地创建(只读非物化)视图.但是在 SQLAlchemy 0.7 中添加这个功能很简单(类似于我在这里给出的例子).你只需要编写一个 编译器扩展 CreateView.有了这个扩展，你就可以写(假设 t 是一个带有 id 列的表对象)

Creating a (read-only non-materialized) view is not supported out of the box as far as I know. But adding this functionality in SQLAlchemy 0.7 is straightforward (similar to the example I gave here). You just have to write a compiler extension CreateView. With this extension, you can then write (assuming that t is a table object with a column 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

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

If you want, you can also specialize for a dialect, e.g.

@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)
         )

这篇关于如何使用 SQLAlchemy 创建 SQL 视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！