CODEWITHSUNDEEP
pythonhtmlflasksqlitejinja2
Christopher Nelson
Christopher Nelson

Reputation: 1017

Cannot get HTML to display SQLite3 data with python & flask

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.

Upvotes: 0

Views: 3157

Answers (2)

ettanany
ettanany

Reputation: 19806

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>

Upvotes: 4

Christopher Nelson
Christopher Nelson

Reputation: 1017

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!

Upvotes: 2

Related Questions