A Database and Setting Up MySQL: Tables, Keys, Docker and a Driver

In this lesson#

This lesson is built from two chapters, in the same order as the slides:

1. Chapter 1 — what a Database is: table, row, column, Primary Key and data types
2. Chapter 2 — setting up MySQL: Docker, Database Client and a Driver

Chapter 1: What a Database Is — Table, Row, Column, Primary Key and Data Types#

What will we learn?#

• Why you need a Database.
• What a Relational Database is.
• Table, Row, Column.
• Primary Key.
• Basic Data Types.

Why do we need this?#

In the files topic we saved data in a JSON file. That worked for one program running on our computer. On a real server, it breaks:

A Database solves all of these: reading and writing exactly what is needed, handling many users at the same time, protection against crashes, rules enforced in the database itself, and a language (SQL) for queries.

Explanation from the basics#

What is a Database?#

Database = a system for organized storage of data, which lets you save, find, update and delete it efficiently and reliably. The software that manages it is called a DBMS (Database Management System): MySQL, PostgreSQL, SQLite…

Relational Database — tables#

The most common kind is a relational database: the data is organized in tables, like an Excel sheet with strict rules.

Table: students
┌────┬────────┬─────┬────────────────┐
│ id │ name   │ age │ email          │   ← columns (the structure)
├────┼────────┼─────┼────────────────┤
│  1 │ Dana   │  24 │ dana@mail.com  │   ← row = one student
│  2 │ Avi    │  31 │ avi@mail.com   │   ← row
│  3 │ Noa    │  22 │ noa@mail.com   │   ← row
└────┴────────┴─────┴────────────────┘
  ↑
  primary key

Primary Key#

Primary Key = a column (usually id) whose value is:

1. Unique — no two rows have the same value.
2. Not empty — every row must have a value.
3. Not changing — an identifier is for the whole life of the row.

Why not use the name? Because there are two “Dana"s. And the email? Because people change their email. A numeric identifier that the database produces by itself is the safe choice. (Remember the Shop that produced identifiers with max + 1? The database does it for us, and more safely.)

And that is exactly the {student_id} in the API’s path: GET /students/7 = “bring the row whose Primary Key is 7”.

Data Types — basic data types#

Every column has a type. In MySQL (the database we will use) there are many types, but this is what we will need:

Basic Constraints#

Rules that the database enforces. Even if there is a bug in the code, the database will refuse:

It is like validation in __init__ (the OOP topic), only the database itself enforces it: another layer of protection.

SQL — the language#

SQL (Structured Query Language, pronounced “sequel” or “S-Q-L”) = the language in which you talk to a relational database. Query = one command in SQL. We will meet only four or five commands:

CREATE TABLE   → create the structure      (day 3)
INSERT         → C - add a row             (day 3)
SELECT         → R - read rows             (day 3)
UPDATE         → U - change rows           (day 4)
DELETE         → D - remove rows           (day 4)

Syntax#

A table design (the product of the design stage, before any code). This is what a design of the table students looks like:

Table: students
| column | type         | constraints                 |
|--------|--------------|-----------------------------|
| id     | INT          | AUTO_INCREMENT PRIMARY KEY  |
| name   | VARCHAR(100) | NOT NULL                    |
| age    | INT          | NOT NULL                    |
| email  | VARCHAR(100) | NOT NULL UNIQUE             |

And this is the SQL command that creates it (we will write and run it in the next lessons; here it is only to see where we are heading):

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

First code example#

From a Python Class to a table design. The Class Product from the OOP topic:

class Product:
    def __init__(self, sku, name, price, stock=0):
        ...

The translation:

Breaking down the code line by line#

Another example#

From dicts to a table, and “messy” types. Data that arrived from a JSON file:

students = [
    {"name": "Dana", "age": 24, "email": "dana@mail.com", "phone": "050-1234567"},
    {"name": "Avi", "age": "31", "email": "avi@mail.com"},
    {"name": "Noa", "age": 22, "email": "dana@mail.com", "phone": None},
]

Design questions:

1. phone appears only in some of the rows. → a column without NOT NULL. When there is no value, NULL is stored (None in Python).
2. Avi’s age is "31" (a string). → the column is INT. In the code we will convert int(...) before saving (or Pydantic will convert, in the students API topic).
3. Noa and Dana have the same email. → with UNIQUE on email, the database will refuse to save Noa. That is exactly what we want (and we will handle this error in the next lessons).
4. There is no id. → AUTO_INCREMENT: the database will give 1, 2, 3.

| column | type         | constraints                |
|--------|--------------|----------------------------|
| id     | INT          | AUTO_INCREMENT PRIMARY KEY |
| name   | VARCHAR(100) | NOT NULL                   |
| age    | INT          | NOT NULL                   |
| email  | VARCHAR(100) | NOT NULL UNIQUE            |
| phone  | VARCHAR(20)  | (nullable)                 |

Why is phone a VARCHAR and not an INT? Because 050-1234567 contains a hyphen, and a phone number that starts with 0 would lose the zero as an INT. Rule: a “number” you don’t do arithmetic on (a phone, a zip code, an ID number) → VARCHAR.

Common mistake#

Mistake 1: a “natural” Primary Key that may change or repeat. A name, an email, a phone number. The day Dana changes her email, everyone who pointed to her by email “loses” her. A numeric id from the database.

Mistake 2: storing a list inside one column.

| id | name | grades     |
|  1 | Dana | "90,85,70" |

You cannot calculate an average in SQL, you cannot search for “everyone who got 90”, and it is hard to update one grade. In the relational world, grades are a separate table (grades, with a student_id). You will learn this in depth later in the track (relationships between tables and JOIN). Here we will work with one table and simple fields.

Mistake 3: giving up NOT NULL and UNIQUE, “because the code checks”. The code checks today. Tomorrow someone will write an import script that bypasses the code, or there will be a bug. The database is the last line of defense.

Mistake 4: thinking SQL is “a program”. SQL is a Declarative language: you say what you want (“all the students from Haifa”), and not how to find it (no loops). The database decides how to perform it efficiently.

Summary#

• A Database solves the problems of the JSON file: focused reading and writing, many users at the same time, resistance to crashes, rules and queries.
• A relational database = tables. Table ≈ a Class + all the objects. Column ≈ an Attribute (with a type). Row ≈ one object.
• Primary Key = a unique identifier, not empty and not changing. Usually id INT AUTO_INCREMENT PRIMARY KEY.
• Types in MySQL: INT, DECIMAL, VARCHAR, and NULL (= None). A bool is stored as 0/1. A phone and a zip code are stored as VARCHAR.
• Constraints: NOT NULL, UNIQUE. The database enforces rules even when the code makes a mistake.
• SQL = a declarative language. Here: CREATE TABLE, INSERT, SELECT, UPDATE, DELETE, and that’s it.

Official documentation#

• MySQL — Creating and Using a Database
• MySQL — Data Types
• MySQL — CREATE TABLE

Chapter 2: Setting Up MySQL — Docker, Database Client and a Driver#

What will we learn?#

• What a database server is, and why we will work with MySQL.
• What a container is, and how to run MySQL in Docker — with one command.
• How to see the database from VS Code with the Database Client extension.
• What a Driver is, and how Python connects to MySQL.
• Where to keep the connection details — and why not in the code.

Why do we need this?#

Before writing a single INSERT, you need to answer “plumbing” questions: where does the database run? How does our code get to it? What are the connection details? In real work, the first thing you get in a new project is exactly this: “here are the connection details for the database”. You need to know what to do with them.

Explanation from the basics#

A database is a server#

MySQL is a database server: a separate program (a process — remember the operating system topic!) that runs all the time and listens on a port — in MySQL that is port 3306. Our code does not open a file — it connects over the network to the server, exactly like a browser that connects to a website (the networking topic: IP + Port).

Why Docker?#

Installing MySQL directly on the computer is a long process: an installation wizard, passwords, a service that runs in the background, and settings that differ between Windows and Mac. And what works for one person — does not always work for another.

Docker (which we installed in the first week) solves this: it runs ready-made programs inside a container.

• Image — the template: “MySQL version 8.4, ready to work”. You download it once.
• Container — a running copy of the Image. Isolated from the rest of the computer, with everything the program needs.

Database Client — the database with an interface#

You can see and operate MySQL without a single line of code, with a VS Code extension named Database Client (by Weijan Chen). It shows a tree of databases and tables, opens a table like a spreadsheet, and runs SQL and shows the result as a table. We will use it throughout these topics to see what our code did in the database.

Driver — the interpreter#

Our code is written in Python. MySQL speaks in a “protocol” of its own (the networking topic: protocol = communication rules). You need a middleman who translates: a Driver (also called a Database Connector) = a library that knows how to:

1. Connect to a database server of a certain kind.
2. Send SQL commands to it.
3. Return the results as Python data (tuples, int, str).

For MySQL we will use the official Driver: mysql-connector-python. You install it with pip, and in the code you write import mysql.connector.

The connection details — and not in the code!#

To connect to MySQL you need five details:

In a real project there is a real password here. If it is written in the code and the code went up to GitHub, it has leaked (remember secrets from the Git topic!). The professional practice:

• The connection details are kept in Environment Variables, or in a .env file that is in .gitignore.
• The code reads them: os.environ.get("DB_PASSWORD").

Syntax#

Running MySQL in a container — one command, once (in PowerShell; on Mac replace the ` with \):

docker run --name kodkod-mysql `
  -e MYSQL_ROOT_PASSWORD=kodkod `
  -e MYSQL_DATABASE=school `
  -p 3306:3306 -d mysql:8.4

Useful commands:

docker ps                     # which containers are running now
docker start kodkod-mysql     # start it again (after a restart of the computer)
docker stop kodkod-mysql      # stop it (the data is kept)

Connecting from Python:

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    port=3306,
    user="root",
    password="kodkod",
    database="school",
)
connection.close()

• mysql.connector.connect(...) — opens a network connection to the server. If the server is not running — an error.
• port=3306 is the default, and you can leave it out.

First code example#

Setting up the database for these topics, step by step.

1. Run MySQL (Docker Desktop needs to be open):

docker run --name kodkod-mysql `
  -e MYSQL_ROOT_PASSWORD=kodkod `
  -e MYSQL_DATABASE=school `
  -p 3306:3306 -d mysql:8.4
docker ps

On the first run Docker downloads the Image (a few minutes). docker ps shows that the container is running:

CONTAINER ID   IMAGE       STATUS         PORTS                    NAMES
ffaa661f5047   mysql:8.4   Up 10 seconds  0.0.0.0:3306->3306/tcp   kodkod-mysql

2. Connect from VS Code:

1. The Extensions tab → search for Database Client (by Weijan Chen) → Install.
2. A database icon appears in the side bar. Click it → + (Create Connection) → choose MySQL.
3. Fill in: Host 127.0.0.1, Port 3306, Username root, Password kodkod, Database school → Connect.
4. The connection appears in the tree, and inside it the database school — still without tables.

3. A Python project:

mkdir ~\dev\kodkod6\week-4\school-db
cd ~\dev\kodkod6\week-4\school-db
git init
python -m venv .venv
.venv\Scripts\Activate.ps1
pip install mysql-connector-python

.gitignore:

__pycache__/
.venv/
.env

setup_db.py:

import os

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    port=3306,
    user="root",
    password=os.environ.get("DB_PASSWORD", "kodkod"),
    database="school",
)
print("Connected:", connection.is_connected())
print("Server version:", connection.server_info)
connection.close()
print("Connected after close:", connection.is_connected())

Output:

Connected: True
Server version: 8.4.11
Connected after close: False

Breaking down the code line by line#

Another example#

The container after restarting the computer#

After a Restart, Docker Desktop comes up — but the container is not running. Run docker run again? No — the name kodkod-mysql is already taken:

docker: Error response from daemon: Conflict. The container name "/kodkod-mysql" is already in use by container ...

The container exists, it is only stopped. Start it:

docker start kodkod-mysql

All the data was kept — the tables and the rows stay in the container until you delete it (docker rm).

When the server is not running#

If Docker Desktop is closed or the container is stopped, the connection from Python fails:

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on 'localhost:3306' (Errno 10061: No connection could be made because the target machine actively refused it)

Can't connect = nobody is listening on port 3306. Check with docker ps, and start it with docker start kodkod-mysql.

Common mistake#

Mistake 1: Docker Desktop is not open.

failed to connect to the docker API ... The system cannot find the file specified.

docker commands talk to Docker Desktop. Open it, wait for it to finish starting, and try again.

Mistake 2: the port is taken.

Bind for 0.0.0.0:3306 failed: port is already allocated

Something is already running on port 3306 — usually a MySQL that was installed in the past directly on the computer. You can use another port on our computer: -p 3307:3306, and then connect with port=3307.

Mistake 3: a database password in code that goes up to GitHub.

connection = mysql.connector.connect(host="prod-db.company.com", user="admin", password="MyRealPassword123")

Even if you delete it in the next Commit, the password stays in the history (remember the Git topic!). Environment variables / .env in .gitignore. (Our password kodkod is for a local learning environment only.)

Summary#

• MySQL = a database server: a process that listens on port 3306, and you connect to it over the network.
• Docker runs MySQL inside a container — with one command, and the same for everyone. docker run once, and after that docker start kodkod-mysql.
• Database Client (a VS Code extension) = an interface to the database: a tree of tables, running SQL and results as a table.
• Driver = a library that translates between Python and the database. For MySQL: mysql-connector-python (import mysql.connector).
• Five connection details: host, port, user, password, database.
• Passwords are not written in code: environment variables or .env in .gitignore.

Official documentation#

• Docker Hub — MySQL official image
• Database Client for VS Code
• Connector/Python — Installation

Practice#

The exercises use only what was taught in this lesson and the lessons before it. Try on your own first, and only then open the solution.

Exercise 1: Table, row, column#

id | name | age | email
---+------+-----+---------------
 1 | Dana |  24 | dana@mail.com
 2 | Avi  |  31 | avi@mail.com

1. How many rows and how many columns are there in the table?
2. What does each row describe, and what does each column describe?
3. Which column is suitable to be the Primary Key, and why not name?

Exercise 2: Types and constraints#

Write the SQL command that creates a table students with:

• id — a whole number, a Primary Key, created automatically;
• name — text, required;
• age — a whole number, required;
• email — text, required and unique.

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

Exercise 3: What would the database catch?#

students = [
    {"name": "Dana", "age": 24, "email": "d@x.com"},
    {"name": "Avi", "age": 24, "email": None},
    {"name": "Noa", "age": 22, "email": "d@x.com"},
]

Which of this data would the students table from the previous exercise reject, and because of which constraint? And why did nobody stop them in the Python list?

Exercise 4: Starting MySQL#

1. What does each part of the following command do?
2. How do you check that the container is running?
3. How do you start it again after restarting the computer — without creating a new one?

docker run --name kodkod-mysql -e MYSQL_ROOT_PASSWORD=kodkod -e MYSQL_DATABASE=school -p 3306:3306 -d mysql:8.4

Exercise 5: A first connection#

Write code that connects to the database school in the container, prints whether the connection is active, closes it, and prints again.

Expected output:

Connected: True
Connected after close: False

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    port=3306,
    user="root",
    password="kodkod",
    database="school",
)
print("Connected:", connection.is_connected())
connection.close()
print("Connected after close:", connection.is_connected())

Connected: True
Connected after close: False