CODEWITHSUNDEEP
pythonjsonsqlalchemy
Alexander Gorbenko
Alexander Gorbenko

Reputation: 510

How to convert sqlalchemy automap query result to json?

I already have database with tables that represent json-reply model

I am using Base = automap_base() to create my classes like this

import simplejson as json
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
from sqlalchemy.orm.attributes import QueryableAttribute


Base = automap_base()

# engine, suppose it has two tables 'user' and 'address' set up
engine = create_engine("mysql+pymysql://test:test@localhost/test")

# reflect the tables
Base.prepare(engine, reflect=True)

session = Session(engine)
Person = Base.classes.person

persons = session.query(Person).all()
for person in persons:
    print (person.name)

s = json.dumps([dict(r) for r in persons.values()])

And i have this error:

Traceback (most recent call last):
  File "c:/Users/rusagm/pythonProjects/sqlalchemytest/send.py", line 24, in <module>
    s = json.dumps([dict(r) for r in persons])
  File "c:/Users/rusagm/pythonProjects/sqlalchemytest/send.py", line 24, in <listcomp>
    s = json.dumps([dict(r) for r in persons])
TypeError: 'person' object is not iterable

I want to create json from my sqlalchemy automapped query. What should i do?

Upvotes: 0

Views: 1339

Answers (1)

s5s
s5s

Reputation: 12134

You could potentially do something like:

import json
from bson import json_util
json.dumps([{i:v for i, v in r.__dict__.items() if i in r.__table__.columns.keys()} for r in persons], default=json_util.default)

But as suggested, a pandas dataframe to_json would be better.

Upvotes: 2

Related Questions