SQLAlchemy RowProxy to dictionary [duplicate]

Question

Is there a simple way to iterate over column name and value pairs?

My version of SQLAlchemy is 0.5.6

Here is the sample code where I tried using dict(row):

import sqlalchemy
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

print "sqlalchemy version:",sqlalchemy.__version__ 

engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()
users_table = Table('users', metadata,
     Column('id', Integer, primary_key=True),
     Column('name', String),
)
metadata.create_all(engine) 

class User(declarative_base()):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    
    def __init__(self, name):
        self.name = name

Session = sessionmaker(bind=engine)
session = Session()

user1 = User("anurag")
session.add(user1)
session.commit()

# uncommenting next line throws exception 'TypeError: 'User' object is not iterable'
#print dict(user1)
# this one also throws 'TypeError: 'User' object is not iterable'
for u in session.query(User).all():
    print dict(u)

Running this code on my system outputs:

Traceback (most recent call last):
  File "untitled-1.py", line 37, in <module>
    print dict(u)
TypeError: 'User' object is not iterable

Answer 1

You may access the internal __dict__ of a SQLAlchemy object, like the following:

for u in session.query(User).all():
    print u.__dict__

Answer 2

I couldn't get a good answer so I use this:

def row2dict(row):
    d = {}
    for column in row.__table__.columns:
        d[column.name] = str(getattr(row, column.name))

    return d

Edit: if above function is too long and not suited for some tastes here is a one liner (python 2.7+)

row2dict = lambda r: {c.name: str(getattr(r, c.name)) for c in r.__table__.columns}

Answer 3

As per @zzzeek in comments:

note that this is the correct answer for modern versions of SQLAlchemy, assuming "row" is a core row object, not an ORM-mapped instance.

for row in resultproxy:
    row_as_dict = row._mapping  # SQLAlchemy 1.4 and greater
    # row_as_dict = dict(row)  # SQLAlchemy 1.3 and earlier

background on row._mapping, new as of SQLAlchemy 1.4: https://docs.sqlalchemy.org/en/stable/core/connections.html#sqlalchemy.engine.Row._mapping

Answer 4

In SQLAlchemy v0.8 and newer, use the inspection system.

from sqlalchemy import inspect

def object_as_dict(obj):
    return {c.key: getattr(obj, c.key)
            for c in inspect(obj).mapper.column_attrs}

user = session.query(User).first()

d = object_as_dict(user)

Note that .key is the attribute name, which can be different from the column name, e.g. in the following case:

class_ = Column('class', Text)

This method also works for column_property.

Answer 5

rows have an _asdict() function which gives a dict

In [8]: r1 = db.session.query(Topic.name).first()

In [9]: r1
Out[9]: (u'blah')

In [10]: r1.name
Out[10]: u'blah'

In [11]: r1._asdict()
Out[11]: {'name': u'blah'}

Answer 6

as @balki mentioned:

The _asdict() method can be used if you're querying a specific field because it is returned as a KeyedTuple.

In [1]: foo = db.session.query(Topic.name).first()
In [2]: foo._asdict()
Out[2]: {'name': u'blah'}

Whereas, if you do not specify a column you can use one of the other proposed methods - such as the one provided by @charlax. Note that this method is only valid for 2.7+.

In [1]: foo = db.session.query(Topic).first()
In [2]: {x.name: getattr(foo, x.name) for x in foo.__table__.columns}
Out[2]: {'name': u'blah'}

Answer 7

Assuming the following functions will be added to the class User the following will return all key-value pairs of all columns:

def columns_to_dict(self):
    dict_ = {}
    for key in self.__mapper__.c.keys():
        dict_[key] = getattr(self, key)
    return dict_

unlike the other answers all but only those attributes of the object are returned which are Column attributes at class level of the object. Therefore no _sa_instance_state or any other attribute SQLalchemy or you add to the object are included. Reference

EDIT: Forget to say, that this also works on inherited Columns.

hybrid_property extention

If you also want to include hybrid_property attributes the following will work:

from sqlalchemy import inspect
from sqlalchemy.ext.hybrid import hybrid_property

def publics_to_dict(self) -> {}:
    dict_ = {}
    for key in self.__mapper__.c.keys():
        if not key.startswith('_'):
            dict_[key] = getattr(self, key)

    for key, prop in inspect(self.__class__).all_orm_descriptors.items():
        if isinstance(prop, hybrid_property):
            dict_[key] = getattr(self, key)
    return dict_

I assume here that you mark Columns with an beginning _ to indicate that you want to hide them, either because you access the attribute by an hybrid_property or you simply do not want to show them. Reference

Tipp all_orm_descriptors also returns hybrid_method and AssociationProxy if you also want to include them.

Remarks to other answers

Every answer (like 1, 2 ) which based on the __dict__ attribute simply returns all attributes of the object. This could be much more attributes then you want. Like I sad this includes _sa_instance_state or any other attribute you define on this object.

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. row.__table__.columns contains the column names of the SQL Database. These can only be equal to the attributes name of the python object. If not you get an AttributeError. For answers (like 1, 2 ) based on class_mapper(obj.__class__).mapped_table.c it is the same.

Answer 8

Old question, but since this the first result for "sqlalchemy row to dict" in Google it deserves a better answer.

The RowProxy object that SqlAlchemy returns has the items() method: http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.RowProxy.items

It simply returns a list of (key, value) tuples. So one can convert a row to dict using the following:

In Python <= 2.6:

rows = conn.execute(query)
list_of_dicts = [dict((key, value) for key, value in row.items()) for row in rows]

In Python >= 2.7:

rows = conn.execute(query)
list_of_dicts = [{key: value for (key, value) in row.items()} for row in rows]

Answer 9

A very simple solution: row._asdict().

• sqlalchemy.engine.Row._asdict() (v1.4)
• sqlalchemy.util.KeyedTuple._asdict() (v1.3)

> data = session.query(Table).all()
> [row._asdict() for row in data]

Answer 10

Following @balki answer, since SQLAlchemy 0.8 you can use _asdict(), available for KeyedTuple objects. This renders a pretty straightforward answer to the original question. Just, change in your example the last two lines (the for loop) for this one:

for u in session.query(User).all():
   print u._asdict()

This works because in the above code u is an object of type class KeyedTuple, since .all() returns a list of KeyedTuple. Therefore it has the method _asdict(), which nicely returns u as a dictionary.

WRT the answer by @STB: AFAIK, anything that .all() returns is a list of KeypedTuple. Therefore, the above works either if you specify a column or not, as long as you are dealing with the result of .all() as applied to a Query object.