CS15 : Databases


https://docs.google.com/presentation/d/1Sh-pFKbJm2ZKw9i26EyKDnw5-fmzZXJJsmgyv4kD6nk/preview
We humans find it easier to navigate through the vast array of data there is out there if the data is structured and easy to manage. Databases provide this functionality - structure firstly and then a special type of language to 'query' the data to draw out useful things.

We are learning ...
  • How data is stored in a structured database
  • How the data in a database can be searched
So that we can ...
  • Create single table databases with a suitable structure
  • Choose suitable datatypes for fields
  • Choose suitable primary keys
  • Perform Query-by-example searches of data in the table
  • Use SQL style commands to search for data

NEEDS AN INTRODUCTION ABOUT STRUCTURED AND UNSTRUCTURED DATA

CGP The Revision Guide Page 52, 53
CGP Exam Practice Workbook Page 58, 59

# Get Ready.png


Activity 1 Tables to CSV files    I   O   A   E 

https://drive.google.com/file/d/1H-K9uS20jNMIX8BnXNh7KwAezYWmNVBS/preview
Click to engage

In the Keeping Data Safe topic, we saw some data storage examples where we saved data in persistent data stores called CSV, or 'Comma Separated Value' files. In fact, when we create these CSV files, we abstract away the structure of the original data store, normally a table. For instance ...

https://drive.google.com/file/d/1RTPi9SSuFLT8k2qn3AvwM7NUFrwTVajM/preview
The relationship between a table and a CSV file - click to engage


Task 1.1 Storing a table as a CSV file
Where we learn how to store records in a CSV file and access them with a Python script.


Set up your data collection methods

First we'll collect some raw data about your peers. Grab yourself a piece of paper, rotate it to landscape format and set up a table like this ...


Look carefully at the sample data I've included, particularly the format and the encoding ...
  • The DOB (Date of Birth) is always in the format DD/MM/YY;
  • The Eye Colour is encoded using 2 letters - 'Gr' stands for 'Green';
  • The Shoe Size is always a float / real even though it could be an Integer;
  • The Nose Length is measured in 'mm' and the units are NOT included in the table;
  • The Hair Colour is encoded using 2 letters - 'Br' stands for 'Brown' and 'Bl' stands for Blonde.
So, before you start collecting data, you need to make sure that you decide on the colours you will need for both the eye and the hair colour and encode these first. It will make it easier later on if you use only one set of colour codes for both the eye and the hair colour ...

Now collect your data!

Collect data on at least 20 of your peers! Make sure that you have encoded the Eye Colour and Hair Colour and you've NOT written the units in the Nose Length!

Now some terminology ...

Whereas in normal conversation, we might refer to the parts of this table as rows and columns, when we are discussing databases, we call them records and fields ...


  • A record is a single row of data in the table which holds the data for one person
  • A field is the name used to identify each value in the record.
Now label your table like the example to identify the records and fields.

Create a CSV file of your data, manually!

Now convert your manual data into a CSV file called peer.csv and make sure that you ...
  • Do not include the field headings;
  • Encode your data;
  • Do not surround each item with quotation marks;
  • Separate each data item with a comma;
  • Store each record on a separate line.
Print out your CSV file for your notes. For instance, my results might look like this (I only collected 5 'cause I'm lazy) ...


Programming challenges

Attempt the following challenges to demonstrate your understanding.
  • Create a Python script using IDLE (File > New File), save it as ... Task 1.1 Challenges.py
  • Use it to record your attempt at the following challenges.
  • When you have completed as many of the challenges as you can, evidence your work through a combination of screenshots and written explanations in a word processed document with a suitable header and footer. 
  • Remember to show the script and the output it generates.
EASY

Use the csv.py module from the Keeping data safe topic to print out the contents of the peer.csv file on the screen one row at a time without formatting. The output should look a little like this (for my list) ...


MEDIUM

Again, use the csv.py module and a script similar to the one in the Building data structures topic (Task 2.2 Movie ratings (automated version)) to print out the data in the form of a table. The output should look like this ...


HARD

Finally, adapt the script from the Medium challenge so that it uses a dictionary to display the actual values for Eye colour and Hair colour rather than the encoded values. The output should look like this ...



Demonstrate your understanding

Irma wanted to know whether it was possible to replace her manual system of recording users data for the FilmBuffs website with a computerised version using CSV files and Python scripts. Write an instruction manual for how she might be able to achieve this. Make sure you use all the keywords from this task in your response.



Could you extend your Python script so that it performs the following functions on the data?
  • Calculating and displaying each persons age from their date of birth;
  • What is the most popular eye colour;
  • Choosing a field to sort the results in ascending or descending order before printing the table;
  • Displaying who has the shortest and who has the longest nose;
  • Searching for a particular persons data;
  • Printing out only the people with black hair;
  • etc, etc.

Activity 2 Creating simple databases   I   O   A   E 

https://drive.google.com/file/d/1xuA-hoEpifQM-lAQ0f8DlaQ8ExjkQhfr/view?usp=drive_web
Click to engage

For this section, we are going to be using a piece of software called DB Browser for SQLite which is free and allows you to create single file databases which can be accessed using Python! Nice!


Task 2.1
Download and open an existing database file
Where we learn how to manage a simple database file


Download the database file

Download the 'films.db' database file and save this in a suitable place in your documents. DO NOT DOUBLE CLICK THE FILE - the operating system will probably not know which application to open it with and you might get an error!

Open the database file using DB Browser for SQLite

Now, open DB Browser for SQLite and click on the 'Open Database' button at the top of the screen. Find the 'db' file you saved and open it. You should see a lot of entries on the 'Database Structure' tab, including an entry under 'Tables' called 'film'.

Inspect the data in the film table

Click on the 'Browse Data' tab. The 'film' table should already be selected and you should see 12 records. You can resize the columns if you like to make the data easier to read. Do you recognise this data from a previous section?

https://drive.google.com/file/d/122-4uzGQ9dTtHc0QncfSpztji3MF1vjY/view?usp=drive_web
The contents of the film table - click to engage

The fields in this table are ...
  • filmID - this is called a primary key and is used to uniquely identify each record in the table
  • title - the title of the film
  • genre - the genre of the film
  • length - the length of the film as a real number (be careful - this is not hours and minutes!)


Evidence your learning

True, there is not much to learn in this task, but nevertheless. In a word processed document with a suitable header and footer, put a screenshot of the table view from DB Browser. Label the field names and the records in a similar way to the last part of Task 1.1.



Task 2.2 Create a new table
Where we learn how to create a new database table


You've got a simple, single table database now that you can experiment with in the next Activity. If you are up for a challenge, we will now try to create a new table in the database by constructing the table first and then importing data into it from a CSV file. Follow the instructions carefully!

Create a new table in the database

Choose 'Edit > Create Table ...' and you should get a popup window asking you for information about the table.

Name the table

Name the table 'user'. Notice that the table is called 'user' rather than 'users'. This is important as it holds instances of each user of our film rating system.

Add fields to the table

Using the 'Add field' button, enter the following field definitions ...

https://drive.google.com/file/d/16pNdjJGfM40Nxmzn4Jx1CM3X4dDdwkgt/view?usp=drive_web
Field definitions - click to engage

When creating the table, we specify the username as the primary key which is used to identify each user.

As you were creating the table, you should have noticed that the software created some instructions in the panel underneath. This is a special type of language called 'Structured Query Language' or 'SQL' for short. We will learn about simple SQL later on in this section.

https://drive.google.com/file/d/1aqLbld4LCqHuaYTEeTy9PVN40RntT80d/view?usp=drive_web
Defining the table in SQL - click to engage

If you are happy that your utterances match mine, click the 'OK' button.

Write changes to the database

Click the 'Write Changes' button to make sure that the changes you have made to the database are saved.

Evidence your learning

Did you click the 'OK' button? Shame - you'll need to get the table definition dialogue back again before you can evidence your learning. Click on the 'Database Structure' tab and select the 'user' table. Now select 'Edit > Modify Table' from the menu bar.

Take a screenshot of the 'Edit table definition' window and put this in a word processed document with a suitable header and footer. Write an explanation to Sampson describing what you have done.



Task 2.3
Import data from a CSV file
Where we learn how to import data from a CSV file into a database table


Download a copy of the following CSV file. It contains a list of users for the FilmBuffs website.


Follow the instructions carefully!

Find your CSV file and ask DB Browser to import it

Choose 'File > Import > Table from CSV file ...'. Find the 'user.csv' file in your userspace, select it and click 'Open'. Because you have already created a table called 'user' and the CSV file is called 'user.csv', DB Browser for SQLite should automatically decide to import the data into the correct table.

Check the import settings


Make your your 'Import CSV file' dialogue looks like mine ...

https://drive.google.com/file/d/1P2vWTvr42Wc5rpNWB2ICMnoiX03E_Qs-/view?usp=drive_web
The 'Import CSV file' dialogue box - click to engage

When you are happy, click the 'OK' button. You should see a dialogue box like this. If you don't, you have probably named either your table or the CSV file incorrectly. Go back and fix it now!

https://drive.google.com/file/d/1h2XpEFMF_K0PoflPdXDc9_guFZQzPzpP/view?usp=drive_web
Are you sure - click to engage

When you are happy, click the 'Yes' button. You should see an 'Import completed' confirmation box. Click 'OK'.

Browse the data you imported in the database

Now click on the 'Browse Data' tab and make sure that the 'user' table is selected. You should see all the data from your CSV file in the table. Again, you can adjust the column widths to make the contents easier to read.

https://drive.google.com/file/d/10AzCQK4roA-6fcyTEnaLiltHwvXSgwA6/view?usp=drive_web
Browsing the 'user' table - click to engage

Write changes to the database

Click the 'Write Changes' button to make sure that you changes have been saved.

Evidence your learning

Provide a screenshot of the data in the users table on a word processed document with a suitable header and footer. Underneath the screenshot, write an explanation to Sampson explaining what you have done. You should be able to compare this to the work you evidenced in Task 1.1.



Yes, you totally could have done that! I would always suggest creating the table first however as it gives you more control of the field definitions. If you really want to import the table structure and the data together, read on!


Task 2.4
 Import a complete table from an SQL file
Where we learn how to import a complete table from an SQL file


The last option for creating a table is to import a text file which creates the table and then imports the data directly into it. The text file has a special 'sql' file extension so that DB Browser knows it's a valid file.

Download the SQL file

Download the rating.sql file and save this in the same folder as your database file.

Inspect the contents of the SQL file

Open the SQL file in Notepad++ - do NOT double click it! Again, the Operating System might complain that it doesn't have a file association or it might open the SQL file in another application instead. Look carefully at the structure of the file - there are four sections. 

The first line signifies a 'database transaction' is occurring ...

https://drive.google.com/file/d/1cPPgWF_BKR2JUh8Xrcia3Fl_1imGEoso/view?usp=drive_web
The first part of the SQL file - click to engage

The second section defines the table structure, using the 'filmID' and the username as a composite key ...

https://drive.google.com/file/d/1h-ipwQwML_mVDtFo6kovLu4-L6VEZG3K/view?usp=drive_web
Defining the new table in SQL - click to engage

The majority of the file INSERTs data into the table ...

https://drive.google.com/file/d/1i_tjDpCY3i6W6YPUQY8-cABGZko1wAUn/view?usp=drive_web
Lots of inserting going on - click to engage

The last part COMMITs changes to the database

https://drive.google.com/file/d/1o3UaWhezJnpEHKrtdGvf58D8ujnDv0dP/view?usp=drive_web
I'm ready to commit - click to engage

Print out the SQL file for your folders (your teacher may give you a copy).

Import the SQL file into the database

Choose 'File > Import > Database from SQL File ...' and locate the 'rating.sql' file you downloaded in Step 1. You should get a dialogue box asking whether you want to create a new database. Make sure you click 'No' ...

https://drive.google.com/file/d/1NuBH7WGo8RQZ1FilHe_iBm3_4pOOxo9-/view?usp=drive_web
Ready to import the SQL file - click to engage

You should be greeted with a dialogue box informing you that the import was successful.

Inspect the contents of the 'rating' table

Click the 'Browse Data' tab and make sure that the 'rating' table is selected. You should see a table of data with three columns ...
  • filmID
  • username
  • rating
This table records each film (from the 'film' table) that each user (from the 'user' table) has watched and what rating they awarded it from 1 through 5. The table is structured so that each user can only rate (or re-rate) each film once. through the use of a composite key of 'filmID' and 'username' (which must be unique).

https://drive.google.com/file/d/1oRh2pIzavgNZxaHR95rnq8NUAiQ9ZB_J/view?usp=drive_web
Browse the 'rating' table - click to engage

We could use this table to investigate which users have watched each film or which films a particular user has watched, but more of this later ...

Write changes to the database

Click the 'Write Changes' button to make sure that the changes you have made to the database have been saved.

Evidencing your learning

In a suitable word processed document, write an step-by-step instruction guide to explain to Sampson how to import data into a database from an SQL file. Also, email a copy of the 'film.db' file to your teacher so that they can check that everything is OK and that you have completed the work satisfactorily.


Activity 3 Selecting, sorting and filtering data   I   O   A   E 

https://drive.google.com/file/d/1-Gt5bPso7xS-CQ5mnQ5nwibcS_7_WBP8/view?usp=drive_web
Click to engage

You will need a copy of the completed 'film.db' database file from the previous activity. If you haven't finished or you have lost it, you can download a completed copy!

If you had, you might not have done the work!

We will be using Structured Query Language (SQL) to extract data from our database. The SQL instructions we write are called queries because they ask the database questions.


Task 3.1
Selecting data
Where we learn how to select data from a database


Open up your database

If you have not done so already, open up the completed film database using DB Browser for SQLite.

Switch to the 'Execute SQL' tab

We have mainly been working in the 'Browse Data' tab. Switch to the 'Execute SQL' tab now will allow us to ask our database questions to help us to search for data using SQL.

https://drive.google.com/file/d/1hrtaVd44kYLw8Q5DSWYgp4x5NzOevlcn/view?usp=drive_web
Structure of the 'Execute SQL' tab - click to engage

Select all data from a table

In SQL, the asterisk (*) is a wildcard character which stands for 'everything' and we use this when we want to retrieve all fields from tables in our database.

SELECT * FROM film

Try out this query yourself in the 'Execute SQL' tab of DB Browser for SQLite.

Selecting all data from a single table - click to engage


Select a single field from a table

Sometimes, we don't need all the fields in the results so we can specify which field we want.

SELECT title FROM film

Try out this query yourself in the 'Execute SQL' tab of DB Browser for SQLite.

Selecting a single field from a table

Select multiple fields from a table

Finally, we can specify multiple fields as a comma separated list.

SELECT title, genre FROM film

Try out this query yourself in the 'Execute SQL' tab of DB Browser for SQLite.

Selecting multiple fields from a table

Demonstrate your learning

Roy wants some data from the database. You challenge is to construct suitable SQL queries to satisfy the following requirements. Provide evidence (screenshot or written explanation) of your SQL queries and explain to Roy how they work. Remember to test them out to make sure they work!
  1. Select all the data from the 'user' table
  2. Select only the 'username' from the 'user' table
  3. Select the 'username' and the 'email_address' from the 'user' table.



Task 3.2
Sorting data
Where we learn how to sort data selected from a database


Once we have selected our data, we can sort it into order. The order can be ASCENDING (starting small and getting bigger) or DESCENDING (starting big and getting smaller). Data can be sorted numerically or alphabetically (by ASCII code).

https://drive.google.com/file/d/1lN_id6zAJhlaSPQdN01uPK2wstek57uo/view?usp=drive_web
Ascending or descending - click to engage

Open the database and switch to the 'Execute SQL' tab

Make sure you have the 'film' database open and that you are working in the 'Execute SQL' tab.

Sort data in ascending order

In SQL, we use the ORDER BY clause to tell the database how to sort the data. For instance, to sort the films into ascending order of length, we would use the following SQL command ...

SELECT * FROM film
ORDER BY film.length ASC

... where ASC stands for ASCending. I've split the command onto multiple lines to make it easier to read. Try out this query yourself in DB Browser. I've been lazy and selected all (*) the fields.

Sorting in ASCending order

Sort data in descending order

To sort in descending order, simple change the end of the query from ASC to DESC (for DESCending).

SELECT * FROM film
ORDER BY film.length DESC

Try the query out yourself in DB Browser to make sure you are comfortable with it's use.

Sorting in DESCending order

Demonstrate your learning

Roy is demanding, isn't he? He wants some more data from the database and has asked you to construct suitable SQL queries to satisfy the following requirements. Write your SQL queries in your notebook / on paper and explain to Roy how they work. Remember to test them out to make sure they work!
  1. Select just the 'title' from the 'film' table in ascending order of 'title'
  2. Select the 'title' and 'length' of each film from the 'film' table in descending order of 'length'
  3. Select the 'title' and 'genre' of each film from the 'film' table in ascending order of 'length'




Task 3.3
Filtering data
Where we learn how to filter data from a database


This is where the real power of databases comes into play. To filter results from our database, we use the WHERE clause followed by a conditional statement.

Open the database and switch to the 'Execute SQL' tab

Make sure you have the 'film' database open and that you are working in the 'Execute SQL' tab.

Filtering data based on the exact contents of a field

Quite simply, we use the WHERE clause to specify an equality. For instance if we only want the films from the 'Action' genre, we could use an SQL command like this ...

SELECT * FROM film
WHERE film.genre = 'Action'

Try out the query in DB Browser.

Filtering based on an exact value

Filtering data based on a range of values in a field

We can also use other standard comparison operators in the WHERE clause. For instance, if we want all films which are 2.0 or more hours long from our table, we could use this SQL query ...

SELECT * FROM film
WHERE film.length >= 2.0

Try out the query in DB Browser.

Filtering based on a range

Filtering data by matching part of a field

Finally, we can search for data my matching part of the contents of a field using the LIKE clause. We also normally have to use the '%' wildcard character before and after the search term to force the SQL statement to match the search term anywhere in the field.

SELECT * FROM film
WHERE film.title LIKE '%the%'

Try out the query in DB Browser.

Filtering based on a match

Combining search criteria using AND and OR

You can use logical AND and OR operations to specify more than one search criteria at a time. For instance, if we wanted all the details for the films in the "Comedy" genre that were longer than 1.5 hours in length, we could write a query like this ...

SELECT * FROM film
WHERE film.genre = 'Comedy'
AND film.length > 1.5

Try out the query in DB Browser

Filtering using AND and multiple criteria

If we wanted all the details of films in the "Science fiction" or the "Historical" genre, we could use the OR operator.

SELECT * FROM film
WHERE film.genre = 'Science fiction'
OR film.genre = 'Historical'

Try out the query in DB Browser

Filtering using OR and multiple criteria

Demonstrate your learning

What, more questions! Roy challenges you to construct suitable SQL queries to satisfy the following requirements. Write your SQL queries in your notebook / on paper and explain to Roy how they operate. Remember to test them out to make sure they work!
  1. All the fields for the films in the 'film' table where the 'genre' is "Historical" displayed in ascending order of 'title'. [2 results]
  2. Just the 'title' of the films from the 'film' table where the 'genre' is "Comedy" displayed in ascending order of 'title'. [2 results]
  3. The 'title' and the 'genre' of the films from the 'film' table where the 'length' is less than 2.0 hours displayed in descending order of 'length'. [8 results]
  4. The 'title' of the films from the 'film' table where the 'title' contains the word "of" displayed in ascending order of 'title'. [4 results]
  5. The 'title' and 'length' of the films from the 'film' table where the 'genre' is "Action" AND the 'title' contains the word "of" displayed in ascending order of 'title'. [1 result]
  6. All the fields for the films in the 'film' table where the 'genre' is "Comedy" OR the 'length' is less than 1.5 displayed in ascending order of 'length'. [5 results including a Comedy film with length greater than 1.5 hours]




In Step 4 of the last task, we used the '%' wildcard character to enable us to search for something in one of the fields in our database by putting it before and after the search term. Investigate what happens if you omit the '%' sign at the start, at the end or both. What does the '%' sign actually do? Under what circumstances would each search string find the word 'the'? Tell your teacher you thoughts.


Activity 4 Advanced operations   I   O   A   E 

Click to engage

The following is not for the fainthearted. You'll know enough by now to be able to search for anything you like in a single table database and sort the results into order. But what about the following operations? Databases do that as well?
  • Inserting records
  • Deleting records
  • Editing records
You could, but why make life easy for yourself?


Task 4.1 Inserting records
Where we learn how to insert records into a database table


Open the database and switch to the 'Execute SQL' tab

Make sure you have the 'film' database open and that you are working in the 'Execute SQL' tab.

Insert a new record in the 'film' table

Let's use SQL to INSERT (that's a clue) a new record into the table. Inserting records always puts them at the end of the table. In fact, we've seen the INSERT command before, can you remember where? (HINT : Look up ...)

INSERT INTO film
VALUES ('013','A Slight Disturbance on Oak Avenue','Horror',2.7) 

Demonstrate your learning

Emma has asked you to add a new film (title, genre and length of your choice) into the database. Remember to use a different, unique ID for the film (currently '014') or you will get a 'UNIQUE constrain failed' error - the 'filmID' must be unique to allow each film in the 'film' table to be identified. Evidence your learning using screenshots and written explanation in a word processed document.



Task 4.2 Deleting records
Where we learn how to delete records from a database table


Open the database and switch to the 'Execute SQL' tab

Make sure you have the 'film' database open and that you are working in the 'Execute SQL' tab.

Delete a record from the 'film' table

To delete a record from a table you have to use a WHERE clause to specify which record to delete. For instance, to delete the new record we just inserted ...

DELETE FROM film
WHERE film.filmID = '013'

Demonstrate your learning

Emma made a mistake and has asked you to delete the record you added in the previous task. Be careful however; if you delete one of the other films, you might inadvertently break an activity which you might engage in later on. Again, evidence your learning using a combination of screenshots and written explanations of what you did, why you did it and what effect it had.



Task 4.3 Updating / editing records
Where we learn how to update and edit records from a database table


Open the database and switch to the 'Execute SQL' tab

Make sure you have the 'film' database open and that you are working in the 'Execute SQL' tab.

Update a record in the 'film' table

Again, the update / edit a record, we have to use a WHERE clause to specify which record we want to update. For instance, one of the film titles in the original 'film' table was wrong, so let's change it.

UPDATE film 
SET title = 'The Prisoner of the Boyfriend' 
WHERE film.filmID = '011' 

Demonstrate your learning

Lastly, Emma has noticed that the title of film 'The First Flames' is wrong - it should be called 'The Last Flames'. Use a suitable SQL query to change the 'title' of the film. Evidence your learning using a combination of screenshots and written explanations in a word processed document with a suitable header and footer.


  

Activity 5 Multiple table operations   I   O   A   E 

https://drive.google.com/file/d/1SAp9jpMNVo5rkd7xy_cgcwzJ7JUJiuR2/view?usp=drive_web
Click to engage

Really, I wouldn't normally introduce the concepts in this section until A Level, so if you've made it this far and you are still with me, well done! You might have been wondering why there were two other tables in the database that we haven't really used yet? Well, we are about to find out!

The database you have created holds details of ...
  • which films are available to stream (stored in the 'film' table)
  • the users of the website (stored in the 'user' table)
  • which films each user has watched together with the rating they gave it (stored in the 'rating' table)
The relationship between these tables gives us the power to find the answer to some pretty complex questions ...


The database can tell me that? We can find the answer to this question using database joins. Let me explain ...

https://drive.google.com/file/d/1c65KY3ssyNA3-g-UXtDH7ihSujUN0P4R/view?usp=drive_web
Click to engage and print yourself a copy


Task 5.1 Complex, man
Where we learn how to select data from multiple tables


Print a copy of the explanation

If your teacher hasn't already given you a copy, print yourself a copy of the explanation from the popup and put it in your folders.

Rubber duck the example

Rubber duck the example to your shoulder partner. Quack, quack!

Try out the query

Open up your copy of the 'film.db' database file. If you haven't got a copy, you can download one from the top of Activity 3. Switch to the 'Execute SQL' tab, type in and execute the query from the example. As you are typing it, READ IT OUT LOUD - this will help you to focus on it's operation.

Executing a complex query using joins

Demonstrate your learning

Now construct a multi-table query which satisfies Georgia's original request (you might need to look back at the start of the activity to find out what it was. Evidence your learning using a combination of screenshots and written explanations - if you achieve this, you are cookin!


Activity 6 Using the database in Python   I   O   A   E 

https://drive.google.com/file/d/1ZHrpEy_Gi6n7twTTMftiHBzRLJPR77HO/view?usp=drive_web
Click to engage

It's all very well and good executing all these queries in DB Browser but what use is that? True, we could export the results as a CSV file and use them in our Python script using the CSV Library but, there is a way of interacting with the database file directly. 

https://drive.google.com/uc?export=download&id=1OZ9P9S3Oc9wCYZPrzp-peJjI2v8_F3mO
Click to download the Cheatsheet


Task 6.1 Using Python to retrieve data from a database
Where we use Python to retrieve data from a database


Create a new Python script

Create a new Python script and save it with a suitable filename in the same folder as your film.db database file.

Use Python to retrieve and display data from the database

Use the 'Cheatsheet' to help you to write Python scripts to retrieve and display the following. The results are always returned as a read only list (called a tuple) but you can 'handle' this like you would any normal Python list.
  • all the details of all the films in the film table
  • just the title of all the films from the film table
  • the title and genre of all the films from the film table
  • all the details of all the films from the film table in ascending order of film length
  • all the details of all the films from the film table in decending order of film length
  • all the details of all the films from the film table where the genre is 'Action'
  • all the details of all the films from the film table where the length is greater than or equal to 2.0 hours
  • all the details of all the films with the word 'the' anywhere in the title
  • all the details of all the films in the 'Comedy' genre with length greater than 1.5 hours
  • all the details of all the films in the 'Science fiction' or the 'Historical' genre


Choose the best script to evidence your learning

Look back at the scripts you have written and choose the 'best' one (you decide what makes it best). Print out this script in colour using Notepad++ and annotate it to help to explain to Zackary how the script works. You can also give this to your teacher to get it checked if you can't find Zackary.



Task 6.2 Using Python to change data in a database
Where we use Python to alter data in a database


Create a new Python script

Create a new Python script and save it with a suitable filename in the same folder as your film.db database file.

Use Python to retrieve and display data from the database

Use the 'Cheatsheet' to help you to write Python scripts to achieve the following. Remember to commit the changes to the database after you have executed the SQL statement.
  • Insert a new film into the film table ...
    - filmID : 013
    - title : A Slight Disturbance on Oak Avenue
    - genre : Horror
    - length : 2.7
  • delete the new film from the table
  • alter the title of film 011 to 'The Prisoner of the Boyfriend'


Choose the best script to evidence your learning

Look back at the scripts you have written and choose the 'best' one (you decide what makes it best). Print out this script in colour using Notepad++ and annotate it to help to explain to Zackary how the script works. You can also give this to your teacher to get it checked if you can't find Zackary.



Task 6.3 Using parameters in your queries
Where we learn how to put parameters in our queries


Try the simple example

Create a new Python script, save it in the same folder as the 'film.db' file and execute it.

import sqlite3

# Connect to database
db = sqlite3.connect('film.db')
cursor = db.cursor()

# Get parameter
title = input('Tell me the film title : ')

# Execute SQL using parameter
cursor.execute('''
SELECT * FROM film WHERE title = ?
''',[title])

# Handle results
for line in cursor:
  print(line)

# Close database connection
db.close()

Programming challenges

Now, write scripts using parameters which satisfy the following requirements. Remember that if you are retrieving data from the database you need to handle it appropriately and if you are changing data you need to commit the changes to the database before you close the connection.
  • Create a Python script using IDLE (File > New File), save it as ... Task 3.1 Challenges.py
  • Use it to record your attempt at the following challenges.
  • When you have completed as many of the challenges as you can, evidence your work through a combination of screenshots and written explanations in a word processed document with a suitable header and footer. 
  • Remember to show the script and the output it generates.
EASY
  1. Allow you to retrieve and display details of films in a specified genre.
  2. Allow you to retrieve and display details of films over a certain length.
  3. Choose a single film to delete based on its filmID.
  4. Choose a single film to delete based on its title.
MEDIUM
  1. Search for a film based on a combination of genre and length.
  2. Ask for details of a new film and add this to the database.
HARD
  1. Use multiple table queries to retrieve and display details of all the films that a given user has watched.
  2. Use multiple table queries to retrieve and display details of all the users that have watched a given film.

Choose the best script to evidence your learning

Look back at the scripts you have written and choose the 'best' one (you decide what makes it best). Print out this script in colour using Notepad++ and annotate it to help to explain to Zackary how the script works. You can also give this to your teacher to get it checked if you can't find Zackary.




There are plenty of further resources you can use to extend your learning if you want to. Investigate these two website as a starting point.
Make sure you tell your teacher what you have learnt!


Assessment Task (Homework)

You have come a long way! To help you to recap what you have learnt during this lesson, I would like you to create your own SQLite tutorial. Use examples from the lesson to help you and present the tutorial in any way you like, as a booklet, a presentation or a video / screencast.

Grading rubric

MASTER : You have created a really informative and useful resource which your teacher has shared with the class!
APPRENTICE You have used screenshots and some written explanation to cover most of the learning.
NOVICE : You have copied and pasted examples from your work and this lesson and plopped them on a presentation.

Click to download revision cards
https://docs.google.com/document/d/1jv1KC7E296A1zay1qK0BiDhLY39BPQXHVPVgeoVD6Rw/export?format=pdf
Remember to print them single sided

# Flash cards.png
Click to load key word list to help you make your own flash cards 

https://goo.gl/forms/16tuav710qmQidqC3
Try to get 5/5!


Hungry for more?

The film database that you've been using in this topic contains a list of random usernames and passwords. The passwords are generated by a tool located called XKPasswd, a really useful service inspired by this XKCD Comic ...

https://xkcd.com/936/