Connexion: Any elegant way to get SQLAlchemy result objects JSONencoded?

[basisklasse]

Description

I would like to get Custom Objects JSON encoded by the connexion framework automatically. ATM i am trying to fiddle around with CustomJSONEncoders, but i don't find the right spot to hook them into connexion.

Expected bahaviour

A call to e.g.:
INFO:werkzeug:127.0.0.1 - - [20/May/2016 19:33:46] "GET /v0/floor/1/rooms HTTP/1.1" 500 -

pointing to:

def get_floor_rooms(floor_id):
rooms = FloorRoomListObject.query.filter_by(floor_id=floor_id)
return [room for room in rooms]

should directly work out of the box without raising the error below

Actual behaviour

Traceback (most recent call last):
File "/Library/Python/2.7/site-packages/flask/app.py", line 1836, in call
return self.wsgi_app(environ, start_response)
File "/Library/Python/2.7/site-packages/flask/app.py", line 1820, in wsgi_app
response = self.make_response(self.handle_exception(e))
File "/Library/Python/2.7/site-packages/flask/app.py", line 1403, in handle_exception
reraise(exc_type, exc_value, tb)
File "/Library/Python/2.7/site-packages/flask/app.py", line 1817, in wsgi_app
response = self.full_dispatch_request()
File "/Library/Python/2.7/site-packages/flask/app.py", line 1477, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/Library/Python/2.7/site-packages/flask/app.py", line 1381, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/Library/Python/2.7/site-packages/flask/app.py", line 1475, in full_dispatch_request
rv = self.dispatch_request()
File "/Library/Python/2.7/site-packages/flask/app.py", line 1461, in dispatch_request
return self.view_functionsrule.endpoint
File "/Library/Python/2.7/site-packages/connexion/decorators/validation.py", line 251, in wrapper
response = function(_args, _kwargs)
File "/Library/Python/2.7/site-packages/connexion/decorators/produces.py", line 128, in wrapper
data = [json.dumps(data, indent=2), '\n']
File "/Library/Python/2.7/site-packages/flask/json.py", line 126, in dumps
rv = _json.dumps(obj, *kwargs)
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/__init__.py", line 250, in dumps
sort_keys=sort_keys, **kw).encode(obj)
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/encoder.py", line 209, in encode
chunks = list(chunks)
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/encoder.py", line 431, in _iterencode
for chunk in _iterencode_list(o, _current_indent_level):
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/encoder.py", line 332, in _iterencode_list
for chunk in chunks:
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/encoder.py", line 442, in _iterencode
o = _default(o)
File "/Library/Python/2.7/site-packages/connexion/decorators/produces.py", line 46, in default
return json.JSONEncoder.default(self, o)
File "/Library/Python/2.7/site-packages/flask/json.py", line 83, in default
return _json.JSONEncoder.default(self, o)
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/encoder.py", line 184, in default
raise TypeError(repr(o) + " is not JSON serializable")

Steps to reproduce

Get a result set from postgres using SQLAlchemy and try to return it in the view.

Additional info:

Output of the commands:

• python --version
  Python 2.7.10
• pip show connexion | grep "^Version\:"
  Version: 1.0.97

[basisklasse]

Ok found that in the official doc...will try :)

Connexion allows you to customize the JSONEncoder class in the Flask app instance json_encoder (connexion.App:app). If you wanna reuse the Connexion’s date-time sezialization, inherit your custom encoder from connexion.decorators.produces.JSONEncoder.

[nmusatti]

How are your room objects defined?

[basisklasse]

Pretty Straight forward i would say ;)

from sqlalchemy import Column, Integer, String
from database import Base

class FloorRoomListObject(Base):
tablename = 'rooms'
id = Column(Integer, primary_key=True)
floor_id = Column(Integer)
name = Column(String())
frontend_css_position_x = Column(Integer)
frontend_css_position_y = Column(Integer)
frontend_css_position_h = Column(Integer)
frontend_css_position_w = Column(Integer)

def __init__(self, floor_id=None, name=None, frontend_css_position_x=None, frontend_css_position_y=None, frontend_css_position_h=None, frontend_css_position_w=None ):
    self.floor_id = floor_id
    self.name = name
    self.frontend_css_position_x = frontend_css_position_x
    self.frontend_css_position_y = frontend_css_position_y
    self.frontend_css_position_h = frontend_css_position_h
    self.frontend_css_position_w = frontend_css_position_w

def __repr__(self):
    return self

but i replaced the return value in repr already with dicts and stringified dicts. But sicne i am totally new to python and flask/connection and i didn't have time to look into the inner workings of serialization etc. But i will try the documented approach...

[nmusatti]

I'm really a beginner myself with Flask, Connexion and API's in general. Working with SQLite I found that, as long as you're using numbers and strings only, it's sufficient to copy dict to repr without any attribute whose name begins with underscore.

[basisklasse]

Hmm, did that...but did't work for me...Maybe the underscores in the names 'frontend_css_position_x' are the problem here...

[basisklasse]

will camelcase everything and lets see ;)

[hjacobs]

@basisklasse if you have a minimal example with SQLAlchemy working we could add it to our Connexion examples folder, I think it would be useful for many people :smile:

[basisklasse]

Ok, i think i have a nice solution.

I added the flask-jsontools extension:
from flask.ext.jsontools import DynamicJSONEncoder

set up SQLAlchemy to use declarative extension with Serializable Base

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from flask.ext.jsontools import JsonSerializableBase

engine = create_engine('postgresql://xxx:yyy@localhost:5432/mydatabase, convert_unicode=True) 
db_session = scoped_session(sessionmaker(bind=engine))

Base = declarative_base(cls=(JsonSerializableBase,))
Base.query = db_session.query_property()

And the models just return themselves as repr:
def __repr__(self): return self

Works like a charm for normal fields, but you have to add own encoding for specific datetime etc stuff...

[nmusatti]

@hjacobs I created a variant of your own connexion-example project that uses SQLAlchemy. Note that I really am a beginner with both SQLAlchemy and Connexion and it is still very much work in progress, but you may check it out here.

[hjacobs]

@nmusatti cool, thanks! I just checked it out :smile: --- can you do a PR to put your example in examples/sqlalchemy ?

[nmusatti]

@hjacobs Certainly. I'll just clean it up and streamline it a bit.

[hjacobs]

We now have a simple SQLAlchemy example: https://github.com/zalando/connexion/tree/master/examples/sqlalchemy

Thanks to @nmusatti :smile:

[thygesteffensen]

Ohh no, and the link is dead... @hjacobs

[nmusatti]

The current link is https://github.com/zalando/connexion/tree/master/examples/swagger2/sqlalchemy