SqlAlchemy - Classic Mapping Inheritance - Instance has Null Identity

Question

I'm seeing a NULL Identity key error when trying to write out an object mapped to a table that inherits another one. I've found this example works fine when using the Declarative approach, but not when using the Classical one.

The tables are set up as follows:

objecttypes = Table('objecttypes', metadata,
                    Column('id', Integer, primary_key=True),
                    Column('name', String)
                    )
things = Table('things', metadata,
               Column('id', Integer, primary_key=True, autoincrement=True),
               Column('name', String),
               Column('object_type_id', Integer, ForeignKey('objecttypes.id')),
               Column('version', Integer),
               Column('timeCreated', DateTime)
               )
locations = Table('locations', metadata,
                  Column('id', Integer, ForeignKey('things.id'), primary_key=True))

objecttypes = Table('objecttypes', metadata,
                    Column('id', Integer, primary_key=True),
                    Column('name', String)
                    )
mapper(Thing, things, version_id_col=things.c.version,
       polymorphic_on="object_type_id",
       with_polymorphic='*',
       polymorphic_load='inline',
       properties={
           'objectType': relationship(ObjectType, cascade_backrefs=False)
       })
mapper(Location, locations, polymorphic_identity=typeDict['location'].id)

The classes are set up as:

class ObjectType(object):
    def __repr__(self):
        return "ObjectType({}, id={})".format(self.name, self.id)

class Thing(object):
    def __init__(self, **kwds):
        for key in kwds:
            setattr(self, key, kwds[key])
        return

    def __repr__(self):
        return "{}, id={}, type={}, version={}".format(self.name, self.id, self.objectType, self.version)

class Location(Thing):
    pass

I get the error when I try creating and merging a new Location:

def write(obj):
    session = scopedSessionFactory()
    obj = session.merge(obj)
    session.commit()
    return obj

# typeDict is a predefined list ObjectType records
tokyo = Location(name="tokyo", objectType=typeDict['location'])
tokyo = write(tokyo)

The error shows up as:

sqlalchemy.orm.exc.FlushError: Instance  has a NULL identity key.

Full example available as a gist.

user_7282091 · Answer

I believe the problem to be the following and might be found in this subtle lines in the SQLA docs:

https://docs.sqlalchemy.org/en/13/orm/mapping_api.html#sqlalchemy.orm.mapper.params.inherits

I report it here and bold the key pieces that might point to at least a partial solution to your problem:

A mapped class or the corresponding Mapper of one indicating a superclass to which this Mapper should inherit from. The mapped class here must be a subclass of the other mapper’s class. When using Declarative, this argument is passed automatically as a result of the natural class hierarchy of the declared classes.

So, when using declarative, you do not bump into this problem as this is passed automatically but it does not for classical approach so it needs to be specified.

As a result of this, your mapper line for Location I believe should be the following:

mapper(Location, locations, inherits=Thing, polymorphic_identity=typeDict['location'].id)

where you notice the addition of inherits=Thing.

Indeed, with your code I reproduce your same error but doing this modification I do not get that error anymore. I haven't checked to all the rest of the code if ok the way it is.

UPDATE: The following SO posts show similar examples of inheritance without any explicit reference to the SQLA doc.

SQLAlchemy, polymorphic inheritance with classical mapping

Need classic mapper example for SqlAlchemy single table inheritance

SqlAlchemy - Classic Mapping Inheritance - Instance has Null Identity

1 Answers