VIEW alternative in SQLAlchemy
AF
I have a table of records in the database that I want to run read
queries against, but I do want to include all of them in the search.
(There are a couple of filtering parameters to exclude records from
the searched pool, including an aptly named "is_active" flag.)
Traditionally, I would think to put this in to a VIEW, and let the DB
optimise out all the non-active records.
Since SQLAlchemy does appear to support views, what is the correct way
to handle this? (Or did i miss it?)
I am using declarative base.
Thanks
:)
Michael Bayer
a view looks just like a table to SQLAlchemy. the only thing that might
not work is reflecting it. otherwise you can just pretend its a Table
(just can't flush to it).
allen.fowler
I am using SQLAlchemy's Declarative Base to fully define and create my
database.
For instance, there is a simple class/table Records, and I would like
to define a class CurrentRecords that is implemented in the database
as a view on Records.
In this way, I can avoid polluting my application code with filtering
out all the non-active records every time I want to query Records.
Thank you,
:)
werner
allen.fowler wrote:
...
> To clarify:
>
> I am using SQLAlchemy's Declarative Base to fully define and create my
> database.
>
> For instance, there is a simple class/table Records, and I would like
> to define a class CurrentRecords that is implemented in the database
> as a view on Records.
>
> In this way, I can avoid polluting my application code with filtering
> out all the non-active records every time I want to query Records.
>
Just define CurrentRecords as a table, i.e. in my app one of my views is:
class Vconsumption(Base):
__table__ = sa.Table(u'vconsumption', metadata,
sa.Column(u'consumptionid', sa.Integer(),
sa.ForeignKey(u'consumption.consumptionid'), primary_key=True),
...
)
consumption = sao.relation(Consumption)
And I relate it back to the real consumption table, but never
write/flush the view and do have to have a unique key which you define
as "primary key" to SA.
Werner
allen.fowler
Consumption table? I am not clear on how you are using this.... can
you clarify?
As an aside, I wonder if it is possible to just subclass my Records
object so that the CurrentRecords class adds/enforces certain
filter_by parameters for any query against it.
Thank you. :)
werner
>
>> ...> To clarify:
>>
>>
>>> I am using SQLAlchemy's Declarative Base to fully define and create my
>>> database.
>>>
>>> For instance, there is a simple class/table Records, and I would like
>>> to define a class CurrentRecords that is implemented in the database
>>> as a view on Records.
>>>
>>> In this way, I can avoid polluting my application code with filtering
>>> out all the non-active records every time I want to query Records.
>>>
>> Just define CurrentRecords as a table, i.e. in my app one of my views is:
>>
>> class Vconsumption(Base):
>> __table__ = sa.Table(u'vconsumption', metadata,
>> sa.Column(u'consumptionid', sa.Integer(),
>> sa.ForeignKey(u'consumption.consumptionid'), primary_key=True),
>> ...
>> )
>>
>> consumption = sao.relation(Consumption)
>>
>> And I relate it back to the real consumption table, but never
>> write/flush the view and do have to have a unique key which you define
>> as "primary key" to SA.
>>
>> Werner
>>
>
> What functional gain does this approuch provide over just querying the
> Consumption table? I am not clear on how you are using this.... can
> you clarify?
>
from four tables plus a description which comes from one of two tables,
but in the application I sometimes need more details from the actual
consumption details, therefore the relation.
> object so that the CurrentRecords class adds/enforces certain
> filter_by parameters for any query against it.
>
Werner
Partial view definition:
CREATE VIEW VCONSUMPTION(
CONSUMPTIONID,
FK_CBBOTTLEID,
DESCRIPTION,
FK_CONSEVENTID,
FK_CONSBATCHID)
AS
select con.consumptionid,
con.fk_cbbottleid,
coalesce(cb.description, ce.description),
ce.conseventid,
cb.consbatchid
etc
> Thank you. :)
>
>
>
> >
>
>
Ross Vandegrift
> As an aside, I wonder if it is possible to just subclass my Records
> object so that the CurrentRecords class adds/enforces certain
> filter_by parameters for any query against it.
Yes - SA's ORM can map to arbitrary Selectable object. Selectable is
the base class for things like select() in SA. This feature is
awesome! Rather than specifying your Table object to orm.mapper,
specify a Selectable that returns rows that are what you want to see.
--
Ross Vandegrift
ro...@kallisti.us
"If the fight gets hot, the songs get hotter. If the going gets tough,
the songs get tougher."
--Woody Guthrie
allen.fowler
further reflection, it occurs to me that these various work arounds
all forgo one critical aspect that makes SQLAlchemy so useful.
Namely, the ability to use use SQLAlchey to fully create and populate
my database in a database agnostic manner. For simple applications
like mine, this is a big part of keeping "Easy Things Easy & Hard
Things Possible".
So, just to clarify:
At this point in time, can SQLAlchemy be used to define and query
simple VIEWs in a database agnostic manner?
And if not, is this a feature that is slated for addition any time
soon?
Thank you,
AF
Michael Bayer
On Aug 9, 2009, at 1:24 PM, allen.fowler wrote:
> So, just to clarify:
>
> At this point in time, can SQLAlchemy be used to define and query
> simple VIEWs in a database agnostic manner?
>
> And if not, is this a feature that is slated for addition any time
> soon?
CREATE VIEW is almost identical across backends and can be achieved
like this:
someselect = select([table.c.foo, table.c.bar])
eng = create_engine('...')
eng.execute("CREATE VIEW foobar AS %s" % someselect.compile(eng))
then build yourself a Table with columns representing the view.
its easy enough to build a CreateView DDL() construct in 0.6 to do
this, i.e.
eng.execute(CreateView("myview", someselect))
I'd consult the sqlalchemy.ext.compiler docs for how to do this.
as a builtin I'm uncomfortable since it implies adding a View() object
to schema which raises lots of thorny questions like "what if I
construct an INSERT against the view ?" " what about materialized
views?" , "what if I join my View() to the underlying Table() ?" etc.
allen.fowler
SQLAlchemy, but I think I get the idea.
Still, though, it looses the auto generation capability via drop_all()/
create_all() vs. traditional tables and feels out-of-place along side
the rest of SQLAlchemy's slickness.
I do understand your concern about adding a View() object at this
stage. Perhaps it could be implemented by limiting it's scope and
thinking of a better word to describe what is needed since it is not a
VIEW in the full meaning of the word. (SimpleView? PreSelected?)
Essentially, this simple case is just a pre-packaged select statement
upon which further refining select queries can be run. The reason for
placing it in the DDL/ORM would be to ensure that: 1) It is clean to
implement at the Python level, and 2) That abstraction happens at the
DB level for both performance reasons and consumption by non-
SQLAlchemy readers.
I imagine that this would solve a great many use-cases for VIEWs in
smaller projects where SQLAlchemy's coolness is already so addictive.
Do you think such a solution is feasible?
Michael Bayer
On Aug 9, 2009, at 5:36 PM, allen.fowler wrote:
>
>
> Still, though, it looses the auto generation capability via
> drop_all()/
> create_all() vs. traditional tables and feels out-of-place along side
> the rest of SQLAlchemy's slickness.
I still have the job of documenting 0.6's features, but even in 0.5
any DDL() construct can be associated with MetaData as an event (this
is in the 0.5 API docs). This can all be done in 0.5 using just
DDL(), 0.6 will just provide a straighter user-definable path right
through the entirety of SQLAs entire DDL generation process.
> I do understand your concern about adding a View() object at this
> stage. Perhaps it could be implemented by limiting it's scope and
> thinking of a better word to describe what is needed since it is not a
> VIEW in the full meaning of the word. (SimpleView? PreSelected?)
All of the edge cases are what come about once I put the whole thing
in core, and people start turning the crank further and further,
expecting to see what "they expect" and then complaining when it
doesn't work (i.e. they cease to be alchemists!). As a usage recipe,
users assume responsibility for the limitations of the approach as
well as its mechanisms.
So here is that recipe: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views
. its 20 lines of public API save for one underscore method which I
suppose we can make public (its just hard to explain, mostly). I
think you'll see its pretty clean as a user recipe.