python - How can I select only one column using SQLAlchemy?

Question

I want to select (and return) one field only from my database with a "where clause". The code is:

from sqlalchemy.orm import load_only
    @application.route("/user", methods=['GET', 'POST'])
    def user():
        user_id = session.query(User, User.validation==request.cookies.get("validation")).options(load_only("id"))
        session.commit()
        return user_id

This fails and the traceback is:

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 1478, in full_dispatch_request
response = self.make_response(rv)
File "/Library/Python/2.7/site-packages/flask/app.py", line 1577, in make_response
rv = self.response_class.force_type(rv, request.environ)
File "/Library/Python/2.7/site-packages/werkzeug/wrappers.py", line 841, in force_type
response = BaseResponse(*_run_wsgi_app(response, environ))
File "/Library/Python/2.7/site-packages/werkzeug/wrappers.py", line 57, in _run_wsgi_app
return _run_wsgi_app(*args)
File "/Library/Python/2.7/site-packages/werkzeug/test.py", line 867, in run_wsgi_app
app_rv = app(environ, start_response)
TypeError: 'Query' object is not callable

How can I select and return just the "id" column? I have tried several other ways too but also with failure. Is "load_only" the correct option?

Answer 1

A Query object accepts entities to query as positional arguments, so just pass it User.id:

user_id = session.query(User.id).
        filter(User.validation == request.cookies.get("validation")).
        scalar()

scalar() returns the first element of the first result or None, if no rows were found. It raises MultipleResultsFound exception for multiple rows.

load_only() indicates that only the given column-based attributes of an entity should be loaded and all others, expect the identity, will be deferred. If you do need the whole User model object later, this can be the way to go. In that case your original query has to change to:

user = session.query(User).
        filter(User.validation == request.cookies.get("validation")).
        options(load_only("id")).
        one()

one() returns exactly one result or raises an exception (0 or more than 1 result). If you accept None as a valid return value for "no user found", use one_or_none().

Note that predicates, the criteria of the WHERE clause, should not be passed to the Query object as entities, but added with filter().

To top it off, views in Flask expect that you return one of:

• a valid response object
• a string
• a (response, status, headers) tuple
• a WSGI application

The machinery will treat anything other than a response object, a string or a tuple as a WSGI application. In your original code you returned a Query object because of the missing call to scalar() or such and this was then treated as a WSGI app.