¿Cómo crear una vista SQL con SQLAlchemy?

Todo está en el título. ¿Existe una forma “Pythonic” (quiero decir, no una consulta “SQL pura”) para definir una vista SQL con SQLAlchemy?

Gracias por tu ayuda,

Actualización: vea también la receta de uso de SQLAlchemy aquí

La creación de una vista (de solo lectura no materializada) no se admite de forma inmediata por lo que sé. Pero agregar esta funcionalidad en SQLAlchemy 0.7 es sencillo (similar al ejemplo que doy aquí ). Sólo tienes que escribir una extensión del comstackdor CreateView . Con esta extensión, puede escribir (suponiendo que t es un objeto de tabla con un id columna)

 createview = CreateView('viewname', t.select().where(tcid>5)) engine.execute(createview) v = Table('viewname', metadata, autoload=True) for r in engine.execute(v.select()): print r 

Aquí hay un ejemplo de trabajo:

 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(tcid>5)) engine.execute(createview) # reflect view and print result v = Table('viewname', metadata, autoload=True) for r in engine.execute(v.select()): print r 

Si lo desea, también puede especializarse para un dialecto, por ejemplo,

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

En estos días hay un paquete PyPI para eso: SQLAlchemy Views .

Desde su página de 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 

Sin embargo, solicitó una consulta sin “SQL puro” , por lo que probablemente desee que la definition anterior se cree con el objeto de consulta SQLAlchemy.

Afortunadamente, el text() en el ejemplo anterior deja claro que el parámetro de definition para CreateView es un objeto de consulta de este tipo. Así que algo como esto debería funcionar:

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

Aquí está el bit interesante:

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

La respuesta de stephan es buena y cubre la mayoría de las bases, pero lo que me dejó insatisfecho fue la falta de integración con el rest de SQLAlchemy (el ORM, la caída automática, etc.). Después de horas de experimentar y juntar el conocimiento de todos los rincones de Internet, se me ocurrió lo siguiente:

 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' 

Tenga en cuenta que estoy utilizando el paquete sqlalchemy_views , solo para simplificar las cosas.

Definir una vista (por ejemplo, globalmente como sus modelos de tabla):

 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] 

Mapeo de las vistas (habilitar la funcionalidad ORM):

Al cargar su aplicación, antes de realizar cualquier consulta y después de configurar la base de datos.

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

Creando las vistas:

Al inicializar la base de datos, ej. Después de una llamada a create_all ().

 from sqlalchemy import orm for view in views: db.engine.execute(CreateView(view)) 

Cómo consultar una vista:

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

Esto devolvería exactamente lo que espera (una lista de objetos que cada uno tiene un objeto SomeModel y un objeto SampleView).

Bajando una vista:

 SampleView.__view__.drop(db.engine) 

También se caerá automáticamente durante una llamada drop_all ().

Obviamente, esta es una solución muy intrincada pero, en mi opinión, es la mejor y más limpia que existe en este momento. Lo he probado estos últimos días y no he tenido ningún problema. No estoy seguro de cómo agregar relaciones (se encontró con problemas allí) pero no es realmente necesario, como se demostró anteriormente en la consulta.

Si alguien tiene algún aporte, encuentra algún problema inesperado o conoce una mejor manera de hacer las cosas, deje un comentario o hágamelo saber.

Esto se probó en SQLAlchemy 1.2.6 y Python 3.6.

SQLAlchemy-utils acaba de agregar esta funcionalidad en 0.33.6 (disponible en pypi). Tiene vistas, vistas materializadas y se integra con el ORM. Aún no está documentado, pero estoy usando con éxito las vistas + ORM.

Puede usar su prueba como ejemplo para las vistas regulares y materializadas utilizando el ORM.

Para crear una vista, una vez que instale el paquete, use el siguiente código de la prueba anterior como base para su vista:

 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 ) 

Donde Base es declarative_base , sa es el paquete SQLAlchemy y create_view es una función de sqlalchemy_utils.view .

No pude encontrar una respuesta corta y práctica.

No necesito la funcionalidad adicional de la Vista (si existe), por lo que simplemente trato una vista como una tabla ordinaria como otras definiciones de tabla.

Así que básicamente tengo a.py donde define todas las tablas y vistas, cosas relacionadas con sql y main.py donde importo esas clases de a.py y las uso.

Esto es lo que agrego en a.py y funciona:

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

En particular, debe agregar la propiedad primary_key aunque no haya una clave principal en la vista.

SQL View sin SQL puro? Puede crear una clase o función para implementar una vista definida.

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