I am working on a quite large code base that has been implemented using sqlalchemy.ext.declarative
, and I need to add a dict-like property to one of the classes. What I need is the same as in this question, but in a declarative fashion. Can anyone with more knowledge in SQLAlchemy give me an example?
Thanks in advance...
Declarative is just another way of defining things. Virtually you end up with the exact same environment than if you used separated mapping.
Since I answered the other question, I'll try this one as well. Hope it gives more upvotes ;)
Well, first we define the classes
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
from sqlalchemy.ext.declarative import declarative_baseengine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)class Note(Base):__tablename__ = 'notes'id_item = Column(Integer, ForeignKey('items.id'), primary_key=True)name = Column(String(20), primary_key=True)value = Column(String(100))def __init__(self, name, value):self.name = nameself.value = value class Item(Base):__tablename__ = 'items'id = Column(Integer, primary_key=True)name = Column(String(20))description = Column(String(100))_notesdict = relation(Note, collection_class=column_mapped_collection(Note.name))notes = association_proxy('_notesdict', 'value', creator=Note)def __init__(self, name, description=''):self.name = nameself.description = descriptionBase.metadata.create_all()
Now let's make a test:
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
I get:
{u'color': u'orange', u'data': u'none', u'size': u'big'}
Now let's check the notes table...
for note in s.query(Note):print note.id_item, note.name, note.value
I get:
1 color orange
1 data none
1 size big
It works!! :D