How to map one class against multiple tables with SQLAlchemy?
Posted
by tote
on Stack Overflow
See other posts from Stack Overflow
or by tote
Published on 2009-08-19T14:42:39Z
Indexed on
2010/05/23
4:40 UTC
Read the original article
Hit count: 141
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"?
© Stack Overflow or respective owner