How to map one class against multiple tables with SQLAlchemy?
database, database-design, python, sqlalchemy
Solution
This is called the entity-attribute-value pattern. There is an example about this under the SQLAlchemy examples directory: vertical/.
If you are using PostgreSQL, then there is also the `hstore` contrib module that can store a string to string mapping. If you are interested then I have some code for a custom type that makes it possible to use that to store extended attributes via SQLAlchemy.
Another option to store custom attributes is to serialize them to a text field. In that case you will lose the ability to filter by attributes.
Problem
Lets say that I have a database structure with three tables that look like this: ``` items - item_id - item_handle attributes - attribute_id - attribute_name item_attributes - item_attribute_id - item_id - attribute_id - attribute_value ``` I would like to be able to do this in SQLAlchemy: ``` item = Item('item1') item.foo = 'bar' session.add(item) session.commit() item1 = session.query(Item).filter_by(handle='item1').one() print item1.foo # => 'bar' ``` I'm new to SQLAlchemy and I found this in the documentation (http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables): ``` j = join(items, item_attributes, items.c.item_id == item_attributes.c.item_id). \ join(attributes, item_attributes.c.attribute_id == attributes.c.attribute_id) mapper(Item, j, properties={ 'item_id': [items.c.item_id, item_attributes.c.item_id], 'attribute_id': [item_attributes.c.attribute_id, attributes.c.attribute_id], }) ``` It only adds item_id and attribute_id to Item and its not possible to add attributes to Item object. Is what I'm trying to achieve possible with SQLAlchemy? Is there a better way to structure the database to get the same behaviour of "dynamic columns"?