VIEW names?
Martin
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
Michael Bayer
On Jan 25, 2008, at 7:25 PM, Martin wrote:
>
> 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?)
we dont provide a function for this currently.
adding reflection for views is not a big deal, but the decision to be
made is how it would be expressed in the API, either as Table(....,
view=True), or View(...). we'd have to decide how view-aware we want
SA to be (i.e. would View act read-only, etc.. though I guess VIEWs
aren't necessarily purely read-only in some cases ?).
Barry Hart
>aren't necessarily purely read-only in some cases ?).
Barry
Looking for last minute shopping deals? Find them fast with Yahoo! Search.
Kevin Cole
>
>
> >SA to be (i.e. would View act read-only, etc.. though I guess VIEWs
> >aren't necessarily purely read-only in some cases ?).
>
> Right. For example, I think SQL Server views are updateable to some extent
> (depending on whether there's a table primary key in the column list,
> whether there are joins, etc.) The rules for this are almost certainly
> DB-specific, too.
<RANT ON>
Well, since the disciples of SQL keep telling me how much clearer it
is than other database languages... The Merriam-Webster defines view
as:
1: extent or range of vision : sight <tried to keep the ship in view>
<sat high in the bleachers to get a good view>
2: the act of seeing or examining : inspection; also : survey <a view
of English literature>
3 a: a mode or manner of looking at or regarding something
b: an opinion or judgment colored by the feeling or bias of its
holder <in my view the plan will fail>
4: scene prospect <the lovely view from the balcony>
5: the foreseeable future <no hope in view>
6: a pictorial representation
Touch, change, and update have very different definitions. So, for
clarity, view should mean... view, regardless of what mangling of
English various implementations of SQL try to perform. Look but don't
touch. Read-only.
<RANT OFF>
--
Ubuntu Linux DC LoCo
Washington, DC
http://dc.ubuntu-us.org/