JeopardyDatabase

Project
Learn how to get data from a database in Python while writing parts of a Jeopardy game, using real Jeopardy data!

Goals

 * practice using the SQL database query language
 * practice getting data from a database in Python

Install the project dependencies

 * Download and install the SQLite database engine. Precompiled SQLite binaries for all platforms can be found at: http://www.sqlite.org/download.html

Download and un-archive the Jeopardy database project skeleton code

 * http://web.mit.edu/jesstess/www/IntermediatePythonWorkshop/JeopardyDatabase.zip

Un-archiving will produce a  folder containing 3 Python files and one SQL database dump.

Windows users only
Please do one of the following:
 * 1) Add   to your Path
 * 2) Copy the   executable you download to your

If you aren't sure what to do, copy the executable.

Create a SQLite database from the database dump
Inside the  folder is a file called   which contains a SQL database dump. We need to turn that database dump into a SQLite database.

Once you have SQLite installed, you can create a database from jeopardy.dump with:

sqlite3 jeopardy.db < jeopardy.dump

This creates a sqlite3 database called

Test your setup
At a command prompt, start  using the   database by running:

sqlite3 jeopardy.db

That should start a sqlite prompt that looks like this:

SQLite version 3.6.12 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>

At that sqlite prompt, type  and hit enter. That should display a list of the tables in this database:

sqlite> .tables category clue sqlite>

From a command prompt, navigate to the  directory and run

python jeopardy_categories.py

You should see a list of 10 jeopardy categories printed to the screen. If you don't, let a staff member know so you can debug this together.

1. Look at the layout of the Jeopardy database
Start sqlite with. Then look at the tables in your database by running the following commands and the sqlite prompt:


 * .table, which will list the tables in the database
 * .schema category, which will show the organization of the category table, including the fields and the data types they store.

It should look like this:

sqlite> .schema category CREATE TABLE "category" (   id INTEGER PRIMARY KEY,    name VARCHAR(255) NOT NULL,    game INTEGER NOT NULL,    boardPosition INTEGER    );

This tells us that the  table has 4 fields: ,  ,  , and.


 * .schema clue

Read these short documents:
 * What is SQL? http://www.w3schools.com/sql/sql_intro.asp
 * What is a database schema? http://wiki.answers.com/Q/What_is_a_database_schema

Check your understanding:
 * What tables are in the database?
 * What is a schema?
 * What fields are in the  table?
 * What fields are in the  table?

2. Query the database with SELECT
Try running the following queries from the sqlite prompt:


 * SELECT * FROM category;
 * SELECT NAME FROM category;
 * SELECT * FROM clue;
 * SELECT text, answer, value FROM clue;
 * SELECT text, answer, value FROM clue LIMIT 10;</tt>

Explore the  and   tables with your own SELECT queries.

Check your understanding:
 * What does  mean in the above queries?
 * What does the  SQL keyword do?
 * Does case matter when making SQL queries?

Step 2 resources: <ul> <li> Using SELECT: http://www.w3schools.com/sql/sql_select.asp </li> </ul>

3. Make database queries from Python
Examine the code in. To make a database query from Python, you need to:
 * 1) Import a Python library for making database connections
 * 2) Establish a connection to the desired database
 * 3) Get a cursor from the database for making queries
 * 4) Execute the database query using the standard SQL syntax
 * 5) Retrieve the list of results from the database cursor
 * 6) Do something useful with the results, like print them
 * 7) Close the database connection

Match up each of these steps with lines of code in the file.

1. Modify to print both the category and game number
tip: Remind yourself of the  schema by running   at a sqlite prompt.

Example output: Example categories:

DETECTIVE FICTION (game #1) THE OLD TESTAMENT (game #2) ASIAN HISTORY (game #4) RIVER SOURCES (game #5) WORLD RELIGION (game #3) SEAN SONG (game #2) ANIMATED MOVIES (game #1) NEW YORK CITY (game #6) AFRICAN WILDLIFE (game #7) LITTLE RED RIDING HOOD (game #8)

2. Modify to only print clues with an $800 value.
A good way to achieve this is by adding a  clause to the SQL query in.

Read about  clauses in this short document:
 * The WHERE Clause: http://www.w3schools.com/sql/sql_where.asp

Example output:

Example clues:

[$800] A: She also created the detectives Tuppence &amp; Tommy Beresford Q: What is 'Agatha Christie'

[$800] A: According to this Old Testament book, this &quot;swords into plowshares&quot; prophet walked naked for 3 years Q: What is 'Isaiah' ...

5. Daily Doubles
Write a script that prints 10 daily doubles and their responses.

tip: The  table has an   field.

Example output:

Category: NEW YORK CITY Question: The heart of Little Italy is this street also found in a Dr. Seuss book title Answer: Mulberry Street === Category: RIVER SOURCES Question: This Mideastern boundary river rises on the slopes of Mount Hermon Answer: the Jordan === Category: ROOM Question: The Titanic has 3 rooms for this--only men were allowed there, as women weren't supposed to do it in public Answer: smoking ...

1. Random category clues
Write a script that randomly chooses a category and prints clues from that category.

tip: SQL supports an " " clause that will return rows in a random order. For example, to randomly pick 1 category id you could use:

SELECT id FROM category ORDER BY RANDOM LIMIT 1

You can also use  to sort the clues by value.

Example output:

5 GUYS NAMED MOE [$200] Last name of Moe of the Three Stooges [$400] Moe Strauss founded this auto parts chain along with Manny Rosenfield &amp; Jack Jackson [$600] Major league catcher Moe Berg was also a WWII spy for this agency, precursor of the CIA [$800] Term for the type of country music Moe Bandy plays, the clubs where he began, or the &quot;Queen&quot; he sang of in 1981 [$1000] This &quot;Kool&quot; rapper's album &quot;How Ya Like Me Now&quot; began a rivalry with LL Cool J

Exercises resources: <ul> <li> Using ORDER BY: http://www.w3schools.com/sql/sql_orderby.asp </li> </ul>

2. Random game categories
Write a script to randomly choose a game number and print the categories from that game.

tip: the  table has   and   fields. round 0 is the Jeopardy round, round 1 is the Double Jeopardy round, and round 2 is Final Jeopardy.

Example output: Categories for game #136: 0 WELCOME TO MY COUNTRY 0 METALS 0 GO GO GAUGUIN 0 FILE UNDER &quot;M&quot; 0 ANIMATED CATS 0 MAO MAO MAO MAO 1 SHAKESPEARE'S OPENING LINES 1 HEY, MARIO! 1 BRIDGE ON THE RIVER.... 1 RUNNING MATES 1 13-LETTER WORDS 1 TONY BENNETT'S SONGBOOK 2 IN THE NEWS 2000

3. Top 20 Jeopardy categories
Read about the  clause and write a script using it to print the 20 most common Jeopardy categories.

An example of using  and   to produce an ordered list of counts on a hypothetical   field is:

SELECT foo, COUNT(foo) AS count FROM my_table GROUP BY foo ORDER BY count

Example output: 81 LITERATURE 79 BEFORE &amp; AFTER 73 WORD ORIGINS 71 SCIENCE 64 BUSINESS &amp; INDUSTRY 63 AMERICAN HISTORY ...

Congratulations!
You've learned about SQL and making database queries from within Python. Keep practicing!