Connecting sqlite to Flask Web App

sqlite will create a file local to the web app and will allow you to run a pretty full SQL relational database. There are some features not supported by sqlite but you are unlikely to need any of them. The wiki already covers using sqlite in the following pages:

Adding to Flask Web App

You will need to import sqlite into your views.py or routes.py file:

import sqlite3
from sqlite3 import Error

I would recommend you create a class for the database functions you will need:

class Database:
    dbname = "test.db"

    def connect(self, db_file):
        try:
            conn = sqlite3.connect(db_file)
            return conn
        except Error as e:
            print(e)
            return none

    def execute(self, sql):
        try:
            c = self.connection.cursor()
            c.execute(sql)
            self.connection.commit()
            self.connection.close()
            return True
        except Error as e:
            print(e)
            return False

    def select(self, sql):
        try:
            cur = self.connection.cursor()
            cur.execute(sql)
            return cur.fetchall()
        except Error as e:
            print(e)
            return False
    
    def __init__(self):
        self.connection = self.connect(self.dbname)

This code will create a database connection whenever an object of the class is created. It also contains a method to run the execute style commands (ie create, insert, update, delete) and a method to run the retrieve style commands (ie select). This method will allow you to change the connection methods at a later date without needing to change any of the web app code.

You could extend this by creating individual methods for 'CheckLogin', or 'GetProduct' or 'GetQuiz', alternatively the sql could be within your routes. This will keep all of the database code in one place.

This example shows how you can make a route to execute the command. This creates an object of the database class, and then runs the execute method to run the sql:

@app.route('/create')
def createuserdb():
    db = Database()
    sql = """CREATE TABLE IF NOT EXISTS users(
                UserID text PRIMARY KEY,
                UserPass text NOT NULL,
                UserEmail text NOT NULL
                )"""
    check = db.execute(sql)