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

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 not my preferred approach. 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   = Col开发者_开发技巧umn(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 clutter my code with manually issued queries?

Have a look at the examples for vertical attribute mapping. 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/