read database (created using sqlite) using html file-CodePudding

I've created a site that got an option to add products(giving name and price)

Then I managed to store this data using sqlite3 with this code:

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()

now I want to display all the products in the product section in my website. My product page is currently empty but here is 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? please let me know .

CodePudding user response：

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 %}