[sqlalchemy] Re: Cascading deletes to children
use the passive_updates=True, passive_deletes='all' flags. These
are described at
http://www.sqlalchemy.org/docs/05/sqlalchemy_orm.html#docstrings_sqlalchemy.orm_modfunc_relation
.
On Dec 5, 2008, at 11:42 AM, James Brady wrote:
Hi all,
I'm trying to get deletes and updates cascaded down from a parent
object to the child objects (connected by ForeignKey).
It all seems pretty simple in the docs, but I can't get it to work!
I'm using MySQL with the InnoDB engine, and have played with all the
variation of the onupdate, ondelete and cascade arguments I can
think of.
The problem is that immediately before the DELETE command is sent to
MySQL, there are UPDATE commands nulling out the foreign key
references of the child objects, so MySQL doesn't trigger it's ON
DELETE CASCADE action, and for some reason cascade=all, delete-
orphan doesn't clean up the children with NULL FKs.
Here's a simplified model:
users_table = Table('tg_user', metadata,
Column('user_id', Integer, primary_key=True),
Column('created', DateTime, default=datetime.now),
mysql_engine='InnoDB',
)
hat_table = Table('hat', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(100)),
Column('user_id', Integer, ForeignKey('tg_user.user_id'),
onupdate='CASCADE', ondelete='CASCADE'),
mysql_engine='InnoDB',
)
class User(object):
pass
class Hat(object):
pass
mapper(User, users_table)
mapper(Hat, hat_table,
properties = {
'user': relation(User, backref=hats, cascade=all, delete,
delete-orphan),
}
)
And the log from SA when I do a User.delete:
BEGIN
UPDATE hat SET user_id=%s WHERE hat.id = %s
[None, 1L]
DELETE FROM tg_user WHERE tg_user.user_id = %s
[1L]
COMMIT
Any help would be much appreciated!
James
--~--~-~--~~~---~--~~
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: Cascading deletes to children
actually, use passive_deletes=True, not 'all'. It will issue DELETEs
only for collections that are already loaded, this doesn't break
anything and prevents unnecessary SELECTs of unloaded collections.
The True setting is needed so that the session can update the state of
those collections during the flush process.
On Dec 5, 2008, at 12:29 PM, Michael Bayer wrote:
use the passive_updates=True, passive_deletes='all' flags. These
are described at
http://www.sqlalchemy.org/docs/05/sqlalchemy_orm.html#docstrings_sqlalchemy.orm_modfunc_relation
.
On Dec 5, 2008, at 11:42 AM, James Brady wrote:
Hi all,
I'm trying to get deletes and updates cascaded down from a parent
object to the child objects (connected by ForeignKey).
It all seems pretty simple in the docs, but I can't get it to work!
I'm using MySQL with the InnoDB engine, and have played with all
the variation of the onupdate, ondelete and cascade arguments I can
think of.
The problem is that immediately before the DELETE command is sent
to MySQL, there are UPDATE commands nulling out the foreign key
references of the child objects, so MySQL doesn't trigger it's ON
DELETE CASCADE action, and for some reason cascade=all, delete-
orphan doesn't clean up the children with NULL FKs.
Here's a simplified model:
users_table = Table('tg_user', metadata,
Column('user_id', Integer, primary_key=True),
Column('created', DateTime, default=datetime.now),
mysql_engine='InnoDB',
)
hat_table = Table('hat', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(100)),
Column('user_id', Integer, ForeignKey('tg_user.user_id'),
onupdate='CASCADE', ondelete='CASCADE'),
mysql_engine='InnoDB',
)
class User(object):
pass
class Hat(object):
pass
mapper(User, users_table)
mapper(Hat, hat_table,
properties = {
'user': relation(User, backref=hats, cascade=all,
delete, delete-orphan),
}
)
And the log from SA when I do a User.delete:
BEGIN
UPDATE hat SET user_id=%s WHERE hat.id = %s
[None, 1L]
DELETE FROM tg_user WHERE tg_user.user_id = %s
[1L]
COMMIT
Any help would be much appreciated!
James
--~--~-~--~~~---~--~~
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: Cascading deletes to children
Ah, I should say I'm using SA 0.4.3 - I going to try the same test on
0.5
On Dec 5, 11:36 am, Michael Bayer [EMAIL PROTECTED] wrote:
actually, use passive_deletes=True, not 'all'. It will issue DELETEs
only for collections that are already loaded, this doesn't break
anything and prevents unnecessary SELECTs of unloaded collections.
The True setting is needed so that the session can update the state of
those collections during the flush process.
On Dec 5, 2008, at 12:29 PM, Michael Bayer wrote:
use the passive_updates=True, passive_deletes='all' flags. These
are described
athttp://www.sqlalchemy.org/docs/05/sqlalchemy_orm.html#docstrings_sqla...
.
On Dec 5, 2008, at 11:42 AM, James Brady wrote:
Hi all,
I'm trying to get deletes and updates cascaded down from a parent
object to the child objects (connected by ForeignKey).
It all seems pretty simple in the docs, but I can't get it to work!
I'm using MySQL with the InnoDB engine, and have played with all
the variation of the onupdate, ondelete and cascade arguments I can
think of.
The problem is that immediately before the DELETE command is sent
to MySQL, there are UPDATE commands nulling out the foreign key
references of the child objects, so MySQL doesn't trigger it's ON
DELETE CASCADE action, and for some reason cascade=all, delete-
orphan doesn't clean up the children with NULL FKs.
Here's a simplified model:
users_table = Table('tg_user', metadata,
Column('user_id', Integer, primary_key=True),
Column('created', DateTime, default=datetime.now),
mysql_engine='InnoDB',
)
hat_table = Table('hat', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(100)),
Column('user_id', Integer, ForeignKey('tg_user.user_id'),
onupdate='CASCADE', ondelete='CASCADE'),
mysql_engine='InnoDB',
)
class User(object):
pass
class Hat(object):
pass
mapper(User, users_table)
mapper(Hat, hat_table,
properties = {
'user': relation(User, backref=hats, cascade=all,
delete, delete-orphan),
}
)
And the log from SA when I do a User.delete:
BEGIN
UPDATE hat SET user_id=%s WHERE hat.id = %s
[None, 1L]
DELETE FROM tg_user WHERE tg_user.user_id = %s
[1L]
COMMIT
Any help would be much appreciated!
James
--~--~-~--~~~---~--~~
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: Cascading deletes to children
Assuming user_id is a surrogate primary key, I dont see any need for
onupdate=CASCADE to be used here. Additionally, ondelete=CASCADE
on your hat.user_id column implies that hat will be deleted when a
user entry is deleted - however your relation has this set up on the
many-to-one side indicating that a user would be deleted when a
hat is deleted.
I think this is the full setup you're looking for:
users_table = Table('tg_user', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', Unicode(16), unique=True),
Column('created', DateTime, default=datetime.now),
mysql_engine='InnoDB',
)
hat_table = Table('hat', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(100)),
Column('user_id', Integer, ForeignKey('tg_user.user_id',
ondelete='CASCADE')),
mysql_engine='InnoDB',
)
class User(object):
pass
class Hat(object):
pass
mapper(User, users_table)
mapper(Hat, hat_table,
properties = {
'user': relation(User,
backref=backref(hats, cascade=all, delete, delete-orphan,
passive_deletes=True)
),
}
)
On Dec 5, 2008, at 1:42 PM, James wrote:
Thanks for your quick response! Unfortunately I made the changes you
suggest and I still get the UPDATE commands being sent to the children
before the DELETE on the parent. My model is now:
users_table = Table('tg_user', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', Unicode(16), unique=True),
Column('created', DateTime, default=datetime.now),
mysql_engine='InnoDB',
)
hat_table = Table('hat', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(100)),
Column('user_id', Integer, ForeignKey('tg_user.user_id',
onupdate='CASCADE', ondelete='CASCADE')),
mysql_engine='InnoDB',
)
class User(object):
pass
class Hat(object):
pass
mapper(User, users_table)
mapper(Hat, hat_table,
properties = {
'user': relation(User, backref=hats, cascade=all, delete,
delete-orphan, passive_updates=True, passive_deletes=True),
}
)
And I get the same pattern in the log:
BEGIN
UPDATE hat SET user_id=%s WHERE hat.id = %s
[None, 1L]
DELETE FROM tg_user WHERE tg_user.user_id = %s
[1L]
COMMIT
The ondelete and cascade arguments I have should be working in this
situation, right?
Thanks again,
James
On Dec 5, 11:36 am, Michael Bayer [EMAIL PROTECTED] wrote:
actually, use passive_deletes=True, not 'all'. It will issue
DELETEs
only for collections that are already loaded, this doesn't break
anything and prevents unnecessary SELECTs of unloaded collections.
The True setting is needed so that the session can update the state
of
those collections during the flush process.
On Dec 5, 2008, at 12:29 PM, Michael Bayer wrote:
use the passive_updates=True, passive_deletes='all' flags. These
are described
athttp://www.sqlalchemy.org/docs/05/sqlalchemy_orm.html#docstrings_sqla
...
.
On Dec 5, 2008, at 11:42 AM, James Brady wrote:
Hi all,
I'm trying to get deletes and updates cascaded down from a parent
object to the child objects (connected by ForeignKey).
It all seems pretty simple in the docs, but I can't get it to work!
I'm using MySQL with the InnoDB engine, and have played with all
the variation of the onupdate, ondelete and cascade arguments I can
think of.
The problem is that immediately before the DELETE command is sent
to MySQL, there are UPDATE commands nulling out the foreign key
references of the child objects, so MySQL doesn't trigger it's ON
DELETE CASCADE action, and for some reason cascade=all, delete-
orphan doesn't clean up the children with NULL FKs.
Here's a simplified model:
users_table = Table('tg_user', metadata,
Column('user_id', Integer, primary_key=True),
Column('created', DateTime, default=datetime.now),
mysql_engine='InnoDB',
)
hat_table = Table('hat', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(100)),
Column('user_id', Integer, ForeignKey('tg_user.user_id'),
onupdate='CASCADE', ondelete='CASCADE'),
mysql_engine='InnoDB',
)
class User(object):
pass
class Hat(object):
pass
mapper(User, users_table)
mapper(Hat, hat_table,
properties = {
'user': relation(User, backref=hats, cascade=all,
delete, delete-orphan),
}
)
And the log from SA when I do a User.delete:
BEGIN
UPDATE hat SET user_id=%s WHERE hat.id = %s
[None, 1L]
DELETE FROM tg_user WHERE tg_user.user_id = %s
[1L]
COMMIT
Any help would be much appreciated!
James
--~--~-~--~~~---~--~~
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
[sqlalchemy] Re: Cascading deletes to children
Yep, the same behaviour in 0.5rc4
On Dec 5, 12:44 pm, James [EMAIL PROTECTED] wrote:
Ah, I should say I'm using SA 0.4.3 - I going to try the same test on
0.5
On Dec 5, 11:36 am, Michael Bayer [EMAIL PROTECTED] wrote:
actually, use passive_deletes=True, not 'all'. It will issue DELETEs
only for collections that are already loaded, this doesn't break
anything and prevents unnecessary SELECTs of unloaded collections.
The True setting is needed so that the session can update the state of
those collections during the flush process.
On Dec 5, 2008, at 12:29 PM, Michael Bayer wrote:
use the passive_updates=True, passive_deletes='all' flags. These
are described
athttp://www.sqlalchemy.org/docs/05/sqlalchemy_orm.html#docstrings_sqla...
.
On Dec 5, 2008, at 11:42 AM, James Brady wrote:
Hi all,
I'm trying to get deletes and updates cascaded down from a parent
object to the child objects (connected by ForeignKey).
It all seems pretty simple in the docs, but I can't get it to work!
I'm using MySQL with the InnoDB engine, and have played with all
the variation of the onupdate, ondelete and cascade arguments I can
think of.
The problem is that immediately before the DELETE command is sent
to MySQL, there are UPDATE commands nulling out the foreign key
references of the child objects, so MySQL doesn't trigger it's ON
DELETE CASCADE action, and for some reason cascade=all, delete-
orphan doesn't clean up the children with NULL FKs.
Here's a simplified model:
users_table = Table('tg_user', metadata,
Column('user_id', Integer, primary_key=True),
Column('created', DateTime, default=datetime.now),
mysql_engine='InnoDB',
)
hat_table = Table('hat', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(100)),
Column('user_id', Integer, ForeignKey('tg_user.user_id'),
onupdate='CASCADE', ondelete='CASCADE'),
mysql_engine='InnoDB',
)
class User(object):
pass
class Hat(object):
pass
mapper(User, users_table)
mapper(Hat, hat_table,
properties = {
'user': relation(User, backref=hats, cascade=all,
delete, delete-orphan),
}
)
And the log from SA when I do a User.delete:
BEGIN
UPDATE hat SET user_id=%s WHERE hat.id = %s
[None, 1L]
DELETE FROM tg_user WHERE tg_user.user_id = %s
[1L]
COMMIT
Any help would be much appreciated!
James
--~--~-~--~~~---~--~~
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: Cascading deletes to children
Ah! I see - I had the cascade and passive_delete arguments in the
wrong place.
This works as expected in 0.4.3 and 0.5 now.
Thanks for the help
James
On Dec 5, 12:47 pm, Michael Bayer [EMAIL PROTECTED] wrote:
Assuming user_id is a surrogate primary key, I dont see any need for
onupdate=CASCADE to be used here. Additionally, ondelete=CASCADE
on your hat.user_id column implies that hat will be deleted when a
user entry is deleted - however your relation has this set up on the
many-to-one side indicating that a user would be deleted when a
hat is deleted.
I think this is the full setup you're looking for:
users_table = Table('tg_user', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', Unicode(16), unique=True),
Column('created', DateTime, default=datetime.now),
mysql_engine='InnoDB',
)
hat_table = Table('hat', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(100)),
Column('user_id', Integer, ForeignKey('tg_user.user_id',
ondelete='CASCADE')),
mysql_engine='InnoDB',
)
class User(object):
pass
class Hat(object):
pass
mapper(User, users_table)
mapper(Hat, hat_table,
properties = {
'user': relation(User,
backref=backref(hats, cascade=all, delete, delete-orphan,
passive_deletes=True)
),
}
)
On Dec 5, 2008, at 1:42 PM, James wrote:
Thanks for your quick response! Unfortunately I made the changes you
suggest and I still get the UPDATE commands being sent to the children
before the DELETE on the parent. My model is now:
users_table = Table('tg_user', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', Unicode(16), unique=True),
Column('created', DateTime, default=datetime.now),
mysql_engine='InnoDB',
)
hat_table = Table('hat', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(100)),
Column('user_id', Integer, ForeignKey('tg_user.user_id',
onupdate='CASCADE', ondelete='CASCADE')),
mysql_engine='InnoDB',
)
class User(object):
pass
class Hat(object):
pass
mapper(User, users_table)
mapper(Hat, hat_table,
properties = {
'user': relation(User, backref=hats, cascade=all, delete,
delete-orphan, passive_updates=True, passive_deletes=True),
}
)
And I get the same pattern in the log:
BEGIN
UPDATE hat SET user_id=%s WHERE hat.id = %s
[None, 1L]
DELETE FROM tg_user WHERE tg_user.user_id = %s
[1L]
COMMIT
The ondelete and cascade arguments I have should be working in this
situation, right?
Thanks again,
James
On Dec 5, 11:36 am, Michael Bayer [EMAIL PROTECTED] wrote:
actually, use passive_deletes=True, not 'all'. It will issue
DELETEs
only for collections that are already loaded, this doesn't break
anything and prevents unnecessary SELECTs of unloaded collections.
The True setting is needed so that the session can update the state
of
those collections during the flush process.
On Dec 5, 2008, at 12:29 PM, Michael Bayer wrote:
use the passive_updates=True, passive_deletes='all' flags. These
are described
athttp://www.sqlalchemy.org/docs/05/sqlalchemy_orm.html#docstrings_sqla
...
.
On Dec 5, 2008, at 11:42 AM, James Brady wrote:
Hi all,
I'm trying to get deletes and updates cascaded down from a parent
object to the child objects (connected by ForeignKey).
It all seems pretty simple in the docs, but I can't get it to work!
I'm using MySQL with the InnoDB engine, and have played with all
the variation of the onupdate, ondelete and cascade arguments I can
think of.
The problem is that immediately before the DELETE command is sent
to MySQL, there are UPDATE commands nulling out the foreign key
references of the child objects, so MySQL doesn't trigger it's ON
DELETE CASCADE action, and for some reason cascade=all, delete-
orphan doesn't clean up the children with NULL FKs.
Here's a simplified model:
users_table = Table('tg_user', metadata,
Column('user_id', Integer, primary_key=True),
Column('created', DateTime, default=datetime.now),
mysql_engine='InnoDB',
)
hat_table = Table('hat', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(100)),
Column('user_id', Integer, ForeignKey('tg_user.user_id'),
onupdate='CASCADE', ondelete='CASCADE'),
mysql_engine='InnoDB',
)
class User(object):
pass
class Hat(object):
pass
mapper(User, users_table)
mapper(Hat, hat_table,
properties = {
'user': relation(User, backref=hats, cascade=all,
delete, delete-orphan),
}
)
And the log from SA when I do a User.delete:
BEGIN
UPDATE hat SET user_id=%s WHERE hat.id = %s
[None, 1L]
DELETE FROM tg_user WHERE tg_user.user_id = %s
[1L]
COMMIT
Any help would be much
