[sqlalchemy] Two Objects, One Table and the inverse
Hi, Is it possible to break a table in two Python objects? Ex: Python objects: class Person: name = Column(String) class Address: street = Column(String) city = Column(String) Table: table Person name varchar city varchar street varchar How can i make the connection between the two? And is it possible to make the inverse? (table Person and Address to a single Python object Person) Thks in advance, -- 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.
Re: [sqlalchemy] Two Objects, One Table and the inverse
On Apr 5, 2011, at 9:06 AM, Israel Ben Guilherme Fonseca wrote: Hi, Is it possible to break a table in two Python objects? Ex: Python objects: class Person: name = Column(String) class Address: street = Column(String) city = Column(String) Table: table Person name varchar city varchar street varchar How can i make the connection between the two? You could map Person and Address both to the table, excluding/including columns from each using mapper include_properties/exclude_properties (see the docs), however relationship() would not be able to handle the join condition of this table, which is table.id==table.id. So in the practical sense, you'd just do this: class Person(object): @property def address(self): return Address(self.street, self.city) or similarly use a composite (search the mapping documentation for composite) to achieve the Address class composed of that subset of columns.If you choose composite I'd note 0.7 improves upon the scalability of the composite feature significantly (for small datasets in memory it doesn't matter though). And is it possible to make the inverse? (table Person and Address to a single Python object Person) you can map to a join of the two tables as described at http://www.sqlalchemy.org/docs/orm/mapper_config.html#mapping-a-class-against-multiple-tables. -- 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.
Re: [sqlalchemy] Two Objects, One Table and the inverse
Thks for the insight Michael. With the @property solution, its not possible to make queries like session.query(Person).filter(Person.address.street=Something) right? Cascade Saving/Updating in that instance should be a problem too. I tested the composite solution, it's nice but the queries get a bit complex to do when we want to restrict fields of the composite Object (because the need of comparator and when we want only one field of the composite object in the restriction not all of them). Not very straight forward. What do you say about it? I really think that in real-world situations i would never want to make table become two objects, it's much more easy(implementation and understanding) to use just one object. I'm doing a paper about ORM solutions and that's why i'm asking about this specific feature. I'll give a look in the inverse solution later. Thanks again! 2011/4/5 Michael Bayer mike...@zzzcomputing.com On Apr 5, 2011, at 9:06 AM, Israel Ben Guilherme Fonseca wrote: Hi, Is it possible to break a table in two Python objects? Ex: Python objects: class Person: name = Column(String) class Address: street = Column(String) city = Column(String) Table: table Person name varchar city varchar street varchar How can i make the connection between the two? You could map Person and Address both to the table, excluding/including columns from each using mapper include_properties/exclude_properties (see the docs), however relationship() would not be able to handle the join condition of this table, which is table.id==table.id. So in the practical sense, you'd just do this: class Person(object): @property def address(self): return Address(self.street, self.city) or similarly use a composite (search the mapping documentation for composite) to achieve the Address class composed of that subset of columns.If you choose composite I'd note 0.7 improves upon the scalability of the composite feature significantly (for small datasets in memory it doesn't matter though). And is it possible to make the inverse? (table Person and Address to a single Python object Person) you can map to a join of the two tables as described at http://www.sqlalchemy.org/docs/orm/mapper_config.html#mapping-a-class-against-multiple-tables . -- 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. -- 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.
Re: [sqlalchemy] Two Objects, One Table and the inverse
On Apr 5, 2011, at 12:30 PM, Israel Ben Guilherme Fonseca wrote: Thks for the insight Michael. With the @property solution, its not possible to make queries like session.query(Person).filter(Person.address.street=Something) right? that's not possible with standard SQLAlchemy expression constructs anyway. Normally you'd use Address.street to get clause elements against Address. You can use hybrids to create this effect fully (see http://www.sqlalchemy.org/docs/07/orm/extensions/hybrid.html ) Cascade Saving/Updating in that instance should be a problem too. I tested the composite solution, it's nice but the queries get a bit complex to do when we want to restrict fields of the composite Object (because the need of comparator and when we want only one field of the composite object in the restriction not all of them). Not very straight forward. I'd argue the original usecase is not very straightforward either, if you have just one table you should map a class to it, then as far as nice accessor patterns, that's all Python convenience stuff you build yourself (like using hybrids above). I don't see much practicality in attempting to make the single table act 100% like two tables do with the ORM, such as being concerned about casades...there are no cascades, this is one table with no foreign key relationships. I see practicality in getting a pattern that works good enough to make coding against the model simple and straightforward. I really think that in real-world situations i would never want to make table become two objects, it's much more easy(implementation and understanding) to use just one object. I'm doing a paper about ORM solutions and that's why i'm asking about this specific feature. well there's your answer... SQLAlchemy is about getting work done and not so much about academic exercises :). -- 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.
RE: [sqlalchemy] Two Objects, One Table and the inverse
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 05 April 2011 18:38 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Two Objects, One Table and the inverse On Apr 5, 2011, at 12:30 PM, Israel Ben Guilherme Fonseca wrote: Thks for the insight Michael. With the @property solution, its not possible to make queries like session.query(Person).filter(Person.address.street=Something) right? that's not possible with standard SQLAlchemy expression constructs anyway. Normally you'd use Address.street to get clause elements against Address.You can use hybrids to create this effect fully (see http://www.sqlalchemy.org/docs/07/orm/extensions/hybrid.html ) Out of interest, if Person and Address were standard mapped classes with a one-to-one relationship between them, could that query be made to work: session.query(Person).filter(Person.address.street==Something) I guess it would have to be equal to: session.query(Person).join(Person.address).filter(Address.street==Somet hing) In order for that to work, Person.address would have to be a smart wrapper for the Address class that adds the join condition into any attribute comparison operations. Good idea, or silly idea? (Or perhaps it already works...) 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.
Re: [sqlalchemy] Two Objects, One Table and the inverse
On Apr 5, 2011, at 1:53 PM, King Simon-NFHD78 simon.k...@motorolasolutions.com wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 05 April 2011 18:38 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Two Objects, One Table and the inverse On Apr 5, 2011, at 12:30 PM, Israel Ben Guilherme Fonseca wrote: Thks for the insight Michael. With the @property solution, its not possible to make queries like session.query(Person).filter(Person.address.street=Something) right? that's not possible with standard SQLAlchemy expression constructs anyway. Normally you'd use Address.street to get clause elements against Address.You can use hybrids to create this effect fully (see http://www.sqlalchemy.org/docs/07/orm/extensions/hybrid.html ) Out of interest, if Person and Address were standard mapped classes with a one-to-one relationship between them, could that query be made to work: session.query(Person).filter(Person.address.street==Something) I guess it would have to be equal to: session.query(Person).join(Person.address).filter(Address.street==Somet hing) In order for that to work, Person.address would have to be a smart wrapper for the Address class that adds the join condition into any attribute comparison operations. Good idea, or silly idea? (Or perhaps it already works...) I've stayed away from class.relationship.attribute partially because the namespace on .relationship isn't really wide open, also it implies a join occurring by itself, which would dilute the more open ended utility of query.join(). 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. -- 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.