2

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

AndroidDev
  • 15,993
  • 29
  • 85
  • 119
  • Possible duplicate of [How to serialize SqlAlchemy result to JSON?](https://stackoverflow.com/questions/5022066/how-to-serialize-sqlalchemy-result-to-json) – Shihe Zhang Nov 15 '18 at 02:23

2 Answers2

4

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 ])
Community
  • 1
  • 1
Austin Phillips
  • 15,228
  • 2
  • 51
  • 50
  • Could you please show __dict__ method. I am really new to python ? – AndroidDev Oct 01 '13 at 07:40
  • @BadLuckBrian Answer edited. Sorry, I had the wrong hyperlink to the SO answer. – Austin Phillips Oct 01 '13 at 07:44
  • print json.dumps([ row2dict(row) for row in query.all() ]) File "alc-test.py", line 28, in row2dict for column in row.__table__.columns: AttributeError: 'KeyedTuple' object has no attribute '__table__' – AndroidDev Oct 01 '13 at 07:51
  • @BadLuckBrian I haven't got access to a machine to test this at present, but perhaps you can use _asdict() as per the latest edit? Basically, the idea is for you to convert a row to dictionary which can then be understood by `json.dumps`. – Austin Phillips Oct 01 '13 at 08:31
2

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.

SilentDirge
  • 827
  • 9
  • 17