Server-Side Scripting/SQL Databases/Python (Flask)

app.py

# This program creates and displays a temperature database
# with options to insert, update, and delete records.
#
# References:
#   https://en.wikibooks.org/wiki/Python_Programming
#   https://en.wikiversity.org/wiki/Python_Programming/Databases

import flask
import sqlite3

app = flask.Flask(__name__)

DATABASE = "temperature.db"

FORM = """
<h1>Temperature Data Entry</h1>
<p>Enter country and temperature. Enter again to update.</p>
<p>Enter country without temperature to delete.</p>
<form method="POST">
<p><label for="country">Country:</label>
<input type="text" id="country" name="country" required></p>
<p><label for="stop">Temperature:</label>
<input type="text" id="temperature" name="temperature"></p>
<p><input type="submit" name="submit" value="Submit"></p>
</form>
<hr>
"""

@app.route('/', methods=["GET"])
def root_get():
    try:
        check_database()
        return FORM + get_data()
    except Exception as exception:
        return exception

@app.route('/', methods=["POST"])
def root_post():
    try:
        country = flask.request.form["country"].strip()
        temperature = flask.request.form["temperature"].strip()

        if not country_exists(country):
            insert_country(country, temperature)
        elif temperature != "":
            update_country(country, temperature)
        else:
            delete_country(country)

        return FORM + get_data()
    except Exception as exception:
        return exception

def check_database():
    with sqlite3.connect(DATABASE) as connection:
        cursor = connection.cursor()
        sql = """
            SELECT COUNT(*) AS Count FROM sqlite_master
            WHERE name = 'Countries';
        """
        cursor.execute(sql)
        count = cursor.fetchone()[0]
        if count > 0:
            return
        
        sql = """
            CREATE TABLE Countries(
                ID INTEGER PRIMARY KEY AUTOINCREMENT,
                Country TEXT UNIQUE NOT NULL,
                Temperature REAL NOT NULL);
        """
        cursor.execute(sql)

def get_data():
    with sqlite3.connect(DATABASE) as connection:
        cursor = connection.cursor()
        sql = """
            SELECT ID, Country, Temperature FROM Countries;
        """
        cursor.execute(sql)
        rows = cursor.fetchall()
        result = "<table><tr><th>ID</th>"
        result += "<th>Country</th>"
        result += "<th>Temperature</th></tr>"
        for row in rows:
            result += f"<tr><td>{row[0]}</td>"
            result += f"<td>{row[1]}</td>"
            result += f"<td>{row[2]}</td></tr>"
        result += "</table>"
        return result

def country_exists(country):
    with sqlite3.connect(DATABASE) as connection:
        cursor = connection.cursor()
        sql = """
            SELECT EXISTS(
                SELECT * FROM Countries
                WHERE Country = ?) AS Count;
        """
        parameters = (country,)
        cursor.execute(sql, parameters)
        result = cursor.fetchone()[0]
        return result

def insert_country(country, temperature):
    with sqlite3.connect(DATABASE) as connection:
        cursor = connection.cursor()
        sql = """
            INSERT INTO Countries (Country, Temperature)
            VALUES(?, ?);
        """
        parameters = (country, temperature)
        cursor.execute(sql, parameters)
        connection.commit()

def update_country(country, temperature):
    with sqlite3.connect(DATABASE) as connection:
        cursor = connection.cursor()
        sql = """
            UPDATE Countries
            SET Temperature = ?
            WHERE Country = ?;
        """
        parameters = (temperature, country)
        cursor.execute(sql, parameters)
        connection.commit()

def delete_country(country):
    with sqlite3.connect(DATABASE) as connection:
        cursor = connection.cursor()
        sql = """
            DELETE FROM Countries
            WHERE Country = ?;
        """
        parameters = (country, )
        cursor.execute(sql, parameters)
        connection.commit()

if __name__ == "__main__":
    app.run(host='0.0.0.0', port=5000)

Try It

Copy and paste the code above into the following free online development environment or use your own Python (Flask) compiler / interpreter / IDE.

• Repl.it

See Also

• Flask