Flask + SQLAlchemy result to JSON

Question

I'm new to Flask and SQLAlchemy and I have spent so much time trying to get my database query to present as JSON using a Flask url (not flask_restful).

I thought I had it working by using n0nSmoker/SQLAlchemy-serializer. It worked using a simple query using this syntax selecting my Trade SQLAlchemy class:

trades = Trade.query.all()

I was able to return trades.to_dict() with no problem.

However because I need to do a group_by, I need to use the more flexible syntax of db.session.query(Trade, Trade.exchange, Trade.trader....... etc this method stopped working.

I have looked at other options like the inspection system but it seems a bit beyond my skill level. Plus it seems like some of the methods like _asdict() aren't available at the row level.

I'm a bit lost and not sure how to proceed. Any help would be hugely appreciated.

Edit: More code requested: top of app.py

from flask import Flask
from flask_restful import Resource, Api
from models import *
from flask_sqlalchemy import SQLAlchemy
from sqlathanor import FlaskBaseModel, initialize_flask_sqlathanor

app = Flask(__name__)
app.config.from_pyfile('config.py')

db = SQLAlchemy(model_class = FlaskBaseModel)
db = initialize_flask_sqlathanor(db)

class Trades(Resource):
    def get(self):
        return AllTrades()

api.add_resource(Trades,'/')


if __name__ == '__main__':
    app.run(host="0.0.0.0")

from models.py

from app import db

class Trade(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    exchange = db.Column(db.String(255))
    trader = db.Column(db.String(255))
    symbol = db.Column(db.String(255))
    coin = db.Column(db.String(255))
    order_id = db.Column(db.String(255))
    price = db.Column(db.String(255))
    qty = db.Column(db.String(255))
    fee = db.Column(db.String(255))
    fee_currency = db.Column(db.String(255))
    order_time = db.Column(db.DateTime(timezone=False))
    side = db.Column(db.String(255))
    maker_taker = db.Column(db.String(255))


def AllTrades():
    #t = Trade
    # trades = db.session.query(
    #   t,
    #   t.exchange,
    #   t.trader,
    #   t.symbol,
    #   t.coin,
    #   db.func.sum(t.qty),
    #   db.func.sum(t.fee).label("fee"),
    #   t.fee_currency,
    #   t.order_id,
    #   t.order_time,
    #   t.maker_taker
    # ).group_by(t.order_id).all()

    trades = Trade.query.all()
    trades = trades.to_json()

    return trades

Full stack trace

    Traceback (most recent call last):
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1997, in __call__
    return self.wsgi_app(environ, start_response)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1985, in wsgi_app
    response = self.handle_exception(e)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_restful/__init__.py", line 273, in error_router
    return original_handler(e)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1540, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/_compat.py", line 32, in reraise
    raise value.with_traceback(tb)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_restful/__init__.py", line 273, in error_router
    return original_handler(e)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/_compat.py", line 32, in reraise
    raise value.with_traceback(tb)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_restful/__init__.py", line 480, in wrapper
    resp = resource(*args, **kwargs)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask/views.py", line 84, in view
    return self.dispatch_request(*args, **kwargs)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_restful/__init__.py", line 595, in dispatch_request
    resp = meth(*args, **kwargs)
  File "/Users/foo/Sites/Reporting_dev/app.py", line 18, in get
    return AllTrades()
  File "/Users/foo/Sites/Reporting_dev/models.py", line 42, in AllTrades
    trades = Trade.query.all()
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 514, in __get__
    return type.query_class(mapper, session=self.sa.session())
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/sqlalchemy/orm/scoping.py", line 74, in __call__
    return self.registry()
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/sqlalchemy/util/_collections.py", line 1001, in __call__
    return self.registry.setdefault(key, self.createfunc())
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2939, in __call__
    return self.class_(**local_kw)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 143, in __init__
    bind = options.pop('bind', None) or db.engine
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 877, in engine
    return self.get_engine()
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 887, in get_engine
    state = get_state(app)
  File "/Users/foo/anaconda3/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py", line 570, in get_state
    'The sqlalchemy extension was not registered to the current ' \
AssertionError: The sqlalchemy extension was not registered to the current application.  Please make sure to call init_app() first.

I recently read this answer to another similar question. I haven’t used the library but thought I’d bring it to your attention as sounds like it might help. — SuperShoot
– SuperShoot, Commented Jul 23, 2018 at 12:32
There’s plenty of discussion about result serialisation in that question aside from the answer I linked to as well. — SuperShoot
– SuperShoot, Commented Jul 23, 2018 at 12:33
I use Flask-Marshmallow for this and it works really nicely. Here's a simple project where I've used it - github.com/tim-thompson/bonsai-trees — Tim Thompson
– Tim Thompson, Commented Jul 23, 2018 at 17:34

Chris Modzelewski · Accepted Answer · 2018-07-23 21:14:52Z

2

I’d suggest you take a look at a new library I released a week or two ago called SQLAthanor.

It adds simple serialization support to SQLAlchemy models, and if you are also using Flask-SQLAlchemy, it works seamlessly with it as well.

Basically, the way it works is this:

You define your Trade model the way you’re (presumably) already doing. The only difference is you import declarative_base or your base model from SQLAthanor instead of from SQLAlchemy (or Flask-SQLAlchemy).
You configure your serialization rules when defining your model. This basically lets you say “when I serialize a Trade to JSON, include attributes X, Y, and Z but not P, D, and Q”.
Execute your query however you would normally. You’ll get back a list of Trade instances, presumably. You can now serialize each instance to JSON by calling <instance>.to_json().

It’s worth mentioning that SQLAthanor:

supports serialization and de-serialization to/from JSON, CSV, YAML, and Python dict
supports serialization/de-serialization of Column attributes, relationships, hybrid properties, association proxies and regular Python @properties
supports custom pre/post processing functions for serialization/de-serialization at the attribute level (for validation or type coercion)
supports enabling/disabling serialization and de-serialization at the attribute level for particular formats (for example, accept passwords inbound, but never include them outbound)

It might be worth taking a look. Here’s a link to the (I hope comprehensive) documentation: http://sqlathanor.readthedocs.io/en/latest/

Hope this helps, and if you have questions or need help getting it working, just let me know (or post questions to SO with the tag sqlathanor)

answered Jul 23, 2018 at 21:14

Chris Modzelewski

1,65812 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Chris Modzelewski Over a year ago

Ack! Looks like a typo in the docs. The function you want to import/use is initialize_flask_sqlathanor rather than initialize_sqlathanor. Sorry for the confusion - I’ll fix the docs in the next patch release (probably later today).

Chris Modzelewski Over a year ago

No worries! Best of luck, and if you run into trouble, please just let me know.

Chris Modzelewski Over a year ago

Can you post a more complete stack trace? Also, it would be helpful if you posted the code you're using to both initialize Flask-SQLAlchemy, and initialize SQLAthanor.That'll help us figure out if the issue is occurring where you are initializing SQLAthanor, or if it's elsewhere in your application (for example, where Flask-SQLAlchemy is getting initialized).

Chris Modzelewski Over a year ago

Gotcha. I believe the issue has to do with how you are initializing db in the first place. With the initialization pattern you're using, you need to use: db = SQLAlchemy(app, model_class = FlaskBaseModel). This will register Flask-SQLAlchemy with your Flask application. For more information, take a look at: flask-sqlalchemy.pocoo.org/2.3/quickstart

Chris Modzelewski Over a year ago

Also, with today's (significant) new release, I updated SQLAthanor's docs to show the more complete pattern you're using.

|

Collectives™ on Stack Overflow

Flask + SQLAlchemy result to JSON

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related