On 08/17/2010 11:32 AM, yota wrote:
> Hello,
>
> sqlalchemy seems to be the proper tool for my needs but I can't figure
> out how to design my project or set the ORM properly.
>
> Let's say, I build a music database, storing tracks and their
> associated metadata in an sql-like database defined as such :
>
> TRACK_TABLE ( ident *, url , duration )
> METADATA_TABLE ( track_ident *, field_name *, field_content )
>
> (track_ident, field_name) being the primary key for METADATA_TABLE...
>
>
> intuitively, a meaningful object for a metadata set would be a
> dictionary-like object. The name and number of fields being unknown in
> advance but stored in the METADATA_TABLE as one row per field.
>
> Here is the question : how may I use the ORM to map
> a table :
> 123 / title / waka waka
> 123 / artist / shakira
> 123 / featuring / my sister
>
> to an object like :
> metadata.title = "waka waka"
> metadata.artist = "shakira"
> metadata.featuring = "my sister"
>
> or a dictionary like :
> metadata = { "title" : "waka waka", "artist" : "shakira",
> "featuring" : "my sister" }
>
> or whatsoever ...
>
> the solution might also be in rewrite of the sql schema :)
>
>
> thanks for your advices
>
SQLAlchemy supports for this via the attribute_mapped_collection and
association_proxy classes:
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection
def create_metadata(field_name, field_content):
return TrackMetaData(field_name=field_name, field_content=field_content)
class Track(Base):
__tablename__ = "track"
id = Column(Integer, primary_key=True)
[...]
field2metadata = relationship(TrackMetaData,
backref="track",
collection_class=attribute_mapped_collection("field_name"))
field2content = association_proxy("field2metadata",
"field_content",
creator=create_metadata)
# I'm only naming this class TrackMetaData to prevent confusion with
sqlalchemy.MetaData.
class TrackMetaData(Base):
__tablename__ = "metadata"
track_id = Column(Integer, ForeignKey("track.id"), primary_key=True)
field_name = Column(Unicode(...), primary_key=True)
field_content = Column(Unicode(...), nullable=False)
In this way you can access field2content like a dictionary:
artist = track.field2content[u"artist"]
track.field2content[u"title"] = u"waka waka"
Note that association_proxy does not supply a comparator yet, so if you
want to join/query on metadata then you need to use the field2metadata
relationship:
# Find all tracks by artist Shakira.
q = Session.query(Track)
q = q.filter(Track.field2metadata.any(and_(TrackMetaData.field_name ==
u"artist",
TrackMetaData.field_content =
u"Shakira")))
tracks = q.all()
# Load all tracks, eagerloading their metadata.
q = Session.query(Track)
q = q.options(joinedload(Track.field2metadata))
tracks = q.all()
-Conor
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
