Flask - SQLite

Flask – Using SQLite Databases

SQLite is a lightweight, file-based relational database. Python has built-in support for SQLite through the sqlite3 module, making it easy to integrate with Flask applications, especially for smaller projects or prototypes.

Setting Up the Database

First, let’s create a simple script (init_db.py) to set up our SQLite database file (database.db) and create a table named students.

import sqlite3

# Connect to the database file (creates it if it doesn't exist)
conn = sqlite3.connect('database.db')
print("Opened database successfully")

# Create the students table
conn.execute('''
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    addr TEXT,
    city TEXT,
    pin TEXT
);
''')
print("Table created successfully or already exists")

# Close the connection
conn.close()

Run this script once from your terminal: python init_db.py

Flask Application with Database Interaction

Now, let’s build a Flask application (app.py) to interact with this database. We’ll create routes to:

Display a form to add new students.
Handle the form submission to add a student to the database.
List all students currently in the database.

Helper Function for Database Connection

It’s good practice to have a helper function to get a database connection. This ensures consistency.

import sqlite3

def get_db_connection():
    conn = sqlite3.connect('database.db')
    # Return rows as dictionary-like objects
    conn.row_factory = sqlite3.Row
    return conn

Setting conn.row_factory = sqlite3.Row allows you to access columns by name (like row['name']) instead of just by index (row[0]), which makes the code more readable.

Flask Application Code (`app.py`):

from flask import Flask, render_template, request, url_for, flash, redirect
import sqlite3
import sys # For error printing

app = Flask(__name__)
# Required for flashing messages
app.config['SECRET_KEY'] = 'your secret key here'

def get_db_connection():
    conn = sqlite3.connect('database.db')
    conn.row_factory = sqlite3.Row
    return conn

@app.route('/')
def index():
    conn = get_db_connection()
    students = conn.execute('SELECT * FROM students').fetchall()
    conn.close()
    return render_template('index.html', students=students)

@app.route('/new', methods=('GET', 'POST'))
def new_student():
    if request.method == 'POST':
        name = request.form['name']
        addr = request.form['addr']
        city = request.form['city']
        pin = request.form['pin']

        if not name:
            flash('Name is required!', 'error')
        else:
            conn = get_db_connection()
            try:
                conn.execute('INSERT INTO students (name, addr, city, pin) VALUES (?, ?, ?, ?)',
                             (name, addr, city, pin))
                conn.commit()
                flash('Student added successfully!', 'success')
            except sqlite3.Error as e:
                print(f"Database error: {e}", file=sys.stderr)
                flash(f'Error adding student: {e}', 'error')
                conn.rollback() # Rollback changes on error
            finally:
                conn.close()
            # Redirect to index page after successful/failed addition
            return redirect(url_for('index'))

    # If GET request, just show the form
    return render_template('new_student.html')

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

Templates

We need three HTML templates placed in a templates folder:

1. `index.html` (Displays the list and provides a link to add new students)

<!DOCTYPE html>
<html>
<head>
    <title>Student List</title>
    <style>
        .alert { padding: 15px; margin-bottom: 20px; border: 1px solid transparent; border-radius: 4px; }
        .alert-success { color: #155724; background-color: #d4edda; border-color: #c3e6cb; }
        .alert-error { color: #721c24; background-color: #f8d7da; border-color: #f5c6cb; }
        table, th, td { border: 1px solid black; border-collapse: collapse; padding: 5px; }
        th { background-color: #f2f2f2; }
    </style>
</head>
<body>
    <h1>Student Records</h1>

    <!-- Display flashed messages -->
    {% with messages = get_flashed_messages(with_categories=true) %}
      {% if messages %}
        {% for category, message in messages %}
          <div class="alert alert-{{ category }}">{{ message }}</div>
        {% endfor %}
      {% endif %}
    {% endwith %}

    <p><a href="{{ url_for('new_student') }}">Add New Student</a></p>

    <table>
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Address</th>
                <th>City</th>
                <th>PIN</th>
            </tr>
        </thead>
        <tbody>
            {% for student in students %}
            <tr>
                <td>{{ student['id'] }}</td>
                <td>{{ student['name'] }}</td>
                <td>{{ student['addr'] }}</td>
                <td>{{ student['city'] }}</td>
                <td>{{ student['pin'] }}</td>
            </tr>
            {% else %}
            <tr>
                <td colspan="5">No students found.</td>
            </tr>
            {% endfor %}
        </tbody>
    </table>
</body>
</html>

2. `new_student.html` (The form for adding a student)

<!DOCTYPE html>
<html>
<head>
    <title>Add New Student</title>
    <style>
        label { display: block; margin-bottom: 5px; }
        input[type=text], textarea { width: 300px; margin-bottom: 10px; }
        .alert-error { color: #721c24; background-color: #f8d7da; border-color: #f5c6cb; padding: 10px; margin-bottom: 10px; border-radius: 4px; }
    </style>
</head>
<body>
    <h1>Add Student Information</h1>

    <!-- Display flashed messages (e.g., validation errors) -->
    {% with messages = get_flashed_messages(with_categories=true) %}
      {% if messages %}
        {% for category, message in messages %}
          {% if category == 'error' %}
             <div class="alert alert-{{ category }}">{{ message }}</div>
          {% endif %}
        {% endfor %}
      {% endif %}
    {% endwith %}

    <form method="post">
        <label for="name">Name:</label>
        <input type="text" name="name" id="name" required><br>

        <label for="addr">Address:</label>
        <textarea name="addr" id="addr"></textarea><br>

        <label for="city">City:</label>
        <input type="text" name="city" id="city"><br>

        <label for="pin">PIN Code:</label>
        <input type="text" name="pin" id="pin"><br>

        <input type="submit" value="Add Student">
    </form>
    <p><a href="{{ url_for('index') }}">Back to List</a></p>
</body>
</html>

Key Improvements and Considerations:

Python 3: Code uses Python 3 syntax (e.g., print() function).
Context Management (with): While not used in this simple get_db_connection example, for more complex scenarios, using with sqlite3.connect(...) as conn: is recommended as it handles commits and rollbacks automatically in many cases. Here, we manage commit() and rollback() manually within a try...except...finally block for clarity in the insertion logic.
Error Handling: Includes basic try...except block for database errors and prints errors to standard error.
sqlite3.Row: Used for accessing query results by column name.
Flashed Messages: Uses Flask’s flash() function and get_flashed_messages() in the template to provide user feedback (requires setting app.config['SECRET_KEY']).
Redirection: Uses redirect(url_for('index')) after form submission for better flow (Post/Redirect/Get pattern).
Basic Validation: Includes a simple check if not name:.
Security: Note that this example doesn’t implement robust input validation or sanitization, which is crucial for real applications to prevent SQL injection and other vulnerabilities.
Scalability: Directly using sqlite3 is fine for simple cases. For larger or more complex applications, using an Object-Relational Mapper (ORM) like SQLAlchemy, often via the Flask-SQLAlchemy extension, is highly recommended. It provides a higher-level abstraction, simplifies database interactions, and helps manage database connections more effectively. This will be covered in a later section.

Run the application (python app.py) and navigate to http://localhost:5000/. You can view the list, click ‘Add New Student’, fill the form, submit it, and see the updated list.