PROGRAMMING FOR THE WEB: SQLITE
TASK 2 - SQLITE BASICS

Activity 2 - Creating a Table

In this activity, we will create a table in our SQLite database using Python. We'll recreate the movies table from our previous SQL lesson, demonstrating how to structure data in our database.

import sqlite3

conn = sqlite3.connect('movies.db')

cursor = conn.cursor()

# Create the movies table

cursor.execute('''

CREATE TABLE IF NOT EXISTS movies (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    title TEXT NOT NULL,

    director TEXT,

    year INTEGER,

    rating FLOAT

)

''')

# Commit the changes and close the connection

conn.commit()

conn.close()

❓ Explanation:

• We use cursor.execute() to run SQL commands.

• The SQL command is a multi-line string ('''  ''') for better readability.

• IF NOT EXISTS prevents errors if the table already exists.

• AUTOINCREMENT for the id field ensures each new entry gets a unique identifier automatically.

• conn.commit() saves the changes to the database.

🚀 Try It Out:

• Run this code. What happens if you run it multiple times?

• Try adding a new column to the table, like genre TEXT.

• Use a SQLite browser tool to open movies.db and verify that the table was created with the correct structure.

• What happens if you try to create a table with a name that already exists?

Activity 4 - Querying Data

In this activity, we will learn how to query our database and retrieve data using Python. We'll write several SELECT statements to fetch movie data, demonstrating how to extract and analyze information from our database.

import sqlite3

conn = sqlite3.connect('movies.db')

cursor = conn.cursor()

# Select all movies

cursor.execute('SELECT * FROM movies')

all_movies = cursor.fetchall()

print("All movies:")

for movie in all_movies:

    print(movie)

# Select movies after 2000

cursor.execute('SELECT title, year FROM movies WHERE year > 2000')

recent_movies = cursor.fetchall()

print("\nMovies after 2000:")

for movie in recent_movies:

    print(f"{movie[0]} ({movie[1]})")

# Select average rating

cursor.execute('SELECT AVG(rating) FROM movies')

avg_rating = cursor.fetchone()[0]

print(f"\nAverage rating: {avg_rating:.2f}")

conn.close()

❓ Explanation:

• cursor.execute() runs our SQL query.

• cursor.fetchall() retrieves all rows from the result.

• cursor.fetchone() retrieves a single row (useful for aggregate functions like AVG).

• The results are returned as tuples (or a list of tuples for multiple rows).

• We use formatted string literals (f-strings) to create more readable output.

🚀 Try It Out:

• Run this code and observe the output.

• Modify the second query to find movies from a different year range.

• Write a new query to find the highest rated movie.

• Try to print the results in a different format (hint: you could use string formatting to align columns).

Final Challenge: Movie Database Manager

Now that you've learned the basics of SQLite in Python, it's time to put your skills to the test! Your challenge is to create a simple movie database manager program. Here's what it should do:

1. Create a movies.py file

2. Create a new SQLite database called my_movie_collection.db

3. Create a table called movies with columns for id, title, director, year, and rating

4. Implement functions to:

   • Add a new movie to the database

   • Display all movies

   • Update the rating of a movie

   • Delete a movie from the database

   • Find all movies by a specific director

Here's a code template for your Movie Database Manager. It already includes a working menu system and user input.
Your task is to fill in the body of each function with the appropriate SQLite operations:

import sqlite3

def create_connection():

    # TODO: Create a connection to my_movie_collection.db

    pass

def create_table(conn):

    # TODO: Create the movies table

    pass

def add_movie(conn, title, director, year, rating):

    # TODO: Insert a new movie into the database

    pass

def display_all_movies(conn):

    # TODO: Select and display all movies

    pass

def update_movie_rating(conn, title, new_rating):

    # TODO: Update the rating of a specified movie

    pass

def delete_movie(conn, title):

    # TODO: Delete a specified movie from the database

    pass

def find_movies_by_director(conn, director):

    # TODO: Find and display all movies by a specific director

    pass

def main():

    conn = create_connection()

    if conn is not None:

        create_table(conn)

        while True:

            print("\n--- Movie Database Manager ---")

            print("1. Add a new movie")

            print("2. Display all movies")

            print("3. Update a movie's rating")

            print("4. Delete a movie")

            print("5. Find movies by director")

            print("6. Exit")

            choice = input("Enter your choice (1-6): ")

            if choice == '1':

                title = input("Enter movie title: ")

                director = input("Enter director name: ")

                year = int(input("Enter release year: "))

                rating = float(input("Enter rating (0-10): "))

                add_movie(conn, title, director, year, rating)

                print("Movie added successfully!")

            elif choice == '2':

                display_all_movies(conn)

            elif choice == '3':

                title = input("Enter movie title to update: ")

                new_rating = float(input("Enter new rating (0-10): "))

                update_movie_rating(conn, title, new_rating)

                print("Rating updated successfully!")

            elif choice == '4':

                title = input("Enter movie title to delete: ")

                delete_movie(conn, title)

                print("Movie deleted successfully!")

            elif choice == '5':

                director = input("Enter director name: ")

                find_movies_by_director(conn, director)

            elif choice == '6':

                print("Thank you for using Movie Database Manager. Goodbye!")

                break

            else:

                print("Invalid choice. Please try again.")

        conn.close()

    else:

        print("Error! Cannot create the database connection.")

if __name__ == '__main__':

    main()

🚀 To complete this challenge:

• Implement each of the functions (create_connection(), create_table(), etc.) using the SQLite operations you learned in the lesson.

• Run the program and use the menu to interact with your movie database.

• Test each operation to ensure it works correctly.
  
  

❓Here are some hints to get you started:

• In create_connection(), use sqlite3.connect() to create a connection to the database.

• In create_table(), use CREATE TABLE IF NOT EXISTS to create the movies table.

• For add_movie(), use an INSERT INTO statement.

• In display_all_movies(), use a SELECT * query and fetch all results.

• For update_movie_rating(), use an UPDATE statement with a WHERE clause.

• In delete_movie(), use a DELETE statement with a WHERE clause.

• For find_movies_by_director(), use a SELECT statement with a WHERE clause.

Remember to commit your changes after operations that modify the database, and to use parameterized queries to prevent SQL injection.

Remember to commit and push your code when done! Here are the terminal commands to do so:

1. 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.

2. Push your changes to GitHub:
   git push origin main