SQLAlchemy execute() 将 ResultProxy 作为元组返回，而不是 dict [英] SQLAlchemy execute() return ResultProxy as Tuple, not dict

问题描述

我有以下代码:

query = """
SELECT Coalesce((SELECT sp.param_value
                 FROM   sites_params sp
                 WHERE  sp.param_name = 'ci'
                        AND sp.site_id = s.id
                 ORDER  BY sp.id DESC
                 LIMIT  1), -1) AS ci
FROM   sites s
WHERE  s.deleted = 0
       AND s.id = 10 

"""

site = db_session.execute(query)
# print site 
# <sqlalchemy.engine.result.ResultProxy object at 0x033E63D0>

site = db_session.execute(query).fetchone()
print site  # (u'375')
print list(site) # [u'375']

为什么 SQLAlchemy 为这个查询返回元组，而不是字典?我想使用以下样式访问查询结果:

Why does SQLAlchemy return tuples, not dicts, for this query? I want to use the following style to access the results of the query:

print site.ci
# u'375'

推荐答案

这是一个老问题，但今天仍然适用.让 SQL Alchemy 返回字典非常有用，尤其是在使用返回 JSON 的基于 RESTful 的 API 时.

This is an old question, but still relevant today. Getting SQL Alchemy to return a dictionary is very useful, especially when working with RESTful based APIs that return JSON.

这是我在 Python 3 中使用 db_session 的方法:

Here is how I did it using the db_session in Python 3:

resultproxy = db_session.execute(query)

d, a = {}, []
for rowproxy in resultproxy:
    # rowproxy.items() returns an array like [(key0, value0), (key1, value1)]
    for column, value in rowproxy.items():
        # build up the dictionary
        d = {**d, **{column: value}}
    a.append(d)

最终结果是数组 a 现在包含字典格式的查询结果.

The end result is that the array a now contains your query results in dictionary format.

至于它在 SQL Alchemy 中的工作原理:

As for how this works in SQL Alchemy:

• db_session.execute(query) 返回一个 ResultProxy 对象
• ResultProxy 对象由 RowProxy 对象组成
• RowProxy 对象有一个 .items() 方法，该方法返回行中所有项目的键、值元组，可以解包为 key, value 在 for 操作中.

• Thedb_session.execute(query) returns a ResultProxy object
• The ResultProxy object is made up of RowProxy objects
• The RowProxy object has an .items() method that returns key, value tuples of all the items in the row, which can be unpacked as key, value in a for operation.

这里有一个单线替代方案:

And here a one-liner alternative:

[{column: value for column, value in rowproxy.items()} for rowproxy in resultproxy]

来自文档:

class sqlalchemy.engine.RowProxy(parent, row, processor, keymap)

class sqlalchemy.engine.RowProxy(parent, row, processors, keymap)

来自单个游标行的代理值.

Proxy values from a single cursor row.

主要遵循有序字典"行为，将结果值映射到基于字符串的列名、结果在行中的整数位置，以及可以映射到产生此结果集的原始列的列实例(对于与构造的 SQL 表达式相对应的结果).

Mostly follows "ordered dictionary" behavior, mapping result values to the string-based column name, the integer position of the result in the row, as well as Column instances which can be mapped to the original Columns that produced this result set (for results that correspond to constructed SQL expressions).

has_key(key)如果此 RowProxy 包含给定的键，则返回 True.

has_key(key) Return True if this RowProxy contains the given key.

物品()返回元组列表，每个元组包含一个键/值对.

items() Return a list of tuples, each tuple containing a key/value pair.

键()将键列表作为由此 RowProxy 表示的字符串返回.

keys() Return the list of keys as strings represented by this RowProxy.

链接:http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.RowProxy.items

这篇关于SQLAlchemy execute() 将 ResultProxy 作为元组返回，而不是 dict的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！