[sqlalchemy] Re: Orphans not deleted using cascade parameter
and here is the new traceback ;-) Traceback (most recent call last): File foo.py, line 38, in module DBSession.flush() File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/scoping.py, line 106, in do return getattr(self.registry(), name)(*args, **kwargs) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/session.py, line 1409, in flush flush_context.execute() File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ unitofwork.py, line 265, in execute UOWExecutor().execute(self, tasks) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ unitofwork.py, line 753, in execute self.execute_save_steps(trans, task) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ unitofwork.py, line 771, in execute_save_steps self.execute_dependencies(trans, task, True) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ unitofwork.py, line 783, in execute_dependencies self.execute_dependency(trans, dep, True) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ unitofwork.py, line 765, in execute_dependency dep.execute(trans, isdelete) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ unitofwork.py, line 722, in execute self.processor.process_dependencies(self.targettask, [elem.state for elem in self.targettask.polymorphic_todelete_elements], trans, delete=True) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ dependency.py, line 181, in process_dependencies self._synchronize(state, child, None, True, uowcommit) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/ dependency.py, line 251, in _synchronize sync.clear(dest, self.mapper, self.prop.synchronize_pairs) File /Users/michael/programming/rumdemo3/lib/python2.5/site- packages/SQLAlchemy-0.5.0beta3-py2.5.egg/sqlalchemy/orm/sync.py, line 28, in clear raise AssertionError(Dependency rule tried to blank-out primary key column '%s' on instance '%s' % (r, mapperutil.state_str(dest))) AssertionError: Dependency rule tried to blank-out primary key column 'project_programming_language.programming_language_id' on instance '[EMAIL PROTECTED]' --~--~-~--~~~---~--~~ 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: Orphans not deleted using cascade parameter
On Sep 4, 2008, at 4:15 AM, Michael Brickenstein wrote: AssertionError: Dependency rule tried to blank-out primary key column 'project_programming_language.programming_language_id' on instance '[EMAIL PROTECTED]' the project_programming_language table's primary key is programming_language_id, and this column is a foreign key to programming_language's primary key column. You can't delete a row from programming_language without also deleting the row from project_programming_language. Set cascade='all, delete-orphan' on the project_languages relation. --~--~-~--~~~---~--~~ 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: Orphans not deleted using cascade parameter
Dear Michael! Thanks, I got it and understand the difference now. Thank you very much for your help and your time. Michael Am 04.09.2008 um 14:35 schrieb Michael Bayer: On Sep 4, 2008, at 4:15 AM, Michael Brickenstein wrote: AssertionError: Dependency rule tried to blank-out primary key column 'project_programming_language.programming_language_id' on instance '[EMAIL PROTECTED]' the project_programming_language table's primary key is programming_language_id, and this column is a foreign key to programming_language's primary key column. You can't delete a row from programming_language without also deleting the row from project_programming_language. Set cascade='all, delete-orphan' on the project_languages relation. --~--~-~--~~~---~--~~ 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: Orphans not deleted using cascade parameter
Hi!
Thanks for the answers.
I have some problems, reproducing it in a small piece of code.
It occurs here.
http://toscawidgets.org/trac/rum/ticket/31
I will provide you with details, when I have isolated the problems.
Michael
On 29 Aug., 17:06, Michael Bayer [EMAIL PROTECTED] wrote:
Unfortunately, without an illustration of your usage pattern, we
can't assist with your issue. Here's the same test case again from
earlier in the thread. Can you modify it to look like your failing
condition ?
rom sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://', echo=True)
Base = declarative_base()
class PublicationElement(Base):
__tablename__ = 'publication'
publication_id = Column(Integer, primary_key=True)
name = Column(Unicode(255))
class SectionElement(Base):
__tablename__ = 'section'
section_id = Column(Integer, primary_key=True)
publication_id = Column(Integer,
ForeignKey('publication.publication_id'), nullable=False)
publication = relation('PublicationElement', cascade=all, delete-
orphan, backref='sections')
name = Column(Unicode(255))
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
sec1 = SectionElement(name='s1',
publication=PublicationElement(name='p1'))
sess = Session()
sess.add(sec1)
sess.commit()
assert sess.query(SectionElement).one().publication.name == 'p1'
sess.delete(sec1)
sess.commit()
assert engine.execute(select count(1) from publication).scalar() == 0
assert engine.execute(select count(1) from section).scalar() == 0
On Aug 29, 2008, at 1:44 AM, Michael Brickenstein wrote:
Hi!
Hi have a similar problem using
table reflection a la sqlsoup. My DB Backend ist postgresql 8.3.
I have a many to many relation:
orms5=# \d project_programming_language
Table public.project_programming_language
Column | Type | Modifiers
-+-+---
project_id | integer | not null
programming_language_id | integer | not null
Indexes:
projprogpkconstraint PRIMARY KEY, btree (project_id,
programming_language_id)
Foreign-key constraints:
programmierspracheconstraint FOREIGN KEY
(programming_language_id) REFERENCES
programming_language(programming_language_id) ON DELETE CASCADE
projektconstraint FOREIGN KEY (project_id) REFERENCES
project(project_id) ON DELETE CASCADE
I got the same message, when delete an object of the table
programming_language:
Dependency rule tried
to blank-out primary key column
As you can see, the foreign key constraints in the db work fine:
I can drop the row via a
DELETE
statement in sql.
Michael
--~--~-~--~~~---~--~~
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: Orphans not deleted using cascade parameter
Hi! Hi have a similar problem using table reflection a la sqlsoup. My DB Backend ist postgresql 8.3. I have a many to many relation: orms5=# \d project_programming_language Table public.project_programming_language Column | Type | Modifiers -+-+--- project_id | integer | not null programming_language_id | integer | not null Indexes: projprogpkconstraint PRIMARY KEY, btree (project_id, programming_language_id) Foreign-key constraints: programmierspracheconstraint FOREIGN KEY (programming_language_id) REFERENCES programming_language(programming_language_id) ON DELETE CASCADE projektconstraint FOREIGN KEY (project_id) REFERENCES project(project_id) ON DELETE CASCADE I got the same message, when delete an object of the table programming_language: Dependency rule tried to blank-out primary key column As you can see, the foreign key constraints in the db work fine: I can drop the row via a DELETE statement in sql. Michael --~--~-~--~~~---~--~~ 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: Orphans not deleted using cascade parameter
I forgot to mention, that I use 0.5.0beta 3. --~--~-~--~~~---~--~~ 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: Orphans not deleted using cascade parameter
Hi Michael, I had the same problem for a while. I'm not exactly sure what I did in order to fix this (have 2 ideas in my mind, need to figure out which of it it was). As soon as I remember, I'll let you know. On Aug 29, 1:44 am, Michael Brickenstein [EMAIL PROTECTED] wrote: Hi! Hi have a similar problem using table reflection a la sqlsoup. My DB Backend ist postgresql 8.3. I have a many to many relation: orms5=# \d project_programming_language Table public.project_programming_language Column | Type | Modifiers -+-+--- project_id | integer | not null programming_language_id | integer | not null Indexes: projprogpkconstraint PRIMARY KEY, btree (project_id, programming_language_id) Foreign-key constraints: programmierspracheconstraint FOREIGN KEY (programming_language_id) REFERENCES programming_language(programming_language_id) ON DELETE CASCADE projektconstraint FOREIGN KEY (project_id) REFERENCES project(project_id) ON DELETE CASCADE I got the same message, when delete an object of the table programming_language: Dependency rule tried to blank-out primary key column As you can see, the foreign key constraints in the db work fine: I can drop the row via a DELETE statement in sql. Michael --~--~-~--~~~---~--~~ 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: Orphans not deleted using cascade parameter
Unfortunately, without an illustration of your usage pattern, we
can't assist with your issue.Here's the same test case again from
earlier in the thread. Can you modify it to look like your failing
condition ?
rom sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://', echo=True)
Base = declarative_base()
class PublicationElement(Base):
__tablename__ = 'publication'
publication_id = Column(Integer, primary_key=True)
name = Column(Unicode(255))
class SectionElement(Base):
__tablename__ = 'section'
section_id = Column(Integer, primary_key=True)
publication_id = Column(Integer,
ForeignKey('publication.publication_id'), nullable=False)
publication = relation('PublicationElement', cascade=all, delete-
orphan, backref='sections')
name = Column(Unicode(255))
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
sec1 = SectionElement(name='s1',
publication=PublicationElement(name='p1'))
sess = Session()
sess.add(sec1)
sess.commit()
assert sess.query(SectionElement).one().publication.name == 'p1'
sess.delete(sec1)
sess.commit()
assert engine.execute(select count(1) from publication).scalar() == 0
assert engine.execute(select count(1) from section).scalar() == 0
On Aug 29, 2008, at 1:44 AM, Michael Brickenstein wrote:
Hi!
Hi have a similar problem using
table reflection a la sqlsoup. My DB Backend ist postgresql 8.3.
I have a many to many relation:
orms5=# \d project_programming_language
Table public.project_programming_language
Column | Type | Modifiers
-+-+---
project_id | integer | not null
programming_language_id | integer | not null
Indexes:
projprogpkconstraint PRIMARY KEY, btree (project_id,
programming_language_id)
Foreign-key constraints:
programmierspracheconstraint FOREIGN KEY
(programming_language_id) REFERENCES
programming_language(programming_language_id) ON DELETE CASCADE
projektconstraint FOREIGN KEY (project_id) REFERENCES
project(project_id) ON DELETE CASCADE
I got the same message, when delete an object of the table
programming_language:
Dependency rule tried
to blank-out primary key column
As you can see, the foreign key constraints in the db work fine:
I can drop the row via a
DELETE
statement in sql.
Michael
--~--~-~--~~~---~--~~
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: Orphans not deleted using cascade parameter
On Wed, Aug 27, 2008 at 12:53:38PM -0700, Alex Mathieu wrote: Thanks Michael, I'll have a look over this !! Bob, thanks also for your help, however, I'm not able to use the code... maybe the indention is wrong here or I don't know... I was able to execute the function, but even by putting a print as the first line of the function, nothing got printed out, weird... (maybe my lack of skills using python, yet :P) Alex, Weird. I've attached it here so maybe that will make things work. -- -- Bob Farrell pH, an Experian Company www.phgroup.com Office Line: 020 7598 0310 Fax: 020 7598 0311 -- --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- def delete_cascade(orm_obj): Perform a cascading delete on any ORM object and its children. # Since we take an ORM _object_, we need to discover its table: obj_table = class_mapper(type(orm_obj)).mapped_table def get_child_tables(parent_table, children=[]): Recursively find all child tables. new_children = [] # Use SQLAlchemy's table_iterator reversed to give us the tables in the # correct order to ensure that we can delete without breaking any constraints # (i.e. we will not delete a parent before its child: for table in obj_table.metadata.table_iterator(reverse=True): for fk in table.foreign_keys: if fk.references(parent_table) and \ (table, fk, parent_table) not in children: new_children.append((table, fk, parent_table)) break # If no new children are found we have reached the top of the recursion so we # fall back down the stack: if not new_children: return [] else: for child in new_children: # Here is the recursive call: children.extend(get_child_tables(child[0])) children.extend(new_children) return children _children = get_child_tables(obj_table) children = [] # This loop filters out any tables who have more than one foreign key where one # of the foreign keys references the root node so we have no duplicates. The # result is a list of tables that reference either the root node or their # parent: for child in _children: if child[0] not in [x[0] for x in children]: children.append(child) elif child[1].references(obj_table): for i, _child in enumerate(children): if _child[0] == child[0]: children[i] = child break # This is a rare-case optimisation that sees if any of the tables reference the # root node indirectly by having a foreign key whose counterpart is a direct # reference to the root node: for child in children: table, fk, parent_table = child if not fk.references(obj_table): parent_fk = fk.column.foreign_key while parent_fk is not None: if parent_fk.references(obj_table): obj_column = ( parent_fk.column.key ) break parent_fk = parent_fk.column.foreign_key # Finally build a select for grandchildren or later to establish which records # need to be removed by seeing which of their parent's records are ancestors of # the root node: if parent_fk is None: sel = select([fk.parent]) parent_fk = fk.column.foreign_key while parent_fk is not None: sel.append_whereclause( parent_fk.parent==parent_fk.column ) tmp = parent_fk.column.foreign_key if tmp is not None: parent_fk = tmp else: break obj_column = ( parent_fk.column.key ) sel.append_whereclause( parent_fk.column==getattr(orm_obj, obj_column) ) in_column = fk.column.key yield delete( fk.parent.table, fk.parent.in_(sel) ) continue # Otherwise simply yield a delete statement to delete the first-generation # child of the root node: else: obj_column = fk.column.key yield delete( table, fk.parent==getattr(orm_obj, obj_column) ) # Build the delete statement for the root node itself by introspectively # discovering the primary keys of
[sqlalchemy] Re: Orphans not deleted using cascade parameter
Hi Michael,
I've switch my definition to the following:
class PublicationElement(Entity):
using_options(tablename='publication')
sections = OneToMany('SectionElement')
covers = OneToMany('PublicationCoverElement')
publication_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
short_name = Field(Unicode(30))
app_download_link = Field(UnicodeText)
class SectionElement(Entity):
using_options(tablename='section')
publication = ManyToOne('PublicationElement',
colname=publication_id, cascade='all, delete-orphan')
feedDetails = OneToOne('FeedDetailsElement',
inverse='SectionElement')
defaultSections = OneToOne('DefaultSectionElement',
inverse='SectionElement')
section_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
articles_on_cover = Field(Integer)
articles_on_sub_cover = Field(Integer)
ad_on_top = Field(Boolean)
ad_on_bottom = Field(Boolean)
poll_rate_minutes = Field(Integer)
poll_weight = Field(Integer)
show_sub_cover = Field(Boolean)
However, nothing seems to change, the rows into my sections table of
my database get their publication_id key set to 0, yet again no entry
gets removed from the database...
Harish Thanks for the hint, but however, no success with this
solution... :(
What would be the next step in order to figure out what is happening ?
Just to confirm, my Publication element is safely removed from the
database, every time.
Thanks again, in advance
On Aug 26, 11:35 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Aug 26, 2008, at 6:08 PM, Alex Mathieu wrote:
Let's say I have two classes:
class PublicationElement(Entity):
using_options(tablename='publication')
sections = OneToMany('SectionElement')
covers = OneToMany('PublicationCoverElement')
publication_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
short_name = Field(Unicode(30))
app_download_link = Field(UnicodeText)
class SectionElement(Entity):
using_options(tablename='section')
publication = ManyToOne('PublicationElement',
colname=publication_id, cascade=all,delete-orphan)
section_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
articles_on_cover = Field(Integer)
articles_on_sub_cover = Field(Integer)
ad_on_top = Field(Boolean)
ad_on_bottom = Field(Boolean)
poll_rate_minutes = Field(Integer)
poll_weight = Field(Integer)
show_sub_cover = Field(Boolean)
If I delete a PublicationElement entity, all the related
SectionElement entities get their publication_id key set to 0... and I
just can't figure it out why. I've been digging through the web, but
wasn't able any answer... Maybe I putted my cascade argument at the
wrong place ? Not sure to get it...
this mapping suggests that the deletion of a SectionElement will cause
the deletion of a related PublicationElement, but not the other way
around. The cascade would have to be moved to the
PublicationElement.sections side of the relation. Im not sure
where the 0 is coming from, SQLAlchemy will normally set foreign key
references to NULL if no row is related.
--~--~-~--~~~---~--~~
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: Orphans not deleted using cascade parameter
F*ck... I just realized that I was using MyISAM table engine...
here's the deal then... I cannot use InnoDB for this projet so I
think I will be writing some recursive code that can determine if an
object has childs dependencies and will delete the proper objects
thanks again =)
On Aug 27, 9:26 am, Alex Mathieu [EMAIL PROTECTED] wrote:
Hi Michael,
I've switch my definition to the following:
class PublicationElement(Entity):
using_options(tablename='publication')
sections = OneToMany('SectionElement')
covers = OneToMany('PublicationCoverElement')
publication_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
short_name = Field(Unicode(30))
app_download_link = Field(UnicodeText)
class SectionElement(Entity):
using_options(tablename='section')
publication = ManyToOne('PublicationElement',
colname=publication_id, cascade='all, delete-orphan')
feedDetails = OneToOne('FeedDetailsElement',
inverse='SectionElement')
defaultSections = OneToOne('DefaultSectionElement',
inverse='SectionElement')
section_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
articles_on_cover = Field(Integer)
articles_on_sub_cover = Field(Integer)
ad_on_top = Field(Boolean)
ad_on_bottom = Field(Boolean)
poll_rate_minutes = Field(Integer)
poll_weight = Field(Integer)
show_sub_cover = Field(Boolean)
However, nothing seems to change, the rows into my sections table of
my database get their publication_id key set to 0, yet again no entry
gets removed from the database...
Harish Thanks for the hint, but however, no success with this
solution... :(
What would be the next step in order to figure out what is happening ?
Just to confirm, my Publication element is safely removed from the
database, every time.
Thanks again, in advance
On Aug 26, 11:35 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Aug 26, 2008, at 6:08 PM, Alex Mathieu wrote:
Let's say I have two classes:
class PublicationElement(Entity):
using_options(tablename='publication')
sections = OneToMany('SectionElement')
covers = OneToMany('PublicationCoverElement')
publication_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
short_name = Field(Unicode(30))
app_download_link = Field(UnicodeText)
class SectionElement(Entity):
using_options(tablename='section')
publication = ManyToOne('PublicationElement',
colname=publication_id, cascade=all,delete-orphan)
section_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
articles_on_cover = Field(Integer)
articles_on_sub_cover = Field(Integer)
ad_on_top = Field(Boolean)
ad_on_bottom = Field(Boolean)
poll_rate_minutes = Field(Integer)
poll_weight = Field(Integer)
show_sub_cover = Field(Boolean)
If I delete a PublicationElement entity, all the related
SectionElement entities get their publication_id key set to 0... and I
just can't figure it out why. I've been digging through the web, but
wasn't able any answer... Maybe I putted my cascade argument at the
wrong place ? Not sure to get it...
this mapping suggests that the deletion of a SectionElement will cause
the deletion of a related PublicationElement, but not the other way
around. The cascade would have to be moved to the
PublicationElement.sections side of the relation. Im not sure
where the 0 is coming from, SQLAlchemy will normally set foreign key
references to NULL if no row is related.
--~--~-~--~~~---~--~~
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: Orphans not deleted using cascade parameter
On Aug 27, 2008, at 10:48 AM, Alex Mathieu wrote: F*ck... I just realized that I was using MyISAM table engine... here's the deal then... I cannot use InnoDB for this projet so I think I will be writing some recursive code that can determine if an object has childs dependencies and will delete the proper objects thanks again =) SQLA's cascade option works with MyISAM tables just fine - it does not require real foreign keys or cascades at the database level to be present. The only thing you'd lose in that case is the ability to reflect ForeignKey objects from an existing schema, but your mapping below should be creating those explicitly. Theres definitely no reason to write this by hand. --~--~-~--~~~---~--~~ 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: Orphans not deleted using cascade parameter
On Wed, Aug 27, 2008 at 07:48:20AM -0700, Alex Mathieu wrote: F*ck... I just realized that I was using MyISAM table engine... here's the deal then... I cannot use InnoDB for this projet so I think I will be writing some recursive code that can determine if an object has childs dependencies and will delete the proper objects thanks again =) I posted this on the ML a while ago - one of the SA devs suggested a use case for it would be your current situation so here it is again. Let me know if you use it and have any problems. def delete_cascade(orm_obj): Perform a cascading delete on any ORM object and its children. # Since we take an ORM _object_, we need to discover its table: obj_table = class_mapper(type(orm_obj)).mapped_table def get_child_tables(parent_table, children=[]): Recursively find all child tables. new_children = [] # Use SQLAlchemy's table_iterator reversed to give us the tables in the # correct order to ensure that we can delete without breaking any constraints # (i.e. we will not delete a parent before its child: for table in obj_table.metadata.table_iterator(reverse=True): for fk in table.foreign_keys: if fk.references(parent_table) and \ (table, fk, parent_table) not in children: new_children.append((table, fk, parent_table)) break # If no new children are found we have reached the top of the recursion so we # fall back down the stack: if not new_children: return [] else: for child in new_children: # Here is the recursive call: children.extend(get_child_tables(child[0])) children.extend(new_children) return children _children = get_child_tables(obj_table) children = [] # This loop filters out any tables who have more than one foreign key where one # of the foreign keys references the root node so we have no duplicates. The # result is a list of tables that reference either the root node or their # parent: for child in _children: if child[0] not in [x[0] for x in children]: children.append(child) elif child[1].references(obj_table): for i, _child in enumerate(children): if _child[0] == child[0]: children[i] = child break # This is a rare-case optimisation that sees if any of the tables reference the # root node indirectly by having a foreign key whose counterpart is a direct # reference to the root node: for child in children: table, fk, parent_table = child if not fk.references(obj_table): parent_fk = fk.column.foreign_key while parent_fk is not None: if parent_fk.references(obj_table): obj_column = ( parent_fk.column.key ) break parent_fk = parent_fk.column.foreign_key # Finally build a select for grandchildren or later to establish which records # need to be removed by seeing which of their parent's records are ancestors of # the root node: if parent_fk is None: sel = select([fk.parent]) parent_fk = fk.column.foreign_key while parent_fk is not None: sel.append_whereclause( parent_fk.parent==parent_fk.column ) tmp = parent_fk.column.foreign_key if tmp is not None: parent_fk = tmp else: break obj_column = ( parent_fk.column.key ) sel.append_whereclause( parent_fk.column==getattr(orm_obj, obj_column) ) in_column = fk.column.key yield delete( fk.parent.table, fk.parent.in_(sel) ) continue # Otherwise simply yield a delete statement to delete the first-generation # child of the root node: else: obj_column = fk.column.key yield delete( table, fk.parent==getattr(orm_obj, obj_column) ) # Build the delete statement for the root node itself by introspectively # discovering the primary keys of the root node's table and deleting a # single record from this table (i.e. the root node): pk = [getattr(orm_obj, x) for x in obj_table.primary_key.keys()] pk_cols = [x for x in obj_table.c if x.primary_key] cond = pk[0] == pk_cols[0] for x, y in zip(pk[1:], pk_cols[1:]): if x and y: cond = x == y yield delete( obj_table, cond ) -- -- Bob Farrell pH, an Experian Company www.phgroup.com Office Line: 020 7598 0310 Fax: 020 7598
[sqlalchemy] Re: Orphans not deleted using cascade parameter
Damn, so why does the cascade statement does not work ? I simply can't
get that one... I've been trying all the examples using the cascade
argument in the relationships declaration, like you said in your first
post, and it won't just work.
I just gave it another try, and my Publication element get deleted,
but my sections elements get their publication_id keys set to 0, and
not being deleted... Here's my entities:
class PublicationElement(Entity):
using_options(tablename='publication')
sections = OneToMany('SectionElement')
covers = OneToMany('PublicationCoverElement')
publication_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
short_name = Field(Unicode(30))
app_download_link = Field(UnicodeText)
class SectionElement(Entity):
using_options(tablename='section')
publication = ManyToOne('PublicationElement',
colname=publication_id, cascade=all, delete, delete-orphan)
feedDetails = OneToOne('FeedDetailsElement', inverse='section')
defaultSections = OneToOne('DefaultSectionElement',
inverse='section')
section_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
articles_on_cover = Field(Integer)
articles_on_sub_cover = Field(Integer)
ad_on_top = Field(Boolean)
ad_on_bottom = Field(Boolean)
poll_rate_minutes = Field(Integer)
poll_weight = Field(Integer)
show_sub_cover = Field(Boolean)
Well well
On Aug 27, 10:53 am, Michael Bayer [EMAIL PROTECTED] wrote:
On Aug 27, 2008, at 10:48 AM, Alex Mathieu wrote:
F*ck... I just realized that I was using MyISAM table engine...
here's the deal then... I cannot use InnoDB for this projet so I
think I will be writing some recursive code that can determine if an
object has childs dependencies and will delete the proper objects
thanks again =)
SQLA's cascade option works with MyISAM tables just fine - it does
not require real foreign keys or cascades at the database level to be
present. The only thing you'd lose in that case is the ability to
reflect ForeignKey objects from an existing schema, but your mapping
below should be creating those explicitly. Theres definitely no
reason to write this by hand.
--~--~-~--~~~---~--~~
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: Orphans not deleted using cascade parameter
I'm not fluent with Elixir, but here is a sample script using
declarative and SQLite (another database that has a very casual notion
of foreign keys) which demonstrates the correct behavior.First
convert this script to Elixir (and also to 0.4 if needed, this script
is made against 0.5) and use with your MySQL database, then work
backwards towards your code to see at what point the 0 starts
popping up.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://', echo=True)
Base = declarative_base()
class PublicationElement(Base):
__tablename__ = 'publication'
publication_id = Column(Integer, primary_key=True)
name = Column(Unicode(255))
class SectionElement(Base):
__tablename__ = 'section'
section_id = Column(Integer, primary_key=True)
publication_id = Column(Integer,
ForeignKey('publication.publication_id'), nullable=False)
publication = relation('PublicationElement', cascade=all, delete-
orphan, backref='sections')
name = Column(Unicode(255))
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
sec1 = SectionElement(name='s1',
publication=PublicationElement(name='p1'))
sess = Session()
sess.add(sec1)
sess.commit()
assert sess.query(SectionElement).one().publication.name == 'p1'
sess.delete(sec1)
sess.commit()
assert engine.execute(select count(1) from publication).scalar() == 0
assert engine.execute(select count(1) from section).scalar() == 0
--~--~-~--~~~---~--~~
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: Orphans not deleted using cascade parameter
Thanks Michael, I'll have a look over this !!
Bob, thanks also for your help, however, I'm not able to use the
code... maybe the indention is wrong here or I don't know... I was
able to execute the function, but even by putting a print as the first
line of the function, nothing got printed out, weird... (maybe my lack
of skills using python, yet :P)
On Aug 27, 3:11 pm, Michael Bayer [EMAIL PROTECTED] wrote:
I'm not fluent with Elixir, but here is a sample script using
declarative and SQLite (another database that has a very casual notion
of foreign keys) which demonstrates the correct behavior. First
convert this script to Elixir (and also to 0.4 if needed, this script
is made against 0.5) and use with your MySQL database, then work
backwards towards your code to see at what point the 0 starts
popping up.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('sqlite://', echo=True)
Base = declarative_base()
class PublicationElement(Base):
__tablename__ = 'publication'
publication_id = Column(Integer, primary_key=True)
name = Column(Unicode(255))
class SectionElement(Base):
__tablename__ = 'section'
section_id = Column(Integer, primary_key=True)
publication_id = Column(Integer,
ForeignKey('publication.publication_id'), nullable=False)
publication = relation('PublicationElement', cascade=all, delete-
orphan, backref='sections')
name = Column(Unicode(255))
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
sec1 = SectionElement(name='s1',
publication=PublicationElement(name='p1'))
sess = Session()
sess.add(sec1)
sess.commit()
assert sess.query(SectionElement).one().publication.name == 'p1'
sess.delete(sec1)
sess.commit()
assert engine.execute(select count(1) from publication).scalar() == 0
assert engine.execute(select count(1) from section).scalar() == 0
--~--~-~--~~~---~--~~
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: Orphans not deleted using cascade parameter
In fact, I think I figured it out how it was working... but I still
get this error:
class 'sqlalchemy.exceptions.AssertionError': Dependency rule tried
to blank-out primary key column 'default_sections.section_id' on
instance '[EMAIL PROTECTED]'
It appears that it tries to set my foregin key in my child element to
zero, instead of deleting it (and now SA is complaining because that
foreign key is the primary key of the table).
Any idea on why it tries to put the key to 0 instead of deleting the
whole child object ?
On Aug 26, 6:08 pm, Alex Mathieu [EMAIL PROTECTED] wrote:
Hi all,
I'm using Elixir SQLAlchemy to manage some entities, it's going
quite well. However, I just realize that when I was deleting my
parents entities, my sons entites were not deleted. The're might be
something I didn't get quite well, so that's why I'm requiring some
help...
Let's say I have two classes:
class PublicationElement(Entity):
using_options(tablename='publication')
sections = OneToMany('SectionElement')
covers = OneToMany('PublicationCoverElement')
publication_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
short_name = Field(Unicode(30))
app_download_link = Field(UnicodeText)
class SectionElement(Entity):
using_options(tablename='section')
publication = ManyToOne('PublicationElement',
colname=publication_id, cascade=all,delete-orphan)
section_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
articles_on_cover = Field(Integer)
articles_on_sub_cover = Field(Integer)
ad_on_top = Field(Boolean)
ad_on_bottom = Field(Boolean)
poll_rate_minutes = Field(Integer)
poll_weight = Field(Integer)
show_sub_cover = Field(Boolean)
If I delete a PublicationElement entity, all the related
SectionElement entities get their publication_id key set to 0... and I
just can't figure it out why. I've been digging through the web, but
wasn't able any answer... Maybe I putted my cascade argument at the
wrong place ? Not sure to get it...
Thanks in advance,
A.
--~--~-~--~~~---~--~~
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: Orphans not deleted using cascade parameter
On Aug 26, 2008, at 6:08 PM, Alex Mathieu wrote:
Let's say I have two classes:
class PublicationElement(Entity):
using_options(tablename='publication')
sections = OneToMany('SectionElement')
covers = OneToMany('PublicationCoverElement')
publication_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
short_name = Field(Unicode(30))
app_download_link = Field(UnicodeText)
class SectionElement(Entity):
using_options(tablename='section')
publication = ManyToOne('PublicationElement',
colname=publication_id, cascade=all,delete-orphan)
section_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
articles_on_cover = Field(Integer)
articles_on_sub_cover = Field(Integer)
ad_on_top = Field(Boolean)
ad_on_bottom = Field(Boolean)
poll_rate_minutes = Field(Integer)
poll_weight = Field(Integer)
show_sub_cover = Field(Boolean)
If I delete a PublicationElement entity, all the related
SectionElement entities get their publication_id key set to 0... and I
just can't figure it out why. I've been digging through the web, but
wasn't able any answer... Maybe I putted my cascade argument at the
wrong place ? Not sure to get it...
this mapping suggests that the deletion of a SectionElement will cause
the deletion of a related PublicationElement, but not the other way
around. The cascade would have to be moved to the
PublicationElement.sections side of the relation.Im not sure
where the 0 is coming from, SQLAlchemy will normally set foreign key
references to NULL if no row is related.
--~--~-~--~~~---~--~~
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: Orphans not deleted using cascade parameter
Try putting the cascade=all, delete, delete-orphan on OneToMany side of
the Relationship.
On Wed, Aug 27, 2008 at 9:05 AM, Michael Bayer [EMAIL PROTECTED]wrote:
On Aug 26, 2008, at 6:08 PM, Alex Mathieu wrote:
Let's say I have two classes:
class PublicationElement(Entity):
using_options(tablename='publication')
sections = OneToMany('SectionElement')
covers = OneToMany('PublicationCoverElement')
publication_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
short_name = Field(Unicode(30))
app_download_link = Field(UnicodeText)
class SectionElement(Entity):
using_options(tablename='section')
publication = ManyToOne('PublicationElement',
colname=publication_id, cascade=all,delete-orphan)
section_id = Field(Integer, primary_key=True)
name = Field(Unicode(255))
articles_on_cover = Field(Integer)
articles_on_sub_cover = Field(Integer)
ad_on_top = Field(Boolean)
ad_on_bottom = Field(Boolean)
poll_rate_minutes = Field(Integer)
poll_weight = Field(Integer)
show_sub_cover = Field(Boolean)
If I delete a PublicationElement entity, all the related
SectionElement entities get their publication_id key set to 0... and I
just can't figure it out why. I've been digging through the web, but
wasn't able any answer... Maybe I putted my cascade argument at the
wrong place ? Not sure to get it...
this mapping suggests that the deletion of a SectionElement will cause
the deletion of a related PublicationElement, but not the other way
around. The cascade would have to be moved to the
PublicationElement.sections side of the relation.Im not sure
where the 0 is coming from, SQLAlchemy will normally set foreign key
references to NULL if no row is related.
--
Regards,
Harish
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
