Coder Perfect

How can I make a Python dict from a SQLAlchemy row object?


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

SQLAlchemy 0.5.6 is the version I’m using.

Here’s an example of how I used dict(row) in my code:

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),

class User(declarative_base()):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    def __init__(self, name): = name

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

user1 = User("anurag")

# 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)

When I run this code on my system, I get the following results:

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

Asked by Anurag Uniyal

Solution #1

a SQLAlchemy object’s internal __dict__, such as this:

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

Answered by hllau

Solution #2

I couldn’t come up with a nice response, so I’ll use this:

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

    return d

Edit: If the above code is too long for your tastes, here is a one-liner (python 2.7+) version.

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

Answered by Anurag Uniyal

Solution #3

In the words of @zzzeek in the comments:

for row in resultproxy:
    row_as_dict = dict(row)

Answered by Alex Brasetvik

Solution #4

Use the inspection system in SQLAlchemy v0.8 and newer.

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 approach applies to column property as well.

Answered by RazerM

Solution #5

The _asdict() function in rows returns a dict.

In [8]: r1 = db.session.query(

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

In [10]:
Out[10]: u'blah'

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

Answered by balki

Post is based on