How to convert SQLAlchemy row object to a Python dict?

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

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

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}

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 = dict(row)

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.

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'}