SQLAlchemy - Dictionary of tags

Question

Hi everybody, I have question regarding the SQLAlchemy. How can I add into my mapped class the dictionary-like attribute, which maps the string keys into string values and which will be stored in the database (in the same or another table as original mapped object). I want this add support for arbitrary tags of my objects.

I found the following example in SQLAlchemy documentation:

from sqlalchemy.orm.collections import column_mapped_collection, attribute_mapped_collection, mapped_collection

mapper(Item, items_table, properties={
# key by column
'notes': relation(Note, collection_class=column_mapped_collection(notes_table.c.keyword)),
# or named attribute
'notes2': relation(Note, collection_class=attribute_mapped_collection('keyword')),
# or any callable
'notes3': relation(Note, collection_class=mapped_collection(lambda entity: entity.a + entity.b))
})

item = Item()
item.notes['color'] = Note('color', 'blue')

But I want the following behavior:

mapper(Item, items_table, properties={
# key by column
'notes': relation(...),
})

item = Item()
item.notes['color'] = 'blue'

It is possible in SQLAlchemy?

Thank you

Answer 1

The simple answer is 'no'.

SQLAlchemy is wrapper on a SQL database.

The relation examples you quote translate a relationship between SQL tables into a Python map-like structure to make it slightly simpler to do the SQL SELECT statements and locate rows in another table.

The

item.notes['color'] = Note('color', 'blue')

is essential because the Note is a separate table with two columns. You can't leave the Note part out.

You must define this other SQL table, and you must create objects which are mapped to that SQL table.

Answer 2

The simple answer is yes.

Just use an association proxy:

from sqlalchemy import Column, Integer, String, Table, create_engine
from sqlalchemy import orm, MetaData, Column, ForeignKey
from sqlalchemy.orm import relation, mapper, sessionmaker
from sqlalchemy.orm.collections import column_mapped_collection
from sqlalchemy.ext.associationproxy import association_proxy

Create a test environment:

engine = create_engine('sqlite:///:memory:', echo=True)
meta = MetaData(bind=engine)

Define the tables:

tb_items = Table('items', meta, 
        Column('id', Integer, primary_key=True), 
        Column('name', String(20)),
        Column('description', String(100)),
    )
tb_notes = Table('notes', meta, 
        Column('id_item', Integer, ForeignKey('items.id'), primary_key=True),
        Column('name', String(20), primary_key=True),
        Column('value', String(100)),
    )
meta.create_all()

Classes (note the association_proxy in the class):

class Note(object):
    def __init__(self, name, value):
        self.name = name
        self.value = value
class Item(object):
    def __init__(self, name, description=''):
        self.name = name
        self.description = description
    notes = association_proxy('_notesdict', 'value', creator=Note)

Mapping:

mapper(Note, tb_notes)
mapper(Item, tb_items, properties={
        '_notesdict': relation(Note, 
             collection_class=column_mapped_collection(tb_notes.c.name)),
    })

Then just test it:

Session = sessionmaker(bind=engine)
s = Session()

i = Item('ball', 'A round full ball')
i.notes['color'] = 'orange'
i.notes['size'] = 'big'
i.notes['data'] = 'none'

s.add(i)
s.commit()
print i.notes

That prints:

{u'color': u'orange', u'data': u'none', u'size': u'big'}

But, are those in the notes table?

>>> print list(tb_notes.select().execute())
[(1, u'color', u'orange'), (1, u'data', u'none'), (1, u'size', u'big')]

It works!! :)