CODEWITHSUNDEEP
pythonsqlalchemy
ChaimKut
ChaimKut

Reputation: 2809

SqlAlchemy attribute that tracks an assigned attribute

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.

Upvotes: 0

Views: 191

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52997

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.

Upvotes: 6

Related Questions