SQLAlchemy execute（）以元组形式返回ResultProxy，而不是di

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

print site.ci
# u'375'

Answer 1

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

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        

Answer 2

这是一个古老的问题，但今天仍然相关。让SQL炼金术返回字典非常有用，特别是在使用返回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炼金术中是如何工作的：

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

这里有一个单列备选方案：

[{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

Answer 3

你看过那些文件了吗？ 它准确地描述了@Gryphius和@Syed Habib M的建议，即使用site['ci']。

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

从文档中：

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.