[sqlalchemy] Re: From arbitrary SELECT to Query
On Dec 7, 2007, at 2:39 PM, Artur Siekielski wrote: > The problem is that I get normal Python list, which eats much > resources when database is big. Much better would be Query object > which supports lazy loading. Note that I cannot use > Query.filter(compoundSelect._whereclause) because CompundSelect > doesn't have _whereclause. id just point out also that, we havent decided against the Query object yielding results as theyre received. i pointed out earlier in this thread that its complicated, but this is something we might finally try to tackle soon. --~--~-~--~~~---~--~~ 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: From arbitrary SELECT to Query
On Dec 9, 2007, at 2:31 PM, Artur Siekielski wrote: > > I'm writing DAO module for db access which must be independent of rest > of the system. I'm looking for a class which can be used as a proxy > for SQL results. Query would be good, if it would be possible to have > fully functional Query instance representing any SQL statement. But I > cannot tell users - here you have a Query object, but filtering > sometimes doesn't work! So it seems that if I want to have object- > oriented proxy for SQL results with lazy loading, I must write my own > wrapper. > OK, ive got the initial implementation for this in r3904. whatever you put into query.select_from(), thats what its going to select from. all the fun starts when you start filter()ing and join()ing. select_from() also needs to be called before you set up any joins or criterions; it will issue a warning if you do otherwise. it still needs some work, its not applied yet to count(), edge cases like group_by()/ having() dont work yet either (ticket 898 is a reminder). we will probably look into moving away from from_statement() and into select_from() since it now handles a superset of use cases. --~--~-~--~~~---~--~~ 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: From arbitrary SELECT to Query
heres the output of:
sel = users.select(users.c.id.in_([7, 8])).alias()
sess.query(User).options(eagerload('addresses')).select_from(sel)[1]
SELECT anon_1.anon_2_id AS anon_1_anon_2_id, anon_1.anon_2_name AS
anon_1_anon_2_name, addresses_1.id AS addresses_1_id,
addresses_1.user_id AS addresses_1_user_id, addresses_1.email_address
AS addresses_1_email_address
FROM (SELECT anon_2.id AS anon_2_id, anon_2.name AS anon_2_name,
anon_2.id AS anon_2_oid
FROM (SELECT users.id AS id, users.name AS name
FROM users
WHERE users.id IN (%(users_id_1)s, %(users_id_2)s)) AS anon_2 ORDER BY
anon_2.id
LIMIT 1 OFFSET 1) AS anon_1 LEFT OUTER JOIN addresses AS addresses_1
ON anon_1.anon_2_id = addresses_1.user_id ORDER BY anon_1.anon_2_id,
addresses_1.id
thats a mouthful.
--~--~-~--~~~---~--~~
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: From arbitrary SELECT to Query
On Dec 9, 2007, at 2:31 PM, Artur Siekielski wrote: > >> no, from_statement replaces all filtering. > > Shouldn't it throw some exception then? funny you should say that, this week we've been adding warnings for query methods that are called when they would ignore some part of the existing criterion, so will add this. > cannot tell users - here you have a Query object, but filtering > sometimes doesn't work! So it seems that if I want to have object- > oriented proxy for SQL results with lazy loading, I must write my own > wrapper. we've had requests for this before, and since we've recently greatly improved our ability to alias clauses against a new selectable, im going to try to commit this. select_from()'s behavior is going to change here but I dont think the replaced behavior is anything anyone was using (i.e. it currently builds a list of clauses,but you dont really need that for anything). note that we haven't had this feature before since its quite complicated; if I say query.select_from(users.select(users.c.id.in_([7, 8])).alias()).filter(User.id==7), the generated query must be: SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name FROM (SELECT users.id AS id, users.name AS name FROM users WHERE users.id IN (%(users_id_1)s, %(users_id_2)s)) AS anon_1 WHERE anon_1.id = %(users_id_3)s ORDER BY anon_1.id i.e. the incoming filter() criterion has to be aliased, the actual columns which the mapper receives are now named differently so are also translated on a row-by-row basis, etc. currently i have it working for non-eager queries. when the feature is complete there still may be more complex queries that just dont come out correctly, we'll have to see. --~--~-~--~~~---~--~~ 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: From arbitrary SELECT to Query
> no, from_statement replaces all filtering. Shouldn't it throw some exception then? > so, what is it youre trying to do exactly ? I'm writing DAO module for db access which must be independent of rest of the system. I'm looking for a class which can be used as a proxy for SQL results. Query would be good, if it would be possible to have fully functional Query instance representing any SQL statement. But I cannot tell users - here you have a Query object, but filtering sometimes doesn't work! So it seems that if I want to have object- oriented proxy for SQL results with lazy loading, I must write my own wrapper. --~--~-~--~~~---~--~~ 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: From arbitrary SELECT to Query
On Dec 9, 2007, at 1:03 PM, Artur Siekielski wrote: > > But is Query object constructed by from_statement fully functional? > Using "filter" doesn't work for me - it returns the same query. no, from_statement replaces all filtering. so, what is it youre trying to do exactly ? --~--~-~--~~~---~--~~ 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: From arbitrary SELECT to Query
But is Query object constructed by from_statement fully functional? Using "filter" doesn't work for me - it returns the same query. --~--~-~--~~~---~--~~ 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: From arbitrary SELECT to Query
On Dec 9, 2007, at 12:31 PM, Artur Siekielski wrote:
>
> Hi again.
> Thanks for hints on using "instances" method. But is there any method
> to get Query object representing query result?
>
> I have spent more time on my problem. It's important for me if I can
> use Query object as a proxy to instances fetched from DB, or if I must
> fall back to raw list. Almost working solution (I'm using PostgreSQL)
> is that:
>
> q =
> dbSession
> .query(DomainClass).select_from(compoundSelect.alias('myalias'))
>
> The problem is visible here:
print q
> SELECT
> FROM DomainClassTable,
>
> The problem is that "DomainClassTable" is always added to FROM clause,
> even if I throw it away by hand from q._from_obj list...
>
hey there -
sure, its in the ORM tutorial, and i just fixed that it had no
docstring in the pydoc in r3901, and its called from_statement().
select_from() is for adding additional FROM clauses to the generated
query. from_statement() is used to entirely replace the compiled
statement with that of your own, i.e.
dbSession.query(DomainClass).from_statement(myselect). this is the
equivalent to query(DomainClass).instances(myselect.execute()).
--~--~-~--~~~---~--~~
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: From arbitrary SELECT to Query
Hi again.
Thanks for hints on using "instances" method. But is there any method
to get Query object representing query result?
I have spent more time on my problem. It's important for me if I can
use Query object as a proxy to instances fetched from DB, or if I must
fall back to raw list. Almost working solution (I'm using PostgreSQL)
is that:
q =
dbSession.query(DomainClass).select_from(compoundSelect.alias('myalias'))
The problem is visible here:
>>> print q
SELECT
FROM DomainClassTable,
The problem is that "DomainClassTable" is always added to FROM clause,
even if I throw it away by hand from q._from_obj list...
--~--~-~--~~~---~--~~
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: From arbitrary SELECT to Query
On Dec 7, 2007, at 2:39 PM, Artur Siekielski wrote: > > I have a compound SELECT statement (of class CompoundSelect) that is > composed of a few normal SELECTs combined by UNION. from_obj of this > SELECT contains all columns of a table that is mapped to a class. I > would like to get objects created using defined mapper, but selected > using my compound SELECT statement. > > I have found a solution: mappers have method 'instances' that can be > used like that: > > instancesList = > someMapper.instances(dbSession.execute(compoundSelect), dbSession) use "instances" on Query. "instances" on mapper is long ago deprecated, added a warning in 3874. > The problem is that I get normal Python list, which eats much > resources when database is big. Much better would be Query object > which supports lazy loading. Note that I cannot use > Query.filter(compoundSelect._whereclause) because CompundSelect > doesn't have _whereclause. apply the appropriate LIMIT/OFFSET criterion to the select in order to limit rows. The Query object doesnt have the option to fetch only part of a result sets since single instances may be comprised of many rows, the same object can appear many times in the same result set, and dependencies may exist between objects present in multiple rows; everything has to be held as unique against its identity across the full result set. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
