Problem
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),
)
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)
When I run this code on my system, I get the following results:
Traceback (most recent call last):
File "untitled-1.py", 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[column.name] = str(getattr(row, column.name))
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: {c.name: str(getattr(r, c.name)) 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(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'}
Answered by balki
Post is based on https://stackoverflow.com/questions/1958219/how-to-convert-sqlalchemy-row-object-to-a-python-dict