SQLite Distribution Example
SQLite is covered in more detail here
sqlstrings.py
create_database = """ CREATE TABLE package ( pack_id INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT, cust_name TEXT, dec_signed TEXT, dest_region TEXT, status TEXT, courier TEXT ); INSERT INTO package (pack_id, cust_name, dec_signed, dest_region, status, courier) values (1000, "Cole", "True", "NORTH", "RECEIVED", ""); """ tracker_display_sql = "SELECT * FROM package WHERE pack_id == ?" customer_new_delivery_sql = """ INSERT INTO package (cust_name,dec_signed,dest_region,status,courier) values (?,?,?,"RECEIVED","") """ get_most_recent_package_id = """SELECT pack_id FROM package ORDER BY pack_id DESC limit 1""" get_delivery_list = "SELECT * FROM package WHERE dest_region == ?" update_delivery = """UPDATE package SET status = 'DELIVERING', courier = ? WHERE dest_region == ?"""
lfs.py
from flask import * ##################### CREATE DATABASE: import sqlite3 from sqlstrings import * import os if not(os.path.exists("lfs.db")): #on first launch db = sqlite3.connect('lfs.db') db.cursor().executescript(create_database) db.close() ##################### SETUP EMPLOYEE ACCOUNTS: users = { # username : [ password, full name ] "jane@altavista.com":["abc123","Jane Citizen"], "wayne@netscape.com":["wayne07","Wayne Smith"], } ##################### CREATE APP AND SET UP LOGIN LOGOUT FUNCTIONS: app = Flask(__name__) app.secret_key = "sssshhhhhhhhh!" @app.route("/") def root(): if session.get("logged_in") == True: return render_template("home.html", user=session["user"]) else: return render_template("home.html") @app.route("/login", methods=["POST"]) def login(): username = request.form["username"] password = request.form["password"] if username in users: if password == users[username][0]: session["logged_in"] = True session["user"] = users[username][1] return redirect("/") @app.route("/logout") def logout(): session.pop("user", None) session.pop("logged_in", None) return redirect("/") ###################################################### ##################### 1. TRACK MY PACKAGE (unsecured): @app.route("/tracker") def tracker(): if session.get("logged_in") == True: return render_template("tracker.html", user=session["user"]) else: return render_template("tracker.html") @app.route("/tracker_display", methods=["GET"]) def tracker_display(): pid = request.args.get("pack_id") db = sqlite3.connect('lfs.db') result = db.cursor().execute(tracker_display_sql, (pid,)).fetchone() db.close() if session.get("logged_in") == True: return render_template("tracker_result.html", main=result, user=session["user"]) else: return render_template("tracker_result.html", main=result) ###################################################### ##################### 2. LOG NEW DELIVERY: @app.route("/customer") def customer(): if session.get("logged_in") == True: return render_template("new_delivery.html", user=session["user"]) else: return redirect("/") # secured, redirect if not logged in @app.route("/customer_new_delivery", methods=["POST"]) def customer_new_delivery(): if session.get("logged_in") == True: cust_name = request.form["cust_name"] dec_signed = request.form["dec_signed"] dest_region = request.form["dest_region"] db = sqlite3.connect('lfs.db') db.cursor().execute(customer_new_delivery_sql, (cust_name,dec_signed,dest_region)) db.commit() most_recent_pack_id = db.cursor().execute(get_most_recent_package_id).fetchone() db.close() return render_template("new_delivery_result.html", main="Your pack ID: " + str(most_recent_pack_id[0]), user=session["user"]) else: return redirect("/") ###################################################### ##################### 3. DISPATCH DELIVERIES: @app.route("/dispatch", methods=["GET"]) def dispatch(): if session.get("logged_in") == True: dest_region = request.args.get("region") db = sqlite3.connect('lfs.db') result = db.cursor().execute(get_delivery_list, (dest_region,)).fetchall() db.close() return render_template("dispatch.html", user=session["user"], main=str(result), r=dest_region) #r=hidden region else: return redirect("/") @app.route("/mark_as_delivering", methods=["POST"]) def mark_as_delivering(): if session.get("logged_in") == True: courier_name = request.form["courier_name"] dest_region = request.form["dest_region"].strip() db = sqlite3.connect('lfs.db') db.cursor().execute(update_delivery, (courier_name,dest_region,)) db.commit() db.close() return redirect("/") ###################################################### ##################### 4. DRIVER ASSISTANCE: @app.route("/delivery") def delivery(): if session.get("logged_in") == True: return render_template("driver_assistance.html", user=session["user"]) else: return redirect("/") app.run(debug=True)
templates folder:
templates\home.html
<header> <title>Local Freight Services</title> <h1 class="page-title">Local Freight Services</h1> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"> <link rel="stylesheet" href="{{ url_for('static', filename='lfs.css') }}"> </header> <main> <div class="intro"> <h1 class="title">Your local delivery service.</h1> </div> <div class="account"> <p class="meta">Current location: <a href="/">home</a><br> {% if user is defined %} Logged in as: <mark>{{user}}</mark>. <a href="/logout">logout</a></p> {% endif %} </div> <div class="icons"> <a href="/"><span class="fa fas fa-home"></span></a> <a href="/tracker"><span class="fa fa-map-marker"></span></a> </div> <div class="links"> <h3>Links</h3> <p><i class="fa fa-map-marker"></i> <a href="/tracker">Track my delivery</a></p> <p><i class="fa fa-lock"></i> <a href="/customer">New delivery request</a></p> <p><i class="fa fa-lock"></i>Warehouse dispatch<br> <a href="/dispatch?region=NORTH">NORTH</a> <a href="/dispatch?region=EAST">EAST</a> <a href="/dispatch?region=WEST">WEST</a> <a href="/dispatch?region=SOUTH">SOUTH</a> </p> <p><i class="fa fa-lock"></i> <a href="/delivery">Driver assistance</a></p> </div> <div class="content"> {% if user is not defined %} <form action="/login" method="post"> <label for="username">Username:</label> <input type="text" name="username" required><br> <label for="password">Password:</label> <input type="password" name="password" required> <input type="submit" value="Login"> </form> {% endif %} <p>We have one large warehouse where we operate our entire delivery service from, which is located in the main street of town. Bring your articles of postage to our customer service outlet at the front of the warehouse, where our customer service attendant will receive your article and tender the delivery fee. Within 24 hours your article is processed and dispatched by the Delivery Dispatch Manager to a Courier for delivery.</p> </div> </main> <footer> <p>Copyright © 2019 Local Freight Services. All rights reserved. <a class="twitter-share-button" href="https://twitter.com/#"><span class="fa fa-twitter"></span></a> <a href="https://facebook.com/#"><span class="fa fa-facebook-square"></span></a> <a href="https://plus.google.com/#"><span class="fa fa-google-plus-square"></span></a> </p> </footer> <script src="{{ url_for('static',filename='lfs.js') }}"></script>
templates\new_delivery.html
<header> <title>Local Freight Services</title> <h1 class="page-title">Local Freight Services</h1> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"> <link rel="stylesheet" href="{{ url_for('static', filename='lfs.css') }}"> </header> <main> <div class="intro"> <h1 class="title">Your local delivery service.</h1> </div> <div class="account"> <p class="meta">Current location: <a href="/">home</a>→<a href='/customer'>New delivery request</a><br> Logged in as: <mark>{{user}}</mark>. <a href="/logout">logout</a></p> </div> <div class="icons"> <a href="/"><span class="fa fas fa-home"></span></a> <a href="/tracker"><span class="fa fa-map-marker"></span></a> </div> <div class="links"> <h3>Links</h3> <p><i class="fa fa-map-marker"></i> <a href="/tracker">Track my delivery</a></p> <p><i class="fa fa-lock"></i> <a href="/customer">New delivery request</a></p> <p><i class="fa fa-lock"></i>Warehouse dispatch<br> <a href="/dispatch?region=NORTH">NORTH</a> <a href="/dispatch?region=EAST">EAST</a> <a href="/dispatch?region=WEST">WEST</a> <a href="/dispatch?region=SOUTH">SOUTH</a> </p> <p><i class="fa fa-lock"></i> <a href="/delivery">Driver assistance</a></p> </div> <div class="content"> <p> <form action='/customer_new_delivery' method='POST'> cust_name: <input type='text' name='cust_name'><br> dec_signed: <select name="dec_signed"> <option value="True">True</option> <option value="False">False</option> </select><br> dest_region: <select name="dest_region"> <option value="NORTH">NORTH</option> <option value="SOUTH">SOUTH</option> <option value="WEST">WEST</option> <option value="EAST">EAST</option> </select><br> <input type='submit'> </form> </div> </main> <footer> <p>Copyright © 2019 Local Freight Services. All rights reserved. <a class="twitter-share-button" href="https://twitter.com/#"><span class="fa fa-twitter"></span></a> <a href="https://facebook.com/#"><span class="fa fa-facebook-square"></span></a> <a href="https://plus.google.com/#"><span class="fa fa-google-plus-square"></span></a> </p> </footer> <script src="{{ url_for('static',filename='lfs.js') }}"></script>
new_delivery_result.html
<header> <title>Local Freight Services</title> <h1 class="page-title">Local Freight Services</h1> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"> <link rel="stylesheet" href="{{ url_for('static', filename='lfs.css') }}"> </header> <main> <div class="intro"> <h1 class="title">Your local delivery service.</h1> </div> <div class="account"> <p class="meta">Current location: <a href="/">home</a>→<a href='/customer'>New delivery request</a><br> Logged in as: <mark>{{user}}</mark>. <a href="/logout">logout</a></p> </div> <div class="icons"> <a href="/"><span class="fa fas fa-home"></span></a> <a href="/tracker"><span class="fa fa-map-marker"></span></a> </div> <div class="links"> <h3>Links</h3> <p><i class="fa fa-map-marker"></i> <a href="/tracker">Track my delivery</a></p> <p><i class="fa fa-lock"></i> <a href="/customer">New delivery request</a></p> <p><i class="fa fa-lock"></i>Warehouse dispatch<br> <a href="/dispatch?region=NORTH">NORTH</a> <a href="/dispatch?region=EAST">EAST</a> <a href="/dispatch?region=WEST">WEST</a> <a href="/dispatch?region=SOUTH">SOUTH</a> </p> <p><i class="fa fa-lock"></i> <a href="/delivery">Driver assistance</a></p> </div> <div class="content"> <p> {{main|safe}} </div> </main> <footer> <p>Copyright © 2019 Local Freight Services. All rights reserved. <a class="twitter-share-button" href="https://twitter.com/#"><span class="fa fa-twitter"></span></a> <a href="https://facebook.com/#"><span class="fa fa-facebook-square"></span></a> <a href="https://plus.google.com/#"><span class="fa fa-google-plus-square"></span></a> </p> </footer> <script src="{{ url_for('static',filename='lfs.js') }}"></script>
dispatch.html
<header> <title>Local Freight Services</title> <h1 class="page-title">Local Freight Services</h1> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"> <link rel="stylesheet" href="{{ url_for('static', filename='lfs.css') }}"> </header> <main> <div class="intro"> <h1 class="title">Your local delivery service.</h1> </div> <div class="account"> <p class="meta">Current location: <a href="/">home</a>→Warehouse dispatch<br> Logged in as: <mark>{{user}}</mark>. <a href="/logout">logout</a></p> </div> <div class="icons"> <a href="/"><span class="fa fas fa-home"></span></a> <a href="/tracker"><span class="fa fa-map-marker"></span></a> </div> <div class="links"> <h3>Links</h3> <p><i class="fa fa-map-marker"></i> <a href="/tracker">Track my delivery</a></p> <p><i class="fa fa-lock"></i> <a href="/customer">New delivery request</a></p> <p><i class="fa fa-lock"></i>Warehouse dispatch<br> <a href="/dispatch?region=NORTH">NORTH</a> <a href="/dispatch?region=EAST">EAST</a> <a href="/dispatch?region=WEST">WEST</a> <a href="/dispatch?region=SOUTH">SOUTH</a> </p> <p><i class="fa fa-lock"></i> <a href="/delivery">Driver assistance</a></p> </div> <div class="content"> <p> {{main|safe}}<br> <form action='/mark_as_delivering' method='POST'> <input name="dest_region" type="hidden" value="{{r}}"> Assign all to courier name: <input type='text' name='courier_name'><br> <input type='submit'> </form> </div> </main> <footer> <p>Copyright © 2019 Local Freight Services. All rights reserved. <a class="twitter-share-button" href="https://twitter.com/#"><span class="fa fa-twitter"></span></a> <a href="https://facebook.com/#"><span class="fa fa-facebook-square"></span></a> <a href="https://plus.google.com/#"><span class="fa fa-google-plus-square"></span></a> </p> </footer> <script src="{{ url_for('static',filename='lfs.js') }}"></script>
tracker.html
<header> <title>Local Freight Services</title> <h1 class="page-title">Local Freight Services</h1> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"> <link rel="stylesheet" href="{{ url_for('static', filename='lfs.css') }}"> </header> <main> <div class="intro"> <h1 class="title">Your local delivery service.</h1> </div> <div class="account"> <p class="meta">Current location: <a href="/">home</a>→<a href='/tracker'>Track my delivery</a><br> {% if user is defined %} Logged in as: <mark>{{user}}</mark>. <a href="/logout">logout</a></p> {% endif %} </div> <div class="icons"> <a href="/"><span class="fa fas fa-home"></span></a> <a href="/tracker"><span class="fa fa-map-marker"></span></a> </div> <div class="links"> <h3>Links</h3> <p><i class="fa fa-map-marker"></i> <a href="/tracker">Track my delivery</a></p> <p><i class="fa fa-lock"></i> <a href="/customer">New delivery request</a></p> <p><i class="fa fa-lock"></i>Warehouse dispatch<br> <a href="/dispatch?region=NORTH">NORTH</a> <a href="/dispatch?region=EAST">EAST</a> <a href="/dispatch?region=WEST">WEST</a> <a href="/dispatch?region=SOUTH">SOUTH</a> </p> <p><i class="fa fa-lock"></i> <a href="/delivery">Driver assistance</a></p> </div> <div class="content"> {% if user is not defined %} <form action="/login" method="post"> <label for="username">Username:</label> <input type="text" name="username" required><br> <label for="password">Password:</label> <input type="password" name="password" required> <input type="submit" value="Login"> </form> {% endif %} <p> <form action='/tracker_display' method='GET'> pack_id: <input type='text' name='pack_id'> <input type='submit'> </form> </div> </main> <footer> <p>Copyright © 2019 Local Freight Services. All rights reserved. <a class="twitter-share-button" href="https://twitter.com/#"><span class="fa fa-twitter"></span></a> <a href="https://facebook.com/#"><span class="fa fa-facebook-square"></span></a> <a href="https://plus.google.com/#"><span class="fa fa-google-plus-square"></span></a> </p> </footer> <script src="{{ url_for('static',filename='lfs.js') }}"></script>
tracker_result.html
<header> <title>Local Freight Services</title> <h1 class="page-title">Local Freight Services</h1> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"> <link rel="stylesheet" href="{{ url_for('static', filename='lfs.css') }}"> </header> <main> <div class="intro"> <h1 class="title">Your local delivery service.</h1> </div> <div class="account"> <p class="meta">Current location: <a href="/">home</a>→<a href='/tracker'>Track my delivery</a><br> {% if user is defined %} Logged in as: <mark>{{user}}</mark>. <a href="/logout">logout</a></p> {% endif %} </div> <div class="icons"> <a href="/"><span class="fa fas fa-home"></span></a> <a href="/tracker"><span class="fa fa-map-marker"></span></a> </div> <div class="links"> <h3>Links</h3> <p><i class="fa fa-map-marker"></i> <a href="/tracker">Track my delivery</a></p> <p><i class="fa fa-lock"></i> <a href="/customer">New delivery request</a></p> <p><i class="fa fa-lock"></i>Warehouse dispatch<br> <a href="/dispatch?region=NORTH">NORTH</a> <a href="/dispatch?region=EAST">EAST</a> <a href="/dispatch?region=WEST">WEST</a> <a href="/dispatch?region=SOUTH">SOUTH</a> </p> <p><i class="fa fa-lock"></i> <a href="/delivery">Driver assistance</a></p> </div> <div class="content"> {% if user is not defined %} <form action="/login" method="post"> <label for="username">Username:</label> <input type="text" name="username" required><br> <label for="password">Password:</label> <input type="password" name="password" required> <input type="submit" value="Login"> </form> {% endif %} <p> <!-- STYLING THE RESULT IN THE TEMPLATE HTML --> <table border=1 style="border-collapse: collapse; border-color: salmon;"> <tr><td>Package ID:</td><td>{{main[0]}}</td></tr> <tr><td>Sender:</td><td>{{main[1]}}</td></tr> <tr><td>Declaration signed:</td><td>{{main[2]}}</td></tr> </table> <!-- TODAYS LESSON - WE REPLACED THE main jinja var with this --> </div> </main> <footer> <p>Copyright © 2019 Local Freight Services. All rights reserved. <a class="twitter-share-button" href="https://twitter.com/#"><span class="fa fa-twitter"></span></a> <a href="https://facebook.com/#"><span class="fa fa-facebook-square"></span></a> <a href="https://plus.google.com/#"><span class="fa fa-google-plus-square"></span></a> </p> </footer> <script src="{{ url_for('static',filename='lfs.js') }}"></script>
driver_assistance.html
<header> <title>Local Freight Services</title> <h1 class="page-title">Local Freight Services</h1> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"> <link rel="stylesheet" href="{{ url_for('static', filename='lfs.css') }}"> </header> <main> <div class="intro"> <h1 class="title">Your local delivery service.</h1> </div> <div class="account"> <p class="meta">Current location: <a href="/">home</a>→<a href='/delivery'>Driver assistance</a><br> Logged in as: <mark>{{user}}</mark>. <a href="/logout">logout</a></p> </div> <div class="icons"> <a href="/"><span class="fa fas fa-home"></span></a> <a href="/tracker"><span class="fa fa-map-marker"></span></a> </div> <div class="links"> <h3>Links</h3> <p><i class="fa fa-map-marker"></i> <a href="/tracker">Track my delivery</a></p> <p><i class="fa fa-lock"></i> <a href="/customer">New delivery request</a></p> <p><i class="fa fa-lock"></i>Warehouse dispatch<br> <a href="/dispatch?region=NORTH">NORTH</a> <a href="/dispatch?region=EAST">EAST</a> <a href="/dispatch?region=WEST">WEST</a> <a href="/dispatch?region=SOUTH">SOUTH</a> </p> <p><i class="fa fa-lock"></i> <a href="/delivery">Driver assistance</a></p> </div> <div class="content"> <p> <h2>driver assistance or address information here?</h2> </div> </main> <footer> <p>Copyright © 2019 Local Freight Services. All rights reserved. <a class="twitter-share-button" href="https://twitter.com/#"><span class="fa fa-twitter"></span></a> <a href="https://facebook.com/#"><span class="fa fa-facebook-square"></span></a> <a href="https://plus.google.com/#"><span class="fa fa-google-plus-square"></span></a> </p> </footer> <script src="{{ url_for('static',filename='lfs.js') }}"></script>
static folder:
lfs.css
@import url("https://fonts.googleapis.com/css?family=Open+Sans:300,400,700"); .support { align-items: center; background: tomato; color: #fff; display: flex; font-size: .9rem; padding: 0 10px 0 0; position: fixed; right: 0; top: 0; width: auto; z-index: 100; } .support a { color: #fff; padding: 10px 0; text-decoration: none; } @supports (display: grid) { .support { background: #058a6e; right: 40px; -webkit-transform: translateX(100%); transform: translateX(100%); transition: right .5s ease-in-out, -webkit-transform .5s ease-in-out; transition: right .5s ease-in-out, transform .5s ease-in-out; transition: right .5s ease-in-out, transform .5s ease-in-out, -webkit-transform .5s ease-in-out; } .support:hover { right: 0; -webkit-transform: translateX(0%); transform: translateX(0%); } } .support svg { fill: #ffffff; height: 30px; margin-right: 5px; padding: 5px; width: 30px; } .unsupported { display: block; } @supports (display: grid) { .unsupported { display: none; } } .supported { display: none; } @supports (display: grid) { .supported { display: block; } } main { display: flex; flex-wrap: wrap; } @media (min-width: 60em) { main { justify-content: flex-end; } } @supports (display: grid) { main { display: grid; grid-gap: 1rem; grid-template-columns: 1fr 1fr; } @media (min-width: 60em) { main { grid-template-columns: repeat(4, 1fr); } } } @media (min-width: 60em) { .intro { flex: 0 0 calc(50% - 3rem); margin-left: calc(25% - 3rem); } } @supports (display: grid) { .intro { grid-column: 1 / span 2; } @media (min-width: 60em) { .intro { grid-column: 2 / span 2; margin-left: 0.5rem; } } } @media (min-width: 60em) { .links { order: 1; } } @supports (display: grid) { .links { grid-column: 1 / span 2; } @media (min-width: 60em) { .links { grid-column: 4 / span 1; } } } @supports (display: grid) { .content { grid-column: 1 / span 2; } @media (min-width: 60em) { .content { grid-column: 2; } } } @media (min-width: 60em) { .content { flex: 0 0 calc(50% - 3rem); } @supports (display: grid) { .content { grid-column: 2 / span 2; } } } .account, .icons { flex: 0 0 calc(50% - 3rem); } @media (min-width: 60em) { @supports (display: flex) { .account, .icons { flex: 0 0 calc(25% - 3rem); } } } /* // Styles: // ------------------------------------------------- */ body { background: #fff; color: #5a5c6c; font-family: 'Open Sans', sans-serif; font-size: 1rem; font-weight: 400; line-height: 1.5; margin: 1rem; } div { margin: 0.5rem; padding: 0 1rem; } @media (min-width: 60em) { @supports (display: flex) { div { flex: 0 0 calc(25% - 3rem); } } } div:hover { background-color: #fbf9fa; } .page-title { color: #7a1f5c; font-size: calc(3rem + .25vw); font-weight: 700; line-height: 1.3; margin: 1rem auto; max-width: calc(70rem + .25vw); text-align: center; } .title { font-weight: 300; line-height: 1.3; margin-bottom: 0; margin-top: 0; } @supports (initial-letter: 2) or (-webkit-initial-letter: 2) { .title:first-letter { color: #7a1f5c; font-weight: 700; initial-letter: 2; -webkit-initial-letter: 2; } } h2 { line-height: 1.2; } p, .icons a, h3 { margin-top: 1rem; } a { color: #bf4080; } a:hover { color: #7c1d46; } .icons { display: flex; justify-content: flex-end; } .icons a { font-size: 1.5rem; padding-left: 0.5rem; padding-right: 0.5rem; } .meta { font-family: Georgia, serif; font-size: calc(0.7rem + 0.25vw); font-style: italic; } form label { display: inline-block; width: 100px; } input { padding: 3px 5px; margin: 6px 0; box-sizing: border-box; } input:focus { border: 1px solid #555; } footer { height: 100px; background: #eee; text-align: center; margin-left: auto; margin-right: auto; }
lfs.js
//JavaScript