Structured Inheritance model for ORM

[Marcel Zoll]

Hi, I am struggling since some days to extend and implement common class inheritance for the SQLAlchemy ORM objects that I define: Here is the to be achieved task:

1. In a first step, I want to use sqlalchemy to model my database and the ORM, aka define tables, define the ORMs and their relations and some extras (association mapper) in Order to make my live easier

2. In a second step I want to take the ORMs and extend the classes with some higher level functionality, modeling common operations which will be executed in the database.

3. In a third step, I'd like to take the extended ORMs from step 2 and extend them again with further convenience functions, which are, however, separate from the db-model logic.

To logically isolate and structure the whole thing, I want to wrap each of these steps into a dedicated module/package. This I would have solved by class Inheritance, which seams to be the natural approach.

However, for all methods which I tried, I cannot figure out how this done correctly even in mock scripts. I will post below some example code which should demonstrate what I want to achieve.

I tried multiple approaches: 

- with classical mapping (wont work, because i need to explicitly map every derived class again, copying all mapper attributes, I would have to double paste a lot of code, which is exactly not the point of inheritance)

- with declarative (there is some deep magic to these @declarative_attributes, which I do not understand when to use and when not, respective at which point they are resolved)

- with hybrid approach, my currently preferred one, as I have at least the table definitions done, before messing with the ORM

I could for all of them not find a single solution which would fulfill all my needs or which do not throw errors:

Errors include:

- The derived class-names cannot be resolved for the relation to work correctly

- The attributes (relation, association_proxy) do not propagate up to the derived classes (I have the feeling they bind to the first concrete mapped class they are in and inheritance onward is impossible)

If anybody could give me some pointers or make the below code example work by some magic alchemistic conjurement I would be very grateful.

Marcel

==================Example Code ===================

import datetime as dt

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import MetaData, Table, Column, Sequence, ForeignKey

from sqlalchemy import Integer, String, DateTime

from sqlalchemy.orm import relationship

from sqlalchemy.ext.associationproxy import association_proxy

from sqlalchemy.orm import Session

from sqlalchemy import create_engine

from sqlalchemy.orm.session import object_session

metadata = MetaData(schema='sandbox')

source_table = Table('source', metadata,

    Column('id', Integer, Sequence("src_id_seq", metadata=metadata), primary_key=True),

    Column('name', String(length=32), unique=True, nullable=False, index=True),

    Column('created', DateTime(timezone=False), default=dt.datetime.utcnow, nullable=False),

    Column('loc_id', ForeignKey(user_table.c.id), nullable=True))

location_table = Table('auth_user_detail', metadata,

    Column('id', Integer,  Sequence("loc_id_seq", metadata=metadata), primary_key=True),

    Column('name', String(length=32), unique=True, nullable=False, index=True),

    Column('created', DateTime(timezone=False), default=dt.datetime.utcnow, nullable=False))

)

# base definition of the Source-class for Mapper

Base = declarative_base(metadata)

class Source_orm(Base):

    __table__ = source_table

    _loc = relationship('Location', uselist=False)

    loc_name = association_proxy('_loc', 'firstname')

    def __init__(self, name):

        self.name = name

# base definition of the Location-class for Mapping

class Location_orm(Base):

    __table__ = location_table

    def __init__(self, name):

        self.name = name

#-------------------

# Higher functions - lvlA : possibly packed into a different module

class Source_lvlA(Source_orm):

    @classmethod

    def get_by_name(cls, session, name):

        return session.query(cls).filter(cls.name == name).one()

    def move_to_loc_by_name(self, loc_name):

        session = object_session(self)

        loc = session.query(Location).filter(Location.name == loc_name).one()

        self._loc = loc

        session.commit()

class Location_lvlA(Location_orm):

    @classmethod

    def get_by_name(cls, session, name):

        return session.query(cls).filter(cls.name == name).one()

    def move_src_here(self, src):

        session = object_session(self)

        src.loc_id = self.id

        session.merge(src)

        session.commit()

#-------------------

# Even Higher functions - lvlB : possibly packed into a different module

class Source_lvlB(Source_lvlA):

    def assemble_info(self):

        return f"<Source> {self.name} at <Location> {self.loc_name}"

class Location_lvlB(Location_lvlA):

    def assemble_info(self):

        return f"<Location> {self.name}"

if __name__ == '__main__':

    engine = create_engine("sqlite://", echo=True)

    engine.execute("""DROP SCHEMA IF EXISTS {schema} CASCADE; """.format(schema=metadata.schema))

    engine.execute("""CREATE SCHEMA {schema};""".format(schema=metadata.schema))

    Base.metadata.create_all(engine)

    session = Session(engine)

    # create low level objects

    s = Source_ormA('MySource')

    s = session.add(a)

    session.flush()

    l = Location_lvlA('MyLocation')

    l = session.add(l)

    session.flush()

    # operate on the db use a higher level function

    s = Source_lvlA.get_by_name(session, 'MySource')

    s.move_to_loc(Location_lvlB.get_by_name('MyLocation'))

    # use highest level functionality

    s = Source_lvlB.get_by_name(session, 'MySource').assemble_info()

[Mike Bayer]

Hi there -

I normally wouldn't do things this way, however in seeking to provide
an alternative architecture, the specific example you've given does
work when used with the correct constructs, so I can provide both
architectures. So you would need to illustrate specific scenarios
that aren't working in terms of a fully runnable example, e.g. the one
below. The awkardness of doing it this way is that ORM declarative is
mapping each subclass individually, using single table inheritance
without a polymorphic_identity. So when you for example query for
Location_lvlB and then later query for Location_lvlA, you can get the
*same* row but in two different object instances. This creates a
conflict against the row which while it might be something you can
work with, the ORM is not aware that it's working this way:

(Pdb) l1 = session.query(Location_lvlA).first()
(Pdb) l2 = session.query(Location_lvlB).first()
(Pdb) l1
<__main__.Location_lvlA object at 0x7f274c47a940>
(Pdb) l2
<__main__.Location_lvlB object at 0x7f274c47abe0>

The second example attached illustrates a safer way to go which is
that you only map one class per hiearchy, the bottommost one, and then
express your various levels of class functionality in terms of mixins.
Hope this helps.

# example 1, the requested architecture
import datetime as dt

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import DateTime
from sqlalchemy import ForeignKey
from sqlalchemy import inspect
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Sequence
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.orm.session import object_session

metadata = MetaData()

source_table = Table(
"source",
metadata,
Column(
"id",
Integer,
Sequence("src_id_seq", metadata=metadata),

primary_key=True,
),

Column("name", String(length=32), unique=True, nullable=False, index=True),
Column(
"created",
DateTime(timezone=False),
default=dt.datetime.utcnow,

nullable=False,
),
)

location_table = Table(
"auth_user_detail",
metadata,
Column(
"id",
Integer,
Sequence("loc_id_seq", metadata=metadata),

primary_key=True,
),

Column("name", String(length=32), unique=True, nullable=False, index=True),
Column(
"created",
DateTime(timezone=False),
default=dt.datetime.utcnow,

nullable=False,
),
Column("source_id", ForeignKey("source.id")),
Column("firstname", String),

)

# base definition of the Source-class for Mapper

Base = declarative_base(metadata=metadata)

class Source_orm(Base):
__table__ = source_table

_loc = relationship("Location_orm", uselist=False)

loc_name = association_proxy("_loc", "firstname")

def __init__(self, name):
self.name = name


# base definition of the Location-class for Mapping
class Location_orm(Base):
__table__ = location_table

def __init__(self, name):
self.name = name


# -------------------
# Higher functions - lvlA : possibly packed into a different module



class Source_lvlA(Source_orm):
@classmethod
def get_by_name(cls, session, name):
return session.query(cls).filter(cls.name == name).one()

def move_to_loc_by_name(self, loc_name):
session = object_session(self)

loc = (
session.query(Location_orm)
.filter(Location_orm.name == loc_name)
.one()
)

self._loc = loc
session.commit()


class Location_lvlA(Location_orm):
@classmethod
def get_by_name(cls, session, name):
return session.query(cls).filter(cls.name == name).one()

def move_src_here(self, src):
session = object_session(self)
src.loc_id = self.id
session.merge(src)
session.commit()


# -------------------
# Even Higher functions - lvlB : possibly packed into a different module


class Source_lvlB(Source_lvlA):
def assemble_info(self):
return f"<Source> {self.name} at <Location> {self.loc_name}"


class Location_lvlB(Location_lvlA):
def assemble_info(self):
return f"<Location> {self.name}"



if __name__ == "__main__":
engine = create_engine("sqlite://", echo=True)

Base.metadata.create_all(engine)
session = Session(engine)

# create low level objects

s = Source_lvlA("MySource")
session.add(s)
session.flush()
l = Location_lvlB("MyLocation")

session.add(l)
session.flush()

# operate on the db use a higher level function
s = Source_lvlA.get_by_name(session, "MySource")

s.move_to_loc_by_name(
Location_lvlB.get_by_name(session, "MyLocation").name
)

# use highest level functionality
s = Source_lvlB.get_by_name(session, "MySource").assemble_info()

# example 2 - the safer architecture

import datetime as dt

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import DateTime
from sqlalchemy import ForeignKey
from sqlalchemy import inspect
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Sequence
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.orm.session import object_session

metadata = MetaData()

source_table = Table(
"source",
metadata,
Column(
"id",
Integer,
Sequence("src_id_seq", metadata=metadata),

primary_key=True,
),

Column("name", String(length=32), unique=True, nullable=False, index=True),
Column(
"created",
DateTime(timezone=False),
default=dt.datetime.utcnow,

nullable=False,
),
)

location_table = Table(
"auth_user_detail",
metadata,
Column(
"id",
Integer,
Sequence("loc_id_seq", metadata=metadata),

primary_key=True,
),

Column("name", String(length=32), unique=True, nullable=False, index=True),
Column(
"created",
DateTime(timezone=False),
default=dt.datetime.utcnow,

nullable=False,
),
Column("source_id", ForeignKey("source.id")),
Column("firstname", String),

)

# base definition of the Source-class for Mapper

Base = declarative_base(metadata=metadata)


class Source_orm(Base):
__abstract__ = True

@declared_attr
def __table__(cls):
return source_table

@declared_attr
def _loc(cls):
return relationship("Location_mapped", uselist=False)

loc_name = association_proxy("_loc", "firstname")

def __init__(self, name):
self.name = name


# base definition of the Location-class for Mapping
class Location_orm(Base):

__abstract__ = True

@declared_attr
def __table__(cls):
return location_table

def __init__(self, name):
self.name = name


# -------------------
# Higher functions - lvlA : possibly packed into a different module

class Source_abstract:
pass


class Location_abstract:
pass


class Source_lvlA(Source_abstract):

@classmethod
def get_by_name(cls, session, name):
return session.query(cls).filter(cls.name == name).one()

def move_to_loc_by_name(self, loc_name):

Location_mapped = inspect(self).mapper.attrs._loc.mapper.class_
session = object_session(self)
loc = (
session.query(Location_mapped)
.filter(Location_mapped.name == loc_name)
.one()
)
self._loc = loc
session.commit()


class Location_lvlA(Location_abstract):

@classmethod
def get_by_name(cls, session, name):
return session.query(cls).filter(cls.name == name).one()

def move_src_here(self, src):
session = object_session(self)
src.loc_id = self.id
session.merge(src)
session.commit()


# -------------------
# Even Higher functions - lvlB : possibly packed into a different module


class Source_lvlB(Source_lvlA):
def assemble_info(self):
return f"<Source> {self.name} at <Location> {self.loc_name}"


class Location_lvlB(Location_lvlA):
def assemble_info(self):
return f"<Location> {self.name}"

class Source_mapped(Source_lvlB, Source_orm):
pass


class Location_mapped(Location_lvlB, Location_orm):
pass

if __name__ == "__main__":
engine = create_engine("sqlite://", echo=True)

Base.metadata.create_all(engine)
session = Session(engine)

# create low level objects

s = Source_mapped("MySource")
session.add(s)
session.flush()
l = Location_mapped("MyLocation")

session.add(l)
session.flush()

# operate on the db use a higher level function

s = Source_mapped.get_by_name(session, "MySource")
s.move_to_loc_by_name(
Location_mapped.get_by_name(session, "MyLocation").name
)

# use highest level functionality

s = Source_mapped.get_by_name(session, "MySource").assemble_info()

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ed3a4914-2b37-4f2e-b213-3c615b85c367%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Marcel Zoll]

Thank you, Mike, for having a look at my clumsy code (there were some mistakes in the code, I only saw later) and making this effort.

Firstly, I have done a bit more tinkering yesterday, and also got aware, that the Mapper or the internal cranking for sqlalchemy in this hierarchical scenario have trouble to resolve the query via a inherited relationship to the correct object. I think this is in essence what you want to get at in your first paragraph.

I think the problem stems from that the relationship object takes the to be querried/mapped-to first argument as a string, and inspects the namespace for this very class, not its derivatives. I was able to resolve this in a kind-off working example by overriding the relationship in every derived class. Not nice, but I understand that it is kind of necessary, becausing finding and resolving  derived classes in the namespace is not trivial.

Arriving at your second example: I am all willing to learn about new techniques and I can see the idea that you tried to established there. However, I also noticed that you in the main routine only operate with the highest derived class 'Source_mapped' and 'Location_mapped'. All other classes are not mapped and in principle are just structure Mixin's. This is kind of against the goal I to achieve a strict hierarchy, so that I could break up the code into different packages, which build on top of each other, but are functional at each level: myproj_lvl0 < myproj_lvlA < myproj_lvlB. Anyhow, I took your second example and wrangled it into the structure that I think is desiderable to make this happen. Please also look at the __main__-routine at the bottom, which should make it more clear what at what kind of functionality I would like to arrive at.

Many Thanks

Marcel

# example 2 - the safer architecture

import datetime as dt

from sqlalchemy import Column
from sqlalchemy import create_engine

from sqlalchemy import DateTime
from sqlalchemy import ForeignKey
from sqlalchemy import inspect
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Sequence
from sqlalchemy import String
from sqlalchemy import Table

from sqlalchemy.ext.associationproxy import association_proxy

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr

from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.orm.session import object_session

metadata = MetaData()

source_table = Table("source", metadata,
    Column("id", Integer, Sequence("src_id_seq", metadata=metadata), primary_key=True),
    Column("name", String(length=32), unique=True, nullable=False, index=True),
    Column("created", DateTime(timezone=False), default=dt.datetime.utcnow, nullable=False)

)

location_table = Table("location_detail", metadata,

    Column("id", Integer, Sequence("loc_id_seq", metadata=metadata), primary_key=True),
    Column("name", String(length=32), unique=True, nullable=False, index=True),

    Column("created", DateTime(timezone=False), default=dt.datetime.utcnow, nullable=False),

    Column("source_id", ForeignKey("source.id"))

)

Base = declarative_base(metadata=metadata)


# Low Level functions - lvl0 : possibly packed into a base package 'myproj_lvl0'

# base definition of the Source-class for Mapper

class Source_orm_lvl0(Base):

    __abstract__ = True

    @declared_attr
    def __table__(cls):
        return source_table

    @declared_attr
    def _loc(cls):

        return relationship("Location_mapped_lvl0", uselist=False)  # <<< need to know the name of the mapped classes

    loc_name = association_proxy("_loc", "name")

    def __init__(self, name):
        self.name = name

# base definition of the Location-class for Mapping

class Location_orm_lvl0(Base):

    __abstract__ = True

    @declared_attr
    def __table__(cls):

        return location_table

    def __init__(self, name):
        self.name = name

class Source_mapped_lvl0(Source_orm_lvl0):
    pass

class Location_mapped_lvl0(Location_orm_lvl0):
    pass


# Higher functions - lvlA : isolated into a different module/package 'myproj_lvlA'

class Source_orm_lvlA(Source_orm_lvl0):
    __abstract__ = True

    @declared_attr
    def _loc(cls):
        return relationship("Location_mapped_lvlA", uselist=False)  # <<< need to override with next hierarchy level class?!

    @classmethod
    def get_by_name(cls, session, name):
        return session.query(cls).filter(cls.name == name).one()

    def move_to_loc_by_name(self, loc_name):

        Location_mapped = inspect(self).mapper.attrs._loc.mapper.class_
        session = object_session(self)

        loc = (
            session.query(Location_mapped)
            .filter(Location_mapped.name == loc_name)
            .one()
        )

        self._loc = loc
        session.commit()


class Location_orm_lvlA(Location_orm_lvl0):
    __abstract__ = True

    @classmethod
    def get_by_name(cls, session, name):
        return session.query(cls).filter(cls.name == name).one()

    def move_src_here(self, src):
        session = object_session(self)

        src._loc = self
        session.merge(src)
        session.commit()


class Source_mapped_lvlA(Source_orm_lvlA):
    pass

class Location_mapped_lvlA(Source_orm_lvlA):
    pass


# Even Higher functions - lvlB : isolated into a different module/package 'myproj_lvlB'

class Source_orm_lvlB(Source_orm_lvlA):
    __abstract__ = True

    @declared_attr
    def _loc(cls):
        return relationship("Location_mapped_lvlB", uselist=False)

    def assemble_info(self):
        return f"<Source> {self.name} at <Location> {self.loc_name}"

class Location_orm_lvlB(Location_orm_lvlA):
    __abstract__ = True

    def assemble_info(self):
        return f"<Location> {self.name}"

class Source_mapped_lvlB(Source_orm_lvlB):
    pass


class Location_mapped_lvlB(Location_orm_lvlB):
    pass


#----------------------------------------------------------------------
if __name__ == "__main__":
    engine = create_engine("sqlite://", echo=False)

    Base.metadata.create_all(engine)
    session = Session(engine)


    # from myproj_lvl0 import Source_mapped_lvl0, Location_mapped_lvl0

    # create low level objects

    s = Source_mapped_lvl0("MySource")
    session.add(s)
    session.flush()
    l = Location_mapped_lvl0("MyLocation")

    session.add(l)
    session.flush()

    # operate on the db use a higher level function

    # from myproj_lvlA import Source_mapped_lvlA, Location_mapped_lvlA

    s = Source_mapped_lvlA.get_by_name(session, "MySource")
    s.move_to_loc_by_name(
        Location_mapped_lvlA.get_by_name(session, "MyLocation").name
    )
    assert isinstance(s._loc, Location_mapped_lvlA)

    # use highest level functionality

    # from myproj_lvlB import Source_mapped_lvlA, Location_mapped_lvlB
    s = Source_mapped_lvlB.get_by_name(session, "MySource")
    print( s.assemble_info() )
    assert isinstance(s._loc, Location_mapped_lvlB)

[Mike Bayer]

On Wed, May 15, 2019 at 4:12 AM Marcel Zoll
<marcel.zo...@gmail.com> wrote:
>>
>> Thank you, Mike, for having a look at my clumsy code (there were some mistakes in the code, I only saw later) and making this effort.
>
>
> Firstly, I have done a bit more tinkering yesterday, and also got aware, that the Mapper or the internal cranking for sqlalchemy in this hierarchical scenario have trouble to resolve the query via a inherited relationship to the correct object. I think this is in essence what you want to get at in your first paragraph.
> I think the problem stems from that the relationship object takes the to be querried/mapped-to first argument as a string, and inspects the namespace for this very class, not its derivatives. I was able to resolve this in a kind-off working example by overriding the relationship in every derived class. Not nice, but I understand that it is kind of necessary, becausing finding and resolving derived classes in the namespace is not trivial.
>
> Arriving at your second example: I am all willing to learn about new techniques and I can see the idea that you tried to established there. However, I also noticed that you in the main routine only operate with the highest derived class 'Source_mapped' and 'Location_mapped'. All other classes are not mapped and in principle are just structure Mixin's. This is kind of against the goal I to achieve a strict hierarchy, so that I could break up the code into different packages, which build on top of each other, but are functional at each level: myproj_lvl0 < myproj_lvlA < myproj_lvlB. Anyhow, I took your second example and wrangled it into the structure that I think is desiderable to make this happen. Please also look at the __main__-routine at the bottom, which should make it more clear what at what kind of functionality I would like to arrive at.

it's the "functional at each level" part that I think goes beyond what
is practical. When you run an operation on a superclass, and it
expects to do ORM operations, it has to be referring to the ultimate
mapped class. The ORM is designed with the idea that the concrete
instantiation classes are the ones that are ultimately mapped and
dealt with in terms of persistence. It should be apparent that it
would be vastly more complicated to make it also "know" about all
kinds of arbitrary subclasses. How would you propose the identity
map issue I illustrated before function? That is, if i query for
Class_lvlA and later for Class_lvlB, what identity key should they
have? If you say they should have the same identity key, what if
both Class_lvlA and Class_lvlB perform an operation against the same
Session - there is only one Class_<xyz> object in the identity map,
suppose it is Class_lvlA, now Class_lvlB consults for its own class
and gets the wrong answer. If you propose they should have
individual identity keys, then how do these two classes consult for
the same row in the identity map? I appreciate that you are
going for some particular level of purity in your design, however I
believe there is some conflation here of separation of concerns vs.
concrete instantiation patterns.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/9f20896a-b740-4107-a6b2-f6994b175d89%40googlegroups.com.

[Marcel Zoll]

it's the "functional at each level" part that I think goes beyond what
is practical.   When you run an operation on a superclass, and it
expects to do ORM operations, it has to be referring to the ultimate
mapped class.  The ORM is designed with the idea that the concrete
instantiation classes are the ones that are ultimately mapped and
dealt with in terms of persistence.

Okay, that is something that I get. However, if you extend the baseclass strictly hierarchical and only make one concrete class for each derived level that you want to operate at (lets say each level isolated in a package), the notion of an 'ultimate' derived class should be well defined, and so should be programming-technically resolvable (So i understand sqlalchemy already does some class-inspections under the hood, even though I do not know by which mechanics that is governed):

I mean something like this (\/ and > should mean 'derives to'):

--- level 0 ---

Base

\/

OBJ01_Declared_lvl0 > OBJ01_Mapped_lvl0

--- level A ---

\/

OBJ01_Extended_lvlA > OBJ01_Mapped_lvlA

--- level B ---

\/

OBJ01_Extended_lvlB > OBJ01_Mapped_lvlB

  It should be apparent that it
would be vastly more complicated to make it also "know" about all
kinds of arbitrary subclasses.

You are correct, that from one derived concrete class knowing the identity of another other derived concrete class of the object it should relate to (relationship(<>)) is hard, and cannot be defined at the baseclass level (possibly where you declare the table) alone. However, I also saw that relationships can be specified by either giving the class of the to be related to object or by its class-name (a string). As this name needs to be resolved to some object at some point by inspection of the namespace (or brute forcing it), cant this resolving not be delayed until this very relationship is used?

A way to potentially resolve this could be that you would kind of use a 'derived_level_Mixin' that you assign to all Mapped classes at each level; so inspecting for this trait in your querying class and the class to be resulted should make things uniquely identifiable.

My Ideal solution would look so that all mapped would actually go by the same class-name, however, by being in different modules they fully-qualified names should still be unique. This would make it more convenient to use, as the classes follow the same basic-interface, while the highest derived class in the highest level package provides most functionality

--- script1.py ---

from myproj_lvlA import Source

...

session.add(Source('MySource'))

session.commit()

s = session.query(Source).first()

print(Source)

--- script2.py ---

from myproj_lvlB import Source  #<< import from higher level module

...

session.add(Source('MySource'))

session.commit()

s = session.query(Source).first()

print(Source)

s.move_to_loc_by_name('MyLocation') #<< functionality only accessible at myprojB

 

  How would you propose the identity
map issue I illustrated before function? That is, if i query for
Class_lvlA and later for Class_lvlB, what identity key should they
have?   If you say they should have the same identity key, what if
both Class_lvlA and Class_lvlB perform an operation against the same
Session - there is only one Class_<xyz> object in the identity map,
suppose it is Class_lvlA, now Class_lvlB consults for its own class
and gets the wrong answer.   If you propose they should have
individual identity keys, then how do these two classes consult for
the same row in the identity map?

You are correct, that a session should not be expected to handle objects of different level (Class_lvlA & Class_lvlB) at the same time, as this could potentially lead to conflicts, also as I understand it is not allowed to map the same table to multiple different, inherently distinct, classes at the same time (which i think is good). I am not 

 

      I appreciate that you are
going for some particular level of purity in your design, however I
believe there is some conflation here of separation of concerns vs.
concrete instantiation patterns.

Yesterday, I found out some interesting properties, that one can make declarations which use the sqlalchemy-metadata and declarative base, but without visiting/importing the module in question this code is not actually executed, resp. the definitions are not actually added to the metadata or the mappers. It might be possible to exploit this to make a construct as depicted above happen (i.e. not visiting/importing the modules declaring mappers for all mapped classes below the current level). I am not sure if this will not eventually violate some rules of good programming practice...

Thanks Marcel

 

[Simon King]

I very much doubt you are going to find a way to get SQLAlchemy to do
what you want automatically. I think you might be better off writing
your classes *without* SQLAlchemy, and then have a separate mechanism
that uses SA's classical mapping tools
(https://docs.sqlalchemy.org/en/13/orm/mapping_styles.html#classical-mapping)
to map them. You can inspect the classes and traverse the hierarchy in
whatever way makes sense to you.

Simon

[Marcel Zoll]

So, I was eventually able to solve this.

I followed this exact pattern as in my last post:

--- level 0 ; 'myproj_lvl0'---

metadata//Tables

>-plugs-into-<

Base 

\/

OBJ01_Declared_lvl0 > OBJ01_Mapped_lvl0

--- level A ; module 'myproj_lvlA' ---

\/

OBJ01_Extended_lvlA > OBJ01_Mapped_lvlA

--- level B ; module 'myproj_lvlB'---

\/

OBJ01_Extended_lvlB > OBJ01_Mapped_lvlB

The key to the success was really just to,  coming from any higher level module, not visit modules of lower levels, which make the mapping concrete (i.e. here the OBJ01_Mapped_lvlX declaring modules). This will cause that only one mapper is concret for any mapped table at each point of time.

Additionally, if all concrete classes are named the same (the class' apparent name, not the fully qualified one), it suffices to use this class-name only once in the declaration of the relationship-attributes (i.e. writing @declared_attribute def _loc: relationshipd('Location')). This will always resolve to the correct mapped class and lifts out a lot of possibly duplicated code. The only thing to be observant about is to consequently use the '@declared_attr'-decorator.

here is the minimal structure i cam up with:

myproj_lvl0

  | - meta_def [metadata0 Metadata; source_table = Tables(...)]

  | - orm_def [from .meta_def import metadata, source_table; Base=declarative_base(metadata); class Source_orm_lvl0(Base): @declared_attr __table__ = source_table @declared_attr def _loc: relation('Location') ...]

  | - mapper_def [from .orm_def import Source_orm_lvl0; class Source(Source_orm_lvl0): pass]

myproj_lvlA

  | - orm_def [from ..myproj_lvl0 import Source_orm_lvl0; Source_orm_lvlA(Source_orm_lvl0): @declared_attr def more: ...]

  | - mapper_def [from .orm_def import Source_orm_lvlA; class Source(Source_orm_lvlA): pass]

myproj_lvlB

 ...(same structure as lvlB)

scripts

  | - create_tables [from myproj_lvl0.meta_def import metadata; metadata.create_all(engine)]

  | - script_lvl0 [from .myproj_lvl0.mapper_def import Source, Location; s = session.merge(Source('ABC')); print(type(s._loc)) # myproj_lvl0.mapper_def.Location ]

  | - script_lvlA [from .myproj_lvlA.mapper_def import Source, Location; s = session.merge(Source('ABC')); print(type(s._loc)) # myproj_lvlA.mapper_def.Location]

  | - script_lvlB [from .myproj_lvlB.mapper_def import Source, Location; s = session.merge(Source('ABC')); print(type(s._loc)) # myproj_lvlB.mapper_def.Location]

I hope that helps if somebody should encounter the same problem

[Marcel Zoll]

I forgot to state that all derived abstract orm classes (e.g Source_orm_lvl0, Source_orm_lvlA) also need the class attribute '__abstract__= True' set