如何使用 SQLAlchemy 仅选择一列? [英] How can I select only one column using SQLAlchemy?

问题描述

我只想从我的数据库中选择(并返回)一个带有where 子句"的字段.代码是:

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

这失败了，回溯是:

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

如何只选择并返回id"列?我也尝试过其他几种方法，但也都失败了.load_only"是正确的选项吗?

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?

推荐答案

A Query 对象接受要查询的实体作为位置参数，因此只需传递它 User.id:

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

scalar() 返回第一个结果的第一个元素，如果没有找到行，则返回 None.它为多行引发 MultipleResultsFound 异常.

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() 表示仅应加载实体的给定基于列的属性，而所有其他属性(除了标识)将被推迟.如果您稍后确实需要整个 User 模型对象，这可能是要走的路.在这种情况下，您的原始查询必须更改为:

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() 只返回一个结果或引发异常(0 个或 1 个以上的结果).如果您接受 None 作为未找到用户"的有效返回值，请使用 one_or_none().

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

请注意，谓词，即 WHERE 子句的条件，不应作为实体传递给 Query 对象，而应添加 filter().

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

最重要的是，Flask 中的视图期望您返回以下之一:

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

• 一个有效的响应对象
• 一个字符串
• 一个 (response, status, headers) 元组
• 一个 WSGI 应用程序

该机制会将响应对象、字符串或元组以外的任何内容视为 WSGI 应用程序.在您的原始代码中，由于缺少对 scalar() 等的调用，您返回了一个 Query 对象，然后将其视为 WSGI 应用程序.

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.

这篇关于如何使用 SQLAlchemy 仅选择一列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！