Skip to content
The Chat Archive
gpt-4o-2024-08-06· OpenAI WildChat-4.8M (AI2)

write an python program to create a simple database. Aassume we are creating database of…

  1. User

    write an python program to create a simple database. Aassume we are creating database of movies. I want you to create two table one for movie info. 2nd one is for it's rating

  2. Assistant

    To create a simple database for storing movie information and their ratings, you can use SQLite, a lightweight database suitable for small applications. Below is a Python program that uses SQLite to create two tables: one for movie information and another for movie ratings. We'll use the sqlite3 module, which is part of the Python standard library.

    import sqlite3
    
    def create_tables():
        # Connect to the database (or create it if it doesn't exist)
        connection = sqlite3.connect('movies.db')
        cursor = connection.cursor()
    
        # Create a table for movie information
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS movie_info (
                movie_id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                genre TEXT,
                release_year INTEGER
            )
        ''')
    
        # Create a table for movie ratings
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS movie_ratings (
                rating_id INTEGER PRIMARY KEY AUTOINCREMENT,
                movie_id INTEGER,
                rating INTEGER,
                FOREIGN KEY(movie_id) REFERENCES movie_info(movie_id)
            )
        ''')
    
        # Commit the transaction and close the connection
        connection.commit()
        connection.close()
    
    def add_movie(title, genre, release_year):
        connection = sqlite3.connect('movies.db')
        cursor = connection.cursor()
    
        cursor.execute('''
            INSERT INTO movie_info (title, genre, release_year)
            VALUES (?, ?, ?)
        ''', (title, genre, release_year))
    
        connection.commit()
        connection.close()
    
    def add_rating(movie_id, rating):
        connection = sqlite3.connect('movies.db')
        cursor = connection.cursor()
    
        cursor.execute('''
            INSERT INTO movie_ratings (movie_id, rating)
            VALUES (?, ?)
        ''', (movie_id, rating))
    
        connection.commit()
        connection.close()
    
    def get_movies():
        connection = sqlite3.connect('movies.db')
        cursor = connection.cursor()
    
        cursor.execute('''
            SELECT * FROM movie_info
        ''')
        movies = cursor.fetchall()
    
        connection.close()
    
        return movies
    
    def get_ratings():
        connection = sqlite3.connect('movies.db')
        cursor = connection.cursor()
    
        cursor.execute('''
            SELECT movie_info.title, movie_ratings.rating
            FROM movie_ratings
            JOIN movie_info ON movie_ratings.movie_id = movie_info.movie_id
        ''')
        ratings = cursor.fetchall()
    
        connection.close()
    
        return ratings
    
    # Run the functions
    if __name__ == "__main__":
        create_tables()
    
        # Add sample data
        add_movie('The Shawshank Redemption', 'Drama', 1994)
        add_movie('The Godfather', 'Crime, Drama', 1972)
    
        add_rating(1, 9)
        add_rating(1, 10)
        add_rating(2, 10)
    
        # Fetch and display data
        print("Movies:")
        for movie in get_movies():
            print(movie)
    
        print("\nRatings:")
        for rating in get_ratings():
            print(rating)
    

    Explanation:

    1. Creating Tables: create_tables() creates two tables:

      • movie_info: Stores the movie's title, genre, and release_year.
      • movie_ratings: Stores the movie_id (foreign key referencing movie_info) and its rating.
    2. Adding Data:

      • add_movie(title, genre, release_year): Inserts a new movie record into the movie_info table.
      • add_rating(movie_id, rating): Inserts a new rating associated with a movie into the movie_ratings table.
    3. Fetching and Displaying Data:

      • get_movies(): Retrieves all movies from the movie_info table.
      • get_ratings(): Retrieves all ratings, joining movie_info and movie_ratings tables to show each movie's title and its ratings.
    4. The if __name__ == "__main__": block adds sample data and prints the stored movies and their ratings.

    This script sets up a basic database, which you can expand by adding more advanced features such as updating or deleting records.