SQLAlchemy mixins, how to inherit from them and variable relationships

Question

I have started to use the SQLAlchemy Mixins and I've read the documentation. It is useful, but I still find that I have questions regarding what I wish to accomplish. I should say that I'm not very experienced with SQLAlchemy.

So what I'd like is a way to inherit columns and relationships from a Mixin to a sub class. Currently I have the former working, but not the latter.

The relationships are explained somewhat in the documentation as follows:

@declarative_mixin
class RefTargetMixin:
   @declared_attr
   def target_id(cls):
       return Column('target_id', ForeignKey('target.id'))

   @declared_attr
   def target(cls):
       return relationship("Target")

This seems to be exactly what I'm looking for in terms of functionality, except for one point: unless I'm mistaken, 'target' as a string is fixed here and is not variable. If I'm to come up with a generic version, I'd like to be able to have a sub class specify the table name and class with which it will have the relationship.

Unfortunately this is where I get stuck. I just don't know how to do this. I have made some fairly primitive attempts, for example:

@declared_attr
def target_id(cls, targetTable):
    return Column('target_id', ForeignKey(f'{targetTable}.id'))

But how to 'call' this method I just don't know. I tried calling 'super' from the sub class init, but that keeps telling me:

TypeError: target_id() missing 1 required positional argument: 'targetTable'

I figured it wouldn't work, but I'm just not sure what the right way would be and I thought I'd at least give it a try before asking here.

Answer 1

I did find an answer and I thought I'd come back and show how I did it.

After a little searching, I came across this question which became the basis of the solution. It's not exact, but it shows that these @declared_attr things can be variable if you know how to go about it.

So let's start with just the target_id property, which was what I had here. There was more, which I will get to, but I'll start with that. This, by the way, all inside the 'parent' mixin.

The basic pattern is that you need a setter, a getter and a variable itself, like so:

_targetTableName = 'Not defined target table name'

def get_targetTableName(self):
    return self._targetTableName

def set_targetTableName(self, value):
    self._targetTableName = value

@declared_attr
def targetTableName(cls):
    return synonym('_targetTableName', descriptor=property(cls.get_targetTableName, cls.set_targetTableName))

Then I also need this within the same mixin:

@declared_attr
def target_id(cls):
    return Column('target_id', ForeignKey(f'{cls.targetTableName}.ID'))

As you can see, this allows me to change what is in the ForeignKey arguments - it's just a string after all.

The next question I would be asking, as a relative newbie, is how I change this 'targetTableName' property in the child? Well, just like this:

class TestChild(TestParent, med.DeclarativeBase):
   __tablename__='testChild01'
   targetTableName = 'SchoolTypes'

So now, when it's all created automatically, your child class will have a foreign key to, in this case, SchoolTypes.ID.

But wait, what if the column in the target isn't called 'ID'? Well you can make that a variable too in the same way. I chose not to because all my tables have an 'ID' column (I don't know if that's considered good or bad practise).

But wait, there's more!

You see, really the target_id was only part of the equation and I hoped that, if I could solve that, I could get to the other part of the prize: the linked object. That is, the SQLAlchemy 'relationship' type that gives you the object linked by your foreign key.

When you create one normally, it looks like this:

targetType = relationship('SchoolType', foreign_keys='Search_Grade_SchoolType.targetID')

So how to make that generic too? Well with the same process as above to make the set things variable.

Let me share with you the code as a whole, to make it a little easier to see. But note, all I've done is have a variable for the target table name, the target class name, and the current class name, all of which you need:

from sqlalchemy import Column, Integer, Boolean, String, DateTime, ForeignKey
from sqlalchemy.orm import backref, relationship

from sqlalchemy.orm import declarative_mixin, synonym
from sqlalchemy.orm import declared_attr

@declarative_mixin
class TestParent:
    id = Column('ID', Integer, primary_key=True, autoincrement=True)

    _targetTableName = 'Not defined target table name'
    _targetClassName = 'Not defined target class name'
    _myClassName = 'Not defined class name'

    def get_myClassName(self):
        return self._myClassName

    def set_myClassName(self, value):
        self._myClassName = value

    @declared_attr
    def myClassName(cls):
        return synonym('_myClassName', descriptor=property(cls.get_myClassName, cls.set_myClassName))

    def get_targetTableName(self):
        return self._targetTableName

    def set_targetTableName(self, value):
        self._targetTableName = value

    @declared_attr
    def targetTableName(cls):
        return synonym('_targetTableName', descriptor=property(cls.get_targetTableName, cls.set_targetTableName))

    def get_targetClassName(self):
        return self._targetClassName

    def set_targetClassName(self, value):
        self._targetClassName = value

    @declared_attr
    def target_id(cls):
        return Column('target_id', ForeignKey(f'{cls.targetTableName}.ID'))

    @declared_attr
    def targetClassName(cls):
        return synonym('_targetClassName', descriptor=property(cls.get_targetClassName, cls.set_targetClassName))

    @declared_attr
    def target_object(cls):
        return relationship(f'{cls.targetClassName}', foreign_keys=f'{cls.myClassName}.target_id')


class TestChild(TestParent, med.DeclarativeBase):
    __tablename__='testChild01'
    name = Column('NAME', String(50), unique=False)
    targetTableName = 'SchoolTypes'
    targetClassName = 'SchoolType'
    myClassName = 'TestChild'

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

    def TestMethod(self):
        print(f'Print me!')

So what does this all do? It allows a child class (TestChild) to have the target_object and target_id etc. set up by just the inputting of a few variables in the child class. The parent can have as many columns as you like and the child will have them too, but they can also have columns of their own (in this case 'ID' from the parent, 'name' from the child.

Why did I even do all this? What does it solve? Well I knew I'd be having quite a few tables that were effectively the same thing. The same structure, except they'd have foreign keys to different tables to each other. So I wanted some kind of inheritance situation to allow re-use of code, and also to allow me to be lazy.