[sqlalchemy] Re: What should the future of SQLAlchemy Query look like ?
On 3/10/07, Michael Bayer [EMAIL PROTECTED] wrote: Well initial response to this post has been overwhelmingly low, but thats fine with me. After trying to tackle this today, I realized that I actually didnt want to add a brand new query object and go through a painful deprecation procedure again and all that...and I also observed that the notion of a query being against a single entity is pretty important..things like get(), load() and all that dont make much sense for multiple entities. Also, all the things Ive wanted to do are not actually that hard as we already have code to do most of it. So I did not add any new classes or modules, no new methods on session, I didnt go with my modified generative approach (its just like it always was) and changed absolutely nothing about Query thats already there (well one little thing i dont think anyone was using). But I added a whole lot of new methods to Query, essentially everything SelectResults was able to do. I was concerned about having just too much crap on Query but i think it turned out fine. Plus I added a little bit of hibernate-like capabilities to query multiple entities and extra columns too. Well, this is just Great ! I also felt those methods belonged in the core somehow. Thanks for all your work! -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: What should the future of SQLAlchemy Query look like ?
I like it all!
Arnar
On 3/10/07, Michael Bayer [EMAIL PROTECTED] wrote:
Well initial response to this post has been overwhelmingly low, but
thats fine with me. After trying to tackle this today, I realized
that I actually didnt want to add a brand new query object and go
through a painful deprecation procedure again and all that...and I
also observed that the notion of a query being against a single entity
is pretty important..things like get(), load() and all that dont make
much sense for multiple entities. Also, all the things Ive wanted to
do are not actually that hard as we already have code to do most of
it.
So I did not add any new classes or modules, no new methods on
session, I didnt go with my modified generative approach (its just
like it always was) and changed absolutely nothing about Query thats
already there (well one little thing i dont think anyone was using).
But I added a whole lot of new methods to Query, essentially
everything SelectResults was able to do. I was concerned about having
just too much crap on Query but i think it turned out fine. Plus I
added a little bit of hibernate-like capabilities to query multiple
entities and extra columns too.
So...with that, heres what Query can do now.
First of all, the same stuff that youve always used is just like it
always was:
query.select_by()
query.select()
query.get()
query.count()
query.select_by_XXX()
A couple of methods that Ive never liked because they are klunky, are
still there anyway:
query.join_to()
query.join_via()
As it stands, Query has two generative methods already (which also i
had to fix a little bit since generative-ness would wipe out what
was previously there). these return for you a new Query with
modifications to its state:
query.options()
query.with_lockmode()
So we add a lot of new generative methods taken from SelectResults,
all of which return a brand new Query. the things you add here will
also take effect on subsequent calls to the regular select(), count(),
select_by(), etc:
query.filter() - adds criterion
query.filter_by() - ditto
query.join() - joins to a property name, or a list
query.outerjoin()
query.order_by()
query.group_by()
query.distinct() - applies DISTINCT
query.offset()
query.limit()
query[3:5] - applies offset 3, limit 2
like SelectResults, we have some executors -
query.list()
list(query)
or just call query.select(), selectfirst(), selectone(), etc. to
execute whatever has been built up.
the aggregates, which take a Column (not sure how popular these are):
query.avg()
query.sum()
.. and others
So a generative example, including join which im really excited
about. join can act either like join_to():
q =
session.query(Person).filter_by(people.c.name.like('%2')).join('status').filter_by(name=active)
print q.list()
or like join_via (which is more deterministic), if you send a list:
l = q.filter(orderitems.c.item_name=='item 4').join(['orders',
'items']).list()
and then, some brand new stuff - better support for querying more
than one thing at a time. the instances() method, which was able to
take a list of *mappers, now returns the results the way the docs say
they do, as a list of tuples, each tuple having an entry for each
mapper. additionally, when that option is used, the uniquing of the
result is turned off - this so that the results you get back
correspond to the rows that went in. and, you can stick arbitrary
columns, not just mappers, in the list too...it will just pull them
from the row.
in addition to the changes on instances(), you can add extra entities/
columns to the compilation as well:
query.add_entity() - adds columns to the select criterion
query.add_column() - adds columns to the select criterion
so what can we do with this ? things like, query an entity and an
aggregate function at the same time:
q = sess.query(User)
q = q.group_by([c for c in
users.c]).outerjoin('addresses').add_column(func.count(addresses.c.address_id).label('count'))
l = q.list()
which will return for you a list of tuples:
[
(user1, 3),
(user2, 5),
(user3, 0),
..etc
]
note the label is needed right now for a function, it makes it
possible for Query to target the column correctly.
another one from the unit tests. the users table has users 7, 8, 9
in it. the addresses table has addresses 1, 2, 3, 4. user #7
refereces address #1, user #8 references addresses 2, 3 and 4.
(user7, user8, user9) = sess.query(User).select()
(address1, address2, address3, address4) =
sess.query(Address).select()
q = sess.query(User)
q = q.add_entity(Address).outerjoin('addresses')
l = q.list()
assert l == [
(user7, address1),
(user8, address2),
[sqlalchemy] Re: What should the future of SQLAlchemy Query look like ?
Michael Bayer ha scritto: hey list - I continue to be troubled by the slightly fragmented nature of SA's Query object (and the cousin SelectResults). When I work with Hibernate, I can see that their querying interface is a little more consistent than ours. We have flags that are used for some things, generative methods for others. so id like to look into defining the next generation of query. Id like it to have a quasi-generative approach, like Hibernates. this means you can say: q = q.where(something).order_by(somethingelse) but also, its the same as: q.where(something) q.order_by(somethingelse) so its really the same instance (this is not how SelectResults works at the moment). the whole business of using SelectResults, using SelectResultsExt, all that crap just to get a different API, id like to get rid of (i mean, itll stay there but you wont need it). im sorry ive made you all type that much. This would be a rewrite of `Query`, and we'd leave the old one around in its usual place. Im thinking we could put this newer `Query` on the session under the method name `select()`. Anyway, I put a wiki page over at http://www.sqlalchemy.org/trac/wiki/QueryV4 , with like 2 lines of code what it might look like. I would like folks to comment on it, and add use cases, sample code, things youd like to see. note that Im looking mostly for the Python API, and maybe a little bit of the method of specifying criterion, but not really a whole new object-query layer (like building a new HQL, or using AST-parsing, etc. i still think thats something else entirely). Please think of something to add, particularly if you are working with polymorphic mappings, or youve had a lot to say in past iterations (i.e. like dmiller, dennis, etc). I dont want to make a move on this until something definitely cool and widely useful has been worked out. if we just have a vague notion of something, theres no point...while we can prototype it, if its a side thing then not enough people are going to use it (and therefore valid complaints wont be heard) unless we parade this thing down the main aisle. this query would hopefully be the last one we write for the SA core (since we are running out of reasonable method names on session ;) ). SA is a great Work, power and useful. Only think , probably is too much finalised to oneTabel - OneMapper prototyping For example, my purpose now is to revisiting a lot of mapper created from different programmers over a huge DB so it's very important for maintain mappers clear, univocity in these mappers. I found different but not equal possibility in some operation for example: - It's not too clear because not all the features of the Table object is not manteined in the Mapper. I've 3 mapper Amapper - Bmapper - Cmapper - Why, if i prefer to use Mapper instead of the Tbl direct qry, i must anyway always explicity the join to other mapper, for retrieve all selected records, Amapper.select_by( BmapperColumnCondition ) retrieve always select * from A where clause so if i'm searching something from B i must redesign selection qry.. - Why ( aa = Amapper, is a mapper bb = Bmapper, is a mapper; aa.Bmapper, is a Unit of Work) this let me use Amapper.c.field == x but i cannot Use Amapper.Bmapper.c.field = y take in mind my work of maintain this huge library so if i must upgrade Cmapper i don't want to manipulate ALL mapper referring to it I hope my explanation is clear, :-) Sorry for my poor English Glauco -- ++ Glauco Uri - Programmatore glauco(at)allevatori.com Sfera Carta Software® [EMAIL PROTECTED] Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054 ++ --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: What should the future of SQLAlchemy Query look like ?
youre looking for the mappers to express the relational concepts as fully as Tables. but thats what Tables are for, why not just use them ? SA's philosophy is very much about dont pretend theres no database. On Mar 9, 5:16 am, Glauco [EMAIL PROTECTED] wrote: Michael Bayer ha scritto: hey list - I continue to be troubled by the slightly fragmented nature of SA's Query object (and the cousin SelectResults). When I work with Hibernate, I can see that their querying interface is a little more consistent than ours. We have flags that are used for some things, generative methods for others. so id like to look into defining the next generation of query. Id like it to have a quasi-generative approach, like Hibernates. this means you can say: q = q.where(something).order_by(somethingelse) but also, its the same as: q.where(something) q.order_by(somethingelse) so its really the same instance (this is not how SelectResults works at the moment). the whole business of using SelectResults, using SelectResultsExt, all that crap just to get a different API, id like to get rid of (i mean, itll stay there but you wont need it). im sorry ive made you all type that much. This would be a rewrite of `Query`, and we'd leave the old one around in its usual place. Im thinking we could put this newer `Query` on the session under the method name `select()`. Anyway, I put a wiki page over athttp://www.sqlalchemy.org/trac/wiki/QueryV4 , with like 2 lines of code what it might look like. I would like folks to comment on it, and add use cases, sample code, things youd like to see. note that Im looking mostly for the Python API, and maybe a little bit of the method of specifying criterion, but not really a whole new object-query layer (like building a new HQL, or using AST-parsing, etc. i still think thats something else entirely). Please think of something to add, particularly if you are working with polymorphic mappings, or youve had a lot to say in past iterations (i.e. like dmiller, dennis, etc). I dont want to make a move on this until something definitely cool and widely useful has been worked out. if we just have a vague notion of something, theres no point...while we can prototype it, if its a side thing then not enough people are going to use it (and therefore valid complaints wont be heard) unless we parade this thing down the main aisle. this query would hopefully be the last one we write for the SA core (since we are running out of reasonable method names on session ;) ). SA is a great Work, power and useful. Only think , probably is too much finalised to oneTabel - OneMapper prototyping For example, my purpose now is to revisiting a lot of mapper created from different programmers over a huge DB so it's very important for maintain mappers clear, univocity in these mappers. I found different but not equal possibility in some operation for example: - It's not too clear because not all the features of the Table object is not manteined in the Mapper. I've 3 mapper Amapper - Bmapper - Cmapper - Why, if i prefer to use Mapper instead of the Tbl direct qry, i must anyway always explicity the join to other mapper, for retrieve all selected records, Amapper.select_by( BmapperColumnCondition ) retrieve always select * from A where clause so if i'm searching something from B i must redesign selection qry.. - Why ( aa = Amapper, is a mapper bb = Bmapper, is a mapper; aa.Bmapper, is a Unit of Work) this let me use Amapper.c.field == x but i cannot Use Amapper.Bmapper.c.field = y take in mind my work of maintain this huge library so if i must upgrade Cmapper i don't want to manipulate ALL mapper referring to it I hope my explanation is clear, :-) Sorry for my poor English Glauco -- ++ Glauco Uri - Programmatore glauco(at)allevatori.com Sfera Carta Software® [EMAIL PROTECTED] Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054 ++ --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: What should the future of SQLAlchemy Query look like ?
On 3/8/07, Michael Bayer [EMAIL PROTECTED] wrote: hey list - I continue to be troubled by the slightly fragmented nature of SA's Query object (and the cousin SelectResults). When I work with Hibernate, I can see that their querying interface is a little more consistent than ours. We have flags that are used for some things, generative methods for others. so id like to look into defining the next generation of query. Id like it to have a quasi-generative approach, like Hibernates. this means you can say: q = q.where(something).order_by(somethingelse) but also, its the same as: q.where(something) q.order_by(somethingelse) so its really the same instance (this is not how SelectResults works at the moment). I like the select results way better. That way you can make a base query and then modify it easily; if you're modifying in place, and you add another where clause, you can't easily re-use the pre-modified version again. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: What should the future of SQLAlchemy Query look like ?
On Mar 8, 2007, at 11:22 AM, Jonathan Ellis wrote: On 3/8/07, Michael Bayer [EMAIL PROTECTED] wrote: hey list - I continue to be troubled by the slightly fragmented nature of SA's Query object (and the cousin SelectResults). When I work with Hibernate, I can see that their querying interface is a little more consistent than ours. We have flags that are used for some things, generative methods for others. so id like to look into defining the next generation of query. Id like it to have a quasi-generative approach, like Hibernates. this means you can say: q = q.where(something).order_by(somethingelse) but also, its the same as: q.where(something) q.order_by(somethingelse) so its really the same instance (this is not how SelectResults works at the moment). I like the select results way better. That way you can make a base query and then modify it easily; if you're modifying in place, and you add another where clause, you can't easily re-use the pre-modified version again. how often do you have a base query hanging around, vs. doing brand new query object each time? with the full generative way, if you are programatically building up your query, now you have to say: if somecriterion: q = q.where(something) if someother criterion: q = q.where(somethingelse) q = q.order_by(something else) q = q.options(foo) ie you have to keep remembering to say q = q.etc, otherwise you are just throwing away your newly generated query objects. this seems inconvenient to me. id like to see what cases make you want to have a base query that is reused, and consider how that might not be needed either. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: What should the future of SQLAlchemy Query look like ?
On 3/8/07, Michael Bayer [EMAIL PROTECTED] wrote: how often do you have a base query hanging around, vs. doing brand new query object each time? with the full generative way, if you are programatically building up your query, now you have to say: if somecriterion: q = q.where(something) if someother criterion: q = q.where(somethingelse) q = q.order_by(something else) q = q.options(foo) ie you have to keep remembering to say q = q.etc, otherwise you are just throwing away your newly generated query objects. this seems inconvenient to me. id like to see what cases make you want to have a base query that is reused, and consider how that might not be needed either. for instance, i have an application in charge of building zips for users to download from a queue of requests. the requests are prioritized, but to prevent starvation of low priority requests, i have another thread that just processes them in order of request time. so i have the base query that's just a module-level variable, and the main threadpool adds on the priority clause, while the other adds on the request-time clause. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: What should the future of SQLAlchemy Query look like ?
On Mar 8, 2007, at 11:49 AM, Jonathan Ellis wrote: for instance, i have an application in charge of building zips for users to download from a queue of requests. the requests are prioritized, but to prevent starvation of low priority requests, i have another thread that just processes them in order of request time. so i have the base query that's just a module-level variable, and the main threadpool adds on the priority clause, while the other adds on the request-time clause. well say you have your query myquery = session.query(SomeClass).select(whatever) your functions that want to build off of it without modifying can just say: def dosomething(): q = myquery.copy().where(something).where(somethingelse) so if you do need a local query, you can get one. i think this is the less common use case, so the extra typing should be associated there (and its not as much extra typing as the q= q.foo thing). --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
