24.9. Exercises: Databases

In these exercises, you will perform several CRUD operations with a new database file.

24.9.1. Part A: Set Up a New Database

  1. Launch Visual Studio Code and use the File menu to open your database_practice directory.

  2. Double-click the .gitignore file to open it in the editor.

  3. Choose a name for a new database file (like exercises.db) and enter it on its own line in .gitignore.

  4. Create an exercises.py file. Start by importing the sqlite3 module, then open a connection to your new database.

  5. Don’t forget to define a cursor object.

  6. Run the program. Your new database should appear in the file tree.

The setup is done. You are ready to go!

24.9.2. Part B: Add a Table

Imagine that you want to apply your new database skills to bring some order to your game closet. To do this, you will use a games table to store some information. The table needs at least 5 columns:

  • game_id: This is the primary key assigned to each row in the games table.

  • title: This is the name of the game.

  • num_players: Gives a range for the number of people required to play the game.

  • min_age: Identifies the youngest recommended player age for the game.

  • ranking: Assigns a value of 1 - 5 to the game. The higher the number, the more you like the game.

The title and num_players columns will hold TEXT data types. game_id and min_age will be integers. ranking will be a FLOAT data type.

  1. Define a sql_query variable and assign it a string to create the games table. Be sure to include the PRIMARY KEY syntax for the game_id column.

  2. Use cursor.execute() to run the SQL query and create the new table.

  3. Verify that the table was created by expanding the database name in the SQLITE EXPLORER tab.

    The expanded SQLite Explorer tab, showing the games table and column names.

24.9.3. Part C: Add Rows

  1. Assign a new string to your sql_query variable. It should use the INSERT INTO keywords to add an entry to the games table. Remember to use placeholders after the VALUES keyword.

  2. For the first new row, set the title as “Qwirkle”, the num_players as “2 - 4”, the min_age as 6, and the ranking as 4.5.

    You do NOT need to include a value for game_id, since the sqlite3 module automatically assigns primary key values.

  3. Reuse the sql_query string to add another entry to the games table. This time, choose your own values for the columns.

  4. It is possible to fill in only SOME of the columns for a given row. To skip one or more columns, leave their names and values out of the SQL command.

    Add 3 - 5 more rows to the games table. At least one of these should NOT fill in all of the columns.

  5. Finish off your code with a .commit() statement, then .close() the connection to the database.

  6. Run your program, then use the Play button in the SQLITE EXPLORER tab to view the games table.

Note

Notice that NULL appears in a cell whenever a value for that column is not assigned.

24.9.4. Part R: Access Game Data

For this section, you will need to use several different query strings to complete the steps.

  1. Use SELECT … FROM … to return results from all of the columns in the games table.

  2. Use list() to convert the returned results, then print the data to the console in a clear fashion. Tip: Define a function to accomplish this.

  3. Repeat step 1, but return and print a single entry based on its game_id value. You will need to include the WHERE keyword in your SQL query.

  4. Repeat step 1, but print only the title data from the table. Bonus: Display the games in alphabetical order.

  5. Select the title and min_age data, but print the results in DECREASING order by age.

24.9.5. Part U: Modify Game Data

Remember to include a WHERE clause in your UPDATE queries! If you leave it out, then ALL rows in the table will be changed.

Don’t forget to view the games table after each step to confirm your work.

  1. Update a single record based on its game_id.

    1. The first entry we added in Part C has a game_id of 1. Use UPDATE ... SET ... WHERE to change the ranking for this entry to a different value.

    2. Use a single UPDATE statement to change two columns for the entry with game_id = 4.

  2. Use ALTER TABLE to add a new column, called will_donate, to the table. Set the data type to BOOLEAN.

  3. With a single UPDATE command, set the will_donate value to True for all entries that have a ranking lower than 3.0.

    Note

    SQLite converts boolean values to integers. True is stored as 1, while False is saved as 0.

24.9.6. Part D: Remove Games

Remember, there is no undo option after running DELETE!

  1. Delete a single row from the table based on its game_id value.

  2. Write a single DELETE command to remove any games from the table that you don’t consider fun. You can use either the ranking or will_donate value to decide which rows to remove.

24.9.7. Bonus Exercises

Whew! You made it through all the exercises. Nice work!

Take a quick break and, if you wish, try these additional tasks that go above and beyond the basic SQL commands.

  1. Use logical operators (AND, OR, NOT) in WHERE statements.

  2. Print the complete records for the games, but only if the min_age column IS NOT null.

  3. Experiment with the ORDER BY keywords.