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 :-)‘