GCSE PYTHON

USING DATABASES
Skills learnt:
1. Executing SQL statements from the Python interface.
2. Importing sqlite3 module and creating a table in a database.
3. Inserting records to a table and displaying them.
4. Updating and deleting records.

A. Importing sqlite3 module and creating a table in a database.

i. In the blank trinket given, import the sqlite3 module. Create a connection to
connect to [Link].
<blank trinket>
Answer:
import sqlite3
connection=[Link]("[Link]")

ii. In the same trinket file, create a cursor object to interact with [Link].

Answer:
cur=[Link]()

iii. In the same trinket file, create a table named bookDetails with 6 fields:
● ISBN: text
● title: text
● author: text
● datePublished: text
● numOfPages: integer
● price: real
Also, assign an appropriate primary key to this table.

Answer:
[Link]('CREATE TABLE bookDetails (ISBN TEXT NOT NULL PRIMARY KEY,
title TEXT, author TEXT, datePublished TEXT, numOfPages INTEGER, price
REAL)')
B. Inserting records to table bookDetails and displaying records.
i. We will be using the trinket file which we used for activity A. Insert the
records given below.

● 9780141994000, Silent Spring, Rachel Carson, 26/03/2020, 336, 9.99

● 9781448130726, H is for Hawk, Helen Macdonald, 31/07/2014, 320, 9.99

Answer:
[Link]('INSERT INTO bookDetails (ISBN, title, author, datePublished,
numOfPages, price) VALUES (?,?,?,?,?,?)', ('9780141994000', 'Silent
Spring', 'Rachel Carson', '26/03/2020', 336, 9.99))
[Link]('INSERT INTO bookDetails (ISBN, title, author, datePublished,
numOfPages, price) VALUES (?,?,?,?,?,?)', ('9781448130726', 'H is for
Hawk', 'Helen Macdonald', '31/07/2014', 320, 9.99))

ii. In the same trinket file, copy the code given below to insert a few more
records.

[Link]('INSERT INTO bookDetails (ISBN, title, author, datePublished,

numOfPages, price) VALUES (?,?,?,?,?,?)', ('9780241146422', 'Underland',
'Robert Macfarlane', '02/05/2019', 496, 10.99))
[Link]('INSERT INTO bookDetails (ISBN, title, author, datePublished,
numOfPages, price) VALUES (?,?,?,?,?,?)', ('9780141989631', 'The Grassling',
'Elizabeth-Jane Burnett', '28/03/2019', 208, 9.99))
[Link]('INSERT INTO bookDetails (ISBN, title, author, datePublished,

iii. Using the SELECT statement, write codes to display all the fields of the
records in the bookDetails table.

Answer:
[Link]('SELECT * FROM bookDetails')
for row in cur:
print(row)

C. Filtering and ordering records.

i. We will continue our codes in the same trinket file. Write a statement to
display the title and author of books with a price less than 10.

Answer:
[Link]('SELECT title,author FROM bookDetails WHERE price<10')
for row in cur:
 print(row)

ii. Remove the statement inserted in (i). Include a statement to print the ISBN,
title, author and datePublished of books in ascending order of title.

Answer:
[Link]('SELECT ISBN, title, author, datePublished FROM bookDetails
ORDER BY title')
for row in cur:
print(row)

iii. Remove the statement inserted in (ii). Include a statement to print the title,
author and datePublished of books with a price greater than 10 in descending
order of author.

Answer:
[Link]('SELECT title, author, datePublished FROM bookDetails WHERE
price>10 ORDER BY author Desc')
for row in cur:
print(row)

iv. Remove the statement inserted in (iii). Include a statement to print the title,
author and price of books with the author starting with R.

Answer:
[Link]('SELECT title, author, price FROM bookDetails WHERE author
LIKE "R%"')
for row in cur:
print(row)

D. Updating and deleting records.

i. We will continue our codes in the same trinket file. Write a statement to
update the title to “The edge of the sea” and datePublished to “01/07/1999”
where author=“Rachel Carson”

Answer:
 [Link]('UPDATE bookDetails SET title="The Edge of the Sea",
datePublished="01/07/1999" WHERE author="Rachel Carson"')

ii. Delete the record from the table with ISBN 9780241333945.

Answer:
[Link]('DELETE FROM bookDetails WHERE ISBN="9780241333945"')

iii. Display the records and check whether the operations in (i) and (ii) were
successful.

E. Insert the user entered records to the table.

i. A database named [Link] contains a table named tempTable that
consists of the temperature data of various countries in the format as given
below.

Maximum Minimum Average

Country
temperature temperature temperature

Afghanista
40 20 32
n

Albania 38 28 35

In the blank trinket given below, create a program that prompts the user to
enter fields for each record.

Answer:
import sqlite3
connection=[Link]("[Link]")
cur=[Link]()
[Link]('CREATE TABLE tempTable (country TEXT NOT NULL PRIMARY
KEY, maxTemp INTEGER, minTemp INTEGER, avgTemp INTEGER)')
myRec=[]
country=input("Enter country:")
while (country!="done"):
maxTemp=int(input("Enter maximum temperature:"))
minTemp=int(input("Enter minimum temperature:"))
avgTemp=int(input("Enter average temperature:"))
[Link](country)
[Link](maxTemp)
[Link](minTemp)
 [Link](avgTemp)
[Link]('INSERT INTO tempTable VALUES (?,?,?,?)',myRec)
[Link]()
myRec=[]
country=input("Enter city: (Enter done to exit)")
[Link]()

ii. Include statements to print the records of the table. Check your program
with sample values.

Answer:
import sqlite3
connection=[Link]("[Link]")
cur=[Link]()
[Link]('CREATE TABLE tempTable (country TEXT NOT NULL PRIMARY
KEY, maxTemp INTEGER, minTemp INTEGER, avgTemp INTEGER)')
myRec=[]
country=input("Enter country:")
while (country!="done"):
maxTemp=int(input("Enter maximum temperature:"))
minTemp=int(input("Enter minimum temperature:"))
avgTemp=int(input("Enter average temperature:"))
[Link](country)
[Link](maxTemp)
[Link](minTemp)
[Link](avgTemp)
[Link]('INSERT INTO tempTable VALUES (?,?,?,?)',myRec)
[Link]()
myRec=[]
country=input("Enter city: (Enter done to exit)")
[Link]("SELECT * FROM tempTable")
for row in cur:
print(row)
[Link]()