[Python]Transform the entity result to JSON

[Python]Transform the entity result to JSON

This post gives the guide of how to use sqlalchemy's engine, session and query API and change the results into JSON format.

from sqlalchemy import create_engine,Column,Integer,String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from json import dumps
 
def to_json(model):
    """ Returns a JSON representation of an SQLAlchemy-backed object.
    """
    json = {}
    json['fields'] = {}
    json['pk'] = getattr(model, 'id')
 
    for col in model._sa_class_manager.mapper.mapped_table.columns:
        json['fields'][col.name] = getattr(model, col.name)
 
    return dumps([json])

engine = create_engine('sqlite:///sqlite.db',echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base()

class User(Base):
	__tablename__ = 'user'
	id = Column(Integer,primary_key=True)
	name = Column(String)
	
session = Session()

'''
user = User(id=2,name='developer')
session.add(user)
session.commit()
'''
users = session.query(User).order_by(User.id).all()
for user in users:
	print to_json(user)
print '--->Done :-)'