SqlAlchemy attribute that tracks an assigned attribute

Question

Goal: Create an SQLAlchemy attribute which tracks/follows changes in another object's SQLAlchemy attribute.

Given:

class ClazzA():
    attributeA = Column(JSONDict)

class ClazzB():
    attributeB = Column(?)

objectA = ClazzA()
objectA.attributeA = {'foo': 1}
objectB = ClazzB()
objectB.attributeB = objectA.attributeA
objectA.attributeA['foo'] = 2

JSONDict is associated with MutableDict as described here: http://docs.sqlalchemy.org/en/latest/orm/extensions/mutable.html#module-sqlalchemy.ext.mutable , i.e. the JSONDict type allows for mutation tracking.

So we have this dictionary on objectA whose changes are being recorded by SQLAlchemy. I would like for attributeB to track attributeA such that even if the application is restarted (i.e. the attributes are reloaded from the DB), then attributeB will continue to reflect changes made to attributeA's dictionary.

Of course, this is closely related to the fact that Python doesn't have an idea of pointers. I was wondering if SQLAlchemy has a solution for this particular problem.

Is `ClazzB` another model? Do you need to persist that model? — univerio, Mar 15 '16 at 18:49
Do your models `ClazzA` and `ClazzB` have any clear relation at DB level? In other words, if we just get an event like "ClazzA.attributeA was changed for this instance", is there a way to say "This ClazzA instance is related to that ClazzB instance"? — Ilja Everilä, Mar 20 '16 at 18:15
@Ilja That would be part of the solution. During `__init__` of a `objectB` (or later) we would point it to the intended/paired/matching property on objectA. `attributeB` would only ever point to an `attributeA` on an object of type `ClazzA`, it's just that we don't know which `objectA` until runtime. — ChaimKut, Mar 21 '16 at 07:25
@ChaimKut it sounds a lot like what you're looking for is a ForeignKey + a property on ClazzB. — Ilja Everilä, Mar 21 '16 at 07:36
Which means we'd need to see a bit more about the real models and what you've tried to do up till now. — Ilja Everilä, Mar 21 '16 at 07:38
@ChaimKut do you have an existing column in your DB in the table modeled by `ClazzB` that should reflect the column in `ClazzA`? I can see little reason to try and do what you describe otherwise, since it would be against [normalisation](https://en.wikipedia.org/wiki/Database_normalization). — Ilja Everilä, Mar 23 '16 at 22:37
@Ilja You can imagine that attributeA is a dictionary of Widgets, and for objectB I am using attributeB to express which Widget I want to be associated with objectB. But if that attributeA Widget described in the dictionary is modified, I want attributeB to reflect that most updated value. — ChaimKut, Mar 24 '16 at 23:30
@ChaimKut hmm, so attributeA of ClazzA holds different widgets in a single JSON structure to which ClazzB should refer to, but do you mean that ClazzB should access values under certain key in that JSON? — Ilja Everilä, Mar 25 '16 at 06:22
Btw I still think that this model abuses relational model. Instead of storing all widgets in a single row's JSON column create a table *widget* with JSON column and refer to that from ClazzB. Update a row and all ClazzB's have the updates... — Ilja Everilä, Mar 26 '16 at 13:46

Ilja Everilä · Accepted Answer · 2016-03-26T16:41:39.083

TL;DR

You want a one-to-many relationship.

from sqlalchemy import ForeignKey, Integer, Column
from sqlalchemy.orm import relationship


class Widget(Base):

    __tablename__ = 'widget'

    widget_id = Column(Integer, primary_key=True)
    # name columns, type columns, ...
    json = Column(JSONDict)


class ClazzB(Base):

    __tablename__ = 'clazzb'

    clazzb_id = Column(Integer, primary_key=True)
    # Your "attributeB"
    widget_id = Column(Integer,
                       ForeignKey('widget.widget_id',
                                  onupdate='cascade',
                                  ondelete='cascade'),
                       nullable=False)

    widget = relationship('Widget')
    # possible association_proxy
    #widget_json = association_proxy('widget', 'json')

Using a Relationship

Define a relationship between models ClazzA and ClazzB. Now since we don't have the whole picture, the below definitions are just examples.

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship


class ClazzA(Base):  # replace Base with the base class of your models

    __tablename__ = 'clazza'  # replace with the real tablename

    # T is the type of your primary key, the column name is just an example
    clazza_id = Column(T, primary_key=True)


class ClazzB(Base):

    # The column that will relate this model to ClazzA
    clazza_id = Column(T, ForeignKey('clazza.clazza_id',
                                     onupdate='cascade',
                                     ondelete='cascade'),
                       nullable=False)

    # A handy accessor for relationship between mapped classes,
    # not strictly required. Configurable to be either very lazy
    # (loaded if accessed by issuing a SELECT) or eager (JOINed
    # when loading objectB for example)
    objectA = relationship('ClazzA')

Now instead of adding a reference to attributeA of ClazzA to ClazzB add a reference to related objectA to objectB on initialization.

objectB = ClazzB(..., objectA=objectA)

The two are now related and to access attributeA of related objectA through objectB do

objectB.objectA.attributeA

No need to track changes to attributeA, since it is the attributeA of the instance.

Now if you must have an attribute attributeB on ClazzB (to avoid refactoring existing code or some such), you could add a property

class ClazzB:

    @property
    def attributeB(self):
        return self.objectA.attributeA

which will return the attributeA of the related objectA with

objectB.attributeB
objectB.attributeB['something'] = 'else'

and so on.

There is also an SQLAlchemy method for accessing attributes across relationships: association proxy. It supports simple querying, but is not for example subscriptable.

class ClazzB(Base):

    attributeB = association_proxy('objectA', 'attributeA')

If you wish for ClazzB.attributeB to access values from the JSONDict under certain key, you can for example use something like this

class ClazzB(Base):

    key = Column(Unicode)

    @property
    def attributeB(self):
        return self.objectA.attributeA[self.key]

You can also make attributeB work as an SQL expression on class level using hybrid properties, if you need such a thing. You would have to write your class level expressions yourself though.

SqlAlchemy attribute that tracks an assigned attribute

1 Answers1

TL;DR

Using a Relationship