JSON Serialization of SQLAlchemy Models

models_json_serialization.py

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker, relationship, backref
import uuid
import json


engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()


class Serializer(object):
    __public__ = None

    def to_dict(self):
        data = {}
        for key in self.__table__.columns.keys():
            data[key] = getattr(self, key)

        for key in self.__mapper__.relationships.keys():
            if key in self.__public__:
                if self.__mapper__.relationships[key].uselist:
                    data[key] = []
                    for item in getattr(self, key):
                        data[key].append(item.to_dict())
                else:
                    data[key] = getattr(self, key)

        return data


class User(Base, Serializer):
    __tablename__ = 'users'
    __public__ = ['id', 'name', 'fullname', 'password', 'addresses']

    id = Column(String(50), primary_key=True)
    name = Column(String(10))
    fullname = Column(String(50))
    password = Column(String(50))
    addresses = relationship("Address",
                             order_by="Address.id",
                             backref="user",
                             cascade="all, delete, delete-orphan")

    def to_json(self):
        return json.dumps(self.to_dict())


class Address(Base, Serializer):
    __tablename__ = 'addresses'
    __public__ = ['id', 'email_address', 'user_id']

    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))

    def to_json(self):
        return json.dumps(self.to_dict())


Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

data = [User(id=uuid.uuid4().hex, name='kiran', fullname="Kiran Gangadharan", password="helloworld"),
        User(id=uuid.uuid4().hex, name='kevin', fullname="Kevin William David", password="hello"),
        User(id=uuid.uuid4().hex, name='ram', fullname="Ramakanth Dorai", password="world")]

session.add_all(data)     # User value not yet written to DB

isaac = User(id='12ADikolkrd', name='isaac', fullname="isaac", password="designer")
isaac.addresses = [Address(email_address="[email protected]"),
                   Address(email_address="[email protected]")]
session.add(isaac)
session.commit()

b_user = session.query(User).filter_by(name="isaac").first()
print "USER: ", b_user.to_json()