SQLAlchemy class inheritance on an existing instance

Question

I'm using SQLAlchemy's joined table inheritance pattern, but using the exact pattern in their docs, I can't work out how to apply the subclass to an existing instance of the parent class.

In their example:

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
    }

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',
    }

...imagine I already have an Employee, who is neither an Engineer nor a Manager, but then they get promoted. Doing something like this...

employee = Employee.query.get(id)
m = Manager()
m.employee = employee
m = session.merge(employee)

...results in this error...

FlushError: New instance <Manager at 0x7f6bf5a35490> with identity key (<class 'models.Employee'>, conflicts with persistent instance <Employee at 0x7f6bf590d210>

So, how would I promote someone?

Alex Martelli · Accepted Answer · 2015-03-21 15:56:32Z

4

Using separate tables (mapped in SQLAlchemy to different classes) to entirely represent the same entity in different roles is somewhere between very dubious and an outright anti-pattern -- exactly because of the issue you're run into: an entity's role can change (here, by a promotion), but changing the SQL table / SQLAlchemy class representing that same entity runs exactly into the kind of problems you just met.

I would recommend changing your data model -- e.g add a field to all employees determining whether they're managers or not, and lose the Manager table altogether. If you're adamant you want to keep this data model, a promotion becomes quite a bit of work...:

copy all the data of the employee identity being promoted in memory
delete said entity from the database
only now, create the manager entity, copying into it the data you saved sub(1), and save it into the database

I can't imagine it being worth these complications to keep the schema now in use...

answered Mar 21, 2015 at 15:56

Alex Martelli

888k175 gold badges1.3k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dr Congo Over a year ago

Thanks. I had a horrible feeling that this would be the answer. In my particular case, the subclasses have a lot of differing data from the parent, as well as the shared stuff that the parent has. This felt like a clean way of setting this up. Gonna have to switch to sets of profile models.

Savir · Accepted Answer · 2015-03-21 22:34:08Z

DISCLAIMER: You should keep @AlexMartelli's answer as chosen. Because he's right and just because... well... He's THE Alex Martelli, with his own Wikipedia page and all ('nuf said) but I was working on my answer before I saw his, and I'm going to post it, anyway. If anything, just as a little example that might clarify how SqlAlchemy deals with Joined Table inheritance.

What SqlAlchemy does when you query a (Manager) is going to the manager table, grab all the attributes, also JOINing the "parent" table (employee) through the id's ForeignKey and bring the rest into your instance, so you can "trick" SqlAlchemy by altering that relationship "manually" (the echo=True argument to the engine is very useful to see what SqlAlchemy is doing)

IMHO, what I'm copying below is extremely discouraged, and I would never pull something like this into real production, but... I really like SqlAlchemy, and I like to share the very little I know of its abilities with whomever wants to listen (and for the people who don't want to listen, I chase them down and shout my opinions at them :-D )

There it goes (some comments inline):

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.schema import CreateSchema
from sqlalchemy import event

Base = declarative_base()

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
    }

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',
    }

if __name__ == '__main__':
    engine = create_engine("mysql://root:password@localhost/tests?charset=utf8",
                           echo=True)
    Base.metadata.create_all(engine)
    Session = sessionmaker()
    Session.configure(bind=engine)
    session = Session()
    employee = Employee(name="Pepe")
    manager = Manager(name="Foobar", manager_name="Mr. Baz")
    session.add_all([employee, manager])
    session.commit()
    """ 
    table tests.employee looks like:
    |  id |   name   |    type    |
    |  1  |   Pepe   |  employee  |
    |  2  |   Foobar |   manager  |

    table tests.manager looks like:
    |  id |   manager_name   |
    |  2  |     Mr. Baz      |

    """
    to_promote = session.query(Employee).filter_by(name="Pepe").first()
    managers = [manager.name for manager in session.query(Manager).all()]
    session.close()

    print ("As of now (point 1), got %s managers: %s"
           % (len(managers), managers))
    print "Employee to_promote: %s" % to_promote.name

    connection = engine.connect()
    connection.execute(
        "START TRANSACTION;"
        "UPDATE employee SET employee.type='manager' WHERE employee.id={0};"
        "INSERT INTO manager (id, manager_name) VALUES ({0}, 'New Pepe');"
        "COMMIT;".format(to_promote.id)
    )
    connection.close()

    """ 
    table tests.employee looks like:
    |  id |   name   |    type    |
    |  1  |   Pepe   |   manager  |
    |  2  |   Foobar |   manager  |

    table tests.manager looks like:
    |  id |   manager_name   |
    |  1  |    New Pepe      |
    |  2  |     Mr. Baz      |

    """
    session = Session()
    managers = [manager.name for manager in session.query(Manager).all()]
    print ("As of now (point 2), got %s managers: %s"
           % (len(managers), managers))

This outputs:

2015-03-21 13:10:41,787 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-03-21 13:10:41,788 INFO sqlalchemy.engine.base.Engine INSERT INTO employee (name, type) VALUES (%s, %s)
2015-03-21 13:10:41,788 INFO sqlalchemy.engine.base.Engine ('Pepe', 'employee')
2015-03-21 13:10:41,789 INFO sqlalchemy.engine.base.Engine INSERT INTO employee (name, type) VALUES (%s, %s)
2015-03-21 13:10:41,789 INFO sqlalchemy.engine.base.Engine ('Foobar', 'manager')
2015-03-21 13:10:41,790 INFO sqlalchemy.engine.base.Engine INSERT INTO manager (id, manager_name) VALUES (%s, %s)
2015-03-21 13:10:41,790 INFO sqlalchemy.engine.base.Engine (2L, 'Mr. Baz')
2015-03-21 13:10:41,791 INFO sqlalchemy.engine.base.Engine COMMIT
2015-03-21 13:10:41,827 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-03-21 13:10:41,828 INFO sqlalchemy.engine.base.Engine SELECT employee.id AS employee_id, employee.name AS employee_name, employee.type AS employee_type 
FROM employee 
WHERE employee.name = %s 
 LIMIT %s
2015-03-21 13:10:41,828 INFO sqlalchemy.engine.base.Engine ('Pepe', 1)
2015-03-21 13:10:41,829 INFO sqlalchemy.engine.base.Engine SELECT manager.id AS manager_id, employee.id AS employee_id, employee.name AS employee_name, employee.type AS employee_type, manager.manager_name AS manager_manager_name 
FROM employee INNER JOIN manager ON employee.id = manager.id
2015-03-21 13:10:41,830 INFO sqlalchemy.engine.base.Engine ()
2015-03-21 13:10:41,830 INFO sqlalchemy.engine.base.Engine ROLLBACK
As of now (point 1), got 1 managers: [u'Foobar']
Employee to_promote: Pepe
2015-03-21 13:10:41,831 INFO sqlalchemy.engine.base.Engine START TRANSACTION;UPDATE employee SET employee.type='manager' WHERE employee.id=1;INSERT INTO manager (id, manager_name) VALUES (1, 'New Pepe');COMMIT;
2015-03-21 13:10:41,831 INFO sqlalchemy.engine.base.Engine ()
2015-03-21 13:10:41,868 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2015-03-21 13:10:41,869 INFO sqlalchemy.engine.base.Engine SELECT manager.id AS manager_id, employee.id AS employee_id, employee.name AS employee_name, employee.type AS employee_type, manager.manager_name AS manager_manager_name 
FROM employee INNER JOIN manager ON employee.id = manager.id
2015-03-21 13:10:41,869 INFO sqlalchemy.engine.base.Engine ()
As of now (point 2), got 2 managers: [u'Pepe', u'Foobar']

Collectives™ on Stack Overflow

SQLAlchemy class inheritance on an existing instance

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related