Anurag Uniyal Anurag Uniyal - 1 year ago 285
Python Question

Convert sqlalchemy row object to python dict

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), but it throws exception , TypeError: 'User' object is not iterable

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)

Running this code on my system outputs:

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

Answer Source

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

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

    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: { str(getattr(r, for c in r.__table__.columns}