Note Taking App with Python, Bottle and SQLite

This is a simple note taking web app where you create a note sing an HTML form at the top of the page, and then all notes that have been created so far are displayed underneath.

We use the Bottle Web App framework to create the web app with Python and then use SQLite to store the note records and be able to retrieve them.

We import all the modules and functions to begin with.

We then create a class for database functions so that we have a container for creating the database table, adding records, and selecting records.

Next we have our index() function which will call the select fucntion in the database class and retrieve all notes that have been created. We then turn that list into an HTML report.

We then create the HTML form to add a note and then create the HTML page that Bottle will return to the user.

We then create the record using the add_note() function with a @post decorator. This fucntion gets the note value from the form and creates a timestamp. These values are then sent to the db.add() function. The add() function adds the timestamp and the note to the database.

add_note() then redirects the user back to the index page.

Finally we call the db.create() function to create the database and table if they do not yet exist.

And we call the run() function to run the Bottle Web App Server when the script is triggered.

Notes:

• The database file will be created in your current working directory and will try to be read from there.

• Creating the HTML pages like we do within the Bottle functions is not the 'proper' or 'secure' way to render an HTML page with Bottle. This should be done using a template file. We are just doing it this was to make the lab simple to understand.

python3 -m pip install bottle

from bottle import run, route, post, redirect, request
import sqlite3
from datetime import datetime

class db:
    def create():
        conn = sqlite3.connect('note.db')
        cursor = conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS note (
                timestamp text,
                note text
            )
        ''')
        conn.commit()
        conn.close()

    def add(timestamp,note):
        conn = sqlite3.connect('note.db')
        cursor = conn.cursor()
        cursor.execute('INSERT INTO note (timestamp, note) VALUES (?,?)', (timestamp,note))
        conn.commit()
        conn.close()

    def select():
        conn = sqlite3.connect('note.db')
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM note order by timestamp desc')
        note = cursor.fetchall()
        conn.close()
        return note

@route('/')
def index():
    note = db.select()
    note_report = ''
    for x in note:
        note_report += f'<hr>{x[0]} -- {x[1]}<br>'

    form = '''
            <form action="./add" method="post">
            New Note: <input type="text" name="note">
            <input type="submit">
            </form>
            '''

    page = f'''
            {form}<br>
            {note_report}
            '''
    return(page)

@post('/add')
def add_note():
    timestamp = datetime.now()
    note = request.forms.get('note')
    db.add(timestamp,note)
    redirect('/')

db.create()
run(host='127.0.0.1', port='8080')