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 — krassowski
– krassowski, Commented Aug 15, 2017 at 21:59

krassowski · Accepted Answer · 2017-08-15 22:22:55Z

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, 2017 at 22:22

answered Aug 15, 2017 at 21:56

krassowski

15.7k4 gold badges75 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alexander · Accepted Answer · 2017-08-15 18:52:23Z

0

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.

answered Aug 15, 2017 at 18:52

Alexander

111k32 gold badges212 silver badges208 bronze badges

Comments

Hanxue · Accepted Answer · 2017-08-17 17:19:15Z

0

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)

answered Aug 17, 2017 at 17:19

Hanxue

12.9k20 gold badges93 silver badges134 bronze badges

Collectives™ on Stack Overflow

SQLAlchemy: Map column name to value automatically

Background

Question

Edit 1

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Background

Question

Edit 1

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related