有没有“Pythonic”的方法(我是说,不使用“纯SQL”查询)在SQLAlchemy中定义SQL视图?
有没有“Pythonic”的方法(我是说,不使用“纯SQL”查询)在SQLAlchemy中定义SQL视图?
更新: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的答案很好,覆盖了大部分内容,但是我不满意的是缺乏与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上进行测试的。
super(CreateView, self).__init__
以及拥有class SampleView(object)
。 - Steven Dickinson__definition__
属性,并调用 CreateView 来按原始帖子中建议的方式创建它。
最后,我必须修改修饰方法:if element.element.name.startswith('view_'): return compiler.visit_drop_view(element)
因为我找不到一种方法将该属性添加到嵌入式表中。 - Casey>>> 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
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
中的一个函数。
该内容基于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()
我找不到一个简短而方便的答案。
我不需要视图的额外功能(如果有的话),所以我将视图视为其他表定义的普通表。
因此,基本上我有一个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
属性。sqlalchemy.exc.NoSuchTableError
。 - Sufiyan AnsariBase.metadata.reflect(views=True)
反映出视图。 - frost-nzcr4没有纯SQL的SQL视图? 您可以创建一个类或函数来实现定义的视图。
function get_view(con):
return Table.query.filter(Table.name==con.name).first()
v = Table('viewname', metadata, autoload=True) class ViewName(object): def __init__(self, name): self.name = name mapper(ViewName, v)
这样做是可行的吗?因为我将在 session 中使用 View。 - Syed Habib Morm.mapper(ViewName, v, primary_key=pk, properties=prop)
,其中pk
和prop
分别是你的主键(或主键)和属性。请参阅 http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#sqlalchemy.orm.mapper。 - stephanv = Table('viewname', metadata, Column('my_id_column', Integer, primary_key=True), autoload=True)
。 - van