Re: [sqlalchemy] Changing Type of an ORM record

[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+unsubscr...@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.
> 

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/2ebfc640-b8ed-4fd4-9dc8-428c36aac5ab%40www.fastmail.com.

Re: [sqlalchemy] Changing Type of an ORM record

[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

-- 
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+unsubscr...@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.

Re: [sqlalchemy] Changing Type of an ORM record

[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+unsubscr...@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.
> 

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/fbb453bc-31ce-4d24-86ea-770a1df56b56%40www.fastmail.com.

[sqlalchemy] 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

-- 
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+unsubscr...@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.