SQLAlchemy: Map column name to value automatically

Question

Background

When I want to create a dictionary to map the column name to values in a result, I need to list out each column individually

    result = self.session.query(Project)\
                .join(Deadline)\
                .values(Project.project_id, Project.project_name,
                    Project.project_root, Deadline.deadline_time)

    output = []
    for row in result:
        r = {}
        r['project_id'] = row.project_id
        r['project_name'] = row.project_name
        r['project_root'] = row.project_root
        r['deadline_time'] = row.deadline_time
        output.append(r)

    return output

The Models are ORM-mapped, and not SQLAlchemy core.

class Deadline(Base):
    __tablename__ = 'deadline'

    deadline_id = Column(Integer, primary_key = True)
    deadline_time = Column(String, nullable = False)


class Project(Base):
    __tablename__ = 'project'

    project_id = Column(Integer, primary_key = True)
    project_name = Column(String, nullable = False)
    project_root = Column(String, nullable = False)
    deadline_id = Column(Integer, ForeignKey("deadline.deadline_id"), nullable=False)
    deadline = relationship("Deadline", primaryjoin = "Project.deadline_id == Deadline.deadline_id", backref=backref("project", uselist=False))

Question

This is not efficient for a table with many columns, nor can I re-use the same code for other tables.

I tried the following but could not get the column name

    column_names = [c["name"] for c in result.column_descriptions]
    return [dict(zip(column_names, row)) for row in result.all()]
    result.__table__.columns.keys()

Edit 1

There are a few similar questions about converting SQLAlchemy result into dict. As per the code above, result.__table__.columns.keys() does not work for ORM-mapped models.

Possible duplicate of [Convert sqlalchemy row object to python dict](https://stackoverflow.com/questions/1958219/convert-sqlalchemy-row-object-to-python-dict) — krassowski, Aug 15 '17 at 21:59

krassowski · Accepted Answer · 2017-08-15T22:22:55.227

1

Your row is SQLAlchemy KeyedTuple. Since 0.8 KeyedTuples have _asdict() method - you can simply use:

output = []

for row in result:
    output.append(row._asdict())

PS. You do not have to specify such a trivial primaryjoin in deadline relationship. SQLAlchemy will determine foreign keys on their own.

edited Aug 15 '17 at 22:22

answered Aug 15 '17 at 21:56

krassowski

13,598
4
60
92

score 0 · Answer 2 · answered Aug 15 '17 at 18:52

I believe you can just use a dictionary comprehension:

r = {k: row[k] for k in row}

To include it with the output in a list comprehension:

output = [{k: row[k] for k in row} for row in result]

Instead of repeating the column name in each row JSON style, you may just want to append values to a list.

score 0 · Answer 3 · answered Aug 17 '17 at 17:19

krassowski's answer is exactly what I need. For succinctness, I apply _asdict() as a lambda function.

result = self.session.query(Project)\
                .join(Deadline)\
                .values(Project.project_id, Project.project_name,
                    Project.project_root, Deadline.deadline_time)
map(lambda x: x._asdict(), result)

SQLAlchemy: Map column name to value automatically

Background

Question

Edit 1

3 Answers3