[Python] SQLAlchemy polymorphism convert object from parent object to a child object

I have something like this:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey

Base = declarative_base()

engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)

class Room(Base):
__tablename__ = 'rooms'
id = Column(Integer, primary_key=True)
floor = Column(Integer)
usage = Column(String(50))
__mapper_args__ = {'polymorphic_on': usage,
'polymorphic_identity': 'empty'}

def __repr__(self):
return f"<Room(id={self.id}, floor={self.floor}, usage='{self.usage}')>"

class Office(Room):
__tablename__ = 'offices'
id = Column(Integer, ForeignKey('rooms.id'), primary_key=True)
computers = Column(Integer)
desks = Column(Integer)
__mapper_args__ = {'polymorphic_identity': 'office'}

def __repr__(self):
return f"<Office(id={self.id}, floor={self.floor}, computers={self.computers}, desks={self.desks}), usage={self.usage}>"

class Bath(Room):
__tablename__ = 'baths'
id = Column(Integer, ForeignKey('rooms.id'), primary_key=True)
toilets = Column(Integer)
sinks = Column(Integer)
__mapper_args__ = {'polymorphic_identity': 'bath'}

def __repr__(self):
return f"<Bath(id={self.id}, floor={self.floor}, toilets={self.toilets}, sinks={self.sinks})>"

# Create tables
Base.metadata.create_all(engine)

# Create a new session
session = Session()

# Add initial Room instances
room_1 = Room(id=1, floor=1, usage='empty')
room_2 = Room(id=2, floor=1, usage='empty')
session.add_all([room_1, room_2])
session.commit()

print("Initial Rooms:", session.query(Room).all())


and i want to convert a room into an office, in a way that when i query the room table it still shows the room with the usage attribute as office, and when i query the office table it just gives me the office

I tried this and it worked, but want to know if there is a way to achieve this without deleting the original room

# Convert room_1 to an Office
session.delete(room_1) # Delete the initial Room instance
session.commit()

# Add Office instance with the same ID as room_1
office_1 = Office(id=1, floor=1, computers=10, desks=5)
session.add(office_1)
session.commit()

# Query and check results
print("Rooms after conversion:", session.query(Room).all())
print("Offices:", session.query(Office).all())

Continue reading...