[sqlalchemy] Re: One To Many Polymorphic Association.
On Monday 27 August 2007 19:57:21 Michael Bayer wrote: On Aug 24, 2007, at 8:39 AM, praddy wrote: ## from sqlalchemy import * meta = BoundMetaData('sqlite://', echo=False) # Parents table. parents = Table('parents', meta, Column(id, Integer, primary_key=True), Column(data, String(50), nullable=False) ) # Children_1 Table. children_1 = Table('children_1', meta, Column(id, Integer, primary_key=True), Column(data, String(50), nullable=False) ) # Children_2 Table. children_2 = Table('children_2', meta, Column(id, Integer, primary_key=True), Column(data, String(50)) ) # Association Table. # This is a generic table which can relate anything to parent. assoc = Table('assoc', meta, # parents.c.id Column(parent_id, Integer, ForeignKey(parents.c.id)), # associate's id either children_1.c.id or children_2.c.id or any other child. Column(assoc_id, Integer), # Which can be either 'child_1' or 'child_2' for now (can be used for extending children # type, decides which table to look in. Column(assoc_type, String(20)) ) ### I am a novice with respect to sqlalchemy may be RDBMS as well. How would you like to work on this scenario to achieve backwards cascading (may not be the right word) which means when one deletes one specific child from children_1 table (for example), there should not be any association entry, which associates that child to the parent, in the association table as well? To allow proper foreign key support, you probably want to place the foreign key to the association table on each of children_1 and children_2, and remove the assoc_id column from the assoc table. this is the key to the blog article about polymorphic associations. its probably easiest for you to work with the example mentioned in the blog and modify. (http://techspot.zzzeek.org/files/ poly_assoc_2.py ). at least, if you study the example it will lead to more insight on how to approach this. If you really want to keep your foreign keys as they are, the ActiveRecord example (http:// techspot.zzzeek.org/files/poly_assoc_1.py ) illustrates that pattern with SA. next, you're going to want to specify cascade='all, delete-orphan' on both relations which reference the association table: mapper(Parent, parents, properties={ 'associations':relation(Association, cascade=all, delete-orphan) }) class Association(object): def _child(self): if self.assoc_type='child1': return self.child_1 else: return self.child_2 child=property(_child) mapper(Association, assoc, properties={ 'child_1':relation(Child1, backref=backref(assoc_1, cascade=all, delete-orphan)), 'child_2':relation(Child2, backref=backref(assoc_2, cascade=all, delete-orphan)), }) mapper(Child1, children_1) mapper(Child2, children_2) im out of town this week so I dont have the resources to work out the full solution but this is the general idea. Mike, I have got the solution this way. - Pradeep Jindal --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- poly_assoc_sep_table.py Description: application/python
[sqlalchemy] Re: One To Many Polymorphic Association.
Currently, unable to find any solution for this problem. I think my little knowledge with respect to sqlalchemy is restricting me to get a solution for this issue. Anyone with somewhat more in-depth knowledge should be able to find a solution. Waiting... Regards, Pradeep Jindal On Aug 25, 1:15 am, Pradeep Jindal [EMAIL PROTECTED] wrote: On Friday 24 August 2007 19:20:00 Gaetan de Menten wrote: You might be interested by:http://techspot.zzzeek.org/?p=13 (also in the examples directory of SQLAlchemy) On 8/24/07, praddy [EMAIL PROTECTED] wrote: ## from sqlalchemy import * meta = BoundMetaData('sqlite://', echo=False) # Parents table. parents = Table('parents', meta, Column(id, Integer, primary_key=True), Column(data, String(50), nullable=False) ) # Children_1 Table. children_1 = Table('children_1', meta, Column(id, Integer, primary_key=True), Column(data, String(50), nullable=False) ) # Children_2 Table. children_2 = Table('children_2', meta, Column(id, Integer, primary_key=True), Column(data, String(50)) ) # Association Table. # This is a generic table which can relate anything to parent. assoc = Table('assoc', meta, # parents.c.id Column(parent_id, Integer, ForeignKey(parents.c.id)), # associate's id either children_1.c.id or children_2.c.id or any other child. Column(assoc_id, Integer), # Which can be either 'child_1' or 'child_2' for now (can be used for extending children # type, decides which table to look in. Column(assoc_type, String(20)) ) ### I am a novice with respect to sqlalchemy may be RDBMS as well. How would you like to work on this scenario to achieve backwards cascading (may not be the right word) which means when one deletes one specific child from children_1 table (for example), there should not be any association entry, which associates that child to the parent, in the association table as well? Thanks for the reply. I have already gone through that article, but was unable to figure out the solution. Anyways, I will give it one more try. Please note that this scenario is already there and I can't make any changes to the tables at all. Thanks - Pradeep Jindal --~--~-~--~~~---~--~~ 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: One To Many Polymorphic Association.
On Aug 24, 2007, at 8:39 AM, praddy wrote: ## from sqlalchemy import * meta = BoundMetaData('sqlite://', echo=False) # Parents table. parents = Table('parents', meta, Column(id, Integer, primary_key=True), Column(data, String(50), nullable=False) ) # Children_1 Table. children_1 = Table('children_1', meta, Column(id, Integer, primary_key=True), Column(data, String(50), nullable=False) ) # Children_2 Table. children_2 = Table('children_2', meta, Column(id, Integer, primary_key=True), Column(data, String(50)) ) # Association Table. # This is a generic table which can relate anything to parent. assoc = Table('assoc', meta, # parents.c.id Column(parent_id, Integer, ForeignKey(parents.c.id)), # associate's id either children_1.c.id or children_2.c.id or any other child. Column(assoc_id, Integer), # Which can be either 'child_1' or 'child_2' for now (can be used for extending children # type, decides which table to look in. Column(assoc_type, String(20)) ) ### I am a novice with respect to sqlalchemy may be RDBMS as well. How would you like to work on this scenario to achieve backwards cascading (may not be the right word) which means when one deletes one specific child from children_1 table (for example), there should not be any association entry, which associates that child to the parent, in the association table as well? To allow proper foreign key support, you probably want to place the foreign key to the association table on each of children_1 and children_2, and remove the assoc_id column from the assoc table. this is the key to the blog article about polymorphic associations. its probably easiest for you to work with the example mentioned in the blog and modify. (http://techspot.zzzeek.org/files/ poly_assoc_2.py ). at least, if you study the example it will lead to more insight on how to approach this. If you really want to keep your foreign keys as they are, the ActiveRecord example (http:// techspot.zzzeek.org/files/poly_assoc_1.py ) illustrates that pattern with SA. next, you're going to want to specify cascade='all, delete-orphan' on both relations which reference the association table: mapper(Parent, parents, properties={ 'associations':relation(Association, cascade=all, delete-orphan) }) class Association(object): def _child(self): if self.assoc_type='child1': return self.child_1 else: return self.child_2 child=property(_child) mapper(Association, assoc, properties={ 'child_1':relation(Child1, backref=backref(assoc_1, cascade=all, delete-orphan)), 'child_2':relation(Child2, backref=backref(assoc_2, cascade=all, delete-orphan)), }) mapper(Child1, children_1) mapper(Child2, children_2) im out of town this week so I dont have the resources to work out the full solution but this is the general idea. --~--~-~--~~~---~--~~ 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: One To Many Polymorphic Association.
On Monday 27 August 2007 19:57:21 Michael Bayer wrote: On Aug 24, 2007, at 8:39 AM, praddy wrote: ## from sqlalchemy import * meta = BoundMetaData('sqlite://', echo=False) # Parents table. parents = Table('parents', meta, Column(id, Integer, primary_key=True), Column(data, String(50), nullable=False) ) # Children_1 Table. children_1 = Table('children_1', meta, Column(id, Integer, primary_key=True), Column(data, String(50), nullable=False) ) # Children_2 Table. children_2 = Table('children_2', meta, Column(id, Integer, primary_key=True), Column(data, String(50)) ) # Association Table. # This is a generic table which can relate anything to parent. assoc = Table('assoc', meta, # parents.c.id Column(parent_id, Integer, ForeignKey(parents.c.id)), # associate's id either children_1.c.id or children_2.c.id or any other child. Column(assoc_id, Integer), # Which can be either 'child_1' or 'child_2' for now (can be used for extending children # type, decides which table to look in. Column(assoc_type, String(20)) ) ### I am a novice with respect to sqlalchemy may be RDBMS as well. How would you like to work on this scenario to achieve backwards cascading (may not be the right word) which means when one deletes one specific child from children_1 table (for example), there should not be any association entry, which associates that child to the parent, in the association table as well? To allow proper foreign key support, you probably want to place the foreign key to the association table on each of children_1 and children_2, and remove the assoc_id column from the assoc table. this is the key to the blog article about polymorphic associations. its probably easiest for you to work with the example mentioned in the blog and modify. (http://techspot.zzzeek.org/files/ poly_assoc_2.py ). at least, if you study the example it will lead to more insight on how to approach this. If you really want to keep your foreign keys as they are, the ActiveRecord example (http:// techspot.zzzeek.org/files/poly_assoc_1.py ) illustrates that pattern with SA. next, you're going to want to specify cascade='all, delete-orphan' on both relations which reference the association table: mapper(Parent, parents, properties={ 'associations':relation(Association, cascade=all, delete-orphan) }) class Association(object): def _child(self): if self.assoc_type='child1': return self.child_1 else: return self.child_2 child=property(_child) mapper(Association, assoc, properties={ 'child_1':relation(Child1, backref=backref(assoc_1, cascade=all, delete-orphan)), 'child_2':relation(Child2, backref=backref(assoc_2, cascade=all, delete-orphan)), }) mapper(Child1, children_1) mapper(Child2, children_2) im out of town this week so I dont have the resources to work out the full solution but this is the general idea. Michael, Thanks for the reply. I think I already got the solution, I will post it here after the testing part of it. Further, I could not change any table in the real scenario, I had to go with the existing tables. Thanks again. Pradeep Jindal --~--~-~--~~~---~--~~ 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: One To Many Polymorphic Association.
You might be interested by: http://techspot.zzzeek.org/?p=13 (also in the examples directory of SQLAlchemy) On 8/24/07, praddy [EMAIL PROTECTED] wrote: ## from sqlalchemy import * meta = BoundMetaData('sqlite://', echo=False) # Parents table. parents = Table('parents', meta, Column(id, Integer, primary_key=True), Column(data, String(50), nullable=False) ) # Children_1 Table. children_1 = Table('children_1', meta, Column(id, Integer, primary_key=True), Column(data, String(50), nullable=False) ) # Children_2 Table. children_2 = Table('children_2', meta, Column(id, Integer, primary_key=True), Column(data, String(50)) ) # Association Table. # This is a generic table which can relate anything to parent. assoc = Table('assoc', meta, # parents.c.id Column(parent_id, Integer, ForeignKey(parents.c.id)), # associate's id either children_1.c.id or children_2.c.id or any other child. Column(assoc_id, Integer), # Which can be either 'child_1' or 'child_2' for now (can be used for extending children # type, decides which table to look in. Column(assoc_type, String(20)) ) ### I am a novice with respect to sqlalchemy may be RDBMS as well. How would you like to work on this scenario to achieve backwards cascading (may not be the right word) which means when one deletes one specific child from children_1 table (for example), there should not be any association entry, which associates that child to the parent, in the association table as well? -- Gaƫtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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: One To Many Polymorphic Association.
On Friday 24 August 2007 19:20:00 Gaetan de Menten wrote: You might be interested by: http://techspot.zzzeek.org/?p=13 (also in the examples directory of SQLAlchemy) On 8/24/07, praddy [EMAIL PROTECTED] wrote: ## from sqlalchemy import * meta = BoundMetaData('sqlite://', echo=False) # Parents table. parents = Table('parents', meta, Column(id, Integer, primary_key=True), Column(data, String(50), nullable=False) ) # Children_1 Table. children_1 = Table('children_1', meta, Column(id, Integer, primary_key=True), Column(data, String(50), nullable=False) ) # Children_2 Table. children_2 = Table('children_2', meta, Column(id, Integer, primary_key=True), Column(data, String(50)) ) # Association Table. # This is a generic table which can relate anything to parent. assoc = Table('assoc', meta, # parents.c.id Column(parent_id, Integer, ForeignKey(parents.c.id)), # associate's id either children_1.c.id or children_2.c.id or any other child. Column(assoc_id, Integer), # Which can be either 'child_1' or 'child_2' for now (can be used for extending children # type, decides which table to look in. Column(assoc_type, String(20)) ) ### I am a novice with respect to sqlalchemy may be RDBMS as well. How would you like to work on this scenario to achieve backwards cascading (may not be the right word) which means when one deletes one specific child from children_1 table (for example), there should not be any association entry, which associates that child to the parent, in the association table as well? Thanks for the reply. I have already gone through that article, but was unable to figure out the solution. Anyways, I will give it one more try. Please note that this scenario is already there and I can't make any changes to the tables at all. Thanks - Pradeep Jindal --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---