new programmmmmer new programmmmmer - 1 month ago 28
JSON Question

Return database information using json in Flask

I can't figure out how to return the information from my database when using GET method because I keep getting errors in my return statements. I keep getting errors like object not subscriptable or stuff to do with dicts.

I tried.

return jsonify({'developers': User.query.all()})


I got the error TypeError: <main.User object at 0x038FC9D0> is not JSON serializable

When i try

return json.dumps(tuple[User.query.all()])


I get the error. TypeError: 'type' object is not subscriptable

from flask import Flask, jsonify,json
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
app.config.from_pyfile('Config.py')
db = SQLAlchemy(app)

class User(db.Model):
User_ID = db.Column(db.Integer, primary_key = True)
firstName = db.Column(db.String(20))
lastName = db.Column(db.String(20))


def __init__(self,firstName, lastName):
self.firstName = firstName
self.lastName = lastName
db.create_all()

@app.route('/', methods = ['GET'])
def index():
return json.dumps(tuple[User.query.all()])

if __name__ == '__main__':
app.run()

Answer

There are a couple problems. For one, SQLAlchemy db.Model types are not json serializable. That fact is covered up when you try to use tuple[...] as another error, but even if you fix that to tuple(...) you will get back to the original issue.

This should work, using a mixin that will json serialize for you. I think this will still have issues with datetime objects, but you can modify the as_dict method to handle that.

class JsonModel(object):
    def as_dict(self):
       return {c.name: getattr(self, c.name) for c in self.__table__.columns}

class User(db.Model, JsonModel):
    User_ID = db.Column(db.Integer, primary_key = True)
    firstName = db.Column(db.String(20))
    lastName = db.Column(db.String(20))


    def __init__(self,firstName, lastName):
       self.firstName = firstName
       self.lastName = lastName
       db.create_all()

@app.route('/', methods = ['GET'])
def index():
    return json.dumps([u.as_dict() for u in User.query.all()])

Tips:

  • Turning on app.debug = True while developing is also a good idea.
  • Running db.create_all in your init may be a bad idea.
  • Using jsonify is nice because it handles the Content-Type header as well.

> kurl -i http://localhost:5000
HTTP/1.0 200 OK
Content-Type: application/json
Content-Length: 102
Server: Werkzeug/0.8.3 Python/2.7.10
Date: Wed, 02 Nov 2016 05:28:42 GMT

{
  "users": [
    {
      "lastName": "Berry",
      "User_ID": 1,
      "firstName": "Sean"
    }
  ]
}