Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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' 
like image 483
user3128993 Avatar asked Dec 23 '13 12:12

user3128993


People also ask

What does SQLAlchemy all () return?

As the documentation says, all() returns the result of the query as a list.

What is ResultProxy?

A ResultProxy is a wrapper around a DBAPI cursor object, and its main goal is to make it easier to use and manipulate the results of a statement.

What is Create_engine in SQLAlchemy?

The create_engine() method of sqlalchemy library takes in the connection URL and returns a sqlalchemy engine that references both a Dialect and a Pool, which together interpret the DBAPI's module functions as well as the behavior of the database.

How do I run a SQLAlchemy query?

Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as shown below, create a table called books with columns book_id and book_price. Insert record into the tables using insert() and values() function as shown.


1 Answers

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

like image 136
jasonrhaas Avatar answered Sep 24 '22 22:09

jasonrhaas