SqlAlchemy: Convert inherited type from one to another

Question

Let's say I have two different types both on the same database table (single table inheritance):

class Employee(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String, nullable = False)
    discriminator = db.Column('type', String)
    __mapper_args__ = {'polymorphic_on': discriminator}

class Manager(Employee):
    __mapper_args__ = {'polymorphic_identity': 'manager'}
    division = db.Column(db.String, nullable = False)
    role = db.Column(db.String, nullable = False)

class Worker(Employee):
    __mapper_args__ = {'polymorphic_identity': 'worker'}
    title = db.Column(db.String, nullable = False)

(Yes, I'm using Flask-SqlAlchemy and not plain vanilla) Now how might I go about converting one declarative model to another. That is, what if a "Worker" was promoted to "Manager?" How do I do that? Do I have to write raw SQL to do that?

Sorry if this has been asked before but I couldn't find it from the Googles. Please note, this is a contrived example.

Answer 1

It's kludgy, and it causes a warning, but you can brute-force modify the discriminator column by setting the property:

john_smith = session.query(Employee).filter_by(name='john smith').one()
john_smith.discriminator = 'manager'
session.commit()

This will cause a warning like,

SAWarning: Flushing object <Worker at 0xdeadbeef> with incompatible polymorphic
identity 'manager'; the object may not refresh and/or load correctly
    mapper._validate_polymorphic_identity(mapper, state, dict_)

You can just ignore that, as long as you fix the issues it will cause. The safest thing is to close the session (session.close()) or expunge everything from it (session.expunge_all()) immediately after the commit.

If you must, you can fix issues with John's object alone by just expunging John from the session (session.expunge(john_smith)). You have to be careful with that; any remaining references to john_smith will keep the object, although thankfully he will be detached from session and you won't be allowed to do anything with them.

---

I tried the other obvious options as well. Neither worked, but both are illustrative of what SQLAlchemy's Session object stores and how:

1. session.refresh(john_smith) fails with

   InvalidRequestError: Could not refresh instance '<Worker at 0xdeadbeef>'
   

   That's because SQLAlchemy queries the database for a Worker (not an Employee) and can't find one by the name of John Smith, because the database now knows that John got promoted due to the fancy new value in his type column.

2. session.expire(john_smith) succeeds but fails to update John as a new class, and any subsequent access to him will result in

   ObjectDeletedError: Instance '<Worker at 0xdeadbeef>' has been deleted, or
   its row is otherwise not present.
   

   SQLAlchemy still thinks John is a Worker, and it tries to query for him as a Worker. That's because he's still persisted in session.identity_map, which looks like this:

   {(saexample2.Employee, (1,)): <saexample2.Worker at 0xdeadbeef>}
   

   So there's John, listed explicitly as a Worker object. When you expunge() John from the session, this entry in the dictionary gets purged. When you expire() him, all of his mapped properties get marked as stale, but he still exists in the dictionary.

Answer 2

I'd suggest reworking your object model. A sign that the object model would benefit from a rethink is, when one object works just as well as the attribute of another. In this case, Worker.title could equally well be "Manager".

Also, Manager.division works better as its own object Division. Not least because a Division would conceivably have a one to many relationship with Worker.

Something like perhaps a Division object with a ForeignKey of manager pointing to an Employee object. The Employee object would have a title attribute; in the Employee.__init__() you can manually check if the employee is manager of any divisions and then set the Employee.title to "Manager" from __init__().