Is there a "Pythonic" way (I mean, no "pure SQL" query) to define an SQL view with SQLAlchemy?
7 Answers
Update: SQLAlchemy now has a great usage recipe here on this topic, which I recommend. It covers different SQL Alchemy versions up to the latest and has ORM integration (see comments below this answer and other answers). And if you look through the version history, you can also learn why using literal_binds
is iffy (in a nutshell: binding parameters should be left to the database), but still arguably any other solution would make most users of the recipe not happy. I leave the below answer mostly for historical reasons.
Original answer: 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
Here is a working example:
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)
)
-
Can I use map v with orm.mapper? like
v = Table('viewname', metadata, autoload=True) class ViewName(object): def __init__(self, name): self.name = name mapper(ViewName, v)
Above is possible? Because I will use View with session. Commented Dec 11, 2013 at 11:27 -
1@SyedHabibM: yes, this is possible. You have to manually set the primary key, though, something like
orm.mapper(ViewName, v, primary_key=pk, properties=prop)
wherepk
andprop
are your primary key (or keys) and properties respectively. See docs.sqlalchemy.org/en/latest/orm/….– stephanCommented Dec 12, 2013 at 7:58 -
2@SyedHabibM: you can do what stephan mentioned also when you use autoloaded tables by overriding a column specification and specify a PK:
v = Table('viewname', metadata, Column('my_id_column', Integer, primary_key=True), autoload=True)
– vanCommented Dec 12, 2013 at 8:23 -
@SyedHabibMI did answer your respective question stackoverflow.com/q/20518521/92092 with a working example now. I'll add van's comment there, too.– stephanCommented Dec 12, 2013 at 8:26
stephan's answer is a good one and covers most bases, but what left me unsatisfied was the lack of integration with the rest of SQLAlchemy (the ORM, automatic dropping etc.). After hours of experimenting and piecing together knowledge from all corners of the internet I came up with the following:
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'
Note that I am utilizing the sqlalchemy_views
package, just to simplify things.
Defining a view (e.g. globally like your Table models):
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]
Mapping the views (enable ORM functionality):
Do when loading up your app, before any queries and after setting up the DB.
for view in views:
if not hasattr(view, '_sa_class_manager'):
orm.mapper(view, view.__view__)
Creating the views:
Do when initializing the database, e.g. after a create_all() call.
from sqlalchemy import orm
for view in views:
db.engine.execute(CreateView(view))
How to query a view:
results = db.session.query(SomeModel, SampleView).join(
SampleView,
SomeModel.id == SampleView.some_model_id
).all()
This would return exactly what you expect (a list of objects that each has a SomeModel object and a SampleView object).
Dropping a view:
SampleView.__view__.drop(db.engine)
It will also automatically get dropped during a drop_all() call.
This is obviously a very hacky solution but in my eyes it is the best one and cleanest one out there at the moment. I have tested it these past few days and have not had any issues. I'm not sure how to add in relationships (ran into problems there) but it's not really necessary, as demonstrated above in the query.
If anyone has any input, finds any unexpected issues, or knows a better way to do things, please do leave a comment or let me know.
This was tested on SQLAlchemy 1.2.6 and Python 3.6.
-
Crazy timing, just been dealing with this myself. For py 2.7 and SQLa 1.1.2 (don't ask...), the only changes needed were the
super(CreateView, self).__init__
and having theclass SampleView(object)
Commented Apr 18, 2018 at 10:23 -
1@Steven Dickinson yup sounds about right! Yeah I figured this is a really common task, which is why I was surprised that the documentation on it was so poor/outdated/shallow. But hey, one step at a time I suppose. Commented Apr 18, 2018 at 16:34
-
2For those looking to do this declaratively, I defined my views in a separate file from my tables with a different metadata instance:
Base = declarative_base(metadata=db.MetaData()) class ViewSample(Base): __tablename__ = 'view_sample'
I still included the__definition__
property and called CreateView to create it as suggested in the original post. Finally, I had to modify the drop decorated method:if element.element.name.startswith('view_'): return compiler.visit_drop_view(element)
because I could not find a way to add the property to the embedded table.– CaseyCommented Nov 20, 2018 at 17:17
These days there's a PyPI package for that: SQLAlchemy Views.
From it's PyPI Page:
>>> 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
However, you asked for a no "pure SQL" query, so you probably want the definition
above to be created with SQLAlchemy query object.
Luckily, the text()
in the example above makes it clear that the definition
parameter to CreateView
is such a query object. So something like this should work:
>>> 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)
... )
Here is the interesting bit:
>>> 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 just added this functionality in 0.33.6 (available in pypi). It has views, materialized views, and it integrates with the ORM. It is not documented yet, but I am successfully using the views + ORM.
You can use their test as an example for both regular and materialized views using the ORM.
To create a view, once you install the package, use the following code from the test above as a base for your view:
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
)
Where Base
is the declarative_base
, sa
is the SQLAlchemy
package, and create_view
is a function from sqlalchemy_utils.view
.
-
1Have you found a way of using it together with alembic? Commented Nov 6, 2020 at 14:58
-
-
Loosely based on https://github.com/sqlalchemy/sqlalchemy/wiki/Views
Complete executable example with sqlalchemy
only, hope you don't spend hours just to make it run.
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()
-
1Thanks for customizable schema name. It certainly saved hours. Another small possible addition - usage of ORM for view. class myview(Base): table = sa.Table('myview', Base.metadata, sa.Column('id', sa.Integer, primary_key=True), schema='myschema' )– heyzlingCommented Jun 7, 2023 at 21:08
-
I couldn't find an short and handy answer.
I don't need extra functionality of View (if any), so I simply treat a view as an ordinary table as other table definitions.
So basically I have a.py
where defines all tables and views, sql related stuff, and main.py
where I import those class from a.py
and use them.
Here's what I add in a.py
and works:
class A_View_From_Your_DataBase(Base):
__tablename__ = 'View_Name'
keyword = Column(String(100), nullable=False, primary_key=True)
Notably, you need to add the primary_key
property even though there's no primary key in the view.
-
This solution didn't work, it says
sqlalchemy.exc.NoSuchTableError
Commented Jan 28, 2021 at 17:57 -
@user:3443247 you need to reflect the views before usage with
Base.metadata.reflect(views=True)
Commented Jun 14, 2022 at 15:41
SQL View without pure SQL? You can create a class or function to implement a defined view.
function get_view(con):
return Table.query.filter(Table.name==con.name).first()
-
3Sorry but that's not what I asked. My english is not perfect, I'm sorry if you misunderstood :) Commented Mar 19, 2012 at 14:47