YKRLLGgG

1createview = CreateView('viewname', t.select().where(t.c.id>5))
2engine.execute(createview)
3
4v = Table('viewname', metadata, autoload=True)
5for r in engine.execute(v.select()):
6    print r
7	
8from sqlalchemy import Table
9from sqlalchemy.ext.compiler import compiles
10from sqlalchemy.sql.expression import Executable, ClauseElement
11
12class CreateView(Executable, ClauseElement):
13    def __init__(self, name, select):
14        self.name = name
15        self.select = select
16
17@compiles(CreateView)
18def visit_create_view(element, compiler, **kw):
19    return "CREATE VIEW %s AS %s" % (
20         element.name,
21         compiler.process(element.select, literal_binds=True)
22         )
23
24# test data
25from sqlalchemy import MetaData, Column, Integer
26from sqlalchemy.engine import create_engine
27engine = create_engine('sqlite://')
28metadata = MetaData(engine)
29t = Table('t',
30          metadata,
31          Column('id', Integer, primary_key=True),
32          Column('number', Integer))
33t.create()
34engine.execute(t.insert().values(id=1, number=3))
35engine.execute(t.insert().values(id=9, number=-3))
36
37# create view
38createview = CreateView('viewname', t.select().where(t.c.id>5))
39engine.execute(createview)
40
41# reflect view and print result
42v = Table('viewname', metadata, autoload=True)
43for r in engine.execute(v.select()):
44    print r
45	
46@compiles(CreateView, 'sqlite')
47def visit_create_view(element, compiler, **kw):
48    return "CREATE VIEW IF NOT EXISTS %s AS %s" % (
49         element.name,
50         compiler.process(element.select, literal_binds=True)
51         )
52	
53>>> from sqlalchemy import Table, MetaData
54>>> from sqlalchemy.sql import text
55>>> from sqlalchemy_views import CreateView, DropView
56
57>>> view = Table('my_view', metadata)
58>>> definition = text("SELECT * FROM my_table")
59
60>>> create_view = CreateView(view, definition, or_replace=True)
61>>> print(str(create_view.compile()).strip())
62CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_table
63	
64>>> from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
65>>> from sqlalchemy.sql import select
66>>> from sqlalchemy_views import CreateView, DropView
67
68>>> metadata = MetaData()
69
70>>> users = Table('users', metadata,
71...     Column('id', Integer, primary_key=True),
72...     Column('name', String),
73...     Column('fullname', String),
74... )
75
76>>> addresses = Table('addresses', metadata,
77...   Column('id', Integer, primary_key=True),
78...   Column('user_id', None, ForeignKey('users.id')),
79...   Column('email_address', String, nullable=False)
80...  )
81	
82>>> view = Table('my_view', metadata)
83>>> definition = select([users, addresses]).where(
84...     users.c.id == addresses.c.user_id
85... )
86>>> create_view = CreateView(view, definition, or_replace=True)
87>>> print(str(create_view.compile()).strip())
88CREATE OR REPLACE VIEW my_view AS SELECT users.id, users.name,
89users.fullname, addresses.id, addresses.user_id, addresses.email_address 
90FROM users, addresses 
91WHERE users.id = addresses.user_id
92	
93function get_view(con):
94  return Table.query.filter(Table.name==con.name).first()