PoC: small eav based on sqlalchemy

eav.py

#!/usr/bin/env python
import datetime
import sqlalchemy

from sqlalchemy import create_engine, Column, Integer, String, Text, Float, Date, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker, relationship, backref

Base = declarative_base()

class Model(Base):
    __tablename__ = 'models'

    id = Column(Integer, primary_key=True)
    name = Column(String(64))

class Attribute(Base):
    __tablename__ = 'attributes'

    id = Column(Integer, primary_key=True)
    key = Column(String(64))
    model_id = Column(Integer, ForeignKey('models.id'))
    model = relationship('Model', backref=backref('attributes'))

    value_str = Column(String(128))
    value_text = Column(Text)
    value_float = Column(Float)
    value_integer = Column(Integer)
    value_date = Column(Date)
    value_datetime = Column(DateTime)

def make_record(session, model, **kwargs):
    instance = Model(name=model)
    session.add(instance)

    for key, value in kwargs.iteritems():
        attribute = Attribute(model=instance)
        attribute.key = key
        if isinstance(value, basestring):
            if len(value) < 128:
                attribute.value_str = value
            else:
                attribute.value_text = value
        elif isinstance(value, float):
            attribute.value_float = value
        elif isinstance(value, int):
            attribute.value_integer = value
        elif isinstance(value, datetime.datetime):
            attribute.value_datetime = value
        elif isinstance(value, datetime.date):
            attribute.value_date = value

        session.add(attribute)

    return instance

def main():
    engine = create_engine('sqlite:///:memory:', echo=False)
    #engine = create_engine('sqlite:////tmp/eav.db', echo=True)
    #engine = create_engine('postgresql+psycopg2://stephane:linux@localhost:5432/eav', echo=False)

    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)


    session = Session()
    for i in range(0, 1000):
        #print i
        instance = make_record(session, 'user', name='Stephane Wirtel', birthday=datetime.date(1980, 9, 15))
        instance = make_record(session, 'link', url='http://www.pycon.fr', name='PyConFR 2012')
        instance = make_record(session, 'link', url='http://wirtel.be', name='Wirtel Stephane')
        instance = make_record(session, 'article', body='Hello', subject='Subject of your article', published_on=datetime.datetime.today())
        #session.commit()

    session.commit()

if __name__ == '__main__':
    main()
    pass