sqlite3 with flask web application CRUD pdf

1. Import necessary modules:

from flask import Flask, render_template, request, redirect, url_for
import sqlite3
from io import BytesIO
from reportlab.pdfgen import canvas

1. Create Flask app and configure SQLite database:

app = Flask(__name__)
app.config['DATABASE'] = 'database.db'

1. Define functions for database operations:

def connect_db():
    return sqlite3.connect(app.config['DATABASE'])

def create_table():
    with connect_db() as connection:
        cursor = connection.cursor()
        cursor.execute('CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, quantity INTEGER)')
        connection.commit()

def insert_item(name, quantity):
    with connect_db() as connection:
        cursor = connection.cursor()
        cursor.execute('INSERT INTO items (name, quantity) VALUES (?, ?)', (name, quantity))
        connection.commit()

def get_all_items():
    with connect_db() as connection:
        cursor = connection.cursor()
        cursor.execute('SELECT * FROM items')
        return cursor.fetchall()

def get_item_by_id(item_id):
    with connect_db() as connection:
        cursor = connection.cursor()
        cursor.execute('SELECT * FROM items WHERE id = ?', (item_id,))
        return cursor.fetchone()

def update_item(item_id, name, quantity):
    with connect_db() as connection:
        cursor = connection.cursor()
        cursor.execute('UPDATE items SET name = ?, quantity = ? WHERE id = ?', (name, quantity, item_id))
        connection.commit()

def delete_item(item_id):
    with connect_db() as connection:
        cursor = connection.cursor()
        cursor.execute('DELETE FROM items WHERE id = ?', (item_id,))
        connection.commit()

1. Create route for displaying items and rendering HTML template:

@app.route('/')
def show_items():
    items = get_all_items()
    return render_template('index.html', items=items)

1. Create route for adding new item:

@app.route('/add', methods=['POST'])
def add_item():
    name = request.form['name']
    quantity = request.form['quantity']
    insert_item(name, quantity)
    return redirect(url_for('show_items'))

1. Create route for updating item:

@app.route('/update/<int:item_id>', methods=['POST'])
def update_item_route(item_id):
    name = request.form['name']
    quantity = request.form['quantity']
    update_item(item_id, name, quantity)
    return redirect(url_for('show_items'))

1. Create route for deleting item:

@app.route('/delete/<int:item_id>')
def delete_item_route(item_id):
    delete_item(item_id)
    return redirect(url_for('show_items'))

1. Create route for generating PDF report:

@app.route('/pdf')
def generate_pdf():
    items = get_all_items()
    pdf_output = BytesIO()
    pdf = canvas.Canvas(pdf_output)
    pdf.drawString(100, 800, 'Items Report')
    y_position = 780
    for item in items:
        y_position -= 20
        pdf.drawString(100, y_position, f"ID: {item[0]}, Name: {item[1]}, Quantity: {item[2]}")
    pdf.save()
    pdf_output.seek(0)
    return pdf_output.getvalue(), 200, {'Content-Type': 'application/pdf', 'Content-Disposition': 'inline; filename=items_report.pdf'}

1. Run the application:

if __name__ == '__main__':
    create_table()
    app.run(debug=True)