Hello,
since I didn't find a direct way to create a VIEW within SQLalchemy
v0.4, I use the
"text"-feature to do that with a SQL/DDL statement, which is maybe not
elegant, but works...
Is there a way to get information about Views? (Which Views exist and
which columns do they provide?)
SQLalchemy maintains a list of table names, but this list seems not to
include Views ("virtual tables")
(at least with the test code at the bottom) and I didn't find any
view_* related list or method.
AFAIK, SQL doesn't offer this information and I don't want to
introduce DBMS specific code
to access special system tables. (It'll be great if the same code
works with SQLite, MySQL, Postgresql...)
The metadata.table_iterator() at the end of the test-code will print
"view2" after a Table was added to the
metadata with the known name 'View2'.
Thank you,
Martin Bernreuther
#! /usr/bin/env python
import sqlalchemy
print sqlalchemy.__version__
import datetime
engine = sqlalchemy.create_engine('sqlite:///testview.db', echo=True)
metadata = sqlalchemy.MetaData()
metadata.bind=engine
table1 = sqlalchemy.Table('Table1', metadata,
sqlalchemy.Column('id', sqlalchemy.Integer,
sqlalchemy.Sequence('id1_seq'), primary_key=True),
sqlalchemy.Column('timestamp', sqlalchemy.DateTime,
default=datetime.datetime.now()),
)
table2 = sqlalchemy.Table('Table2', metadata,
sqlalchemy.Column('Table1_id', sqlalchemy.Integer,
sqlalchemy.ForeignKey("Table1.id")),
sqlalchemy.Column('date', sqlalchemy.Date,
default=datetime.date.today()),
)
metadata.create_all(engine)
conn = engine.connect()
try:
conn.execute(sqlalchemy.text("CREATE VIEW View2 AS SELECT * FROM
Table2 INNER JOIN Table1 ON Table2.Table1_id=Table1.id"))
except sqlalchemy.exceptions.OperationalError, strerror:
print "could not create View2: ", strerror
print "engine.table_names():\t", engine.table_names()
if not engine.has_table("View2"): print "View2 not found within
table_names!"
for t in metadata.table_iterator(): print t
view2 = sqlalchemy.Table('View2', metadata, autoload=True)
conn.execute(table1.insert(), id=1)
conn.execute(table2.insert(), Table1_id=1)
result=conn.execute(sqlalchemy.select([view2]))
row=result.fetchone()
print row
conn.close()
print "engine.table_names():\t", engine.table_names()
for t in metadata.table_iterator(): print t
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
