App Skeleton — Login to Main Window

A copy-and-customise starter app for your NEA, built from just two Python files:

• forms.py — the Tkinter UI: every window and screen. You draw it in the Visual Tkinter Builder and paste the code in.
• database.py — every SQL statement, gathered into a single Database class.

…plus app.db, the SQLite file the Database class creates automatically the first time you run the program. You run forms.py; it imports database.py; that is the whole program.

What these two files give you

• All SQL in one Database class. forms.py never touches sqlite3 — that earns you “modular structure” and “well-structured data handling” marks.
• One db object created once and handed to every window, so every screen just calls self.db.check_login(...), self.db.get_all_appointments(...), and so on.
• A login window that checks the database and only opens the main window when the username and password match a stored user.
• A main window shown after login, with a log out that returns to the login window without restarting the whole app (withdraw / deiconify).
• Password checking kept inside database.py, so the UI never stores passwords or sees how they are hashed.
• One SQLite file created automatically, with referential integrity between tables once you add more.

File layout

my_app/
├── forms.py       # every window / screen — the Tkinter UI · RUN THIS FILE
├── database.py    # the Database class — every SQL statement
└── app.db         # the SQLite database (created automatically on first run)

forms.py is the entry point — the file you run to start the program. It imports database.py; database.py never runs on its own and never opens a window.

The golden rule: forms.py never touches sqlite3, and database.py never touches Tkinter. The UI calls Python methods; the database file turns those into SQL. Keep that line clean and the rest looks after itself.

database.py — one Database class owns all SQL

Everything to do with data lives in one class. It creates the tables when you first make a Database object, and it provides a method for every question the UI needs to ask — create_user, check_login, get_all_users, and (later) create_appointment, get_all_appointments, … Group the methods by table with a comment header so the class stays tidy as it grows. Because login is a question about the data (“does this username exist, and does the password match?”), the password check lives here too.

# database.py
import sqlite3
import hashlib

DB_FILE = "app.db"


def hash_password(password):
    """Turn a plaintext password into a hash. NEVER store the plaintext."""
    return hashlib.sha256(password.encode()).hexdigest()


class Database:
    """One object owns every SQL statement in the app."""

    def __init__(self, db_path):
        self.db_path = db_path
        self._create_tables()           # make sure the tables exist on first run

    # ------------------------------------------------------------------
    # Internal helpers
    # ------------------------------------------------------------------

    def _connect(self):
        connection = sqlite3.connect(self.db_path)
        connection.row_factory = sqlite3.Row             # rows accessible by column name
        connection.execute("PRAGMA foreign_keys = ON")   # enforce FOREIGN KEYs
        return connection

    def _create_tables(self):
        connection = self._connect()
        cursor = connection.cursor()
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id            INTEGER PRIMARY KEY AUTOINCREMENT,
                username      TEXT NOT NULL UNIQUE,
                email         TEXT NOT NULL,
                password_hash TEXT NOT NULL,
                created_at    TEXT DEFAULT CURRENT_TIMESTAMP
            )
        """)
        # As the project grows, add more CREATE TABLE statements here.
        connection.commit()
        connection.close()

    # ------------------------------------------------------------------
    # Users
    # ------------------------------------------------------------------

    def create_user(self, username, email, password):
        connection = self._connect()
        cursor = connection.cursor()
        try:
            cursor.execute(
                "INSERT INTO users (username, email, password_hash) "
                "VALUES (:username, :email, :password_hash)",
                {"username": username,
                 "email": email,
                 "password_hash": hash_password(password)},
            )
            connection.commit()
            return cursor.lastrowid
        except sqlite3.IntegrityError:
            return None              # username already exists
        finally:
            connection.close()

    def get_user_by_username(self, username):
        connection = self._connect()
        cursor = connection.cursor()
        cursor.execute(
            "SELECT id, username, email, created_at FROM users "
            "WHERE username = :username",
            {"username": username},
        )
        user = cursor.fetchone()
        connection.close()
        return user

    def check_login(self, username, password):
        """Return the user row if the password matches, otherwise None."""
        connection = self._connect()
        cursor = connection.cursor()
        cursor.execute(
            "SELECT id, username, email, password_hash FROM users "
            "WHERE username = :username",
            {"username": username},
        )
        user = cursor.fetchone()
        connection.close()

        if user is None:
            return None                                  # no such username
        if hash_password(password) == user["password_hash"]:
            return user                                  # password matches → success
        return None                                      # wrong password

    def get_all_users(self):
        connection = self._connect()
        cursor = connection.cursor()
        cursor.execute("SELECT id, username, email, created_at FROM users")
        users = cursor.fetchall()
        connection.close()
        return users

    def update_user_email(self, user_id, new_email):
        connection = self._connect()
        cursor = connection.cursor()
        cursor.execute(
            "UPDATE users SET email = :new_email WHERE id = :user_id",
            {"new_email": new_email, "user_id": user_id},
        )
        connection.commit()
        success = cursor.rowcount > 0
        connection.close()
        return success

    def delete_user(self, user_id):
        connection = self._connect()
        cursor = connection.cursor()
        cursor.execute(
            "DELETE FROM users WHERE id = :user_id",
            {"user_id": user_id},
        )
        connection.commit()
        success = cursor.rowcount > 0
        connection.close()
        return success


# Notice there is NO `if __name__ == "__main__":` launcher down here.
# database.py is a module: forms.py imports it. The file you actually
# run is forms.py — that is the program's single entry point.

Why this pattern earns marks

Mark-scheme phrase	How the pattern delivers it
“well-structured and modular”	One Database class is the single home for all data access; forms.py has zero SQL.
“fully exploited the programming facilities of the language”	Classes, parameterised queries, hashlib, exception handling on inserts.
“efficient exception handling”	create_user catches IntegrityError for a duplicate username and returns None instead of crashing.
“validation … referential integrity”	NOT NULL, UNIQUE, :name placeholders, and FOREIGN KEYs once you add related tables.
“minimises the use of global variables”	The one db object is created once and handed to each window; nothing reaches for a global.

forms.py — the windows, drawn for you

Building a Tkinter layout by hand is slow and easy to get wrong. Use the Visual Tkinter Builder instead:

You create the Database object once at the bottom of the file and hand it to the first window; each window passes it on to the next. The builder gives you the widgets; you fill in the button handlers (the bodies of open_main and logout) and add the import line:

# forms.py — RUN THIS FILE to start the program
import tkinter as tk
from tkinter import ttk, messagebox
from database import DB_FILE, Database


THEME_BG = "#e8f1f5"
THEME_FG = "#0b3954"
THEME_ACCENT = "#087e8b"
THEME_ACCENT_FG = "#ffffff"
THEME_FIELD_BG = "#ffffff"
THEME_FONT = ("Segoe UI", 10)


def apply_theme(root):
    """Apply the project theme as defaults; per-widget options override these."""
    root.configure(bg=THEME_BG)
    root.option_add("*background", THEME_BG)
    root.option_add("*foreground", THEME_FG)
    root.option_add("*font", THEME_FONT)
    root.option_add("*Entry.background", THEME_FIELD_BG)
    root.option_add("*Button.background", THEME_ACCENT)
    root.option_add("*Button.foreground", THEME_ACCENT_FG)
    style = ttk.Style(root)
    try:
        style.theme_use("clam")
    except tk.TclError:
        pass
    style.configure(".", background=THEME_BG, foreground=THEME_FG,
                    fieldbackground=THEME_FIELD_BG, font=THEME_FONT)


class Login:
    """The login window. It IS the root window of the app."""

    def __init__(self, root, db):
        self.root = root
        self.db = db                                # the one shared Database object
        root.title("Login")
        root.geometry("360x240")
        apply_theme(root)

        self.label1 = tk.Label(root, text="Username")
        self.label1.place(x=40, y=40, width=80, height=26)
        self.entry1 = tk.Entry(root)
        self.entry1.place(x=140, y=38, width=170, height=28)

        self.label2 = tk.Label(root, text="Password")
        self.label2.place(x=40, y=90, width=80, height=26)
        self.entry2 = tk.Entry(root, show="*")
        self.entry2.place(x=140, y=88, width=170, height=28)

        self.button1 = tk.Button(root, text="Log in", command=self.open_main)
        self.button1.place(x=140, y=140, width=100, height=34)

    def open_main(self):
        username = self.entry1.get().strip()
        password = self.entry2.get()

        # 1. basic validation — don't even query on empty boxes
        if not username or not password:
            messagebox.showwarning("Missing details",
                                   "Please enter a username and a password.")
            return

        # 2. ask the Database object to check the credentials
        user = self.db.check_login(username, password)
        if user is None:
            messagebox.showerror("Login failed",
                                 "Wrong username or password.")
            self.entry2.delete(0, tk.END)       # clear the password, keep the username
            return

        # 3. success → hide the login window and open the main window
        self.root.withdraw()
        Main(self.root, self.db)


class Main:
    """The main window, shown after a successful login."""

    def __init__(self, master, db):
        self.master = master                     # the (now hidden) login window
        self.db = db                             # the same Database object, passed on
        self.win = tk.Toplevel(master)
        self.win.title("Main")
        self.win.geometry("480x320")
        apply_theme(self.win)
        # Clicking the X on the main window quits the whole app cleanly
        self.win.protocol("WM_DELETE_WINDOW", master.destroy)

        self.label1 = tk.Label(self.win, text="Welcome!",
                               font=("Helvetica", 14, "bold"))
        self.label1.place(x=30, y=28, width=220, height=28)

        self.button1 = tk.Button(self.win, text="Log out", command=self.logout)
        self.button1.place(x=30, y=80, width=100, height=34)

    def logout(self):
        self.win.destroy()                       # close the main window
        self.master.deiconify()                  # show the login window again


if __name__ == "__main__":
    db = Database(DB_FILE)              # create the data layer ONCE (also creates tables)
    root = tk.Tk()
    app = Login(root, db)              # hand the same db to the first window
    root.mainloop()

How forms.py reaches the database

When the Log in button runs self.db.check_login(...), where does the database come from? It was created once, at the bottom of the file, and handed down. Four lines connect the two files:

from database import DB_FILE, Database     # 1. import the class from database.py

db = Database(DB_FILE)                      # 2. create ONE db object (also makes the
                                            #    tables; it stores the path internally)

app = Login(root, db)                       # 3. hand the SAME db to the first window,
                                            #    which passes it on to Main, and so on

user = self.db.check_login(username, password)   # 4. call a method on that object;
                                                 #    it opens its own connection inside

self.db is the one Database object, shared by every window. You don’t pass the database into check_login (or any other method) because the object already carries self.db_path and opens its own connection inside _connect(). So “ask the Database object to check the credentials” really means “call a method on db”.

From login to main window, and back again

There is exactly one Tk() for the whole program — it is the login window. The main window is a Toplevel on top of it. That single-root design (rather than creating a second Tk()) is what keeps messagebox, focus and logout behaving:

1. forms.py runs → Database(DB_FILE) creates the data layer → the login window appears.
2. The user types credentials and clicks Log in → open_main() calls self.db.check_login(...).
3. On success the login window is hidden (withdraw()) and the Main window opens, handed the same db.
4. Log out destroys the main window and shows the login again (deiconify()) — no restart, no second Tk().
5. Closing the main window’s X calls master.destroy(), which ends mainloop() and quits cleanly — so a hidden login root is never left running.

user is the row check_login() returned — a sqlite3.Row with id, username, email and password_hash — so if you later want to greet the user by name, pass user into Main and read user["username"].

First, add a user from Python’s interactive mode

A brand-new app.db has no users, so every login would fail. Before the first login, create one by hand in the terminal — this is also a great way to prove your database.py works on its own, with no GUI in the way.

Open a terminal in the same folder as database.py and start Python:

$ python
>>> from database import DB_FILE, Database
>>> db = Database(DB_FILE)                      # creates app.db and the tables
>>> db.create_user("jstephenson", "jo@example.com", "secret123")
1                                              # ← the new user's id
>>> user = db.check_login("jstephenson", "secret123")
>>> user["username"]                           # ← read columns by name
'jstephenson'
>>> db.check_login("jstephenson", "wrongpw") is None
True                                           # ← wrong password → None
>>> exit()

Now run the program (python forms.py), type jstephenson / secret123, and click Log in — the Main window opens. Any other username or a wrong password gives the “Wrong username or password” error. The same Database methods you just called by hand are exactly the ones the form calls when a button is pressed.

Adding more screens and tables

Everything above is two files. Growing the app never changes that — you add more window classes to forms.py and more methods (and CREATE TABLE statements) to the one Database class. Say the project now needs appointments, each one belonging to a user.

1. Add the table inside _create_tables()

Add a second CREATE TABLE next to the first. A FOREIGN KEY ties the new table back to users so an appointment can’t point at a user who doesn’t exist — that’s the referential integrity the mark scheme rewards. (Foreign keys are already enforced, because _connect() runs PRAGMA foreign_keys = ON every time.)

    def _create_tables(self):
        connection = self._connect()
        cursor = connection.cursor()
        cursor.execute("""CREATE TABLE IF NOT EXISTS users ( ... )""")   # as above

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS appointments (
                id        INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id   INTEGER NOT NULL,
                title     TEXT NOT NULL,
                appt_date TEXT NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        """)

        connection.commit()
        connection.close()

2. Add its methods to the same Database class

Drop the new methods in alongside the user ones, under a comment header. Because the tables are related, a method can join them — letting a screen show the username instead of a meaningless user_id. With one class this is just another method; there is no “which class does this go in?” to worry about:

    # ------------------------------------------------------------------
    # Appointments
    # ------------------------------------------------------------------

    def create_appointment(self, user_id, title, appt_date):
        connection = self._connect()
        cursor = connection.cursor()
        cursor.execute(
            "INSERT INTO appointments (user_id, title, appt_date) "
            "VALUES (:user_id, :title, :appt_date)",
            {"user_id": user_id, "title": title, "appt_date": appt_date},
        )
        connection.commit()
        new_id = cursor.lastrowid
        connection.close()
        return new_id

    def get_appointments_for_user(self, user_id):
        connection = self._connect()
        cursor = connection.cursor()
        cursor.execute(
            "SELECT id, title, appt_date FROM appointments "
            "WHERE user_id = :user_id ORDER BY appt_date",
            {"user_id": user_id},
        )
        rows = cursor.fetchall()
        connection.close()
        return rows

    def get_all_appointments(self):
        """Every appointment, with the owner's username via a JOIN."""
        connection = self._connect()
        cursor = connection.cursor()
        cursor.execute(
            "SELECT appointments.id, users.username, "
            "       appointments.title, appointments.appt_date "
            "FROM appointments "
            "JOIN users ON users.id = appointments.user_id "
            "ORDER BY appointments.appt_date"
        )
        rows = cursor.fetchall()
        connection.close()
        return rows

    # UPDATE and DELETE follow exactly the same shape as the user methods.

3. Give the new window the db it needs

Draw the new window in the Visual Tkinter Builder, paste its class into forms.py, and pass it the same db every other window already has. For example, from a button on the Main window:

# in forms.py — a "View appointments" button handler on Main
def open_appointments(self):
    Appointments(self.win, self.db)        # hand over the same Database object


class Appointments:
    def __init__(self, master, db):
        self.db = db                       # same object → same app.db, all tables
        # ... widgets exported from the builder ...

    def refresh_list(self):
        for row in self.db.get_all_appointments():   # call the new method
            ...                                       # put each row in a Treeview

The recipe never changes: new table → new CREATE TABLE → new methods on Database → the window already has self.db, so it can call them. Nothing reaches for a global — which is exactly what “minimises the use of global variables” in the mark scheme is asking for.

Where to put validation

In the two-file design, validation lives in two layers, and good NEAs use both.

1. In forms.py — usability checks, before the database call. The button handler checks what the user typed and shows a friendly messagebox if something is wrong, before any SQL runs. This catches the everyday mistakes early — empty boxes, text where a number or date is expected, values out of range. The login handler already does it:

if not username or not password:
    messagebox.showwarning("Missing details", "Please enter a username and a password.")
    return

Keep these about the input: presence, type, format, length.

2. In database.py — integrity checks that protect the data whatever screen calls. The schema’s constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY) are the last line of defence — even if a UI check is missed, the database refuses bad data — and the Database method catches the resulting error and turns it into a clean result for the UI:

try:
    cursor.execute("INSERT INTO users ...", {...})
except sqlite3.IntegrityError:
    return None        # e.g. duplicate username → the UI shows "that name is taken"

For a business rule that needs to look at the data — “an appointment can’t be in the past”, “no double-booking” — write it as a method on Database (it’s a question about the data) and have the handler react to what it returns.

Kind of check	Where it goes
Empty / required field, type, format, length, range	forms.py button handler, before the call
Required / unique / referential (NOT NULL, UNIQUE, FOREIGN KEY)	schema in _create_tables(), with the method catching IntegrityError
Business rules needing a lookup (free slot? in date order?)	a method on Database; the handler shows the result

Rule of thumb: validate in the UI for friendly, early feedback; enforce in the database for integrity. Don’t rely on only one — the UI keeps users happy, the database keeps the data correct. Doing both is exactly what “effective validation for all key components” and “efficient exception handling” reward.

Common pitfalls

• messagebox.showerror(...) without parent= — the popup may appear behind your window or steal focus. For dialogs raised from a Toplevel, pass parent= (e.g. parent=self.win).
• Creating two Tk() instances in the same process. Always one root (the login window), with Toplevel, withdraw and deiconify to switch windows.
• Creating a new Database in every window. Make it once in if __name__ == "__main__": and pass self.db down — one shared object, not a new one per screen.
• Letting the main window’s X-button leave a hidden login root alive. Set protocol("WM_DELETE_WINDOW", master.destroy) on the main Toplevel so closing it quits the whole app.
• Forgetting PRAGMA foreign_keys = ON. SQLite ships with foreign-key enforcement off by default. _connect() turns it on every time — without it, referential integrity is silently optional.
• Storing plaintext passwords. The column is password_hash for a reason — create_user hashes before inserting and check_login compares hashes, so the real password never reaches app.db.
• Putting SQL in forms.py or Tkinter in database.py. The moment a window builds a query string, or the data file imports tkinter, you’ve lost the structure marks. Keep each file to its one job.

Bookmark this page when you start the project — it’s the two-file structure to copy first, then grow into the rest of your NEA.