SQLAlchemy Falcon Class error

Question

我正在尝试使用Falcon和SQLAlchemy使用postgresql数据库设置API。使用 gunicorn 启动 API

我在尝试访问/v1/users 时收到以下错误。

我尝试浏览SQLAlchemy文档，但找不到任何有效的解决方案。

谢谢

[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>'

我的项目文件

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()

数据库初始化.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/用户.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

我遇到了同样的问题。在我的情况下，它已针对以下更改进行了修复。相反，session.remove(( 使用以下代码。

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

这将起作用。

Answer 2

我们使用 Class 在 python 中为 SQL 创建模型，该模型继承了 ORM 中内置的类，如 SQLAlchemy。在这里，您正在尝试创建User模型。但是由于它需要是一个类才能正常工作，因此您在 user.py 中将其声明为函数。

要使其正常工作，您所要做的就是将def User更改为 class User .

所以user.py看起来像这样：

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