SQLAlchemy execute() return ResultProxy as Tuple, not dict

sqlalchemy resultproxy to list
sqlalchemy execute stored procedure
sqlalchemy resultproxy length
sqlalchemy execute query
sqlalchemy check connection status
sqlalchemy session execute
sqlalchemy return dict instead of tuple
sqlalchemy _asdict()

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'

Working with Engines and Connections, A ResultProxy that returns no rows, such as that of an UPDATE statement (​without The Connection.execute() method can of course accommodate more than that, a collection of tuples or individual values in *multiparams may be passed: The format of this dictionary is not guaranteed to stay the same in future releases. results is a tuple and I want it to be of type dict() SQLAlchemy already does this for you if you use engine.execute instead of raw_connection(). With engine.execute, fetchone will return a SQLAlchemy Row object and fetchall will return a list of Row objects. Row objects can be accessed by key, just like a dict:

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.

sqlalchemy, sqlalchemy documentation: Converting a query result to dict. The method name starts with a single underscore, to match the namedtuple API (it's not private!) as_declarative @as_declarative() class Base: def _asdict(self): return {c.key:  I was under the impression that a RowProxy object would be returned for every result in the result set, which supports dict-like operations (as is mentioned in this post), but it appears the results are just plain tuples. The SQLAlchemy docs are not 100% clear on what the expected type of the results from a cursor is, only offering this example

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        

ResultProxy - sqlalchemy - Python documentation, ResultProxy - 5 members - Wraps a DB-API cursor object to provide easier access to row columns. Subsequent calls to ResultProxy.fetchall() will return an empty list. the inserted_primary_key attribute provides a tuple of primary key values for a do not support an aggregate rowcount result from an executemany call. sqlalchemy Converting a query result to dict. Example. First the setup for the example: If you're querying columns individually, the row is a KeyedTuple which has an _asdict method. The method name starts with a single underscore, to match the namedtuple API (it's not private!).

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?

SQLAlchemy engine SELECT, from sqlalchemy import create_engine dbname = 'test.db' engine = create_engine('sqlite:///' + dbname) result = engine.execute('SELECT * FROM person WHERE id=:id', ResultProxy object at 0x1013c9da0> row = result.​fetchone() print(row) # (3, 'Melinda', 100) - Its a tuple print(row['name']) # Melinda - And a dictionary  Every answer (like 1, 2 ) which is based on the dict() function only works on SQLalchemy row objects returned by session.execute() not on the classes you define to work with, like the class User from the question. The solving answer which is based on row.__table__.columns will definitely not work.

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)

sqlalchemy/result.py at master · zzzeek/sqlalchemy · GitHub, from sqlalchemy.cresultproxy import tuplegetter as _tuplegetter. _UNPICKLED are cached. """ if not context.compiled._result_columns: return self. compiled_statement = context.compiled.statement by_key = dict( such as passed to connection.execute(). ResultProxy "open" from a semantic perspective, meaning the. You need to interact with the ResultProxy. As per SQLAlchemy's documentation: The returned result is an instance of ResultProxy, which references a DBAPI cursor and provides a largely compatible interface with that of the DBAPI cursor. The DBAPI cursor will be closed by the ResultProxy when all of its result rows (if any) are exhausted.

aiopg.sa, import asyncio from aiopg.sa import create_engine import sqlalchemy as sa So you can execute SQL query built by tbl.insert().values(val='abc') or Typically, the format is either a dictionary passed to *multiparams: Return True if this ResultProxy is closed (no pending rows in underlying cursor). ..method:: as_tuple(). Every answer (like 1, 2) which is based on the dict() function only works on SQLalchemy row objects returned by session.execute() not on the classes you define to work with, like the class User from the question. The solving answer which is based on row.__table__.columns will definitely not work.

Sqlalchemy Hack, New comments cannot be posted and votes cannot be cast http://stackoverflow.​com/questions/1958219/convert-sqlalchemy-row-object-to-python-dict the software using Cython to get performance comparable to emulators written in C and  The title of the question does not match the question itself. According to docs Result rows returned by Query that contain multiple ORM entities and/or column expressions make use of this class to return rows. where this class is sqlalchemy.util.KeyedTuple which is row object from the question's title.

I have data in a 2D tuple (or say coming from a Numpy table), and need to insert it into an SQL table. Using Sqlalchemy Core with SQLite, how can I efficiently and simply insert this data into my t

Comments
  • * confused * you select "sp.param_value" but expect the result proxy to have a member called "ci" ? you can use result proxies like a dict, but you'd use something like print site['param_value']
  • resultproxy return tuple, i want, thats resultproxy return dict site['ci'] or site.ci
  • I agree with @Gryphius. You can try like this print site['ci']
  • @Gryphius, @Syed Habib M, its isnt works! ResultProxy return tuple, not dict and not somethink other!!! (u'375')
  • Did you try that? Are you sure there is column name like 'ci' in Coalesce table?
  • I believe you meant to assign the result of session.execute() to resultproxy, like so: resultproxy = db_session.execute(query)
  • This seems to be working for me with less typing [dict(row) for row in resultproxy]
  • Not working on sqlachemy 1.1. The ResultProxy elements are just tuples. The only way seems to be accessing the result.keys() and building your own dicts with that.
  • @tothemario The docs for (currently) version 1.2.x still contain my copy & pasted snippet from above. So it seems the ResultProxy is still not just a tuple.