python - SQLAlchemy execute() 将 ResultProxy 作为元组返回，而不是字典

Question

我有以下代码:

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 为这个查询返回元组，而不是字典？我想使用以下样式来访问查询结果:

print site.ci
# u'375'

Answer 1

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

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

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 现在包含字典格式的查询结果。

至于这在 SQL Alchemy 中是如何工作的:

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

这是一个单行替代方案:

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

来自文档:

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

Proxy values from a single cursor row.

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) Return True if this RowProxy contains the given key.

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

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