SQLite Examples

SQLite is covered in more detail here

Note: When you connect to an SQLite database file that does not exist, SQLite creates a new database for you.

simpleblog.py

from flask import Flask
from flask import request
from flask import redirect
import sqlite3

app = Flask(__name__)

#---------on launch:
try:
    db = sqlite3.connect('blog.db')
    cursor = db.cursor()
    #INTEGER PRIMARY KEY == alias for ROWID == an auto-incrementing row..:
    cursor.execute('''CREATE TABLE IF NOT EXISTS
                    blog(INTEGER PRIMARY KEY, comment TEXT);''')
    db.commit()
except Exception:
    db.rollback()
finally:
    db.close()

postPage = '''
<form action="/post" method="POST">
  <textarea name="comment" cols="40" rows="5"></textarea>
  <input type="submit" value="make post">
</form>'''

#---------on post:
@app.route("/post", methods=["POST"])
def post():
    try:
        comment = request.form["comment"]
        db = sqlite3.connect('blog.db')
        cursor = db.cursor()
        cursor.execute("INSERT INTO blog (comment) values (?)",(comment,))
        db.commit()
    except Exception:
        db.rollback()
    finally:
        db.close()
        return redirect("/")

#---------on request:
@app.route("/")
def main():
    comments = ""
    try:
        db = sqlite3.connect('blog.db')
        cursor = db.cursor()
        cursor.execute("SELECT * FROM blog")
        result = cursor.fetchall()
        if len(result) > 0:
            for record in result:
                comments += record[1] + "<br>"
    except Exception as error:
        comments = "Something went wrong: " + str(error)
    finally:
        db.close()
        return postPage + "<br>" + comments
    
app.run()

sqlite_basic_a.py

from flask import Flask
import sqlite3

app = Flask(__name__)

@app.route("/")
def main():
    message = ""
    try:
        db = sqlite3.connect('test.db') #creates if not exists already
        cursor = db.cursor()
        cursor.execute("CREATE TABLE users(id INTEGER PRIMARY KEY, email TEXT);")
        cursor.execute("INSERT INTO users (id,email) values (?, ?)",(99, "me@my.com"))
        db.commit()
        message = "Database was created, and some records were inserted."
    except Exception:
        db.rollback()
        message = "Something went wrong."
    finally:
        db.close()
        return message
    
app.run()

sqlite_basic_b.py

from flask import Flask
import sqlite3

app = Flask(__name__)

lots_of_records = [
         (1,"me@my.com"),
         (2,"you@your.com"),
         (3,"here@now.com")
         ]

@app.route("/")
def main():
    message = ""
    try:
        db = sqlite3.connect('test.db')
        cursor = db.cursor()
        cursor.execute("CREATE TABLE users(id INTEGER PRIMARY KEY, email TEXT);")
        cursor.executemany('''INSERT INTO users (id,email)
                          values (?, ?)''',lots_of_records)
        db.commit()
        message = "Database was created, and some records were inserted."
    except Exception as error:
        db.rollback()
        message = "Something went wrong: " + str(error)
    finally:
        db.close()
        return message
    
app.run()

sqlite_basic_c.py

from flask import Flask
import sqlite3

app = Flask(__name__)

#uses the test.db created in the previous sqlite example!

@app.route("/")
def main():
    message = ""
    try:
        db = sqlite3.connect('test.db')
        cursor = db.cursor()
        
        #------------FETCH A SINGLE RECORD:
        cursor.execute("SELECT * FROM users WHERE id=?", (1,))
        #^^ supplied parameters must be in tuple format
        result = cursor.fetchone()
        message += "<h3>result with id=1:</h3><br>"
        message += result[1] + "<br>"
        cursor.execute("SELECT * FROM users WHERE id=? AND email=?", (2,"you@your.com"))
        result = cursor.fetchone()
        message += "<h3>result with id=2 AND email=you@your.com:</h3><br>"
        message += str(result[0]) + "&nbsp;" + result[1] + "<br>"
        #^^ the result is indexed by column nums. id == 0, email == 1, etc..
        #-----------------------------------

        #------------MULTIPLE RECORDS:
        cursor.execute("SELECT * FROM users")
        result = cursor.fetchall()
        message += "<h3>result with all records:</h3><br>"
        for record in result:
            message += "ID: " + str(record[0]) + ", email: " + record[1] + "<br>"
        #-----------------------------------
            
    except Exception as error:
        message = "Something went wrong: " + str(error)
        
    finally:
        db.close()
        return message
    
app.run()

sqlite_basic_d.py

from flask import Flask
import sqlite3
app = Flask(__name__)


###------------- sql statements that will be used:
sql_create = """
  CREATE TABLE IF NOT EXISTS users(
    id INTEGER PRIMARY KEY,
    email TEXT
  );
"""
sql_insert = "INSERT OR IGNORE INTO users (id, email) values (?, ?)"
sql_select = "SELECT * FROM users"


###------------- on server initialise:
db = sqlite3.connect('info.db') #creates new database
db.cursor().execute(sql_create) #create users table
db.cursor().execute(sql_insert, (1, "me@my.com")) #insert tuple
try:
  db.commit() #save
except Exception:
  db.rollback() #oops!
finally:
  db.close() #close


###------------- on application landing:
@app.route("/")
def main():
  html = ""
  try:
    db = sqlite3.connect('info.db')
    html = str(db.cursor().execute(sql_select).fetchall())
  except Exception as error_msg:
    html = str(error_msg)
  finally:
    db.close()
    return html
    
app.run(debug=True)