The leaderboard that forgets
You write a game. It keeps the scores in a list inside the program. You close the game. The scores are gone.
A program forgets everything when it stops. To keep data, the program must save it in a safe place. The usual place is a database (an organised store of data).
Today your code connects to one, and reads, adds, changes and deletes data.
Key words
| Word | What it means |
|---|---|
| Database | An organised store of data that programs can search and change. |
| Data source | Any place your program gets data from, such as a database file. |
| Connection | An open link between your program and the database. |
| Parameter | An extra setting you give when you open a connection. |
| Credentials | The username and password that prove who you are. |
| Query | A question or an instruction you send to a database, written in SQL. |
| Placeholder | A mark such as ? in a query. The real value is sent separately. |
| Commit | Save the changes you made to the database, so they stay. |
| SQL injection | An attack where a user types SQL as their input and changes your query. |
| CRUD | Create, Read, Update, Delete: the four things a program does with data. |
The cells students fill in are left blank in this preview.
Quick check: the words
Have a go
Quick check: the words
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
What a connection needs
| Part | What it is | SQLite file example | MySQL lab example |
|---|---|---|---|
| Database name or data source | Which data to open | shop.db | shop |
| Server and port | Where the database runs. A file has none | None: it is a file | db, port 3306 |
| Credentials | Username and password | None needed | student and student |
| Optional parameters | Extra settings, such as how many seconds to wait | timeout=5 (wait 5 seconds) | a time limit, or a secure connection |
The cells students fill in are left blank in this preview.
Try it: connect, create, read
import sqlite3
conn = sqlite3.connect(":memory:") # a database that lives in memory
conn.execute("CREATE TABLE players (name TEXT, points INTEGER)")
conn.execute("INSERT INTO players VALUES (?, ?)", ("Aisha", 12))
conn.commit()
for row in conn.execute("SELECT name, points FROM players"):
print(row)
conn.close()
# Add a second player and run again.
CRUD in SQL and in Python
| Job | SQL | Python call |
|---|---|---|
| Create (store) | INSERT | conn.execute("INSERT ...", values) |
| Read (extract) | SELECT | conn.execute("SELECT ...").fetchall() |
| Update | UPDATE | conn.execute("UPDATE ...", values) |
| Delete | DELETE | conn.execute("DELETE ...", values) |
The cells students fill in are left blank in this preview.
Never join user text into a query
Suppose you build a query by joining the name a user typed. A user types x' OR '1'='1. The query now says: find a player named x, or any row where 1 equals 1. 1 always equals 1, so the query returns every player.
This is SQL injection. The fix is a placeholder. The query and the value travel separately, so the value can never become part of the query. This is called a prepared statement.
Try it: unsafe and safe
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (name TEXT, secret TEXT)")
conn.execute("INSERT INTO users VALUES ('Aisha', 'cat'), ('Sam', 'dog')")
typed = "x' OR '1'='1"
unsafe = "SELECT * FROM users WHERE name = '" + typed + "'"
print("Unsafe:", conn.execute(unsafe).fetchall())
print("Safe: ", conn.execute("SELECT * FROM users WHERE name = ?", (typed,)).fetchall())