Changing Type of an ORM record

[Richard Damon]

I asked this a bit ago, but never got an answer, so trying again wording
a bit different to see if I can get help.

Going to use the example from the documentation, as hopefully that will
give me the hints needed to handle my more complicate case.

Using SQLAlchemy 1.4 ORM and Joined Table Inheritance

# Define Base Class for Inheritance

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    type = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'employee',
        'polymorphic_on':type
    }

# Define some Sub-classes

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'engineer',
    }

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_name = Column(String(30))

    __mapper_args__ = {
        'polymorphic_identity':'manager',
    }

The question is, given an existing record for an 'Employee', how to I
change it from an Employee to say an Engineer. I don't want to make a
'new' record with a new ID number, as the id number is referenced in
other tables.

Not using ORM, it would be a simple matter of writing the data into the
Engineer table with an INSERT, forcing the ID to match the ID of the
employee, and then change the value of the type field in the Employee
table with an UPDATE. The question is, is there a more "ORM' way to do this?



--
Richard Damon

[Mike Bayer]

On Sun, May 2, 2021, at 4:44 PM, Richard Damon wrote:

I asked this a bit ago, but never got an answer, so trying again wording

a bit different to see if I can get help.

sorry if this got missed.

The question is, given an existing record for an 'Employee', how to I

change it from an Employee to say an Engineer. I don't want to make a

'new' record with a new ID number, as the id number is referenced in

other tables.

Not using ORM, it would be a simple matter of writing the data into the

Engineer table with an INSERT, forcing the ID to match the ID of the

employee, and then change the value of the type field in the Employee

table with an UPDATE. The question is, is there a more "ORM' way to do this?

this request comes up from time to time however the Core method you refer towards is the best way to do this.    This kind of operation is unusual enough that it's simpler for users to write the Core routine they want for their needs rather than adding a complex generalized feature to the ORM that would not be used often and would be difficult to develop and support.

-- 

Richard Damon

-- 

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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c7b852a4-8d94-6341-c1ad-376051244059%40Damon-Family.org.

[Richard Damon]

On 5/2/21 6:34 PM, Mike Bayer wrote:
>
>
> On Sun, May 2, 2021, at 4:44 PM, Richard Damon wrote:
>> I asked this a bit ago, but never got an answer, so trying again wording
>> a bit different to see if I can get help.
>
> sorry if this got missed.
>
>>
>> The question is, given an existing record for an 'Employee', how to I
>> change it from an Employee to say an Engineer. I don't want to make a
>> 'new' record with a new ID number, as the id number is referenced in
>> other tables.
>>
>>
>> Not using ORM, it would be a simple matter of writing the data into the
>> Engineer table with an INSERT, forcing the ID to match the ID of the
>> employee, and then change the value of the type field in the Employee
>> table with an UPDATE. The question is, is there a more "ORM' way to
>> do this?
>
> this request comes up from time to time however the Core method you
> refer towards is the best way to do this.    This kind of operation is
> unusual enough that it's simpler for users to write the Core routine
> they want for their needs rather than adding a complex generalized
> feature to the ORM that would not be used often and would be difficult
> to develop and support.

Ok, understand. Maybe my application is a bit unusual, but it seems I
will be doing this a LOT. In my case nothing will REALLY be the base
class, but many of the sub-classes will have relations referencing the
id number of in the base class, and when importing a change set from an
external source, those id numbers might not match, so the base class
includes a UUID to match things up, and to avoid foreign key errors in
some of the derived objects, it seems I want to first make a pass to
create all the new nodes as just the base class so they don't create
broken relationships, and then upgrade them to their final type so I can
fill in the relationships. Since many of the relationships will by
cycles, I can't start at the base and build up.

I presume that after changing the base record I should tell SQLAlchemy
to flush the old record out of its cache so it will re-read it with its
new identity.

--
Richard Damon

[Mike Bayer]

On Mon, May 3, 2021, at 12:31 PM, Richard Damon wrote:

Ok, understand. Maybe my application is a bit unusual, but it seems I

will be doing this a LOT.

that's fine, you'll make a function to do what you need and use it everywhere

In my case nothing will REALLY be the base

class, but many of the sub-classes will have relations referencing the

id number of in the base class, and when importing a change set from an

external source, those id numbers might not match, so the base class

includes a UUID to match things up, and to avoid foreign key errors in

some of the derived objects, it seems I want to first make a pass to

create all the new nodes as just the base class so they don't create

broken relationships, and then upgrade them to their final type so I can

fill in the relationships. Since many of the relationships will by

cycles, I can't start at the base and build up.

you could maybe use Core to create the rows for the new records without the relationships then use normal ORM techniques for the second pass.      you can manufacture "persistent" objects that you didnt actually load from the database by building the Python object that looks like the database row(s), converting it to be "detached" using transient_to_detached (https://docs.sqlalchemy.org/en/14/orm/session_api.html?highlight=transient_to_detached#sqlalchemy.orm.make_transient_to_detached)  , then you can session.add() that "detached" object into the Session and the Session will see it as an object that it loaded from the database.

I presume that after changing the base record I should tell SQLAlchemy

to flush the old record out of its cache so it will re-read it with its

new identity.

changing the class of a record doesn't change its "identity" from a PK perspective, but if you mean its pk + class, then yes.    Or the above transient-to-detached step may save you some round trips.

-- 

Richard Damon

-- 

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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d7b66178-52bd-60cc-b8bd-655d46e535af%40Damon-Family.org.