How can I combine the awesomness of SQLAlchemy and EAV DB schemas?

Question

Aight ya'll, I gotta lay a Python question on you...

I've been doing some work with Pylons recently and quite like the SQLAlchemy model for database interaction. There's one section of my website though which I think could benefit from an EAV schema.

Using this as my table example:

id | userid | type   | value
---+--------+--------|------------
1  |  1     |  phone | 111 111 111
---+--------+--------|------------
2  |  1     |  age   | 40

I can manually run queries like the following to extract and update data:

SELECT value FROM table WHERE userid=1 AND type='phone'
UPDATE table SET value=41 WHERE userid=1 AND type='age'

That's easy and works... But manually constructing queries is for pussies. I want to use SQLAlchemy to create my table model and let it do all the leg work.

If I were to use a standard schema where each type had it's own column, I could do the following:

class People(Base):

   __tablename__ = 'people'

   id      = Column(Integer, primary_key=True)
   userid  = Column(Integer, ForeignKey('users.id'))
   phone   = Column(Unicode(40))
   age     = Column(Integer)

Then I could pull out the data using:

data = Session.query(People).filter_by(id=1).first()
print data.age

I want to be able to do the same for my EAV schema. So basically, I need a way to extend SQLAlchemy and tell it that when I call data.age that in fact means, I want SELECT value FROM table WHERE id=1 AND type='age'.

Is this doable? Or will I be forced to rape my code with manually issued queries?

Answer 1

Have a look at the examples for vertical attribute mapping (the examples themselves are in the hg repository). I think this is more or less what you're after. The examples present a dict-like interface rather than attributes as in your example (probably better for arbitrary metadata keys, rather than a few specific attributes).

If you'd rather map each attribute separately: stuff in the docs that might be of interest:

• sql expressions as mapped attributes: how you can indeed map an attribute to an arbitrary sql expression (read-only)
• changing attribute behaviour, esp. using descriptors and custom comparators: this boils down to just using normal python properties for your attributes, and doing whatever you need on get/set + optionally prescribing how comparison with other values (for queries) needs to work.
• associationproxy: basically provides a simpler view on a relation. For example, in your case, you could make _age a relation to your KeyValue (or whatever you want to call it), using a custom join condition (not only userid but also specifying the type "age"), and using uselist=False (because there is only one age per user, you want a single value, not a list). You could then use age = association_proxy('_age', 'value') to make it "show" only the value, rather than an entire KeyValue object.

I suppose I'd go with either something based on the vertical attribute mapping example, or with associationproxy/