This project covers the basic concepts behind querying with Python.
We have a movie database that's fairly comprehensive. We'd like to explore it a bit with Python. Here are the columns:
movie_title color num_critic_for_reviews movie_facebook_likes duration director_name director_facebook_likes actor_3_name actor_3_facebook_likes actor_2_name actor_2_facebook_likes actor_1_name actor_1_facebook_likes gross genres num_voted_users cast_total_facebook_likes facenumber_in_poster plot_keywords movie_imdb_link num_user_for_reviews language country content_rating budget title_year imdb_score aspect_ratio
This data has been extracted from a Kaggle problem set, and you can set it up yourself using the setup-on-your-own guide.
You'll be using a few sections of the Python documentation:
- Sqlite: https://docs.python.org/3.5/library/sqlite3.html
- For-statements: https://docs.python.org/3/tutorial/controlflow.html#for-statements
- Dictionaries: https://docs.python.org/3/tutorial/datastructures.html#dictionaries
You'll also possibly want to look at the SQLITE console syntax so you can run sqlite3 movies.sqlite3 to poke around the data, but it's not necessary to complete the project.
Before you start, create a script with the following lines in it:
import sqlite3
connection = sqlite3.connect('movies.sqlite3')
connection.row_factory = sqlite3.Row
cursor = connection.cursor()
cursor.execute('select * from movies')
row = cursor.fetchone()
print(row["movie_title"])
The goal is to dump a standard set of output from the database in one pass. See if you can reproduce the following:
The movie where Ringo Starr was actor_1 was:
?
The two movies where Ringo Starr was an actor were:
?
?
These movies all featured Kristen Stewart:
?
?
...
Bill Murray has been in a lot of movies:
?
?
...
I really liked Valarie Pettiford in ?.
Anthony Hopkins has been in a lot of movies: ?, ?, ?, ?
SOO many people have been in a Batman movie. Just look at this list!
?
?
...
The total number of movies in the database is ?.
The highest grossing movie is ? with ? total.
The person who has been in the most movies is ? with ? total movies.
There are ? Superman movies, but there are ? Batman movies!
I just figured out that the Batman movies have grossed ? while Superman movies only grossed ?
The Superman movies gross an average of ? per movie, Batman grosses ? per movie.