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

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

On Windows

 * 1) Download the SQLite command line binary from http://www.sqlite.org/download.html. Download the first link under "Precompiled Binaries For Windows".
 * 2) Copy   into your   folder.

On OS X

 * You already have SQLite installed and don't have to do anything!

On Linux

 * Install the  package through your package manager.

3. 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 (this needs to be run in the command prompt):

sqlite3 jeopardy.db < jeopardy.dump

This creates a sqlite3 database called

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

To quit sqlite:

sqlite> .quit

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: sqlite3 jeopardy.db

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. tip: Note that the category name is in the category table, and the question and answer are in the clue table.

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!