Python Sql Alchemy - How to jsonify a class object result from a database query

Question

Basically, I just want to json encode the results of my sql query.

x = db.session.query(User).filter_by(username = request.form['username'], password = request.form['password']).first()
  print vars(x)
return jsonify(x)

raise TypeError(repr(o) + " is not JSON serializable")

TypeError: < User WashingtonGeorge> is not JSON serializable

Here is the result for the print vars(x)

{'_updated': None, 'username': u'WashingtonGeorge', 'password': u'Washington', '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x7fd12a50c8d0>, 'firstname': u'George', 'lastname': u'Washington', '_created': None, 'fullname': u'George Washington', '_id': 1, 'email': u'WashingtonGeorge@yahoo.com'}

You need to serialize the results. look at this answer http://stackoverflow.com/questions/7102754/jsonify-a-sqlalchemy-result-set-in-flask?rq=1 — codegeek, May 12 '14 at 15:10
To JSON serialize , here is my solution: https://stackoverflow.com/questions/20171690/how-to-jsonify-objects-from-sqlalchemy/53519960#53519960 — NicoNing, Dec 22 '18 at 03:42

score 5 · Answer 1 · answered Nov 02 '14 at 22:05

You can use marshallow. Here is the example. define a serializer.py and put it beside your main.py file, and:

in serializer.py

from marshmallow import Serializer

###### USER SERIALIZER #####
class UserSerializer(Serializer):
    class Meta:
        # Fields to expose
        fields = ('username')
        # you can add any other member of class user in fields

#Return the user data in json format
def get_user_serialized(user):
    return UserSerializer(user).data

in your main.py

from .serializers import get_user_serialized
...
...
...

x = db.session.query(User).filter_by(username = request.form['username'], password = request.form['password']).first()

serialized = [get_user_serialized(item) for item in x]
res = jsonify(res=serialized)
return res

Here's a gist that is up to date with marshmallow 1.0: https://gist.github.com/sloria/aecc540db3a95fbbee2f — Steve L, Nov 04 '14 at 23:31

score 1 · Answer 2 · answered May 12 '14 at 04:13

1

With JSON serialize you can do that.

Look this: http://prschmid.blogspot.com/2012/12/json-serializing-sqlalchemy-objects.html

it work for me

answered May 12 '14 at 04:13

yograterol

593
3
7

Nitin Nain · Answer 3 · 2014-05-13T06:42:38.010

0

I assume the variable '_updated' & '_created' in your class User are of type DateTime. Datetime is not serilzable in jsonify. You can just convert your datetime variable into str. i.e. str(some_date_in_datetimeformat) might work.
Also, '_sa_instance_state' which is of type sqlalchemy.orm.state.InstanceState should be serializable.

Update:

jsonify is not able to serialize your object. The best solution is to define a function serialize() inside your class and use it to make the data serializable. So your class becomes,

class User(object):
    def serialize():
        return {
            '_id': 1,
            'username': u'WashingtonGeorge'
            'fullname': u'George Washington'
            # other fields that you need in the json 
        }

then at the place where you want to jsonify the data, just call.

jsonify(x.serialize())

edited May 13 '14 at 06:42

answered May 12 '14 at 15:17

Nitin Nain

5,113
1
37
51

I tried delecting the attributes which contained datetime fields and the method you suggested didn't work. results = db.session.query(User).filter_by(username = request.form['username'], password = request.form['password']).first() del results._created del results._updated print vars(results) return jsonify(results = results) – olleh May 13 '14 at 02:27
I updated my answer. This is a common problem with jsonify, and has been answered before on stackoverflow (See codegeek's comment on your question.) – Nitin Nain May 13 '14 at 06:45

Python Sql Alchemy - How to jsonify a class object result from a database query

3 Answers3