On Nov 10, 2010, at 8:00 AM, Mene wrote:
> Yes, I definitely want to emit a DELETE statement. The Problem is that
> calling master.delete() only gives an delete statement for the master
> table. However, I need n querys: DELETE child1... , DELETE child2 and
> so on.
OK, so I think there's another question that you mean to be asking here, since
I'm sure its obvious that to emit a DELETE statement for child1, child2, etc.,
you do the same thing for those Table objects:
child1.delete().where(...)
child2.delete().where(...)
child3.delete().where(...)
... etc
I think the question you want here is "how do I get at all the Table objects in
an inheritance hierarchy given only the base class?"
for mapper in master_mapper.polymorphic_iterator():
Session.execute(mapper.local_table.delete().where(...))
Note that concrete inheritance is the least wieldy inheritance scheme. If you
were using joined table inheritance, you could configure ON DELETE CASCADE on
all your tables and you could then emit a single DELETE just for the master
table that would automatically delete from all related tables.
>
> On 8 Nov., 20:31, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> On Nov 8, 2010, at 11:02 AM, Mene wrote:
>>
>>
>>
>>
>>
>>> Hi all,
>>> I have some 'child' tables which inherit from the same 'master' table,
>>> now I'd like to delete some entries from all tables. The 'where'-part
>>> comes solely from the master table.
>>> I have decided to use Concrete Table Inheritance since I don't need
>>> the inheritance at points other than deleting, but I expect the tables
>>> to have a lot of entries and at the moment I don't know how many child
>>> tables I will have in the future. Also the delete process won't occur
>>> often.
>>
>>> Each table has a compound primary key of user and room.
>>> I use version 0.4.8 (and I can't change this)
>>
>>> I need a delete statement that deletes according to user_id and the
>>> length of the room identifier.
>>> Also master is only an abstract class, so I don't need to have a table
>>> in my database (AFAIK).
>>
>> There's only two choices here, you can either load the objects with the ORM
>> and individually pass them to session.delete(), or you can emit DELETE
>> statements against the tables directly using SQL expressions or strings.
>> The SQL expression would be along the lines of
>> Session.execute(master.delete().where(...)).
>>
>>
>>
>>> master = Table('master', metadata,
>>> Column('user_id', Integer, ForeignKey('user.user_id',
>>> ondelete='CASCADE'), primary_key=True),
>>> Column('room', Unicode(16), ForeignKey('room.id'),
>>> primary_key=True)
>>> )
>>
>>> child1 = Table('child1', metadata,
>>> Column('user_id', Integer, ForeignKey('user.user_id',
>>> ondelete='CASCADE'), primary_key=True),
>>> Column('room', Unicode(16), ForeignKey('room.id'),
>>> primary_key=True),
>>> Column('child1_data', Unicode(16))
>>> )
>>
>>> child2 = Table('child2', metadata,
>>> Column('user_id', Integer, ForeignKey('user.user_id',
>>> ondelete='CASCADE'), primary_key=True),
>>> Column('room', Unicode(16), ForeignKey('room.id'),
>>> primary_key=True),
>>> Column('child2_data', Unicode(16))
>>> )
>>
>>> join = polymorphic_union({
>>> 'master': master,
>>> 'child1': child1,
>>> 'child2': child2
>>> }, 'type', 'pjoin')
>>
>>> master_mapper = mapper(Master, master, with_polymorphic=('*',
>>> vote_join), \
>>> polymorphic_on=vote_join.c.type, polymorphic_identity='vote_data')
>>
>>> child1_mapper = mapper(Child1, child1, inherits=master, \
>>> concrete=True, polymorphic_identity='child1')
>>
>>> child2_mapper = mapper(Child2, child2, inherits=master, \
>>> concrete=True, polymorphic_identity='child2')
>>
>>> Thanks for your time,
>>> Mene
>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To post to this group, send email to sqlalch...@googlegroups.com.
>>> To unsubscribe from this group, send email to
>>> sqlalchemy+unsubscr...@googlegroups.com.
>>> For more options, visit this group
>>> athttp://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 sqlalch...@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 sqlalch...@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.
