SQLAlchemy Falcon Class error

Question

I am trying to set up an API using Falcon and SQLAlchemy using a postgresql database. Using gunicorn to start the API

I am getting the following error when trying to access /v1/users.

I tried to browse the SQLAlchemy documentation but could not find any working solution.

Thanks

[2017-03-15 10:20:26 +0100] [20516] [ERROR] Error handling request /v1/users
    Traceback (most recent call last):
      File "/Users/juliencourtes/Documents/projects/FalconAPI/venv/lib/python3.6/site-packages/gunicorn/workers/sync.py", line 135, in handle
        self.handle_request(listener, req, client, addr)
      File "/Users/juliencourtes/Documents/projects/FalconAPI/venv/lib/python3.6/site-packages/gunicorn/workers/sync.py", line 176, in handle_request
        respiter = self.wsgi(environ, resp.start_response)
      File "/Users/juliencourtes/Documents/projects/FalconAPI/venv/lib/python3.6/site-packages/falcon/api.py", line 209, in __call__
        responder(req, resp, **params)
      File "/Users/juliencourtes/Documents/projects/FalconAPI/app/api/v1/users.py", line 50, in on_get
        users = session.query(User).all()
      File "/Users/juliencourtes/Documents/projects/FalconAPI/venv/lib/python3.6/site-packages/sqlalchemy/orm/scoping.py", line 157, in do
        return getattr(self.registry(), name)(*args, **kwargs)
      File "/Users/juliencourtes/Documents/projects/FalconAPI/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1330, in query
        return self._query_cls(entities, self, **kwargs)
      File "/Users/juliencourtes/Documents/projects/FalconAPI/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 139, in __init__
        self._set_entities(entities)
      File "/Users/juliencourtes/Documents/projects/FalconAPI/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 148, in _set_entities
        entity_wrapper(self, ent)
      File "/Users/juliencourtes/Documents/projects/FalconAPI/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3947, in __init__
        "expected - got '%r'" % (column, )
    sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped entity expected - got '<function User at 0x1040216a8>'

My projects files

main.py

import falcon
from app.api.v1 import users
from app.middleware import DatabaseSessionManager
from app.database import db_session, init_session
from app.api.common import base

class App(falcon.API):

    def __init__(self,*args, **kwargs):
        super(App, self).__init__(*args, **kwargs)
        self.add_route('/',base.BaseResource())
        self.add_route('/v1/users',users.Collection())


init_session()
mdlw = [DatabaseSessionManager(db_session)]
application = App(middleware=mdlw)

session.py

import sqlalchemy.orm.scoping as scoping
from sqlalchemy.exc import SQLAlchemyError

from app import config

class DatabaseSessionManager(object):
    def __init__(self, db_session):
        self._session_factory = db_session
        self._scoped = isinstance(db_session, scoping.ScopedSession)

    def process_request(self, req, res, resource=None):
        req.context['session'] = self._session_factory


    def process_response(self, req, res, resource=None):
        session = req.context['session']

        if config.DB_AUTOCOMMIT:
            try:
                session.commit()
            except SQLAlchemyError as ex:
                session.rollback()


        if self._scoped:
            session.remove()
        else:
            session.close()

database init.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
import psycopg2
from app import config


db_session = scoped_session(sessionmaker())
engine = create_engine('postgresql+psycopg2://xxxxx@localhost/falcon_api')


def init_session():
    db_session.configure(bind=engine)

user.py

from sqlalchemy import Column
from sqlalchemy import String, Integer,Text
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

def User(Base):
    __tablename__ = 'user'

    id = Column('id', UUID, primary_key=True)
    firstname = Column('firstname', String)
    lastname = Column('lastname', String)

    #It tells python how to print the class, used for debugging
    def __repr__(self):
        return "<User(id='%s', name='%s', lastname='%s')>"% \
            (self.id, self.firstname, self.lastname)

    def __init__(self, id , firstname, lastname):
        self.id = id
        self.firstname = firstname
        self.lastname = lastname

api/v1/users.py

import falcon
import json
from sqlalchemy.ext.declarative import declarative_base
try:
    from collections import OrderedDict
except ImportError:
    OrderedDict = dict
from app.models import User
class Collection():
    """
    Handle for endpoint: /v1/users
    """

    def to_json(self, body_dict):
        return json.dumps(body_dict)


    def on_error(self, resp, error=None):
        resp.status = error['status']
        meta = OrderedDict()
        meta['code'] = error['code']
        meta['message'] = error['message']

        obj = OrderedDict()
        obj['meta'] = meta
        resp.body = self.to_json(obj)

    def on_success(self, resp, data=None):
        resp.status = falcon.HTTP_200
        meta = OrderedDict()
        meta['code'] = 200
        meta['message'] = 'OK'

        obj = OrderedDict()
        obj['meta'] = meta
        obj['data'] = data
        resp.body = self.to_json(obj)


    def on_get(self, req, resp):
        session = req.context['session']

        #Bugging here
        users = session.query(User).all()

Answer 1

I have faced same problem..It got fixed in my case for the following changes.. instead session.remove() use the following code.

if self._scoped:
     self._session_factory.remove()

This will work.

Answer 2

We create models for SQL in python using Class which inherits built in class from ORM like SQLAlchemy. Here you are trying to create User model. But as it need to be a class to work properly instead you declared it as a function in user.py.

All you have to do to make it work is just change def User to class User.

So user.py will look something like:

...
class User(Base):
    __tablename__ = 'user'
...