SQLite Airline

Airline Version 1:

CREATE
  LOOP:
    SELECT (display)
    INSERT
  END LOOP

airline_v1.py

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

################################## CREATE AIRLINE DATABASE ##########
db = sqlite3.connect("airline.db")
db.cursor().executescript("""
  CREATE TABLE IF NOT EXISTS passengers (
    seat TEXT PRIMARY KEY UNIQUE NOT NULL,
    name TEXT
  );

  INSERT OR IGNORE INTO passengers (seat, name) VALUES ('A1','Martha');
  INSERT OR IGNORE INTO passengers (seat, name) VALUES ('A2','Glenn');
""")
db.commit()
db.close()
######################################################################

display_passengers_html = """
  {% for each_passenger_tuple in passenger_list %}
    SEAT: <mark>{{each_passenger_tuple[0]}}</mark>,<br>
    PASSENGER NAME: <mark>{{each_passenger_tuple[1]}}</mark><br>
    <hr>
  {% endfor %}
  <br>
"""

add_new_passenger_html = """
  <form action='/addpassenger' method='POST'>
    SEAT: <input type='text' name='seat'><br>
    PASSENGER NAME: <input type='text' name='name'><br>
    <input type='submit'>
  </form>
"""

@app.route("/addpassenger", methods=["POST"])
def insert():
  seat = request.form["seat"]
  name = request.form["name"]
  db = sqlite3.connect("airline.db")
  db.cursor().execute("INSERT OR IGNORE INTO passengers (seat, name) VALUES (?,?)",(seat,name))
  db.commit()
  db.close()
  return redirect("/")

@app.route("/")
def main():
  db = sqlite3.connect("airline.db")
  records = db.cursor().execute("SELECT * FROM passengers").fetchall()
  db.close()
  html = display_passengers_html + add_new_passenger_html
  return render_template_string(html, passenger_list = records)

app.run(debug = True)

Airline Version 2:

CREATE
  LOOP:
    SELECT (display)
    INSERT
    UPDATE
    DELETE
  END LOOP

airline_v2.py

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

################################## CREATE AIRLINE DATABASE ##########
db = sqlite3.connect("airline.db")
db.cursor().executescript("""
  CREATE TABLE IF NOT EXISTS passengers (
    seat TEXT PRIMARY KEY UNIQUE NOT NULL,
    name TEXT
  );

  INSERT OR IGNORE INTO passengers (seat, name) VALUES ('A1','Martha');
  INSERT OR IGNORE INTO passengers (seat, name) VALUES ('A2','Glenn');
""")
db.commit()
db.close()
######################################################################

display_passengers_html = """
  {% for each_passenger_tuple in passenger_list %}
    SEAT: <mark>{{each_passenger_tuple[0]}}</mark>,<br>
    PASSENGER NAME: <mark>{{each_passenger_tuple[1]}}</mark><br>
    <a href='/delete?seat={{each_passenger_tuple[0]}}'>delete</a><br>
    <a href='/update?seat={{each_passenger_tuple[0]}}'>update</a><br>
    <hr>
  {% endfor %}
  <br>
"""

add_new_passenger_html = """
  <form action='/addpassenger' method='POST'>
    SEAT: <input type='text' name='seat'><br>
    PASSENGER NAME: <input type='text' name='name'><br>
    <input type='submit'>
  </form>
"""

update_existing_passenger_html = """
  <form action='/updatepassenger' method='POST'>
    <input type='hidden' name='originalseat' value={{os}}>
    SEAT: <input type='text' name='newseat' value={{ns}}><br>
    PASSENGER NAME: <input type='text' name='name' value={{n}}><br>
    <input type='submit'>
  </form>
"""

@app.route("/update", methods=["GET"])
def update_step1_view_existing():
  seat = request.args.get("seat")
  db = sqlite3.connect("airline.db")
  filter_by_seat_sql = "SELECT * FROM passengers WHERE seat = ?"
  record = db.cursor().execute(filter_by_seat_sql,(seat,)).fetchone()
  print(record)
  os = record[0] #os = original seat (unchangeable - hidden field)
  ns = record[0] #ns = new seat (initially original seat)
  n = record[1] #n = name
  db.close()
  return render_template_string(update_existing_passenger_html, os=os, ns=ns, n=n)

@app.route("/updatepassenger", methods=["POST"])
def update_step2_commit_changes():
  originalseat = request.form["originalseat"]
  newseat = request.form["newseat"]
  name = request.form["name"]
  db = sqlite3.connect("airline.db")
  update_query_sql = """
UPDATE passengers
SET seat = ?, name = ?
WHERE seat = ?;
"""
  db.cursor().execute(update_query_sql,(newseat,name,originalseat))
  db.commit()
  db.close()
  return redirect("/")

@app.route("/addpassenger", methods=["POST"]) #POST values submitted via form
def insert():
  seat = request.form["seat"]
  name = request.form["name"]
  db = sqlite3.connect("airline.db")
  db.cursor().execute("INSERT OR IGNORE INTO passengers (seat, name) VALUES (?,?)",(seat,name))
  db.commit()
  db.close()
  return redirect("/")

@app.route("/delete", methods=["GET"]) #GET value of seat from URL ?seat=...
def remove():
  seat = request.args.get("seat")
  db = sqlite3.connect("airline.db")
  db.cursor().execute("DELETE FROM passengers WHERE seat == ?",(seat,))
  db.commit()
  db.close()
  return redirect("/")
  
@app.route("/")
def main():
  db = sqlite3.connect("airline.db")
  records = db.cursor().execute("SELECT * FROM passengers").fetchall()
  db.close()
  html = display_passengers_html + add_new_passenger_html
  return render_template_string(html, passenger_list = records)

app.run(debug = True)