Embedded Files
PROGRAMMING FOR THE WEB: SQLITE
Activity 1 - Setting Up the Flask Application
Activity 1 - Setting Up the Flask Application
In this activity, we will set up a basic Flask application and create a SQLite database connection. This will serve as the foundation for our movie database web application. Remember to create all of your files and folders via the terminal. Rename old files if you want to keep them!
NOTE: This activity assumes you will be using your movie database from last lesson! If you don't have that you will need extra code for creating the movie table.
app.py
from flask import Flask, render_template, request, redirect, url_for
import sqlite3
app = Flask(__name__)
def get_db_connection():
conn = sqlite3.connect('my_movie_collection.db')
return conn
@app.route('/')
def index():
conn = get_db_connection()
movies = conn.execute('SELECT * FROM movies').fetchall()
conn.close()
return render_template('index.html', movies=movies)
if __name__ == '__main__':
app.run(debug=True)
templates/index.html
<!DOCTYPE html>
<html lang="term-for-english">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Movie List</title>
</head>
<body>
<h1>Movies in Database</h1>
<p>{{ movies }}</p>
</body>
</html>
❓ Explanation:
❓ Explanation:
We import necessary modules from Flask and sqlite3.
The get_db_connection() function creates and returns a database connection.
The index route fetches all movies from the database and passes them to the template.
🚀 Try It Out:
🚀 Try It Out:
Create a new Python file named app.py and paste the above code.
Create a templates folder and add the basic index.html file above.
Run the Flask application and visit http://localhost:5000. What do you see?
Activity 2 - Creating a Form to Add Movies
Activity 2 - Creating a Form to Add Movies
In this activity, we will create a form that allows users to add new movies to our database. This demonstrates how to handle POST requests and insert data into SQLite through Flask.
app.py
@app.route('/add', methods=['GET', 'POST'])
def add_movie():
# On a form submission (POST)
if request.method == 'POST':
title = request.form['title']
director = request.form['director']
year = int(request.form['year'])
rating = float(request.form['rating'])
conn = get_db_connection()
conn.execute('INSERT INTO movies (title, director, year, rating) VALUES (?, ?, ?, ?)',
(title, director, year, rating))
conn.commit()
conn.close()
return redirect(url_for('index'))
# On visiting the page (GET)
return render_template('add.html')
HTML for add.html:
<h1>Add a New Movie</h1>
<form method="post">
<input type="text" name="title" placeholder="Title" required>
<input type="text" name="director" placeholder="Director" required>
<input type="number" name="year" placeholder="Year" required>
<input type="number" step="0.1" name="rating" placeholder="Rating" required>
<input type="submit" value="Add Movie">
</form>
❓ Explanation:
❓ Explanation:
The route handles both GET (display form) and POST (process form) requests.
We extract form data using request.form and insert it into the database.
After adding the movie, we redirect to the index page to see the updated list.
🚀 Try It Out:
🚀 Try It Out:
Add the new route to your app.py file.
Create add.html in your templates folder with the provided HTML.
Run the app and visit http://localhost:5000/add to add a new movie.
After adding a movie, check if it appears on the main page. Do the movies present neatly on the page?
Activity 3 - Displaying Movies in a Table
Activity 3 - Displaying Movies in a Table
In this activity, we will update our index page to display movies in a neat HTML table. This shows how to present database data in a structured format on a web page.
Update the index() function in app.py:
@app.route('/')
def index():
conn = get_db_connection()
conn.row_factory = sqlite3.Row
movies = conn.execute('SELECT * FROM movies').fetchall()
conn.close()
return render_template('index.html', movies=movies)
Update index.html:
<h1>Movie Database</h1>
<a href="{{ url_for('add_movie') }}">Add a New Movie</a>
<table>
<tr>
<th>Title</th>
<th>Director</th>
<th>Year</th>
<th>Rating</th>
</tr>
{% for movie in movies %}
<tr>
<td>{{ movie['title'] }}</td>
<td>{{ movie['director'] }}</td>
<td>{{ movie['year'] }}</td>
<td>{{ movie['rating'] }}</td>
</tr>
{% endfor %}
</table>
❓ Explanation:
❓ Explanation:
We fetch all movies from the database in the index() function.
In the template, we use a for loop to iterate over the movies and display them in table rows.
We access movie attributes using dictionary-style notation (e.g., movie['title']) because we set row_factory = sqlite3.Row.
Otherwise we would have to access each attribute like this: movie[1], movie[2] etc.
🚀 Try It Out:
🚀 Try It Out:
Update your index() function and index.html template with the provided code.
Refresh your browser at http://localhost:5000. You should see your movies displayed in a table.
Try adding more movies and see how the table updates. View the HTML source code in your browser tools to see how more rows have been added.
Final Challenge: Implementing a Search Feature
Final Challenge: Implementing a Search Feature
Now that you have a basic movie database web app, your challenge is to implement a search feature.
Here's what it should do:
Create a new route /search that accepts GET requests with a query parameter.
Add a search form to your index.html that submits to this new route.
Modify your SQLite query to filter movies based on the search term (hint: use the LIKE operator).
Display the search results in a table similar to the index page.
Here's a starter template for the search route:
@app.route('/search')
def search_movies():
query = request.args.get('query', '')
conn = get_db_connection()
conn.row_factory = sqlite3.Row
# TODO: Write a SQL query that searches for movies based on the query
# The query should search in both title and director fields
# The % symbol is a wildcard character that matches zero or more characters
movies = conn.execute('Your SQL query here', ('%' + query + '%',)).fetchall()
conn.close()
return render_template('search.html', movies=movies, query=query)
And a basic structure for search.html:
<h1>Search Results for "{{ query }}"</h1>
<a href="{{ url_for('index') }}">Back to Home</a>
<!-- TODO: Add a table to display search results, similar to index.html -->
Fill in the TODO sections with your code.
This challenge will help you practice everything you've learned in this lesson about integrating SQLite with Flask. Good luck!
Remember to commit and push your code when done! Here are the terminal commands to do so:
Commit your changes with a meaningful message:
git commit -am "Your message here"
Note: The -a flag automatically stages all modified files before committing, -m is to add a message.Push your changes to GitHub:
git push origin main
Page updated
Report abuse