DinerManagerGUIBackend.py

from flask import Flask, request, jsonify, Response
import sqlite3
import json

app = Flask(__name__)


def connectdb():
    conn = sqlite3.connect('diners.db')
    if not conn: print ("error")
    return conn


@app.route('/canteens', defaults={'canteen_id': None})  # Response to GET
@app.route('/canteens/<int:canteen_id>', methods=['GET'])  # GET with ID
def get_canteens(canteen_id):
    conn = connectdb()
    cursor = conn.cursor()

    if canteen_id is not None:
        # if ID specified
        cursor.execute("SELECT * FROM CANTEEN WHERE id=?", (canteen_id,))
        row = cursor.fetchone()  # row tuple
        conn.close()
        if row:
            canteen = {
                'id': row[0],
                'name': row[1],
                'location': row[2],
                'time_open': row[3],
                'time_closed': row[4]
            }
            return jsonify(canteen)
        else:
            return jsonify({"error": "Canteen not found"}), 404
    else:
        # if filtered by time
        open_time = request.args.get('open_time')
        close_time = request.args.get('close_time')
        if open_time and close_time:
            cursor.execute("""
                SELECT * FROM CANTEEN WHERE time_open = ?  AND time_closed = ? """,
                           (open_time, close_time))  # select rows based on given time range
        # if no filter select all
        else:
            cursor.execute("SELECT * FROM CANTEEN")
        rows = cursor.fetchall()
        canteens = []
        for row in rows:
            canteen = {
                'id': row[0],
                'name': row[1],
                'location': row[2],
                'time_open': row[3],
                'time_closed': row[4]
            }
            canteens.append(canteen)
        conn.close()
        return Response(json.dumps(canteens), status=200, mimetype='application/json')  # jsonify didn't work here properly?


# add new canteen
@app.route('/canteens', methods=['POST'])
def post_canteen():
    conn = connectdb()
    cursor = conn.cursor()

    # get JSON request body
    data = request.get_json()
    new_name = data['name']
    new_location = data['location']
    new_time_open = data['time_open']
    new_time_closed = data['time_closed']

    # SQL insert
    cursor.execute("INSERT INTO CANTEEN (Name, Location, time_open, time_closed) \
             VALUES (?, ?, ?, ?)", (new_name, new_location, new_time_open, new_time_closed))

    conn.commit()
    last_row_id = cursor.lastrowid
    conn.close()
    return jsonify({"message": f"New canteen with id: {last_row_id} added"}), 201


# update canteen
@app.route('/canteens/<int:id>', methods=['PUT'])
def update_canteen(id):
    conn = connectdb()
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM CANTEEN WHERE id=?", (id,))
    update_canteen = cursor.fetchone()
    if not update_canteen:
        conn.close()
        return jsonify({"error": "Canteen not found"}), 404

    # get data
    data = request.get_json()
    name = data.get('name', update_canteen[1])
    location = data.get('location', update_canteen[2])
    time_open = data.get('time_open', update_canteen[3])
    time_closed = data.get('time_closed', update_canteen[4])

    # SQL update
    cursor.execute("UPDATE CANTEEN SET Name = ?, Location = ?, time_open = ?, time_closed = ? WHERE id = ?",
                   (name, location, time_open, time_closed, id))
    conn.commit()
    conn.close()

    return jsonify({"message": "Canteen updated"}), 200


# delete canteen
@app.route('/canteens/<int:id>', methods=['DELETE'])
def delete_canteen(id):
    conn = connectdb()
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM CANTEEN WHERE id=?", (id,))
    selected_canteen = cursor.fetchone()
    if not selected_canteen:
        conn.close()
        return jsonify({"error": "Canteen not found"}), 404
    cursor.execute("DELETE FROM CANTEEN WHERE id=?", (id,))
    conn.commit()
    conn.close()
    return jsonify({"message": "Canteen deleted"}), 200

if __name__ == '__main__':
    app.run()