Block 1 · Exercise 10

Python Database Access

Starter notebook: 10-database-access-starter
Fabric path: /lakehouse/default/Files/data/solutions/10.database-access/

Open the notebook 10-database-access-starter in your workspace and run its first cell. It copies /lakehouse/default/Files/data/solutions/10.database-access/starter/ to the notebook's own disk, so the calcu package imports next to the calculator. Run the calculator cell once : it prints the five calculations from a text file. Each step has a comment in that cell where your code goes.

Do one part at a time, 1a, 1b and so on, and compare with the expected output before you go on. The solution is in 10-database-access-solution, under every part on the exercise site, and at the back of the exercises PDF.

Step 1Table

The calculator cell keeps its five calculations in the list records, one tuple of kind, two numbers and result. Replace the text file with a SQLite table.

1a

Done

Delete the two open() blocks. Add import sqlite3 at the top of the cell and open a connection to calculations.db in a variable con. Print con.

Hint 1

The slide DB API Concepts shows sqlite3.connect. A SQLite database is one file, and the file is made when you connect.

Check your output

<sqlite3.Connection object at 0x...>

The address varies.

Show solutionHide solution

python

import sqlite3

con = sqlite3.connect("calculations.db")
print(con)

1b

Done

Create a table Calculations with four columns : Id for the kind, as text, Nr1 and Nr2 for the two numbers and Result, all three as real numbers. Drop the table first when it exists, so that the cell can run again. Do it in a with con: block. Then print the table names from sqlite_master.

Hint 1

The slide Using with says what with con: does when the block ends.

Hint 2

DROP TABLE IF EXISTS does not fail on the first run, when there is no table yet.

Hint 3

SELECT name FROM sqlite_master lists the tables of the database. fetchall() gives them as a list of tuples.

Check your output

[('Calculations',)]

Show solutionHide solution

python

with con:
    con.execute("DROP TABLE IF EXISTS Calculations")
    con.execute("CREATE TABLE Calculations (Id TEXT, Nr1 REAL, Nr2 REAL, Result REAL)")

print(con.execute("SELECT name FROM sqlite_master").fetchall())

1c

Done

Write a comment that says why the table is dropped and created again on every run, and what you would change to keep the history of earlier runs.

Show solutionHide solution

python

# DROP TABLE throws away the rows of every earlier run. Without the DROP, and with
# CREATE TABLE IF NOT EXISTS, the rows of earlier runs stay.

Step 2Insert

2a

Done

Insert the first record of records into the table, in a with con: block. Use ? placeholders for the four values, and pass the tuple as it is. Print con.execute("SELECT count(*) FROM Calculations").fetchone().

Hint 1

The slide Parameterized Queries shows a placeholder for every value. The number of ? marks has to match the number of columns.

Hint 2

execute takes the tuple of values as its second argument.

Check your output

(1,)

Show solutionHide solution

python

with con:
    con.execute(
        "INSERT INTO Calculations (Id, Nr1, Nr2, Result) VALUES (?, ?, ?, ?)",
        records[0],
    )

print(con.execute("SELECT count(*) FROM Calculations").fetchone())

2b

Done

Insert every record, with a loop, in one with con: block. Run the whole cell again, so that the table is dropped and made new first. Print the row count again.

Hint 1

A for loop over records inside the with block. The tuple of each round goes to execute as in 2a.

Check your output

(5,)

Show solutionHide solution

python

insert_sql = "INSERT INTO Calculations (Id, Nr1, Nr2, Result) VALUES (?, ?, ?, ?)"
with con:
    for record in records:
        con.execute(insert_sql, record)

print(con.execute("SELECT count(*) FROM Calculations").fetchone())

2c

Done

Write a comment that says why the SQL text uses ? placeholders and not an f-string with the values in it.

Hint 1

The slide Parameterized Queries calls quoting values into the SQL a discouraged option.

Show solutionHide solution

python

# The ? placeholders pass the values apart from the SQL text, so a value can never change the query.

Step 3Rollback

3a

Done

Insert two more calculations in one with con: block, each with the same ? insert : Add of 1.0 and 1.0, and Divide of 1.0 and 0.0. Take the results from your add and divide. Put the block in a try and print rolled back : and the error when ZeroDivisionError is raised.

Hint 1

The arguments of execute are worked out before execute runs. Look at which of the two calls fails, and at what the other call has already done.

Check your output

rolled back : float division by zero

Show solutionHide solution

python

try:
    with con:
        con.execute(insert_sql, ("Add", 1.0, 1.0, add(1.0, 1.0)))
        con.execute(insert_sql, ("Divide", 1.0, 0.0, divide(1.0, 0.0)))
except ZeroDivisionError as error:
    print("rolled back :", error)

3b

Done

Print the row count again. The Add insert ran before the division failed. Is its row in the table? Write a comment that says why, or why not. Then close the connection with con.close().

Hint 1

The slide Transactions says what a transaction does when one of its statements fails. Which block is the transaction here?

Check your output

(5,)

Show solutionHide solution

python

print(con.execute("SELECT count(*) FROM Calculations").fetchone())
# The Add row is not in the table : the with block is one transaction, and it rolled back as a whole.
con.close()

Step 4Read back

4a

Done

Run the second cell of the starter. Connect to calculations.db again and print every row of Calculations. Close the connection.

Hint 1

The slide Retrieving Data shows a query and a loop over the rows. A connection can execute a query directly, and the result can be looped over.

Check your output

('Add', 3.0, 4.0, 7.0)
('Subtract', 10.0, 2.5, 7.5)
('Multiply', 6.0, 7.0, 42.0)
('Divide', 1.0, 8.0, 0.125)
('Faculty', 5.0, 0.0, 120.0)

Show solutionHide solution

python

con = sqlite3.connect("calculations.db")
for row in con.execute("SELECT * FROM Calculations"):
    print(row)
con.close()

4b

Done

Ask the database a question instead of printing everything : for every kind, how many calculations are there and what is the largest result? Use pd.read_sql with a GROUP BY and print the result. The connection can sit in a with block.

Hint 1

pd.read_sql(sql, con) returns a DataFrame. It is the SQL you would write in any other database, with count(*) and max(Result) under an alias.

Check your output

         Id  n  largest
0       Add  1    7.000
1    Divide  1    0.125
2   Faculty  1  120.000
3  Multiply  1   42.000
4  Subtract  1    7.500

Show solutionHide solution

python

with sqlite3.connect("calculations.db") as con:
    print(pd.read_sql(
        "SELECT Id, count(*) AS n, max(Result) AS largest FROM Calculations GROUP BY Id", con
    ))

If time permits Done

Give the table an Id INTEGER PRIMARY KEY column and rename the kind column to Kind. Which inserts have to change?
Add a created column that SQLite fills itself, with DEFAULT CURRENT_TIMESTAMP, and print the rows ordered by it.
Set con.row_factory = sqlite3.Row before the SELECT, and read each value by column name instead of by position. The slide Dictionary Cursor shows how.
Trainer demonstration : the interactive calculator writes every calculation it is asked for to the same table. It runs as a program in a terminal, so read it with from pathlib import Path and print(Path("/lakehouse/default/Files/data/solutions/10.database-access/interactive/calculator10.py").read_text()).