Problems with Update and Table Inheritance

Dev Setup

Python 3.5
SQLAlchemy 1.13
OSX

I am a bit new to inheritance modeling for tables so it's quite possible this is not a bug but rather lack of understanding. I've also tried scanning through past issues and I don't think I've seen this addressed though this posting from google groups may be relevant

Goal

I'm trying to update a single column in the parent class (People) while using the child class (Engineer).

Working Code

import os
import sys

from sqlalchemy import Column, create_engine, ForeignKey, Integer, String, DateTime

from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import func
from sqlalchemy.ext.declarative import declarative_base


try:
   os.remove('test.db')
except FileNotFoundError:
   pass 

engine = create_engine('sqlite:///test.db', echo=True)
Session = sessionmaker(engine)

Base = declarative_base()


class People(Base):
    __tablename__ = 'people'
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}

    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    updated = Column(DateTime, server_default=func.now(), onupdate=func.now())

class Engineer(People):
    __tablename__ = 'engineer'
    __mapper_args__ = {'polymorphic_identity': 'engineer'}
    id = Column(Integer, ForeignKey('people.id'), primary_key=True)
    kind = Column(String(100), nullable=True)

Base.metadata.create_all(engine)

session = Session()

e = Engineer()
e.name = 'Mike'
session.add(e)
session.flush()
session.commit()

# works when updating the object
e.name = "Doug"
session.add(e)
session.commit()


# works using the base class for the query
count = session.query(People).filter(
                           People.name.is_('Doug')).update({People.name: 'James'})

# fails when using the derived class
count = session.query(Engineer).filter(
                           Engineer.name.is_('James')).update({Engineer.name: 'Mary'})

session.commit()
print("Count: {}".format(count))

Problem

The last update fails:

session.query(Engineer).filter(
                           Engineer.name.is_('James')).update({Engineer.name: 'Mary'})

# Generated SQL
# UPDATE engineer SET name=?, updated=CURRENT_TIMESTAMP FROM people WHERE # people.name IS ?

As you can see the statement is trying to update rows in incorrect table, name is in the parent table.

I'm a little unclear about how inheritance tables should work but it seems like updates should work transparently with the derived object. Meaning, when I update Engineer.name querying against the Engineer object, SQLAlchemy should know to update the People table.

I believe the the google google groups possibly explains why SQLAlchemy doesn't emit two update statements if I were to update kind. One for the update to kind and another for the onupdate column updated, correct?

Dev Setup

Goal

Working Code

Problem

Comments (1)