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?

Ilja Everilä · Answer 1 · 2019-01-12T07:46:47.627

96

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.

edited Jan 12 '19 at 07:46

answered May 10 '16 at 09:21

Ilja Everilä

50,538
7
126
127

Thank you. I am afraid neither of the options you proses works. The first returns error: TypeError: user() takes no arguments (2 given), and the second: TypeError: 'User' object is not callable. I am stuck! – user1903663 May 10 '16 at 09:37
That is actually a separate error. Your view is expecting you to return a callable of some kind, it seems. I have 0 experience with Flask in general, so you have to consult the documentation for what your view has to return. – Ilja Everilä May 10 '16 at 09:39
As to how you get `TypeError: user() takes no arguments (2 given)` I cannot say a thing without seeing what you've actually done. It would seem that the view function is passed arguments though it expects none. – Ilja Everilä May 10 '16 at 09:42
Actually, read http://flask.pocoo.org/docs/0.10/quickstart/#about-responses and especially how it handles anything but a response object, a string or a tuple: "If none of that works, Flask will assume the return value is a valid WSGI application and convert that into a response object.". So I'm guessing your `User.id` is an integer and Flask is trying to treat that as a full blown WSGI application. Try `return str(user_id)`. – Ilja Everilä May 10 '16 at 09:44
very interesting, thank you! return str(user_id) works. Fascinating insight. – user1903663 May 10 '16 at 09:51
This explains `TypeError: user() takes no arguments (2 given)` too: you managed to somehow return the view function itself, as it is named `user`. Flask then went on to treat it as WSGI app and passed it `environ` and `start_response`. – Ilja Everilä May 10 '16 at 09:54

score 30 · Answer 2 · answered Sep 10 '20 at 07:29

Note: If you call Model.query(params) you would see the following error

ERROR: 'BaseQuery' object is not callable

You need to call query(params) on session

example:

rows = session.query(Model.name).filter(Model.age >= 20).all()

Alternative:

You can do this using with_entities

example: To fetch names & age where age is greater than 20

rows = Model.query.with_entities(Model.name, Model.age).filter(Model.age >= 20).all()

score 13 · Answer 3 · answered May 10 '16 at 09:24

13

To query the content of one column instead of the entire table flask-sqlalchemy, which I suppose can give you a hint about sqlalchemy itself would work gets you to query the session as you are doing, with a different syntax.

If your table looks something like:

class User(...):
    id = db.Column(db.Integer, primary_key=True)
    ...

You can query it with:

user_ids = session.query(User.id)
all_ids = user_ids.all()

This returns a list of all User Ids.

answered May 10 '16 at 09:24

ciacicode

708
1
5
13

1

Just a note that in regular (not flask) sqlalchemy, this returns a list of one-length tuples, which need to be unpacked like [id for id, in session.query(User.id)] – Willow Jul 29 '22 at 17:45

score 11 · Accepted Answer · answered May 10 '16 at 09:21

11

You would have to do something along these lines:

session.query(Table.col1).filter(User.name=='Name')

answered May 10 '16 at 09:21

Tkingovr

1,390
16
33

thank you for your time. Intuitively that looks like it should work I agree. But error is: TypeError: 'Query' object is not callable – user1903663 May 10 '16 at 09:39
@user1903663 check my answer on this: https://stackoverflow.com/a/63824820/5466534 – Sahith Vibudhi Sep 10 '20 at 07:30

score 2 · Answer 5 · answered May 10 '16 at 09:52

2

The problem has nothing to do with queries and db connection. Inside @application.route("/user", methods=['GET', 'POST']) statement you return a user_id which is a result of a select from database. In Flask you should return something appropriate.

answered May 10 '16 at 09:52

Alexey Smirnov

2,573
14
20

that is actually true, i replied to the question but looking at the error stack with werkzeug erroring alexey is spot on. – ciacicode May 10 '16 at 10:00

How can I select only one column using SQLAlchemy?

5 Answers5

Linked