[sqlalchemy] Re: Mapping and querying multiple levels of foreign key'd tables
On Wed, Mar 05, 2008 at 08:31:12AM +0100, Christoph Haas wrote: Basically I have three tables like 'companies', 'departments' and 'employees'. I have already set up foreign keys and gave all of them one-to-many relationships. So a company has several departments. And each department has several employees. So for an ORM-mapped company object mycompany I can get the departments by the property mycompany.departments. Works well. Now I'd like to create a query for all employees of a certain company. And I'm not sure how to properly define a mapper relation propery that would give me that. Like mycompany.employees. Do I have to use JOINs myself in the mapper? In my application I'd then like to query like this: Session.query(Employee).filter_by(employee.company=my_company) Meanwhile I re-read http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_joins explaining that a relation path A-bars-B-bats-C-widgets-D is queried as: session.query(Foo).join(['bars', 'bats', 'widgets']).filter(...) So in my example I already managed to get this working: session.query(Employee)join(['department','company']). \ filter(model.Company.id==mycompany.id) Is this the common way to deal with CompanyDepartment-Employee paths? If it is - can I perhaps even omit the .id part somehow? I tried: session.query(Employee)join(['department','company']). \ filter(model.Company==mycompany) But SQLAlchemy didn't like that: ArgumentError: filter() argument must be of type sqlalchemy.sql.ClauseElement or string Thanks for any comments. Christoph -- [EMAIL PROTECTED] www.workaround.org JID: [EMAIL PROTECTED] gpg key: 79CC6586 fingerprint: 9B26F48E6F2B0A3F7E33E6B7095E77C579CC6586 --~--~-~--~~~---~--~~ 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: Integrating the ORM with Trellis
On Mar 4, 2008, at 7:27 PM, Phillip J. Eby wrote: Okay, so I did a matching uninstrument_attribute and pre_uninstrument_attribute, which look like really dumb names at this point. I propose the following name changes (in addition to the ones in my previous patch): pre_instrument_attribute - install_descriptor pre_uninstrument_attribute - uninstall_descriptor That okay? instrument_attribute will then call install_descriptor to do the actual installing. And of course the hooks on the adapted thingy from the class would work the same way. If that's okay with you, then after I'm done I'll post a patch for review before checkin. After that, I'll start work on the Trellis-side support for this, and then eventually dig into collections stuff. hey phillip - It all sounds great to me ! I think its been established in the past several months that my names are generally pretty bad so I usually defer to others at this point. - mike --~--~-~--~~~---~--~~ 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: Python 2.6 hash behavior change
On Mar 5, 2008, at 3:35 AM, Denis S. Otkidach wrote: All hash table implementations (including one in Python) work the same way: first it looks up a list of key-value pairs by hash, and then iterate through the list to find a needed key by comparing it with = operator. Thus __eq__ method _is_ used to lookup values in dictionaries. So, this won't work in some cases: 1) when __eq__ may return False when comparing to identical (but not the same, since Python always checks with is first), 2) when __eq__ may return True for objects we want to be assumed different - in some rare cases when they produce the same hash. Although both are not our cases (we always return True, and using id() for hash guarantees they will never clash), I believe your suggestion to use IdentitySet and IdentityDict is a right direction. I want to establish a Expression-friendly token in the source code for sets and dicts such that we can change underlying implementations as needed. We were doing some source code browsing yesterday and it seems to use __cmp__() for the equality check - so we might not need to move to IdentitySet/Dict just yet. My knowledge of hashtables says that two objects with different hash values can still be subject to the equality comparison if they are placed in the same bucket, so we do need to worry about equality comparison in any case. We have filed http://bugs.python.org/issue2235 to deal with __hash__ not being checked along an inheritance hierarchy so at least as far as 2.6, we will be able to make a base class that provides a default __hash__() method, but it seems like Guido in py3k wants __hash__() and __eq__() to always be redefined at the same level.It seems overly rigid to me but I'm not the person to take up an argument about that (any takers ? :) ). It seems like the long term approach here is to get really nice IdentitySet and IdentityDicts going, and this may even be a place I want to start looking into optional, native extensions for performance purposes. --~--~-~--~~~---~--~~ 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: Intersect of ORM queries
pardon my sql-ignorancy, but cant u express this in just one expression? it should be possible, it is a graph/set arithmetics after all... mmh, (could be very wrong!) something like - get all rows that has some b_id from the looked list - group(?) somehow by a_id, and then finger the a_id which collection of b's matches the looked list. on 2nd thought, maybe no, this is a procedural way, not a set-arithmetics way... On Wednesday 05 March 2008 17:23:52 Eric Ongerth wrote: I know how to issue an Intersect of multiple Selects, but I am wondering if there is a simple way to seek the intersection of multiple ORM queries. I already know how to get the results I want in pure SQL or in SA-assisted non-ORM statements. I just wonder if I could use sqlalchemy even more powerfully in the case of a particular M:M relationship in my model. Here's the situation, very simple. Table a is mapped to class A, and table b mapped to class B. I have a many:many relationship between class A and class B, via a secondary table whose only columns are a_id and b_id. What I'm looking for is, I have a list of Bs and I want to find the (single) A that has exactly those same Bs in its Bs collection. (I know the results will either be a single A or None, because of certain uniqueness constraints that don't matter to this question). So to do this with selects, this should work fine: (pardon the pseudo- SQL) intersect( select a_id from secondary_table where b_id == b_being_sought[0].id, select a_id from secondary_table where b_id == b_being_sought[1].id, ... select a_id from secondary_able where b_id == b_being_sought[n].id ) -- Easily generated with a Python loop over the list of B's that I'm searching for. So each of those selects returns a number of rows from the secondary table, all linked to ONE of the B's in the list; and the intersect returns the single (or none) row in the secondary table which refers to the A which has *all* of those B's in its B collection. Fine. But it would be so syntactically smooth if I could just do something like: intersect( query(A).filter(b=b_being_sought[0]), query(A).filter(b=b_being_sought[1]), ... query(A).filter(b=b_being_sought[n]) ) Is this possible in some way? I haven't found a way to make this work ORM-style, because intersect() only wants select statements. Am I correct in thinking that I could build each ORM query, steal its where_clause and use those where_clauses as my set of selects for the intersect()? But that is enough extra steps, and enough exposition of internals, to clearly make it a confusing and backwards way of getting the results I want. Definitely not a path to more readable code -- if that were the only way, then I would just do it the non-ORM way above. All comments appreciated. --~--~-~--~~~---~--~~ 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: Intersect of ORM queries
On Mar 5, 2008, at 10:50 AM, Eric Ongerth wrote: Anyway -- so what would really clean it all up would be: session.query(A).filter(A.bs.contains(list_of_bs_being_sought)).all(). THAT would do exactly what I'm trying to accomplish. But it would require contains() to accept a list and know what to do with it. My proposal would be that the expected behavior is for contains() to construct an intersect of selects where each select is like the one it creates in its simpler case where the argument to contains() is a scalar instead of a list. Does that make sense? Well i think we'd call the operator intersect(). However I think this will do what you want right now if you were to say: session.query(A).filter(A.bs == list_of_bs).all() since it will generate individual EXISTS predicates for each element in the 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] InstrumentedList in SA 0.4.3
With SA version 0.3.11 I used to import InstrumentedList as follows: from sqlalchemy.orm.attributes import InstrumentedList Now I upgraded to SA 0.4.3 and cannot import InstrumentedList from the same file. I looked into the SQLAlchemy file attribute.py and noticed that this file uses the import as in version 0.3.11 Should I use class InstrumentedAttribute instead of InstrumentedList? Or else, how can I import InstrumentedList to my files? Thanks --~--~-~--~~~---~--~~ 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: Mapping and querying multiple levels of foreign key'd tables
Moin, Michael...
thanks for your quick reply.
On Wed, Mar 05, 2008 at 11:16:33AM -0500, Michael Bayer wrote:
On Mar 5, 2008, at 4:14 AM, Christoph Haas wrote:
Meanwhile I re-read
http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_joins
explaining that a relation path A-bars-B-bats-C-widgets-D is
queried as:
session.query(Foo).join(['bars', 'bats', 'widgets']).filter(...)
So in my example I already managed to get this working:
session.query(Employee)join(['department','company']). \
filter(model.Company.id==mycompany.id)
Is this the common way to deal with CompanyDepartment-Employee
paths?
If it is - can I perhaps even omit the .id part somehow? I tried:
session.query(Employee)join(['department','company']). \
filter(model.Company==mycompany)
But SQLAlchemy didn't like that:
ArgumentError: filter() argument must be of type
sqlalchemy.sql.ClauseElement or string
you want to compare Employee.department.company to the element you
have, so it would be:
session.query(Employee).join('department').filter(Department.company
== mycompany)
Oh, right. That saves the second JOIN as the department.company_id is
already a field on the departments. Yet another case of thinking too
abstract instead of remembering how the actual database tables look. :)
or even cooler, you could do
session.query(Employee).filter(Employee.department.has(
Department.company==mycompany))
Somehow .has and .any have always been somewhat magical to me. I think
I'll rather use the less cool way because it's more likely I understand
my code then in a few years. Works well, thanks. So I was basically on
the right track with the JOIN.
But now I'm curious. Why do I get the ArgumentError if I try
.filter(Company==my_company)
while
.filter(Company.id==my_company.id)
works? I was comparing ORM objects directly instead of fields/properties
of a mapped object. But shouldn't that work, too?
Cheers
Christoph
--
[EMAIL PROTECTED] www.workaround.org JID: [EMAIL PROTECTED]
gpg key: 79CC6586 fingerprint: 9B26F48E6F2B0A3F7E33E6B7095E77C579CC6586
--~--~-~--~~~---~--~~
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: Mapping and querying multiple levels of foreign key'd tables
On Mar 5, 2008, at 1:00 PM, Christoph Haas wrote: But now I'm curious. Why do I get the ArgumentError if I try .filter(Company==my_company) while .filter(Company.id==my_company.id) works? I was comparing ORM objects directly instead of fields/ properties of a mapped object. But shouldn't that work, too? The relation()-based descriptors are the ones which are aware of comparisons to instances, i.e. Department.company==somecompany. Try saying print Department.company==mycompany to see how that works. --~--~-~--~~~---~--~~ 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] proposed FAQ entry
Ran into something this morning, fix and problem are simple.
Q) How do I map a column that is a Python reserved word or already used
by SA?
A)
table_a = Table ('tbl_a', metadata, autoload=True)
mapper(AObj, table_a, properties={'type_col' : table_a.c.type,
'pass_col':table_a.c['pass']})
This may be covered under 3.1 I am using autoload=True and insert your
problem here, but I think type is a common enough column name.
--~--~-~--~~~---~--~~
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: Intersect of ORM queries
Cool. I wasn't sure if it was ready for filter(A.bs == list_of_bs). When I tried to do that before, I must have let some silly syntax error keep me from realizing that it was a workable construction. Thanks! On Mar 5, 8:20 am, Michael Bayer [EMAIL PROTECTED] wrote: On Mar 5, 2008, at 10:50 AM, Eric Ongerth wrote: Anyway -- so what would really clean it all up would be: session.query(A).filter(A.bs.contains(list_of_bs_being_sought)).all(). THAT would do exactly what I'm trying to accomplish. But it would require contains() to accept a list and know what to do with it. My proposal would be that the expected behavior is for contains() to construct an intersect of selects where each select is like the one it creates in its simpler case where the argument to contains() is a scalar instead of a list. Does that make sense? Well i think we'd call the operator intersect(). However I think this will do what you want right now if you were to say: session.query(A).filter(A.bs == list_of_bs).all() since it will generate individual EXISTS predicates for each element in the 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 -~--~~~~--~~--~--~---
