[Solved] SQLAlchemy execute() return ResultProxy as Tuple, not dict

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:

print site.ci
# u'375'

Solution #1:

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.

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)

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

As for how this works in SQL Alchemy:

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

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.

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

Respondent: jasonrhaas

Solution #2:

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

The ResultProxy does 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:

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.

Respondent: sebastian

Solution #3:

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        
Respondent: Lucas Lima

Solution #4:

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 dict attribute with both keys and values.

python sqlalchemy get column names dynamically?

Respondent: hamx0r

Solution #5:

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

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

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

Solution #6:

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]
Respondent: JackCid

Solution #7:

As you call db.execute(sql).fetchall(), you can easily use the following function to parse the return data to a dict:

def query_to_dict(ret):
    if ret is not None:
        return [{key: value for key, value in row.items()} for row in ret if row is not None]
    else:
        return [{}]
Respondent: d0zingcat

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.