SQLAlchemy returns tuple not dictionary

Question

I've updated SQLAlchemy to 0.6 but it broke everything. I've noticed it returns tuple not a dictionary anymore. Here's a sample query:

query = session.query(User.id, User.username, User.email).filter(and_(User.id == id, User.username == username)).limit(1)
result = session.execute(query).fetchone()

This piece of code used to return a dictionary in 0.5.

My question is how can I return a dictionary?

Answer 1

Are you sure it isn't a ResultProxy which pretends to be a tuple when you print it? Many objects in the ORM are not what their __str__ function returns.

Answer 2

session.execute has never returned a dict, it returns a RowProxy object, that can be indexed like a dict using either integer keys for positional lookup, string keys for label based lookup up or Column objects to lookup the value of that column. The problem here is that session.execute(query) doesn't do what you seem to expect it to do. It converts the Query object to a Select statement, executes that and returns the result directly. The resultset doesn't know anything about ORM level features. What changed between 0.5 ad 0.6 is that ORM uses a different algorithm to label the columns in queries, it now prepends the table name to the label. So when previously row['id'] happened to work, now row['users_id'] works. In both cases row[User.__table__.columns['id']] works.

To execute ORM queries you should actually use the .all(), .first() and .one() methods or iterate over it or using numeric indexing. Query returns named tuple objects. Zip the tuple with its keys if you want a dict:

row = session.query(User.id, User.username, User.email)\
    .filter(and_(User.id == id, User.username == username)).first()
print("id=%s username=%s email=%s" % row) # positional
print(row.id, row.username) # by label
print(dict(zip(row.keys(), row))) # as a dict

Answer 3

This should work: dict(zip(['id','username','email'],result)) (or you could use a dictionary comprehension if you're on Python 3.x).
Also, you don't need to call session.execute on a session.query object. You'll want to use the .one() method on it instead. This also obviates the need for the .limit(1) call hanging off the end of your query.

Answer 4

I solved this using:

# Get a list os user_ids 
data_from_db = session.execute(select(User.id)).all()

# Parsing each Row object to a dict by "_mapping" attribute
return [dict(data._mapping) for data in data_from_db]