Python SQLAlchemy execute() 将 ResultProxy 作为元组返回，而不是 dict

SQLAlchemy execute() return ResultProxy as Tuple, not dict

提问by user3128993

I have the following code:

我有以下代码：

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']

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

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

print site.ci
# u'375'

回答by hamx0r

This may help solve the OPs question. I think the problem he was having is that the row object only contained column values, but not the column names themselves, as is the case with ORM queries where the results have a dictattribute with both keys and values.

这可能有助于解决 OP 问题。我认为他遇到的问题是行对象只包含列值，而不包含列名本身，就像 ORM 查询的情况一样，其中结果具有包含键和值的dict属性。

python sqlalchemy get column names dynamically?

python sqlalchemy 动态获取列名？

回答by sebastian

Did you take a look at the ResultProxydocs? It describes exactly what @Gryphius and @Syed Habib M suggest, namely to use site['ci'].

您是否查看了ResultProxy文档？它准确地描述了@Gryphius 和@Syed Habib M 的建议，即使用site['ci'].

The ResultProxydoes not "return a tuple" as you claim - it is (not surprisingly) a proxy that behaves (e.g. prints) like a tuple but also supports dictionary-like access:

ResultProxy并不像您声称的那样“返回元组” - 它是（不足为奇）一个代理，其行为（例如打印）像元组，但也支持类似字典的访问：

From the docs:

从文档：

Individual columns may be accessed by their integer position, case-insensitive column name, or by schema.Column object. e.g.:

row = fetchone()

col1 = row[0] # access via integer position

col2 = row['col2'] # access via name

col3 = row[mytable.c.mycol] # access via Column object.

可以通过其整数位置、不区分大小写的列名或 schema.Column 对象来访问各个列。例如：

行 = fetchone()

col1 = row[0] # 通过整数位置访问

col2 = row['col2'] # 通过名称访问

col3 = row[mytable.c.mycol] # 通过 Column 对象访问。

回答by mtoloo

You can easily convert each result row to a dictionary by using dict(site). Then site['ci']would be available if cicolumn is exists.

您可以使用 轻松地将每个结果行转换为字典dict(site)。然后，site['ci']如果将可ci列存在。

In order to have site.ci(according to https://stackoverflow.com/a/22084672/487460):

为了拥有site.ci（根据https://stackoverflow.com/a/22084672/487460）：

from collections import namedtuple
Site = namedtuple('Site', site.keys())
record = Site(*site)

回答by jasonrhaas

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.

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

Here is how I did it using the db_sessionin Python 3:

以下是我使用db_sessionPython 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)

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

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

As for how this works in SQL Alchemy:

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

• Thedb_session.execute(query)returns a ResultProxyobject
• The ResultProxyobject is made up of RowProxyobjects
• The RowProxyobject has an .items()method that returns key, value tuples of all the items in the row, which can be unpacked as key, valuein a foroperation.

• 该db_session.execute(query)回报ResultProxy对象
• 该ResultProxy物体制造了RowProxy对象
• 该RowProxy对象有一个.items()方法，该方法返回行中所有项目的键、值元组，可以像key, value在for操作中一样解包。

And here a one-liner alternative:

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

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

From the docs:

从文档：

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.

class sqlalchemy.engine.RowProxy（父、行、处理器、键映射）

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

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

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

items() 返回一个元组列表，每个元组包含一个键/值对。

keys() 将键列表作为由该 RowProxy 表示的字符串返回。

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

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

回答by Lucas Lima

I've built a simple class to work like a database interface in our processes. Here it goes:

我已经构建了一个简单的类来像我们流程中的数据库接口一样工作。它是这样的：

from sqlalchemy import create_engine
class DBConnection:
    def __init__(self, db_instance):
        self.db_engine = create_engine('your_database_uri_string')
        self.db_engine.connect()

    def read(self, statement):
        """Executes a read query and returns a list of dicts, whose keys are column names."""
        data = self.db_engine.execute(statement).fetchall()
        results = []

        if len(data)==0:
            return results

        # results from sqlalchemy are returned as a list of tuples; this procedure converts it into a list of dicts
        for row_number, row in enumerate(data):
            results.append({})
            for column_number, value in enumerate(row):
                results[row_number][row.keys()[column_number]] = value

        return results        

回答by HymanCid

I prefer to create a helper class and method with list comprehensions:

我更喜欢创建一个带有列表推导式的辅助类和方法：

class ResultHelper():

@classmethod
def resultproxy_to_dict_list(cls, sql_alchemy_rowset):        
        return [{tuple[0]: tuple[1] for tuple in rowproxy.items()} 
                                    for rowproxy in sql_alchemy_rowset]