Cannot get HTML to display SQLite3 data with python & flask

Question

I'm having an issue getting an HTML page to display my SQLite3 data using Python & Flask. I found a question on here and used the same code, plus different variations to no success. There are no error messages and the HTML page loads, however there is no data present. This is the code I am using to get the data;

@app.route("/viewpackages", methods=['GET'])
def viewpackages():
    con = sqlite3.connect('utpg.db')
    db = con.cursor()
    user_id = session.get('id')
    getpackages = db.execute("SELECT pck_id, client_id, date, price,  privatelesson,"
                             "shortgamelesson, playinglesson, notes FROM packages WHERE client_id = ?;", (user_id, ))

    return render_template("view_packages.html", items=getpackages.fetchall())

this is the code I attempting to use to display the data;

<table>
{% for item in items %}
<tr>
    <td>{{column1}}</td>
    <td>{{column2}}</td>
    <td>{{column3}}</td>
    <td>{{column4}}</td>
    <td>{{column5}}</td>
    <td>{{column6}}</td>
    <td>{{column7}}</td>
    <td>{{column8}}</td>
</tr>
{% endfor %}
</table>

I tried changing 'column' to the actual db column name, with the same issue. I tried calling SELECT * instead of each individual column, same issue. If I run this code in python;

con = sqlite3.connect('utpg.db')
db = con.cursor()
user_id = session.get('id')
getpackages = db.execute("SELECT pck_id, client_id, date, price,    privatelesson, shortgamelesson, playinglesson, notes FROM   packages WHERE        client_id = ?;", (user_id, ))
packages = getpackages.fetchall()
for row in packages:
     print(row)

It returns the desired results. So I am thinking there is an issue with the HTML somewhere but I cannot figure it out.

ettanany · Accepted Answer · 2016-10-02 13:55:09Z

4

Please try the following code and let me know if it works for you:

<table>
{% for item in items %}
<tr>
    <td>{{ item[0] }}</td>
    <td>{{ item[1] }}</td>
    <td>{{ item[2] }}</td>
    <td>{{ item[3] }}</td>
    <td>{{ item[4] }}</td>
    <td>{{ item[5] }}</td>
    <td>{{ item[6] }}</td>
    <td>{{ item[7] }}</td>
</tr>
{% endfor %}
</table>

Test with the following code:

app.py

from flask import Flask, render_template
import sqlite3

app = Flask(__name__)
app.secret_key = 'development key'


@app.route('/users')
def get_users():
    con = sqlite3.connect('mydb.db')
    db = con.cursor()
    db.execute('insert into user values ("Aaa", "AAA")')
    db.execute('insert into user values ("Bbb", "BBB")')
    res = db.execute('select * from user')
    return render_template('users.html', users=res.fetchall())

if __name__ == "__main__":
    app.run(host="0.0.0.0", debug=True)

templates/users.html

<html !DOCTYPE>
<head>
<title>SQLite</title>
</head>
<body>
<table>
{% for user in users %}
<tr>
    <td>{{ user[0] }}</td>
    <td>{{ user[1] }}</td>
</tr>
{% endfor %}
</table>
</body>
</html>

edited Oct 2, 2016 at 13:55

answered Oct 2, 2016 at 12:27

ettanany

20k9 gold badges49 silver badges64 bronze badges

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

6 Comments

furas Over a year ago

you will need {{ item[0] }} or you have to set con.row_factory = lite.Row to get {{ item["client_id"] }} or maybe {{ item.client_id }}

Christopher Nelson Over a year ago

Unfortunately not.. Still just the same black page. I just viewed the page source in the browser and I can see the table is built, not obviously without any data.

Christopher Nelson Over a year ago

Can I create a for loop in the HTML table like that? for item in enumerate(items): item[x] ?

furas Over a year ago

try for col in item to get value instead of index and use {{ col }}

Christopher Nelson Over a year ago

This did display data, but what it did was take the entire row from the database and display it in each row in the table. So I got 8 listings. I tried to possibly get it by index.. col[0][0], col[0][1] but that didn't work.

|

Christopher Nelson · Accepted Answer · 2016-10-02 13:13:20Z

Here is what I had to do.. For anyone else having this issue, I'm not sure why but Jinja did not want to let me access by index so I did..

@app.route("/viewpackages", methods=['GET'])
def viewpackages():
con = sqlite3.connect('utpg.db')
con.row_factory = sqlite3.Row
db = con.cursor()
user_id = session.get('id')
getpackages = db.execute("SELECT pck_id, client_id, date, price, privatelesson,"
                         "shortgamelesson, playinglesson, notes FROM packages WHERE client_id = ?;", (user_id, ))

return render_template("view_packages.html", items=getpackages.fetchall())

and change the HTML to..

<table>
{% for col in items %}
<tr>
<td>{{ col['pck_id'] }}</td>
<td>{{ col['client_id'] }}</td>
<td>{{ col['date'] }}</td>
<td>{{ col['price'] }}</td>
<td>{{ col['privatelesson'] }}</td>
<td>{{ col['shortgamelesson'] }}</td>
<td>{{ col['playinglesson'] }}</td>
<td>{{ col['notes'] }}</td>
</tr>
{% endfor %}
</table>

All working now, thank for pointing me in the right direction!

Collectives™ on Stack Overflow

Cannot get HTML to display SQLite3 data with python & flask

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related