8

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.

DominicM
  • 6,520
  • 13
  • 39
  • 60
  • 1
    This question http://stackoverflow.com/questions/8947616/python-sqlalchemy-get-column-names-dynamically documents `result.keys()`. Which is, what you probably want. – wolfmanx Mar 06 '17 at 14:59

1 Answers1

15

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: https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#tutorial-selecting-data

half of a glazier
  • 1,864
  • 2
  • 15
  • 45
adarsh
  • 6,738
  • 4
  • 30
  • 52
  • I was able to get column names in the for loop but can't get the values now. There is no clear documentation I can find on getattr. I tried getattr(Device, column.name) but it does not output a value. Can you provide an example? – DominicM Oct 09 '15 at 13:07
  • I edited my answer with an example. See if that works for you. – adarsh Oct 09 '15 at 13:49
  • I am a little unclear by the example (I am new to python). Could you provide example showing how to print the column name and column value in a simple for in loop? – DominicM Oct 09 '15 at 15:06
  • I edited my answer. Let me know if you need more explanation. – adarsh Oct 09 '15 at 15:44
  • I am getting "selectQuery error occurred: getattr(): attribute name must be string" error. "print sample_row" outputs "" presumably because it is not being looped over and so is not a string value. I also don't understand the way you are using the for in loop. Can you make it more readable by using the first code snippet in the question as a starting point? – DominicM Oct 10 '15 at 15:54