[sqlalchemy] Re: unexpected chained relations and "append" behaviour
Hello Simon,
thanks for your answer, I will have a look into that.
By the way: len(car.parts) does indeed work, try it ;)
Greetings, Tom
On Wed, Sep 23, 2009 at 4:57 PM, King Simon-NFHD78
wrote:
>
>> -Original Message-
>> From: sqlalchemy@googlegroups.com
>> [mailto:sqlalch...@googlegroups.com] On Behalf Of Crusty
>> Sent: 23 September 2009 15:48
>> To: sqlalchemy@googlegroups.com
>> Subject: [sqlalchemy] unexpected chained relations and
>> "append" behaviour
>>
>>
>> Hello everyone,
>>
>> I have a realy simple model for you to consider:
>>
>> 1 car has n wheels
>> car.wheels is a relation from cars to wheels
>> wheel.car is a backref to cars
>>
>> 1 car has n parts
>> car.parts is a relation from car to parts
>>
>> I just wondered why my app was really getting slow, turned on SA debug
>> mode, and saw that
>>
>> my_new_doorknob = model.Part("doorknob")
>> wheel.car.parts.append(my_new_door_knob)
>>
>> is downloading the entire "parts" table WHERE parts.car == car.id
>> (that is around 20.000 entries) just so that it can append my new
>> doorknob to that relation.
>>
>> Furthermore I noticed a similar behaviour when doing
>> something like this:
>>
>> amount_of_parts = len(car.parts)
>>
>> Instead of sending a COUNT to the database, it populates the entire
>> car.parts relation (around 20.000 entries) just to get the count. Of
>> course I could avoid using relations, and just use my __init__
>> functions, or setting:
>>
>> my_new_doorknob = model.Part("doorknob")
>> my_new_doorknob.car_id = car.id
>> DBSession.append(my_new_doorknob)
>>
>> But then I could as well just write literal SQL if I cant use the "R"
>> part of ORM...
>>
>> Has anyone observed similar behaviour or is this a "feature" and
>> intended to work like this?
>>
>> Greetings, Tom
>
> Yes, this is exactly how it is intended to work. You may like to read
> http://www.sqlalchemy.org/docs/05/mappers.html#working-with-large-collec
> tions for hints on how to improve performance. In particular, making
> your car.parts property a 'dynamic' relation rather than the default
> will prevent SA from loading the entire collection unless you
> specifically ask it to.
>
> However, the len(car.parts) line won't work. SA deliberately doesn't
> implement the __len__ method for Query objects because it is called
> implicitly by python in a number of situations, and running a
> potentially slow query when you aren't expecting it is a bad idea.
> Instead you would use car.parts.count().
>
> Hope that helps,
>
> Simon
>
> >
>
--~--~-~--~~~---~--~~
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] unexpected chained relations and "append" behaviour
Hello everyone,
I have a realy simple model for you to consider:
1 car has n wheels
car.wheels is a relation from cars to wheels
wheel.car is a backref to cars
1 car has n parts
car.parts is a relation from car to parts
I just wondered why my app was really getting slow, turned on SA debug
mode, and saw that
my_new_doorknob = model.Part("doorknob")
wheel.car.parts.append(my_new_door_knob)
is downloading the entire "parts" table WHERE parts.car == car.id
(that is around 20.000 entries) just so that it can append my new
doorknob to that relation.
Furthermore I noticed a similar behaviour when doing something like this:
amount_of_parts = len(car.parts)
Instead of sending a COUNT to the database, it populates the entire
car.parts relation (around 20.000 entries) just to get the count. Of
course I could avoid using relations, and just use my __init__
functions, or setting:
my_new_doorknob = model.Part("doorknob")
my_new_doorknob.car_id = car.id
DBSession.append(my_new_doorknob)
But then I could as well just write literal SQL if I cant use the "R"
part of ORM...
Has anyone observed similar behaviour or is this a "feature" and
intended to work like this?
Greetings, Tom
--~--~-~--~~~---~--~~
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: do I need subqueries for this?
Hello Connor,
yes that does help indeed, thanks a lot.
My problem is though that i cannot use relation operators because I
use a count over a certain row and group by clauses.
So I kinda had to send my query "manually" but still I cant really get
this stuff done cuz I am not really an SQL wizard.
Greetings, Tom
On Tue, Sep 15, 2009 at 5:30 PM, Conor wrote:
>
> On Sep 15, 5:38 am, Crusty wrote:
>> Hey everyone,
>>
>> sorry for the title, I couldnt think of any way to describe this in
>> short.
>> I have 3 Classes, which have basically this relationship:
>>
>> 1 Class1 has n Class2 ( 1:n)
>> 1 Class2 has n Class3 ( 1:n)
>>
>> So basically it looks like this:
>>
>> Class1
>> |-- Class2
>> |-- Class3
>>
>> Now if I join them all together, i get something like this:
>>
>> Class1 Class2 Class3
>> --
>> 1 1 1
>> 1 1 2
>> 1 2 1
>> 1 2 2
>> 2 1 1
>> 2 1 2
>> 2 2 1
>> 2 2 2
>> etc
>>
>> so if I loop through the results i would have something like this:
>>
>> for (class1, class2, class3) in results:
>> print class1, class2, class3
>>
>> But what I would really like to do is:
>>
>> for (class1, class2_results) in class1:
>> print "results for class1:
>> for (result, class3_results) in class2_results:
>> print "results for class2:"
>> for result in class3_results:
>> print "result"
>>
>> which will give me an output more like this:
>>
>> results for class1:
>> result1
>> results for class2:
>> result1
>>
>>
>> And so on.
>> In short, I want to get get xxx rows of class1 repeating, but I want
>> to get one result per class1, containing nested results.
>>
>> Is that possible and do I need subqueries for that?
>>
>> Greetings,
>>
>> Tom
>
> As long as you have ORM relations set up (I will assume you have
> Class1.class2_results and Class2.class3_results), you can use
> eagerloading to get your nested loops while still sending only one
> query to the database:
> q = session.query(Class1)
> q = q.options(eagerload_all("class2_results.class3_results"))
> for class1 in q:
> print "results for class1:"
> for class2 in class1.class2_results:
> print "results for class2:"
> for class3 in class2.class3_results:
> print "result"
>
> The generated SQL will look like:
> SELECT
> FROM Class1 LEFT OUTER JOIN Class2 ON <...> LEFT OUTER JOIN Class3 ON
> <...>
>
> If you need to join the classes manually (to use Class2 and/or Class3
> in an ORDER BY clause, for example), you can use contains_eager to
> notify sqlalchemy about those joins:
> q = session.query(Class1)
> q = q.outerjoin(Class1.class2_results)
> q = q.outerjoin(Class2.class3_results)
> q = q.options(contains_eager("class2_results"))
> q = q.options(contains_eager("class2_results.class3_results"))
> for class1 in q:
> print "results for class1:"
> for class2 in class1.class2_results:
> print "results for class2:"
> for class3 in class2.class3_results:
> print "result"
>
> Hope it helps,
> -Conor
> >
>
--~--~-~--~~~---~--~~
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] do I need subqueries for this?
Hey everyone, sorry for the title, I couldnt think of any way to describe this in short. I have 3 Classes, which have basically this relationship: 1 Class1 has n Class2 ( 1:n) 1 Class2 has n Class3 ( 1:n) So basically it looks like this: Class1 |-- Class2 |-- Class3 Now if I join them all together, i get something like this: Class1 Class2 Class3 -- 1 1 1 1 1 2 1 2 1 1 2 2 2 1 1 2 1 2 2 2 1 2 2 2 etc so if I loop through the results i would have something like this: for (class1, class2, class3) in results: print class1, class2, class3 But what I would really like to do is: for (class1, class2_results) in class1: print "results for class1: for (result, class3_results) in class2_results: print "results for class2:" for result in class3_results: print "result" which will give me an output more like this: results for class1: result1 results for class2: result1 And so on. In short, I want to get get xxx rows of class1 repeating, but I want to get one result per class1, containing nested results. Is that possible and do I need subqueries for that? Greetings, Tom --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
