Python Trainer

Quick start

Do steps 2 and 3 first. Before the allowed time limit starts, you must create the venv and install Flask before you run Create CRUD App.

1. Open an empty folder in VS Code (File → Open Folder).
2. Open the terminal (Ctrl+`) and create the virtual environment:

python -m venv venv
venv\Scripts\activate

3. Install Flask (the terminal should show (venv) at the start of the line):

pip install flask

4. Press Ctrl+Shift+P, run Python Trainer: Create CRUD App, and pick an app from the list (see below).
5. Run the app in the same terminal:

python app.py

6. Open http://127.0.0.1:5000 in your browser.

If any file the app needs already exists, the command lists the files and changes nothing.

Every app is created with a static/style.css file that is already linked in its HTML, so you can restyle the pages by editing that one file:

<link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">

Apps you can create

The tables and columns follow the skill test papers. Run python app.py and open http://127.0.0.1:5000. The database file app.db is created on the first run.

Neil's versions

These four are standalone apps (they do not use crud.py). Each has its own app.py, db.py, and templates/ folder. The database file is created on the first run: Clinic.db, Attendance.db, Registration.db, or Intramurals.db. The Intrams app uses sessions, so register a user (pick the admin role) and log in first.

Books (ISBN)

• Search fills the form and shows ITEM IS FOUND, or shows ITEM NOT FOUND.
• Edit saves every field except the ISBN and shows RECORD SUCCESSFULLY UPDATED. A blank form shows NO RECORD TO EDIT, and an unknown ISBN shows ISBN IS NOT FOUND.
• Delete removes the record and shows RECORD SUCCESSFULLY DELETED.
• Add shows RECORD SUCCESSFULLY SAVED. A blank form shows NO RECORD TO ADD, and an existing ISBN shows RECORD ALREADY EXISTS. In both cases the cursor goes to the ISBN box.
• The list always shows a TOTAL column (price × quantity), the total number of books, and the grand total.

Events Registration

• Events and participants each have a page with Add, Search, Update, Delete, and a list.
• Participant Registration computes the fee paid as the event's evRFee minus the participant's partDRate. The list shows the transactions from latest to oldest.
• Registration Monitoring filters by event name and shows the participant's full name, date, and fee paid. It also shows COUNT() of records, SUM() of fees paid, and total discounts as (count × evRFee) − sum.

Employee Attendance

• Departments and employees use the same Add, Search, Update, Delete page, with Edit and Del links on each row.
• Attendance Recording saves date/time in and out, and each row has a Cancel button.
• Attendance Monitoring works by employee number (name, department, records, total hours, rate per hour, salary) or by date range (records and total hours). Cancelled records are not counted.

Clinic Consultations

• Doctors, patients, and consultations each have an Add, Search, Update, Delete page.
• Consultations Inquiry has the five searches from the paper: doctors by specialization, patients by age range, consultations by patient ID, by doctor ID, and by date range. Every result shows a COUNT.

ICT Congress Registration

• Registration is the Add, Search, Update, Delete page for students.
• Attendance looks up an ID and records it with the prompts ID # is NOT YET REGISTERED, Student Attendance is SUCCESSFULLY RECORDED, and Student's Attendance RECORD ALREADY EXISTS.
• Raffle picks a random winner from students who attended. Tick campuses to filter, or tick none for all.
• Report (By Campus) lists students with the number of registrants, total collection, and number of attendees.
• Report (Summary) shows registered, attended, and total collection for each campus, plus totals.
• The campuses are Main, Banilad, LM, and Pardo. Change the CAMPUSES list in app.py to edit them.

How crud.py works (all apps except Basic and Books)

These four apps share a file called crud.py. It creates the page /manage/<key> for every table you describe in ENTITIES inside app.py. Each page has the fields, the Add, Search, Update, and Delete buttons, a prompt box, and a list.

ENTITIES = {
    "doctors": {
        "title": "Doctors Management",   # page heading
        "table": "Doctor",               # SQLite table name
        "pk": "docID",                   # primary key column
        "fields": [
            {"name": "docID", "label": "Doctor ID", "type": "number"},
            {"name": "docLName", "label": "Last Name", "type": "text"},
        ],
    },
}
register_crud(app, ENTITIES)

Field types: text, number, date, datetime-local, textarea, and select. A select gets its choices from options, which is either a SQL query that returns (value, label) or a plain list:

{"name": "depCode", "label": "Dept.", "type": "select",
 "options": "SELECT depCode, depName FROM Departments ORDER BY depName"}
{"name": "campus", "label": "Campus", "type": "select", "options": ["Main", "Banilad"]}

Add a new table page

1. Add the table to SCHEMA with CREATE TABLE IF NOT EXISTS.
2. Add an entry to ENTITIES. Its key becomes the URL, for example "members" gives /manage/members.
3. Add a link to the menu in index():

("Members Management", url_for("manage", key="members")),

Add a page with your own query

Write a normal route, add a file in templates/, and link it from the menu:

@app.route("/summary")
def summary():
    rows = get_db().execute("SELECT campus, COUNT(*) AS n FROM Registration GROUP BY campus").fetchall()
    return render_template("summary.html", rows=rows)

Start the template with {% extends "base.html" %} and put your content inside {% block content %}. The "Back to Menu" link is added automatically.

If you change the columns of a table that already exists, delete app.db so it is created again.

What the Basic CRUD app creates

your-folder/
├── app.py                 # routes + database code
├── templates/
│   └── index.html         # the page the browser shows
└── app.db                 # created automatically on first run

How the CRUD works (Basic CRUD app)

The sections below use the Basic CRUD app as the example. The same ideas apply to every app.

• get_db() opens the SQLite connection and close_db() closes it after each request.
• init_db() creates the books table the first time the app runs.
• Always use ? placeholders in SQL. Never build SQL with string formatting.
• Call db.commit() after every INSERT, UPDATE, or DELETE, or the change is not saved.

How the SQLite functions work

These are the Python sqlite3 calls used in every route.

Example: UPDATE

def update_book(book_id, title, author, year):
    db = get_db()
    cur = db.execute(
        "UPDATE books SET title = ?, author = ?, year = ? WHERE id = ?",
        (title, author, year, book_id),
    )
    db.commit()
    return cur.rowcount   # 1 if the book was found, 0 if the id does not exist

How to read it:

• UPDATE books names the table.
• SET title = ?, author = ?, year = ? lists the columns to change.
• WHERE id = ? picks which row to change. Never leave out WHERE, or every row in the table is changed.
• The tuple (title, author, year, book_id) must be in the same order as the ? marks.
• db.commit() saves the change. Without it, the update is lost.

Use it inside a route:

@app.route("/edit/<int:book_id>", methods=["POST"])
def edit(book_id):
    changed = update_book(
        book_id,
        request.form["title"].strip(),
        request.form["author"].strip(),
        request.form.get("year") or None,
    )
    if changed == 0:
        return "Book not found", 404
    return redirect(url_for("index"))

How the Edit button flows through the app:

1. Clicking Edit opens /?edit=3.
2. index() runs SELECT * FROM books WHERE id = ? and sends that row to the page as book.
3. The form fills in with the row's current values and its action points to /edit/3.
4. Clicking Update sends a POST to /edit/3, which calls update_book.
5. The route redirects to /, and the list shows the new values.

To change only one column:

db.execute("UPDATE books SET year = ? WHERE id = ?", (1999, 3))
db.commit()

Example: INSERT, SELECT, DELETE as functions

def add_book(title, author, year):
    db = get_db()
    cur = db.execute("INSERT INTO books (title, author, year) VALUES (?, ?, ?)",
                     (title, author, year))
    db.commit()
    return cur.lastrowid          # id of the new row


def get_books():
    return get_db().execute("SELECT * FROM books ORDER BY id DESC").fetchall()


def get_book(book_id):
    return get_db().execute("SELECT * FROM books WHERE id = ?", (book_id,)).fetchone()


def delete_book(book_id):
    db = get_db()
    cur = db.execute("DELETE FROM books WHERE id = ?", (book_id,))
    db.commit()
    return cur.rowcount

A single value still needs a tuple, so write (book_id,) with the comma.

How the page is linked to the route

render_template("index.html", books=books, book=book) loads templates/index.html and passes the values after it into the HTML. Inside the HTML, {{ b.title }} prints a value and {% for b in books %} loops over a list.

The folder must be named exactly templates and sit next to app.py.

How to route a new function

A route is a Python function with @app.route(...) above it. Follow these steps every time.

1. Add the function in app.py

Put it above the if __name__ == "__main__": line.

@app.route("/about")
def about():
    return render_template("about.html")

2. Create the matching file in templates/

Create templates/about.html:

<h1>About</h1>
<a href="{{ url_for('index') }}">Back</a>

3. Link to it from any page

Use the function name inside url_for, not the URL:

<a href="{{ url_for('about') }}">About</a>

Open http://127.0.0.1:5000/about. With debug=True the server reloads when you save.

Route that reads one record

A URL part in <angle brackets> becomes a function argument. int: makes it a number.

@app.route("/book/<int:book_id>")
def book_detail(book_id):
    book = get_db().execute("SELECT * FROM books WHERE id = ?", (book_id,)).fetchone()
    if book is None:
        return "Book not found", 404
    return render_template("book.html", book=book)

<a href="{{ url_for('book_detail', book_id=b.id) }}">View</a>

Route that saves a form (POST)

Use methods=["POST"] for anything that changes data, then redirect so a refresh does not resubmit the form.

@app.route("/add", methods=["POST"])
def add():
    db = get_db()
    db.execute("INSERT INTO books (title, author) VALUES (?, ?)",
               (request.form["title"], request.form["author"]))
    db.commit()
    return redirect(url_for("index"))

<form method="post" action="{{ url_for('add') }}">
  <input name="title">
  <input name="author">
  <button>Save</button>
</form>

The name="..." of each input must match the key in request.form["..."].

Route with a search box (query string)

@app.route("/search")
def search():
    q = request.args.get("q", "")
    books = get_db().execute(
        "SELECT * FROM books WHERE title LIKE ?", (f"%{q}%",)
    ).fetchall()
    return render_template("index.html", books=books, book=None)

<form action="{{ url_for('search') }}">
  <input name="q" placeholder="Search title">
  <button>Search</button>
</form>

Using a different table

To CRUD something else (students, events, products):

1. Change the CREATE TABLE statement in init_db().
2. Change the column names in each INSERT, UPDATE, and SELECT.
3. Change the <input name="..."> fields and table columns in templates/index.html.

If you change the columns of a table that already exists, delete app.db so it is created again.

Common errors

Notes

• debug=True is for development only. Turn it off before deploying.
• Delete is POST-only so a link or crawler cannot delete records.