Flask-SQLAlchemy iterate column values on a single row

Question

I'm new to Flask and Python so apologies in advance. I'm using Flask-SQLAlchemy to return a database row, this all works fine:

customer = Customers.query.filter_by(cat_id = page).first()
return render_template('test.html',
    customer = customer
    )

My problem is I'm trying to figure out how to then display the column values for this row in my jinja template using a loop. Is this the best way to approach this problem? I'm getting the "object is not iterable" error which I kind of understand but I'm not sure how to work around it.

In my template I'm currently using:

{{customer.id}}
{{customer.name}}
{{customer.area}}
etc.

But I would like to be doing something like this:

{% for item in customer %}
    {{item[column]}}
{% endfor %}

Could the query be converted to a dictionary?

I've searched all over trying to figure this out with no luck, this leads me to think I may be on the wrong track.

Any advice much appreciated.

---

Update:
I think this is progress. The main change is in views.py where I've added .__dict__ which from what I've read accesses the internal __dict__ of a SQLAlchemy object. The for template loop now outputs the column values but it also outputs lots of other undesired stuff. Is there anyway to clean this up?

models.py

class Customers(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    cust_name = db.Column(db.String(64))
    cust_area = db.Column(db.String(64))
    cat_id = db.Column(db.Integer(8), index = True)

views.py

customer = Customers.query.filter_by(cat_id = page).first()
return render_template('test.html',
    customer = customer.__dict__
    )

test.html

{% for key, value in customer.items() %}
    {{ key }} , {{ value }}
{% endfor %}

Output

cust_name , John _sa_instance_state , 
<sqlalchemy.orm.state.InstanceState object at 0x03961D50> id , 1 cat_id , 2 cust_area , England

Answer 1

Convert the customer row to a dictionary first in your view.

customer = Customers.query.filter_by(cat_id = page).first()
customer_dict = dict((col, getattr(customer, col)) for col in customer.__table__.columns.keys())
return render_template('test.html',
customer_dict = customer_dict
)

You can use iteritems() on the customer_dict row.

{% for key, value in customer_dict.iteritems() %}

   {{ key }} , {{ value }}

{% endfor %}