Connecting Flask with a Simple Database (SQLite)

1) What You'll Learn

What a database is and why we use one
What SQLite is and why it's great for small projects
How to connect a Flask web app to an SQLite database
How to save, list, and delete data (notes) from a real database
How to keep your Python code clean and safe with parameterized queries

2) What You Need

A computer with Python 3.9+ installed
Basic Python knowledge (variables, functions)
A web browser (Chrome, Firefox, Edge, etc.)
Internet to install Flask

3) Quick Vocabulary

Database: A place to store information so it doesn't disappear when your app stops.
Table: Like a spreadsheet inside the database (rows and columns).
Row: One record in the table (like one note).
Column: A type of data in the table (like content or date).
Primary Key: A special number that uniquely identifies each row (like an ID).
SQLite: A tiny, file-based database that comes with Python. Great for beginners.
SQL: A language to talk to databases (create tables, insert rows, delete rows).

4) The App You'll Build

We'll build "My Notes," a tiny web app where you can:

Add a note
See all notes
Delete a note

It uses:

Flask for the web app
SQLite for the database
HTML templates for the pages

5) Project Setup

A) Make a project folder (name it notes_app)

B) Create a virtual environment

Windows (PowerShell):
python -m venv .venv .venv\Scripts\Activate
macOS/Linux (Terminal):
python3 -m venv .venv source .venv/bin/activate

C) Install Flask

pip install flask

Note:

SQLite is already included with Python via the sqlite3 module. No extra install needed.

6) Project Structure

Create this structure:

notes_app/
  app.py
  templates/
    base.html
    index.html

7) Code: app.py

Copy this into notes_app/app.py

from flask import Flask, render_template, request, redirect, url_for
import sqlite3

app = Flask(__name__)
DB_NAME = "notes.db"

def get_connection():
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row  # lets us access columns by name
    return conn

def create_table():
    # Create the table if it doesn't exist
    with get_connection() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS notes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                content TEXT NOT NULL CHECK(length(content) <= 200),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        conn.commit()

@app.route("/", methods=["GET", "POST"])
def index():
    create_table()  # make sure table exists

    if request.method == "POST":
        # Handle the form submission to add a note
        content = request.form.get("content", "").trim() if hasattr(str, "trim") else request.form.get("content", "").strip()
        if content:
            with get_connection() as conn:
                # Use parameterized query to avoid SQL injection
                conn.execute("INSERT INTO notes (content) VALUES (?)", (content,))
                conn.commit()
        return redirect(url_for("index"))

    # If GET request, show the notes
    with get_connection() as conn:
        notes = conn.execute(
            "SELECT id, content, created_at FROM notes ORDER BY id DESC"
        ).fetchall()
    return render_template("index.html", notes=notes)

@app.route("/delete/<int:note_id>", methods=["POST"])
def delete(note_id):
    with get_connection() as conn:
        conn.execute("DELETE FROM notes WHERE id = ?", (note_id,))
        conn.commit()
    return redirect(url_for("index"))

if __name__ == "__main__":
    create_table()
    app.run(debug=True)

Note:

If your Python doesn't support str.trim(), no worries; the code falls back to .strip(). You can also just use .strip().

8) Code: templates/base.html

Copy this into notes_app/templates/base.html

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>My Notes</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <style>
    body { font-family: system-ui, Arial, sans-serif; max-width: 800px; margin: 2rem auto; padding: 0 1rem; }
    header { display: flex; justify-content: space-between; align-items: center; }
    form.add { display: flex; gap: 0.5rem; margin: 1rem 0; }
    input[type=text] { flex: 1; padding: 0.6rem; }
    button { padding: 0.6rem 0.9rem; cursor: pointer; }
    ul { list-style: none; padding: 0; }
    li { background: #f6f8fa; border: 1px solid #e5e7eb; padding: 0.7rem; margin-bottom: 0.5rem; display: flex; justify-content: space-between; align-items: center; }
    small { color: #6b7280; }
  </style>
</head>
<body>
  <header>
    <h1>My Notes</h1>
  </header>

  {% block content %}{% endblock %}

  <footer>
    <p><small>Built with Flask + SQLite</small></p>
  </footer>
</body>
</html>

9) Code: templates/index.html

Copy this into notes_app/templates/index.html

{% extends "base.html" %}
{% block content %}
<form class="add" action="{{ url_for('index') }}" method="post">
  <input type="text" name="content" placeholder="Type a note (max 200 chars)" maxlength="200" required>
  <button type="submit">Add</button>
</form>

{% if notes %}
<ul>
  {% for n in notes %}
  <li>
    <div>
      <div>{{ n['content'] }}</div>
      <small>#{{ n['id'] }} • {{ n['created_at'] }}</small>
    </div>
    <form action="{{ url_for('delete', note_id=n['id']) }}" method="post" onsubmit="return confirm('Delete this note?');">
      <button type="submit">Delete</button>
    </form>
  </li>
  {% endfor %}
</ul>
{% else %}
<p>No notes yet. Add your first note above!</p>
{% endif %}
{% endblock %}

10) Run Your App

In the terminal, make sure your virtual environment is active.
Run:
python app.py
Open your browser and go to:
http://127.0.0.1:5000
Try:
- Add a note
- See it appear in the list
- Delete a note

11) How the Code Works (Simple Explanations)

Connecting to SQLite:
- get_connection() opens the database file notes.db and lets us read rows by column names.
Creating the table:
- create_table() makes a table named notes if it doesn't already exist.
- Columns:
  - id: automatically increases for each new note
  - content: the note text (limited to 200 characters)
  - created_at: time the note was created (set automatically)
Adding a note:
- In the index() route, if the method is POST, we read content from the form and insert it into the notes table using a "?" placeholder. This keeps it safe.
Listing notes:
- On GET, we select all notes and pass them to index.html to display.
Deleting a note:
- The /delete/<int:note_id> route runs a DELETE query for that specific id. Only accessible by POST to avoid accidental deletes.
Templates:
- base.html is a shared layout (header, footer, styles).
- index.html fills in the content with the form and the list of notes.

12) Safety and Good Habits

Always use parameterized queries (the ? in SQL) to avoid SQL injection.
Keep database actions inside with get_connection() as conn blocks to ensure they close properly.
Use limit checks (we used max 200 characters) to avoid overly large inputs.
For learning only: This is a simple app. Real apps need user accounts, better validation, and more security.

13) Troubleshooting

ModuleNotFoundError: No module named 'flask'
- You forgot to install Flask or your virtual environment isn't active.
- Run: pip install flask and ensure the venv is activated.
TemplateNotFound: index.html
- Make sure your templates folder is named templates and is in the same folder as app.py.
The page doesn't update after adding a note
- Check the terminal for errors.
- Make sure the form method="post" and the route allows methods=["GET", "POST"].
- The code redirects back to index after adding; this prevents double submissions.
I want to reset everything
- Stop the app, delete notes.db, then run the app again. A fresh table will be created.
Port already in use
- Another program is using port 5000. Try: app.run(debug=True, port=5001)

14) Challenge Ideas (Try One!)

Edit notes: Add an Edit button and a route to update a note's content.
Search notes: Add a small search bar to filter notes that contain a word.
Categories or tags: Add another column to group notes (like "school", "home").
Character counter: Show how many characters are left while typing a note.

15) Wrap-Up

You just built a real Flask app that saves information in a real database. You learned how to:

Connect to SQLite
Create a table
Insert, list, and delete data
Use HTML templates with Flask

Great job leveling up your web development skills!

Connecting Flask with a Simple Database (SQLite)

1) What You'll Learn

What a database is and why we use one
What SQLite is and why it's great for small projects
How to connect a Flask web app to an SQLite database
How to save, list, and delete data (notes) from a real database
How to keep your Python code clean and safe with parameterized queries

2) What You Need

A computer with Python 3.9+ installed
Basic Python knowledge (variables, functions)
A web browser (Chrome, Firefox, Edge, etc.)
Internet to install Flask

3) Quick Vocabulary

Database: A place to store information so it doesn't disappear when your app stops.
Table: Like a spreadsheet inside the database (rows and columns).
Row: One record in the table (like one note).
Column: A type of data in the table (like content or date).
Primary Key: A special number that uniquely identifies each row (like an ID).
SQLite: A tiny, file-based database that comes with Python. Great for beginners.
SQL: A language to talk to databases (create tables, insert rows, delete rows).

4) The App You'll Build

We'll build "My Notes," a tiny web app where you can:

Add a note
See all notes
Delete a note

It uses:

Flask for the web app
SQLite for the database
HTML templates for the pages

5) Project Setup

A) Make a project folder (name it notes_app)

B) Create a virtual environment

Windows (PowerShell):
python -m venv .venv .venv\Scripts\Activate
macOS/Linux (Terminal):
python3 -m venv .venv source .venv/bin/activate

C) Install Flask

pip install flask

Note:

SQLite is already included with Python via the sqlite3 module. No extra install needed.

6) Project Structure

Create this structure:

notes_app/
  app.py
  templates/
    base.html
    index.html

7) Code: app.py

Copy this into notes_app/app.py

from flask import Flask, render_template, request, redirect, url_for
import sqlite3

app = Flask(__name__)
DB_NAME = "notes.db"

def get_connection():
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row  # lets us access columns by name
    return conn

def create_table():
    # Create the table if it doesn't exist
    with get_connection() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS notes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                content TEXT NOT NULL CHECK(length(content) <= 200),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        conn.commit()

@app.route("/", methods=["GET", "POST"])
def index():
    create_table()  # make sure table exists

    if request.method == "POST":
        # Handle the form submission to add a note
        content = request.form.get("content", "").trim() if hasattr(str, "trim") else request.form.get("content", "").strip()
        if content:
            with get_connection() as conn:
                # Use parameterized query to avoid SQL injection
                conn.execute("INSERT INTO notes (content) VALUES (?)", (content,))
                conn.commit()
        return redirect(url_for("index"))

    # If GET request, show the notes
    with get_connection() as conn:
        notes = conn.execute(
            "SELECT id, content, created_at FROM notes ORDER BY id DESC"
        ).fetchall()
    return render_template("index.html", notes=notes)

@app.route("/delete/<int:note_id>", methods=["POST"])
def delete(note_id):
    with get_connection() as conn:
        conn.execute("DELETE FROM notes WHERE id = ?", (note_id,))
        conn.commit()
    return redirect(url_for("index"))

if __name__ == "__main__":
    create_table()
    app.run(debug=True)

Note:

If your Python doesn't support str.trim(), no worries; the code falls back to .strip(). You can also just use .strip().

8) Code: templates/base.html

Copy this into notes_app/templates/base.html

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>My Notes</title>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <style>
    body { font-family: system-ui, Arial, sans-serif; max-width: 800px; margin: 2rem auto; padding: 0 1rem; }
    header { display: flex; justify-content: space-between; align-items: center; }
    form.add { display: flex; gap: 0.5rem; margin: 1rem 0; }
    input[type=text] { flex: 1; padding: 0.6rem; }
    button { padding: 0.6rem 0.9rem; cursor: pointer; }
    ul { list-style: none; padding: 0; }
    li { background: #f6f8fa; border: 1px solid #e5e7eb; padding: 0.7rem; margin-bottom: 0.5rem; display: flex; justify-content: space-between; align-items: center; }
    small { color: #6b7280; }
  </style>
</head>
<body>
  <header>
    <h1>My Notes</h1>
  </header>

  {% block content %}{% endblock %}

  <footer>
    <p><small>Built with Flask + SQLite</small></p>
  </footer>
</body>
</html>

9) Code: templates/index.html

Copy this into notes_app/templates/index.html

{% extends "base.html" %}
{% block content %}
<form class="add" action="{{ url_for('index') }}" method="post">
  <input type="text" name="content" placeholder="Type a note (max 200 chars)" maxlength="200" required>
  <button type="submit">Add</button>
</form>

{% if notes %}
<ul>
  {% for n in notes %}
  <li>
    <div>
      <div>{{ n['content'] }}</div>
      <small>#{{ n['id'] }} • {{ n['created_at'] }}</small>
    </div>
    <form action="{{ url_for('delete', note_id=n['id']) }}" method="post" onsubmit="return confirm('Delete this note?');">
      <button type="submit">Delete</button>
    </form>
  </li>
  {% endfor %}
</ul>
{% else %}
<p>No notes yet. Add your first note above!</p>
{% endif %}
{% endblock %}

10) Run Your App

In the terminal, make sure your virtual environment is active.
Run:
python app.py
Open your browser and go to:
http://127.0.0.1:5000
Try:
- Add a note
- See it appear in the list
- Delete a note

11) How the Code Works (Simple Explanations)

Connecting to SQLite:
- get_connection() opens the database file notes.db and lets us read rows by column names.
Creating the table:
- create_table() makes a table named notes if it doesn't already exist.
- Columns:
  - id: automatically increases for each new note
  - content: the note text (limited to 200 characters)
  - created_at: time the note was created (set automatically)
Adding a note:
- In the index() route, if the method is POST, we read content from the form and insert it into the notes table using a "?" placeholder. This keeps it safe.
Listing notes:
- On GET, we select all notes and pass them to index.html to display.
Deleting a note:
- The /delete/<int:note_id> route runs a DELETE query for that specific id. Only accessible by POST to avoid accidental deletes.
Templates:
- base.html is a shared layout (header, footer, styles).
- index.html fills in the content with the form and the list of notes.

12) Safety and Good Habits

Always use parameterized queries (the ? in SQL) to avoid SQL injection.
Keep database actions inside with get_connection() as conn blocks to ensure they close properly.
Use limit checks (we used max 200 characters) to avoid overly large inputs.
For learning only: This is a simple app. Real apps need user accounts, better validation, and more security.

13) Troubleshooting

ModuleNotFoundError: No module named 'flask'
- You forgot to install Flask or your virtual environment isn't active.
- Run: pip install flask and ensure the venv is activated.
TemplateNotFound: index.html
- Make sure your templates folder is named templates and is in the same folder as app.py.
The page doesn't update after adding a note
- Check the terminal for errors.
- Make sure the form method="post" and the route allows methods=["GET", "POST"].
- The code redirects back to index after adding; this prevents double submissions.
I want to reset everything
- Stop the app, delete notes.db, then run the app again. A fresh table will be created.
Port already in use
- Another program is using port 5000. Try: app.run(debug=True, port=5001)

14) Challenge Ideas (Try One!)

Edit notes: Add an Edit button and a route to update a note's content.
Search notes: Add a small search bar to filter notes that contain a word.
Categories or tags: Add another column to group notes (like "school", "home").
Character counter: Show how many characters are left while typing a note.

15) Wrap-Up

You just built a real Flask app that saves information in a real database. You learned how to:

Connect to SQLite
Create a table
Insert, list, and delete data
Use HTML templates with Flask

Great job leveling up your web development skills!

web-dev Topics

web-dev Tutorial

Connecting Flask with a Simple Database (SQLite)

1) What You'll Learn

2) What You Need

3) Quick Vocabulary

4) The App You'll Build

5) Project Setup

A) Make a project folder (name it notes_app)

B) Create a virtual environment

C) Install Flask

Note:

6) Project Structure

7) Code: app.py

Note:

8) Code: templates/base.html

9) Code: templates/index.html

10) Run Your App

11) How the Code Works (Simple Explanations)

12) Safety and Good Habits

13) Troubleshooting

14) Challenge Ideas (Try One!)

15) Wrap-Up

web-dev Topics

web-dev Tutorial

Connecting Flask with a Simple Database (SQLite)

1) What You'll Learn

2) What You Need

3) Quick Vocabulary

4) The App You'll Build

5) Project Setup

A) Make a project folder (name it notes_app)

B) Create a virtual environment

C) Install Flask

Note:

6) Project Structure

7) Code: app.py

Note:

8) Code: templates/base.html

9) Code: templates/index.html

10) Run Your App

11) How the Code Works (Simple Explanations)

12) Safety and Good Habits

13) Troubleshooting

14) Challenge Ideas (Try One!)

15) Wrap-Up