CODEWITHSUNDEEP
python
AndroidDev
AndroidDev

Reputation: 16375

Dumping SQLAlchemy output to JSON

I have written a small python script that uses SQLAlchemy to read all records of the db. Here is some of the code

Base=declarative_base()
Session = sessionmaker(bind=engine)
cess=Session()

class Test(Base):
     __tablename__ = 'test'
     my_id = Column(Integer, primary_key=True)
     name = Column(String)
     def __init__(self, id, name):
         self.my_id = id
         self.name = name
     def __repr__(self):
        return "<User('%d','%s')>" % (self.id, self.name)



query= cess.query(Test.my_id, Test.name).order_by(Test.my_id).all()

Now the query object i want to convert to a json string. How can i do this ? using json.dumps(query) throws an exception ?

Kind Regards

Upvotes: 2

Views: 16092

Answers (2)

SilentDirge
SilentDirge

Reputation: 837

How I did it:

fe = SomeClass.query.get(int(1))
fe_dict = fe.__dict__
del fe_dict['_sa_instance_state']
return flask.jsonify(fe_dict)

Basically, given the object you've retrieved, grab the dict for the class instance, remove the sqlalchemy object that can't be json serialized and convert to json. I'm using flask to do this but I think json.dumps() would work the same.

Upvotes: 2

Austin Phillips
Austin Phillips

Reputation: 15746

json.dumps will convert object according to its conversion table.

Since you have rows of type Test, these cannot be directly serialized. Probably the quickest approach is to convert each returned row to a Python dict and then pass this through to json.dumps.

This answer describes how you might go about converting a table row to a dict.

Or, perhaps the _asdict() method from row object can be utilised directly.

query = cess.query(Test.my_id, Test.name).order_by(Test.my_id).all()

json.dumps([ row._asdict() for row in query ])

An alternative might be to access the __dict__ attribute directly on each row, although you should check the output to ensure that there are no internal state variables in row.__dict__.

query = cess.query(Test.my_id, Test.name).order_by(Test.my_id).all()

json.dumps([ row.__dict__ for row in query ])

Upvotes: 4

Related Questions