Note: this is a hack for storing multiple values referenced by another table in You want a single tuple's JSON attribute. Ideally the values would exist as separate tuples referenced with foreign keys (a one-to-manyone-to-many relationship). This solution's referential integrity can fail in multiple ways: if ClazzB instance's primary key is updated (cannot find value from JSON or points to wrong value) or the tuple is deleted (stale value is left behind in JSON). It is possible to handle these cases with triggers, but this is left as an excercise for the reader.
from sqlalchemy import ForeignKey, Integer, Column
from sqlalchemy.orm import relationship
class ClazzA(Base):
__tablename__ = 'clazza'
clazza_id = Column(Integer, primary_key=True)
attributeA = Column(JSONDict)
class ClazzB(Base):
__tablename__ = 'clazzb'
clazzb_id = Column(Integer, primary_key=True)
clazza_id = Column(Integer,
ForeignKey('clazza.clazza_id',
onupdate='cascade',
ondelete='cascade'),
nullable=False)
objectA = relationship('ClazzA')
@property
def attributeB(self):
return self.objectA.attributeA[str(self.clazzb_id)]
@attributeB.setter
def attributeB(self, value):
self.objectA.attributeA[str(self.clazzb_id)] = value
...
objectA = ClazzA(attributeA={})
session.add(objectA)
objectB = ClazzB(objectA=objectA)
session.add(objectB)
# Flush objects to DB, creates primary keys, which we need for accessing
# the dictionary
session.flush()
objectB.attributeB = {'a': 1, 'b': 2}
objectA.attributeA[str(objectB.clazzb_id)]['a'] == 1
Runnable example: http://pastebin.com/eNLxTFSE
The proper model without integrity issues for such a thing would be a simple one-to-many relationship from widget to ClazzB:
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')
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.
Using Events
You could try and implement the tracking behaviour with ORM events, except the event you need is not yet implemented. Were it to be added, your models could look something like
from sqlalchemy.orm import backref
class ClazzA(Base):
key = Column(...)
attributeA = Column(JSONDict)
class ClazzB(Base):
clazza_key = Column(..., ForeignKey(...))
attributeB = Column(JSONDict)
# Define a one-to-one relationship, will create the scalar back
# reference on ClazzA as ClazzA.objectB
relationship('ClazzA', backref=backref('objectB', uselist=False))
but you still have to define the relationship (see Using a Relationship) between the models.
This can be more error prone and you have to be careful not to allow concurrent updates to mess with the mirroring. Proper use of transactions is the key. All in all I would not recommend this, as it would go against normalization.
Registering the event handler
from sqlalchemy import events
@events.listens_for(ClazzA.attributeA, 'changed')
def clazza_attra_changed(target, value, oldvalue, initiator):
# target is the objectA instance, whose attributeA has been changed
# assuming a One-to-One relationship set related objectB's
# attributeB to the new value
target.objectB.attributeB = value