write an python program to create a simple database. Aassume we are creating database of…
- 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
- 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
sqlite3module, 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:
-
Creating Tables:
create_tables()creates two tables:movie_info: Stores the movie'stitle,genre, andrelease_year.movie_ratings: Stores themovie_id(foreign key referencingmovie_info) and itsrating.
-
Adding Data:
add_movie(title, genre, release_year): Inserts a new movie record into themovie_infotable.add_rating(movie_id, rating): Inserts a new rating associated with a movie into themovie_ratingstable.
-
Fetching and Displaying Data:
get_movies(): Retrieves all movies from themovie_infotable.get_ratings(): Retrieves all ratings, joiningmovie_infoandmovie_ratingstables to show each movie's title and its ratings.
-
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.
-