SQLAlchemy – MappedCollection problem

Question

I have some problems with setting up the dictionary collection in Python's SQLAlchemy:

I am using declarative definition of tables. I have Item table in 1:N relation with Record table. I set up the relation using the following code:

_Base = declarative_base()

class Record(_Base):
    __tablename__ = 'records'

    item_id = Column(String(M_ITEM_ID), ForeignKey('items.id'))
    id = Column(String(M_RECORD_ID), primary_key=True)
    uri = Column(String(M_RECORD_URI))
    name = Column(String(M_RECORD_NAME))

class Item(_Base):
    __tablename__ = 'items'

    id = Column(String(M_ITEM_ID), primary_key=True)

    records = relation(Record, collection_class=column_mapped_collection(Record.name), backref='item')

Now I want to work with the Items and Records. Let's create some objects:

i1 = Item(id='id1')
r = Record(id='mujrecord')

And now I want to associate these objects using the following code:

i1.records['source_wav'] = r

but the Record r doesn't have set the name attribute (the foreign key). Is there any solution how to automatically ensure this? (I know that setting the foreign key during the Record creation works, but it doesn't sound good for me).

Many thanks

Answer 1

You have:

backref='item'

Is this a typo for

backref='name'

?

Answer 2

I can't comment yet, so I'm just going to write this as a separate answer:

from sqlalchemy.orm import validates

class Item(_Base):
    [...]

    @validates('records')
    def validate_record(self, key, record):
      record.name=key
      return record

This is basically a copy of Gunnlaugur's answer but abusing the validates decorator to do something more useful than exploding.

Answer 3

You want something like this:

from sqlalchemy.orm import validates

class Item(_Base):
    [...]

    @validates('records')
    def validate_record(self, key, record):
      assert record.name is not None, "Record fails validation, must have a name"
      return record

With this, you get the desired validation:

>>> i1 = Item(id='id1')
>>> r = Record(id='mujrecord')
>>> i1.records['source_wav'] = r
Traceback (most recent call last):
  [...]
AssertionError: Record fails validation, must have a name
>>> r.name = 'foo'
>>> i1.records['source_wav'] = r
>>>