[sqlalchemy] Re: VIEW alternative in SQLAlchemy
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. --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: VIEW alternative in SQLAlchemy
On Aug 9, 1:42 pm, Michael Bayer wrote:
> 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.
I am still using 0.5, and am not familiar with "below ORM" usage of
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?
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: VIEW alternative in SQLAlchemy
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.
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: VIEW alternative in SQLAlchemy
On Aug 7, 11:45 am, "Michael Bayer" wrote: > AF wrote: > > > Hello, > > > 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. > > 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). After reading the other helpful messages in this thread, and upon 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 --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: VIEW alternative in SQLAlchemy
On Fri, Aug 07, 2009 at 09:39:33AM -0700, allen.fowler wrote: > 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 r...@kallisti.us "If the fight gets hot, the songs get hotter. If the going gets tough, the songs get tougher." --Woody Guthrie --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: VIEW alternative in SQLAlchemy
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 >> > > 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? > As you can see from the view definition (below) it just contains id's 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. > 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. > Do not know the answer to this, nor do I have a guess :-) . 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. :) > > > > > > > --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: VIEW alternative in SQLAlchemy
> ...> 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? 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. :) --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: VIEW alternative in SQLAlchemy
Allen, 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 --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: VIEW alternative in SQLAlchemy
On Aug 7, 11:45 am, "Michael Bayer" wrote: > AF wrote: > > > Hello, > > > 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. > > 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). 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. Thank you, :) --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: VIEW alternative in SQLAlchemy
AF wrote: > > Hello, > > 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. 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). --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
