Python Computer Science Databases

Beginner

80 mins

Teacher/Student led

What you need:
Chromebook/Laptop/PC

Retrieving and Displaying Data

In this lesson, you'll learn to query and display data in your Flask app using your SQLite database. You'll create a hobbies table, extend the users table, install a database viewer extension, and build a search feature to find users.

Learning Goals Learning Outcomes Teacher Notes

Warning: Your screen size is quite small for viewing this. Rotate your device into landscape mode if you can.

1 Introduction
2 Install SQLite Extension
3 Add Columns to Users Table
4 Create Hobbies Table
5 Add Form and Route for Hobbies
6 View Hobbies List
7 Associate Hobby with User
8 SQL JOINs
9 Display Email and Hobbies with the Users
10 What is Querying Data?
11 Create Search Page
12 Challenge
13 Wrap Up

1 - Introduction

In this lesson, you'll build on the SQLite database you created in the previous lesson by learning how to query and display data in your Flask app.

You'll add a new 'hobbies' table, extend the 'users' table with additional columns including a hobby_id for linking records, install a VS Code extension to explore your database visually, and create a search page to find users based on name, age, or hobby.

By the end, you'll understand how to fetch and present database data in a web app. Here's what you'll do:

Install the SQLite extension.
Modify the users table and create a hobbies table.
Add forms and routes for hobbies.
Implement a search feature.

Continue working in your 'FlaskSQLiteIntro' project folder from the previous lesson.

2 - Install SQLite Extension

To easily view and explore your SQLite database, install the 'SQLite' extension by alexcvzz in VS Code.

Open VS Code in your 'FlaskSQLiteIntro' folder.
Go to the Extensions view.
Search for 'SQLite' by alexcvzz.
Click Install.

Once installed, you can open your 'users.db' file:

Right-click 'users.db' in the Explorer, select 'Open Database', then in the SQLITE EXPLORER panel (it may appear on the left or you can open it via View > Explorer), expand the database to view tables. This tool helps you see your data without writing code.

Test it by opening 'users.db' and viewing the 'users' table. You should see any data you added previously.

3 - Add Columns to Users Table

We'll extend the 'users' table by adding columns: 'email' (text) and 'hobby_id' (integer) to link to a hobby.

Create a new file 'update_users_table.py' and add this code:

import sqlite3

conn = sqlite3.connect('users.db')
c = conn.cursor()

# Add email column (allow NULL for existing rows)
c.execute('''ALTER TABLE users ADD COLUMN email TEXT''')

# Add hobby_id column (allow NULL for existing rows)
c.execute('''ALTER TABLE users ADD COLUMN hobby_id INTEGER''')

conn.commit()
conn.close()
print('Users table updated successfully!')

This uses ALTER TABLE to add columns. 'hobby_id' will reference a hobby later, demonstrating foreign keys basics (though not enforced in this simple setup).

Run python update_users_table.py in the terminal. Use the SQLite extension to verify the new columns in the 'users' table.

4 - Create Hobbies Table

Now create a 'hobbies' table with columns: id (primary key), name (text).

Create a file 'create_hobbies.py' and add this code:

import sqlite3

conn = sqlite3.connect('users.db')
c = conn.cursor()

c.execute('''CREATE TABLE IF NOT EXISTS hobbies
             (id INTEGER PRIMARY KEY,
              name TEXT NOT NULL)''')

conn.commit()
conn.close()
print('Hobbies table created successfully!')

This is similar to creating the users table. The id is auto-incrementing.

Run python create_hobbies.py. Check the new table using the SQLite extension.

5 - Add Form and Route for Hobbies

Create a form to add hobbies and a route to insert them into the database.

Update 'app.py' to this full version (replacing existing content):

from flask import Flask, render_template, request
import sqlite3

app = Flask(__name__)

@app.route('/')
def home():
    return render_template('form.html')

@app.route('/add_user', methods=['POST'])
def add_user():
    name = request.form['name']
    age = int(request.form['age'])
    conn = sqlite3.connect('users.db')
    c = conn.cursor()
    c.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, age))
    conn.commit()
    conn.close()
    return 'User added successfully!'

@app.route('/users')
def view_users():
    conn = sqlite3.connect('users.db')
    c = conn.cursor()
    c.execute("SELECT * FROM users")
    users = c.fetchall()
    conn.close()
    return render_template('users.html', users=users)

@app.route('/add_hobby', methods=['GET', 'POST'])
def add_hobby():
    if request.method == 'POST':
        name = request.form['name']
        conn = sqlite3.connect('users.db')
        c = conn.cursor()
        c.execute("INSERT INTO hobbies (name) VALUES (?)", (name,))
        conn.commit()
        conn.close()
        return 'Hobby added successfully!'
    return render_template('hobby_form.html')

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

In 'templates', create 'hobby_form.html':

<!DOCTYPE html>
<html>
<head><title>Add Hobby</title></head>
<body>
<h1>Add Hobby</h1>
<form method="post">
    <label>Hobby Name:</label><input type="text" name="name"><br>
    <button type="submit">Submit</button>
</form>
<a href="/">Back to User Form</a>
</body>
</html>

In the terminal, run python app.py. Then visit http://127.0.0.1:5000/add_hobby, add a hobby, and check it in the SQLite extension.

Unlock the Full Learning Experience

Get ready to embark on an incredible learning journey! Get access to this lesson and hundreds more in our Digital Skills Curriculum.

Get started

Copyright Notice
This lesson is copyright of DigitalSkills.org. Unauthorised use, copying or distribution is not allowed.