[FIXED] Display database using HTML file

Issue

My site has an option to add products (giving name and price). I store this data using sqlite3 :

import sqlite3
from main import *
from flask import flash

conn =  sqlite3.connect('products.db')
c = conn.cursor()
c.execute(""" CREATE TABLE IF NOT EXISTS products (
            name text,
            price integer
        )  """)
c.execute("SELECT * FROM products")
existence = False

for name in c:
    if name[0] == request.form["product"]:
        existence = True

if existence:
    flash('This item already exist !! ',category='error')
else:
    flash('Item added successfully',category='success')

c.execute (" INSERT OR IGNORE INTO products VALUES (?,?)",[request.form["product"],request.form["price"]])
c.execute("SELECT * FROM products")

conn.commit()
conn.close()

I want to display all products in my website. How it looks like:

{% extends "base.html" %}
{% block title%} Products {% endblock %}
{% block content %}
<h1></h1>
{% endblock %}

Maybe I could use the Python file to display my data? Maybe I should use another way to store my data?

Solution

Firstly, create an app route to your products page.

Replace the 'products.html' in return render_template at the end of the python file with whatever your html page name is.

Assign a variable to where you selected all the product then add a fetchall. Return this as product = products when rendering the products.html.

import sqlite3
from main import *
from flask import Flask, render_template, flash

@app.route('/products')
def products():
    conn =  sqlite3.connect('products.db')
        
    c = conn.cursor()
        
    c.execute(""" CREATE TABLE IF NOT EXISTS products (
                  name text,
                  price integer
              )  """)
    
    products = c.execute("SELECT * FROM products").fetchall()
        
    existence = False
        
    for name in c:
        if name[0] == request.form["product"]:
            existence = True
        
    if existence:
          flash('This item already exist !! ',category='error')
      else:
          flash('Item added successfully',category='success')
        
    c.execute (" INSERT OR IGNORE INTO products VALUES (?,?)"[request.form["product"],request.form["price"]])
        
    c.execute("SELECT * FROM products")
          
    conn.commit()
        
    conn.close()

    return render_template('products.html', products=products)

To display this in your actual product page, we need to use python 'for loops' built in Jinja. This goes through every product in your database and prints out the price and name.

{% extends "base.html" %}
{% block title%} Products {% endblock %}
{% block content %}
  {% for product in products %}
    <h1>{{ product.price }}</h1>
    <h1>{{ product.name }}</h1>
  {% endfor %}
{% endblock %}

Answered By - whoisyersinia