Get column names Dynamically with SQLAlchemy

Question

I am querying sqlite db with SQLAlchemy like this:

import db

...

results = session.query(table).all()
        for result in results:
            print result
            print "how to print column name?"

Here is a snippet from db class:

class Device(Base):
    __tablename__ = "devices"
    name = Column(String(250), primary_key=True)
    location = Column(String(250), nullable=False)

    def __init__(self, name, location):
        self.name = name
        self.location = location

Attempting to use ".column_descriptions" on result as per the documentation throws "'list' object has no attribute 'column_descriptions'" error.

What is the proper way of getting column names dynamically along with the values? I want this so I can build a single function to handle json conversion of all queries instead of repeating code all over the place.

Answer 1

You can use Device.__table__.columns to get the list of columns in your Device model in your code. These are Column objects and you can get the name by using .name on them while iterating. Then when you query, you can just use getattr or something to get the values that you need.

EDIT:

Example:

col_names = Device.__table__.columns.keys()  # This gets the column names
sample_row = Device.query.first()  # I just query for a random row
required_values = {name: getattr(sample_row, name) for name in col_names}
# This is a dictionary comprehension

You can also use the select syntax, perhaps.

EDIT2:

Here is an article on dictionary comprehension

Okay, so what this dictionary comprehension does is that it gets the keys as the name of column, and the values as the values of corresponding columns from the sample row that I queried for.

You wanted to print a list, so we can do that like this:

Replace the dictionary comprehension line with this.

print([getattr(sample_row, name) for name in col_names])

This will print the values from that row in a list, but you don't get the column names.

But for that you can create a list of tuples.

print([(name, getattr(sample_row, name)) for name in col_names])

But an easier way for you to do this probably would be to use the select syntax instead which is provided by SQLAlchemy.

Docs here: http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#selecting