When I am using SQLALchemy how would one iterate through column names?
Eg.
Column Name 1, Column Name 2, Column Name 3, etc...
The second question is I have the following query:
root = dbsession.query(MyTable).filter(MyTable.name==u'john').all()
However, when I do:
for row in root:
print row
I don't get any results. Instead I have to do:
print row.name, row.age, etc...
Can't I just do print row to return data from all the columns?
dbsession.query(MyTable).filter(MyTable.name==u'john') will go through ORM and return you objects. If you just want to return all columns, you can bypass ORM with this:
query = dbsession.query(MyTable).filter(MyTable.name==u'john')
rows = query.statement.execute().fetchall()
for row in rows:
print row
There is an easier way to iterate through column names without using 'query', and details for accessing tables and columns can be found in the docs here.
Essentially it will look something like this:
metadata = MetaData()
metadata.reflect(engine)
tbl = Table('mytable', metadata)
for column in tbl.c:
print column.name
I recognize this post is out of date, but this info might still be helpful for someone looking.
You can do something like this.
rows = MyTable.query.all()
# Get all the column names of the table in order to iterate through
column_keys = MyTable.__table__.columns.keys()
# Temporary dictionary to keep the return value from table
rows_dic_temp = {}
rows_dic = []
# Iterate through the returned output data set
for row in rows:
for col in column_keys:
rows_dic_temp[col] = getattr(row, col)
rows_dic.append(rows_dic_temp)
rows_dic_temp= {}
return jsonify(rows_dic)
If MyTable is an ORM mapped class, you can define __str__ or __repr__ methods and then it will print the way you want it to. Otherwise you just want to do for row in session.execute(): instead.
