changing polymorphic identity on the fly

[Pavel Andreev]

Hi,

I've got a newbie question regarding mapper inheritance. In the
tutorial scheme (Employee/Engineer/Manager), how do I promote an
engineer to a manager? The ideal code, as I see it, would be:

unlucky_engineer = session.query(Engineer).first()
unlucky_engineer.__class__ = Manager
# unlucky_engineer.type = 'manager'
unlucky_engineer.manager_data = 'used to be an engineer'
session.commit()

Yet it does nothing. I would like to automate the following tasks:

- Update employees table, setting type for the employee to 'manager'
- Delete the associated row from the engineers table
- Insert a new row to the managers table

Is there some kind of switch_identity(instance,newclass,**newattrs)
somewhere around?

Thanks for any guidance,
Pavel

[Michael Bayer]

You can see that the various combinations of INSERT/DELETE depending on
what the class change is can get complicated (such as, horizontal move
between sibling classes is a DELETE and an INSERT, a move to a subclass is
an INSERT, a move to a nephew a DELETE then INSERT + INSERT, etc.), so
this is not a feature implemented within SQLAlchemy at this time. At the
moment I'd be comfortable putting up a recipe on the wiki which issues
those table calls (recipes are how I can put up fragments of SQLAlchemy
features that do not necessarily work in all cases).

Just a sketch it would be like:

def change_class(instance, newclass):
to_add = set()
to_delete = set()

if issubclass(newclass, instance.__class__):
for cls in newclass.__mro__:
if cls is instance.__class__:
break
to_add.add(class_mapper(cls).local_table)
else:
for cls in instance.__class__.__mro__:
if issubclass(newclass, cls):
target = cls
break
to_delete.add(class_mapper(cls).local_table)
else:
raise Exception("class %r and %r do not share a common base" %
(instance.__class__, newclass)
for cls in newclass.__mro__:
if cls is target:
break
to_add.add(class_mapper(cls).local_table)

from sqlalchemy.sql.util import sort_tables
for table in reversed(sort_tables(to_delete)):
engine.execute(table.delete().where( ... need to figure out PK
here... )

for table in sort_tables(to_add):
engine.execute(table.insert().where( ... need to figure out PK here
and attributes ... )

sorry that's not complete / working.

> Pavel
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>

[Pavel Andreev]

Hi Michael,

Thanks for the quick response.

> You can see that the various combinations of INSERT/DELETE depending on
> what the class change is can get complicated (such as, horizontal move
> between sibling classes is a DELETE and an INSERT, a move to a subclass is
> an INSERT, a move to a nephew a DELETE then INSERT + INSERT, etc.), so
> this is not a feature implemented within SQLAlchemy at this time.   At the
> moment I'd be comfortable putting up a recipe on the wiki which issues
> those table calls (recipes are how I can put up fragments of SQLAlchemy
> features that do not necessarily work in all cases).

The class change gets even more complicated if we consider relations
that are not common to old and new classes (e.g. EngineerProjects).
These would have to be cascaded the normal way as when deleting the
instance, yet the full instance delete can't be used since we want to
preserve the PK (because of the relations that are not affected by
class change, e.g. FavouriteMeals). I believe the unit-of-work would
the right place for these tasks, simulating deletion of the instance
up to he "common denominator", then simulating creation from there to
the new class. Which is, of course, a lot of work to do, so for the
time being I'll resort to manual table updates.

Another quick question: if all I need to do is change the
discriminator column, that is, if I'm sure no other tables are
affected, is there a way to do this without manual table updates?
Simply assigning the attribute doesn't seem to trigger table update.

Pavel

[Michael Bayer]

Pavel Andreev wrote:
> Another quick question: if all I need to do is change the
> discriminator column, that is, if I'm sure no other tables are
> affected, is there a way to do this without manual table updates?
> Simply assigning the attribute doesn't seem to trigger table update.

the discriminator is hardwired to the class. so as long as thats what it
sees its going to use that discriminator value. you'd need to change this
over in the manual thing you're doing.

[jens.t...@gmail.com]

Hi,

I came upon this thread because I've got almost the exact same question. Several years on, though, how would I go about promoting polymorphic objects across siblings today?

Thank you!

Jens

[Mike Bayer]

On Wed, Aug 9, 2017 at 6:53 PM, <jens.t...@gmail.com> wrote:
> Hi,
>
> I came upon this thread because I've got almost the exact same question.
> Several years on, though, how would I go about promoting polymorphic objects
> across siblings today?

UPDATE the discriminator name, perform INSERT / DELETE of joined
inheritance tables as needed....

>
> Thank you!
> Jens
>
>
> On Tuesday, February 9, 2010 at 4:09:23 AM UTC+10, Michael Bayer wrote:
>>
>> Pavel Andreev wrote:
>> > Another quick question: if all I need to do is change the
>> > discriminator column, that is, if I'm sure no other tables are
>> > affected, is there a way to do this without manual table updates?
>> > Simply assigning the attribute doesn't seem to trigger table update.
>>
>> the discriminator is hardwired to the class. so as long as thats what it
>> sees its going to use that discriminator value. you'd need to change this
>> over in the manual thing you're doing.
>>
>> >
>> > Pavel
>> >
>> > --
>> > You received this message because you are subscribed to the Google
>> > Groups
>> > "sqlalchemy" group.
>> > To post to this group, send email to sqlal...@googlegroups.com.
>> > To unsubscribe from this group, send email to
>> > sqlalchemy+...@googlegroups.com.
>> > For more options, visit this group at
>> > http://groups.google.com/group/sqlalchemy?hl=en.
>> >
>> >
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---

> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.

> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.

> To post to this group, send email to sqlal...@googlegroups.com.

> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[jens.t...@gmail.com]

Thanks, Mike!

My implementation uses the single table inheritance model and I would like to (i.e. need to) keep the id the same.

Talking about the example in the documentation, do I understand you correctly that changing from Manager to Engineer is as simple as updating the type field?

Cheers,

Jens

[Mike Bayer]

On Wed, Aug 9, 2017 at 8:04 PM, <jens.t...@gmail.com> wrote:
> Thanks, Mike!
>
> My implementation uses the single table inheritance model and I would like
> to (i.e. need to) keep the id the same.
>
> Talking about the example in the documentation, do I understand you
> correctly that changing from Manager to Engineer is as simple as updating
> the type field?

yes. also locally you want to clear out those objects from your
Session and reload (cleanest would be to expunge() totally).

>
> Cheers,
> Jens
>
>
> On Thursday, August 10, 2017 at 9:18:50 AM UTC+10, Mike Bayer wrote:
>>
>> On Wed, Aug 9, 2017 at 6:53 PM, <jens.t...@gmail.com> wrote:
>> > Hi,
>> >
>> > I came upon this thread because I've got almost the exact same question.
>> > Several years on, though, how would I go about promoting polymorphic
>> > objects
>> > across siblings today?
>>
>> UPDATE the discriminator name, perform INSERT / DELETE of joined
>> inheritance tables as needed....
>>
>> > Thank you!
>> > Jens
>

[jens.t...@gmail.com]

Thank you Mike!

So suppose I have an a Manager object manager and I wanted to promote that to Engineer:

# Modify the persisted data of the manager object in the db directly.
session = object_session(manager)
session.execute(manager.__table__.update() \
                                 .where(manager.__table__.c.id == manager.id) \
                                 .values({'type': 'engineer'}))
# session.flush() ?

# Expunge the object.
session.expunge(manager)

# Subsequent reads would then return Engineer objects…

I suspect that modifying the object directly would not work with expunge(). However, if I wouldn't expunge() and all I do is modify the object then perhaps that's ok?

# Modify the object's type directly, then manager's class would not be accurate anymore.
manager.type = "engineer"

Also, if for some reason the session were to rollback() then the above execute() would not be persisted and manager remain a manager. Correct?

Thanks!

Jens

[Mike Bayer]

On Thu, Jan 4, 2018 at 11:08 AM, <jens.t...@gmail.com> wrote:
> Thank you Mike!
>
> So suppose I have an a Manager object manager and I wanted to promote that
> to Engineer:
>
> # Modify the persisted data of the manager object in the db directly.
> session = object_session(manager)
> session.execute(manager.__table__.update() \
> .where(manager.__table__.c.id ==
> manager.id) \
> .values({'type': 'engineer'}))
> # session.flush() ?
>
> # Expunge the object.
> session.expunge(manager)
>
> # Subsequent reads would then return Engineer objects…
>
> I suspect that modifying the object directly would not work with expunge().
> However, if I wouldn't expunge() and all I do is modify the object then
> perhaps that's ok?

you'd have to reassign the class, e.g. manager.__class__ = Engineer.
You can try all that but I can't guarantee everything will always
work, not really sure.

>
> # Modify the object's type directly, then manager's class would not be
> accurate anymore.
> manager.type = "engineer"
>
> Also, if for some reason the session were to rollback() then the above
> execute() would not be persisted and manager remain a manager. Correct?

in the database, sure. if you modified the class of your object, that
would still be there. it would get pretty broken quick.

>
> Thanks!
> Jens
>
>
> On Thursday, August 10, 2017 at 5:25:37 PM UTC+2, Mike Bayer wrote:
>>
>> > Talking about the example in the documentation, do I understand you
>> > correctly that changing from Manager to Engineer is as simple as
>> > updating
>> > the type field?
>>
>> yes. also locally you want to clear out those objects from your
>> Session and reload (cleanest would be to expunge() totally).
>>