# 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 = """ Temperature Data Entry Enter country and temperature. Country:  Temperature:     """ @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 = "ID" result += "Country" result += "Temperature" for row in rows: result += f"{row[0]}" result += f"{row[1]}" result += f"{row[2]}" result += "" 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) Copy and paste the code above into the following free online development environment or use your own Python (Flask) compiler / interpreter / IDE. Repl.it Flask
{'wiki_source': 'wikiversity', 'other_info': "{'content_model': 'wikitext', 'language': 'en', 'title': 'Server-Side Scripting/SQL Databases/Python (Flask)', 'type': 'text'}"}