如何使用SQLAlchemy创建SQL视图? [英] How to create an SQL View with SQLAlchemy?
问题描述
是否有"Pythonic"方式(我的意思是,没有"pure SQL"查询)来使用SQLAlchemy定义SQL视图?
Is there a "Pythonic" way (I mean, no "pure SQL" query) to define an SQL view with 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屋!