How to Create an SQL View with SQLalchemy

How to create an SQL View with SQLAlchemy?

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

sqlalchemy creating VIEW with ORM

the library sqlalchemy-utils now includes functionality for creating views, and it associates the view with sqlalchemy's metadata so that it is possible to create the view using Base.metadata.create_all

example:

# installation: pip install sqlalchemy-utils
from sqlalchemy_utils import create_view
from sqlalchemy import select, func

# engine Base & Table declaration elided for brevity

stmt = select([
    TableB.id.label('table_b_id'),
    TableB.coupon_code,
    TableB.number_of_rebought_items,
    TableA.id.label('table_a_action_id'),
    TableA.time,
    TableA.parameters
]).select_from(TableB.__table__.outerjoin(TableA, TableB.generate_action == TableA.id))

# attaches the view to the metadata using the select statement
view = create_view('my_view', stmt, Base.metadata)

# provides an ORM interface to the view
class MyView(Base):
    __table__ = view

# will create all tables & views defined with ``create_view``
Base.metadata.create_all()

# At this point running the following yields 0, as expected,
# indicating that the view has been constructed on the server 
engine.execute(select([func.count('*')], from_obj=MyView)).scalar() 

SQLAlchemy Create View in PostgresQL

I found the solution. The issue was to do with autocommit. Setting autocommit to true when creating the engine solved the issue as follows:

engine = db.create_engine('postgresql://user:pass@localhost:5432/db_name').execution_options(autocommit=True)

Special mention to @ilja-everilä

Use a SQL view with a SQLAlchemy ORM class?

I found a "solution" that works in my use case. Since I know the column "coin" in my view will hold unique values, I re-defined that column as a primary key. I guess this will be true for any column that is being grouped by.

I hope this solution works for everyone who runs into this post :-)

Is possible to mapping view with class using mapper in SqlAlchemy?

You can do this, but you have to define a primary key manually. Assuming that id is a column of v which you want to use as the primary key (as is the case in my original example code), this works:

from sqlalchemy import orm

class ViewName(object):
    def __init__(self, name):
       self.name = name

orm.mapper(ViewName, v, primary_key=[v.c.id])

Session = orm.sessionmaker(bind=engine)
for r in Session().query(ViewName):
    print r.id, r.number

To test this, just paste this snippet at the end of my working example in the answer linked above. See the documentation for more details (e.g. you can use properties to define foreign keys).

EDIT (van in a comment to my answer linked above):
Alternatively, you can change the view definiton in my original code (and your question) slightly and write:

v = Table('viewname', metadata, Column('id', Integer, primary_key=True), autoload=True)

i.e., add the primary key in the table definition already. Then you don't need the primary_key argument in the orm.mapper and the rest of the code in your question works out of the box.

SQLAlchemy view handling

The use of reflect parameter of MetaData has been deprecated since version 0.8 and should be replaced with the more expressive MetaData.reflect() method, which allows reflecting views as well:

meta = MetaData(Engine)
meta.reflect(views=True)
nav = meta.tables['name_address_vw']

---

Related Topics