[sqlalchemy] Re: unexpected chained relations and "append" behaviour
> -Original Message- > From: sqlalchemy@googlegroups.com > [mailto:sqlalch...@googlegroups.com] On Behalf Of Crusty > Sent: 24 September 2009 16:16 > To: sqlalchemy@googlegroups.com > Subject: [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 > len(car.parts) works with your current configuration, because accessing car.parts loads the entire relation and returns it as a python list. But if you change it to be a 'dynamic' relation, it will no longer be a list but a Query instance, which no longer has a __len__ method. 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] 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] Re: unexpected chained relations and "append" behaviour
> -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
-~--~~~~--~~--~--~---
