# Connecting, a Table and Inserting Data: Connection, CREATE TABLE and INSERT<no value>

## In this lesson

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

1. **Chapter 1** — opening a Connection, checking that the connection works and closing it properly
2. **Chapter 2** — basic SQL: CREATE TABLE and INSERT

## Chapter 1: Opening a Connection, Checking That the Connection Works and Closing It Properly

### What will we learn?

The **life cycle** of all work with a database:

```text
1. open a connection  →  2. create a cursor  →  3. run a query  →  4. read the result  →  5. close
```

- **Connection** and **Cursor**: what each one is.
- **A check query** (Health Check): "does the database answer at all?"
- **Closing properly**, even when there is an error.
- **`get_connection()`** — one function that the whole project will use.

### Why do we need this?

Every CRUD function and every Endpoint in the server (the students API topic) starts by opening a connection and ends by closing it. **A connection that is not closed** is a classic bug: every connection to MySQL takes resources — both on our side and on the server — and MySQL has a **limited number** of connections at once. On a server that handles thousands of requests, after a while the connections run out (`Too many connections`) and everything gets stuck. It is exactly like open files in the files lesson, only at the scale of a server.

### Explanation from the basics

#### Connection — the open line

**Connection** = an object that represents **an open channel** between our code and the MySQL server. Through it you create a Cursor, through it you confirm changes (`commit`, later in this topic), and through it you close. As long as it is open, it takes resources.

#### Cursor

**Cursor** = an object that runs a query **and holds its results**, and lets you read them row by row. Imagine it as a finger that goes over the query's results.

You create it from the Connection, and run through it:

```python
cursor = connection.cursor()        # create a cursor
cursor.execute("SELECT 1")          # run a query through it
```

#### Reading results

| Cursor method | Returns | When |
|---|---|---|
| `fetchone()` | **One row** (a tuple), or `None` if there is none | You expect one row: a check, or a search by id |
| `fetchall()` | **A list** of all the rows (a list of tuples) | You expect several rows |

Every row is a **tuple** (remember tuples!): `("Dana", 24)`. (Later in this topic we will learn to get rows as a dict.)

{{< box "analogy" "Analogy: a phone call to a call center" >}}
**Connection** = the call itself: you dialed, and the line is open. **Cursor** = the representative who looks up information for you and reads you result after result. **`fetchone`** = "tell me the first one". **`fetchall`** = "read me all of them". **`close`** = hanging up. If you don't hang up, the line stays busy, and others cannot call.
{{< /box >}}

#### A check query (Health Check)

To check that the connection **really works** (and not only that the object was created), you run a simple query that does not depend on any table:

```sql
SELECT 1
SELECT VERSION()
```

If an answer came back, the database is alive and answering. That is exactly how the `/health` Endpoint of real servers works (the backend and FastAPI topic).

#### Closing properly — try/finally

What happens if the query fails **after** we opened a connection?

```python
connection = get_connection()
cursor = connection.cursor()
cursor.execute("SELEC 1")          # typo -> exception
connection.close()                 # never reached!
```

The error stops the function, and `close` does not run. The solution we learned in the exceptions topic: **`finally`**, which **always** runs:

```python
connection = get_connection()
try:
    ...                            # work with the database
finally:
    connection.close()             # runs even if an exception happened
```

### Syntax

```python
import mysql.connector

def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")

connection = get_connection()
try:
    cursor = connection.cursor()     # create a cursor
    cursor.execute("SELECT 1")       # run a query
    row = cursor.fetchone()          # read one row -> (1,)
finally:
    connection.close()               # always close
```

### First code example

`week-4/school-db/check_connection.py`:

```python
import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute("SELECT VERSION()")
    row = cursor.fetchone()
    print("Row returned:", row)
    print("MySQL version:", row[0])

    cursor.execute("SELECT 1 + 1")
    print("The database can calculate: 1 + 1 =", cursor.fetchone()[0])
    print("Connection works!")
finally:
    connection.close()
    print("Connection closed.")
```

**Output:**

```text
Row returned: ('8.4.11',)
MySQL version: 8.4.11
The database can calculate: 1 + 1 = 2
Connection works!
Connection closed.
```

### Breaking down the code line by line

| Code | Explanation |
|---|---|
| `mysql.connector.connect(...)` | **Opening**. You get a Connection object (the OOP topic: a Class and an object!). |
| `try:` | From here, any error will still reach the `finally`. |
| `cursor = connection.cursor()` | Creates a **Cursor** — through it you run queries. |
| `cursor.execute("SELECT VERSION()")` | **Runs a query**. The result is kept in the Cursor. |
| `cursor.fetchone()` | One row: a **tuple** with one column: `('8.4.11',)`. The comma = a tuple of one item (remember tuples!). |
| `row[0]` | The first column in the row. |
| `cursor.fetchone()[0]` | The first row → the first column. Common for queries that return a single value. |
| `finally: connection.close()` | **Closing**, always. Even if one of the queries fails. |

### Another example

**An error in the middle, and proof that `finally` closes.** And also: what happens when you try to use a closed connection.

```python
import mysql.connector

def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")

def run_check(sql):
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute(sql)
        value = cursor.fetchone()[0]
        print("OK:", sql, "->", value)
    except mysql.connector.Error as error:
        print("Query failed:", sql, "|", type(error).__name__ + ":", error)
    finally:
        connection.close()
        print("  (connection closed)")
    return connection

run_check("SELECT 40 + 2")
closed = run_check("SELEC 1")

try:
    closed.cursor()
except mysql.connector.Error as error:
    print("Using a closed connection:", error)
```

**Output:**

```text
OK: SELECT 40 + 2 -> 42
  (connection closed)
Query failed: SELEC 1 | ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELEC 1' at line 1
  (connection closed)
Using a closed connection: MySQL Connection not available
```

**Breakdown:**

- `mysql.connector.Error` — **the base class** of all the Driver's errors (the OOP topic: a hierarchy of Exceptions!). `ProgrammingError` (an error in the SQL), `IntegrityError` (a constraint violation, later) and `OperationalError` (a problem with the connection) are its subclasses.
- In the query with a typo (`SELEC`), the error was caught, **and `finally` still closed the connection**.
- `1064` is MySQL's **error number** for a syntax error, and `near 'SELEC 1'` says exactly where the problem is. It is an **SQL Syntax Error** (the same idea as in Python, a different language).
- Using the connection after `close` → `MySQL Connection not available`. The connection is "dead", and you need to open a new one.

#### `get_connection` — one point of connection

In a real project, **every** function needs a connection. Instead of writing the five connection details in dozens of places, you write **one function** (DRY). This will be the basis of the file `db.py` throughout these topics:

```python
import mysql.connector

def get_connection():
    return mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")

def check_database():
    connection = get_connection()
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT VERSION()")
        return {"status": "ok", "mysql_version": cursor.fetchone()[0]}
    except mysql.connector.Error as error:
        return {"status": "error", "message": str(error)}
    finally:
        connection.close()

if __name__ == "__main__":
    print(check_database())
```

```text
{'status': 'ok', 'mysql_version': '8.4.11'}
```

`check_database` returns a **dict**. In the students API topic you can connect it to an Endpoint `GET /health`, and FastAPI will turn it into JSON.

### Common mistake

**Mistake 1: `close` without `finally`.** It works on the "happy path", but every error in the middle leaves a connection open. On a server, after a few hundred failed requests, MySQL refuses new connections: `Too many connections`.

**Mistake 2: forgetting `[0]`.**

```python
cursor.execute("SELECT VERSION()")
version = cursor.fetchone()
print("Version: " + version)
```

```text
TypeError: can only concatenate str (not "tuple") to str
```

`fetchone()` returns a **row** (a tuple), even when it has one column. The value itself = `[0]`.

**Mistake 3: `fetchone()` when there are no results.** `fetchone()` returns **`None`** if there is no row, and then `None[0]` → `TypeError: 'NoneType' object is not subscriptable`. Later in this topic (searching by id) we will always check `if row is None`.

**Mistake 4: a new query before all the results were read.**

```python
cursor.execute("SELECT id FROM students")    # 3 rows
first = cursor.fetchone()                    # read only the first one
cursor.execute("SELECT 1")
```

```text
mysql.connector.errors.InternalError: Unread result found
```

In MySQL, the results arrive from the server **gradually**, and a new query cannot go out before the previous one was read to the end. **When you expect several rows — `fetchall()`. When you need one row — filter with `WHERE` so that one row comes back.**

### Summary

- The cycle: **opening (`connect`) → a Cursor (`cursor()`) → a query (`execute`) → reading (`fetchone`/`fetchall`) → closing (`close`)**.
- **Connection** = the channel to the database server. **Cursor** = runs a query and holds results.
- `fetchone()` = one row (a tuple) or `None`. `fetchall()` = a list of tuples. A single value: `cursor.fetchone()[0]`.
- **A check query**: `SELECT 1` / `SELECT VERSION()`. The basis for `/health`.
- **Always `try/finally` + `close()`.**
- **One `get_connection()`** for the whole project (DRY). Errors: `mysql.connector.Error` and its subclasses.

<!-- docs:start — generated by scripts/intro6/site_docs.py; edit the links there -->

### Official documentation

- [Connector/Python — Connecting to MySQL](https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html)
- [MySQLConnection.is_connected()](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-is-connected.html)
- [MySQLConnection.close()](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-close.html)

<!-- docs:end -->

## Chapter 2: Basic SQL — CREATE TABLE and INSERT

### What will we learn?

The first two commands in SQL:

| Command | What it does | CRUD |
|---|---|---|
| **`CREATE TABLE`** | Creates a **structure**: a table with columns | — (preparation) |
| **`INSERT INTO`** | Adds a **row** | **C**reate |

In this lesson we focus on **SQL itself**, and run it in the **Database Client** in VS Code. At the end we will also see how to run the same SQL from Python. (In the next lessons we will learn to work from Python **properly** — parameters, commit and results.)

### Why do we need this?

The database `school` runs in the container — but it is **empty**. A table is the "drawer" where the data will sit, and `INSERT` is the first action in every system: registering a user, adding a product, placing an order. In the API we will build in the students API topic, `POST /students` = `INSERT INTO students`.

### Explanation from the basics

#### The structure of an SQL command

- **Keywords** like `CREATE`, `TABLE`, `INSERT`, `INTO`, `VALUES` are written, by convention, **in capital letters**, to tell them apart from names. (SQL is not case-sensitive for keywords, but it is more readable.)
- **Names** of tables and columns: `snake_case`, in small letters, and usually in the plural for tables (`students`).
- **Strings** in SQL are written in **single quotes**: `'Dana'`.
- A command ends with `;` (required when you run several commands in a row, and always recommended).

#### CREATE TABLE

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

Every line in the brackets = **a column**: `<name> <TYPE> <constraints>`, separated by commas. (No comma after the last column!)

#### IF NOT EXISTS

`CREATE TABLE students` **fails** if the table already exists. On a server that starts again every day, you need a command that is safe to run **again**:

```sql
CREATE TABLE IF NOT EXISTS students (...);
```

"Create the table **only if** it does not exist yet". If it exists, nothing happens, and without an error. (Notice: if the table exists **with a different structure**, it will **not** change.)

#### INSERT INTO

```sql
INSERT INTO students (name, age, email)
VALUES ('Dana', 24, 'dana@mail.com');
```

- **`INSERT INTO <table>`** — into which table.
- **`(name, age, email)`** — **which columns** you fill, and in which order.
- **`VALUES (...)`** — the values, **in exactly the same order**.
- **`id` is not in the list** — `AUTO_INCREMENT` fills it by itself.

You can add several rows in one command:

```sql
INSERT INTO students (name, age, email) VALUES
    ('Avi', 31, 'avi@mail.com'),
    ('Noa', 22, 'noa@mail.com');
```

{{< box "analogy" "Analogy: a registration form" >}}
`CREATE TABLE` = printing **the empty form**: which fields there are, and which are required. `INSERT` = someone **fills in one form** and submits it. The office (the database) gives a file number (`id`) by itself, and returns a form that is missing a required field.
{{< /box >}}

### Syntax

```sql
CREATE TABLE IF NOT EXISTS table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column_a VARCHAR(100) NOT NULL,
    column_b INT NOT NULL,
    column_c VARCHAR(100) UNIQUE
);

INSERT INTO table_name (column_a, column_b, column_c)
VALUES ('text value', 42, 'unique value');
```

**How to run SQL in Database Client:**

1. Create a new file with the extension `.sql`, for example `school.sql`.
2. At the top of the file, choose **the connection** and the database **`school`** (or: right-click `school` in the tree → **Open Query**).
3. Write the commands, select them and click **Run** (or `Ctrl+Enter`).
4. In the tree on the side, click **Refresh** — and the table appears. Clicking it opens the rows like a spreadsheet.

### First code example

`school.sql`:

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

SHOW TABLES;
```

Run it in Database Client. The result of `SHOW TABLES`:

```text
Tables_in_school
----------------
students
```

The table exists! And in the tree on the side, `students` appears with its four columns.

### Breaking down the code line by line

| Line | Explanation |
|---|---|
| `CREATE TABLE IF NOT EXISTS students (` | Create a table named `students`, if there is none yet. |
| `id INT AUTO_INCREMENT PRIMARY KEY,` | An identifier: a whole number, which the database fills by itself (1, 2, 3...), the primary key. MySQL **does not reuse** the number of a deleted row. |
| `name VARCHAR(100) NOT NULL,` | Text of up to 100 characters, required. |
| `age INT NOT NULL,` | A whole number, required. |
| `email VARCHAR(100) NOT NULL UNIQUE` | Text, required, unique. **No comma**, because this is the last column. |
| `);` | The end of the command. |
| `SHOW TABLES;` | A MySQL command: which tables there are in the current database. |

Run the same command again: there is no error. Thanks to `IF NOT EXISTS`.

### Another example

**Adding rows, and what happens when you break constraints.** Add to `school.sql` and run:

```sql
INSERT INTO students (name, age, email) VALUES ('Dana', 24, 'dana@mail.com');

INSERT INTO students (name, age, email) VALUES
    ('Avi', 31, 'avi@mail.com'),
    ('Noa', 22, 'noa@mail.com');

SELECT * FROM students;
```

```text
id | name | age | email
---+------+-----+--------------
 1 | Dana |  24 | dana@mail.com
 2 | Avi  |  31 | avi@mail.com
 3 | Noa  |  22 | noa@mail.com
```

**The database gave the id by itself**: 1, 2, 3. (`SELECT *` = "show everything" — in the next lesson we will learn it in depth.)

**Now let's break constraints** — run **each command separately**:

```sql
INSERT INTO students (name, age, email) VALUES ('Tom', 27, 'dana@mail.com');
```

```text
1062 (23000): Duplicate entry 'dana@mail.com' for key 'students.email'
```

```sql
INSERT INTO students (name, email) VALUES ('Rina', 'rina@mail.com');
```

```text
1364 (HY000): Field 'age' doesn't have a default value
```

```sql
INSERT INTO students (name, age, email) VALUES ('Ron', 30);
```

```text
1136 (21S01): Column count doesn't match value count at row 1
```

We asked for 3 columns, but gave only 2 values. The number of values must be identical to the number of columns.

**Breakdown:**

- **`Duplicate entry ... for key 'students.email'`** — Dana's email already exists. **The database refused**, and the row was **not** added.
- **`Field 'age' doesn't have a default value`** — we did not fill `age`, and it is required (`NOT NULL`, without a default).
- The number at the start of every message (`1062`, `1364`, `1136`) is MySQL's **error code** — you can search for it on the internet.
- From Python, a `UNIQUE` violation arrives as an **`IntegrityError`** (Integrity = the soundness of the data). We will catch it later in this topic, and return **409** from the API in the students API topic.
- **The data stayed valid.** That is the power of constraints: even if there is a bug in the code, the database keeps the rules.

#### The same SQL — from Python

On a server we will not run SQL by hand — **the code** will run it. This is what the same `CREATE TABLE` and `INSERT` look like from Python:

```python
import mysql.connector

CREATE_STUDENTS = """
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
)
"""

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute(CREATE_STUDENTS)
    cursor.execute(
        "INSERT INTO students (name, age, email) VALUES ('Tom', 27, 'tom@mail.com')")
    connection.commit()
    print("Inserted Tom")
finally:
    connection.close()
```

- `"""..."""` — a string over several lines, convenient for long SQL.
- `cursor.execute(...)` runs **one command** in every call.
- `connection.commit()` — **saves** the insertion in the database. Without `commit`, the row disappears when you close the connection. (In the lesson "queries from Python" we will expand on it.)

### Common mistake

**Mistake 1: double quotes for strings.**

```sql
INSERT INTO students (name, age, email) VALUES ("Dana", 24, "dana@mail.com");
```

In standard SQL, **double quotes are for names** (of tables and columns), and **single quotes for strings**. MySQL "forgives" and accepts it, but other databases (PostgreSQL) will throw an error. **Always `'...'` for strings.**

**Mistake 2: a comma after the last column.**

```sql
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
);
```

```text
1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 4
```

**Mistake 3: forgetting the list of columns in an INSERT.**

```sql
INSERT INTO students VALUES ('Dana', 24, 'dana@mail.com');
```

```text
1136 (21S01): Column count doesn't match value count at row 1
```

Without a list of columns, SQL expects a value for **every** column, including `id`, and in the table's order. **Always write the list of columns explicitly.** It is also robust to future changes in the table.

**Mistake 4: changing `CREATE TABLE IF NOT EXISTS` and expecting the table to be updated.** You added a column `city` to the command and ran it: nothing happened, because the table already exists. At the learning stage, the simple solution: delete the table — `DROP TABLE students;` — and run it again. (This also deletes all the rows! In real systems you use **Migrations**, tools that change the structure of an existing table without losing data. A topic for later.)

### Summary

- **`CREATE TABLE IF NOT EXISTS name (col TYPE constraints, ...);`** — creates a structure. `IF NOT EXISTS` = safe to run again (but does not update an existing structure).
- **`INSERT INTO name (cols) VALUES (...);`** — adds a row. Always with a list of columns. An `id` with `AUTO_INCREMENT` fills itself.
- Strings in SQL are in **single quotes** `'...'`. No comma after the last column.
- Breaking a constraint → an error with a code (`1062` a duplicate, `1364` a missing required field). The row is not added, and the data stays valid.
- Run SQL in **Database Client**, and from Python with `cursor.execute(...)` and `commit()`.

<!-- docs:start — generated by scripts/intro6/site_docs.py; edit the links there -->

### Official documentation

- [MySQL — CREATE TABLE](https://dev.mysql.com/doc/refman/8.4/en/create-table.html)
- [MySQL — INSERT](https://dev.mysql.com/doc/refman/8.4/en/insert.html)

<!-- docs:end -->

<!-- exercises:start — generated by scripts/intro6/sync_site_exercises.py; edit scripts/intro6/exercises/ -->
## 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: Health Check

Write code that connects to the database `school`, runs two check queries — `SELECT 1 + 1` and `SELECT 'ok'` — prints the results, and closes the connection **in any case**.

**Expected output:**

```text
The database computed: 2
Status: ok
Connection closed
```

{{< solution >}}
```python
import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute("SELECT 1 + 1")
    two = cursor.fetchone()[0]
    print("The database computed:", two)
    cursor.execute("SELECT 'ok'")
    status = cursor.fetchone()[0]
    print("Status:", status)
finally:
    connection.close()
    print("Connection closed")
```

```text
The database computed: 2
Status: ok
Connection closed
```

- `connection.cursor()` creates a Cursor, and `cursor.execute(...)` runs a query through it.
- `fetchone()` returns one row as a **tuple**, so `[0]` — the first column.
- `finally` guarantees that the connection is closed even if the query fails.
{{< /solution >}}

### Exercise 2: Connection and Cursor

1. What is the difference between a Connection and a Cursor?
2. What is the difference between `fetchone()` and `fetchall()`?

{{< solution >}}
1. **Connection** — the open connection to the MySQL server: through it you create a Cursor, save (`commit`) and close. **Cursor** — the object that runs a query (`execute`) and holds its results.
2. **`fetchone()`** — one row (a tuple), or `None` if there is none. **`fetchall()`** — **a list** of all the rows, or an empty list.
{{< /solution >}}

### Exercise 3: CREATE TABLE IF NOT EXISTS

Write Python code that creates the table `students` (id, name, age, a unique email) in the database `school` only if it does not exist. Run the creation twice, and show that there is no error.

**Expected output:**

```text
table ready
table ready
```

{{< solution >}}
```python
import mysql.connector

CREATE_STUDENTS = """
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
)
"""

for _ in range(2):
    connection = mysql.connector.connect(
        host="localhost", user="root", password="kodkod", database="school")
    try:
        cursor = connection.cursor()
        cursor.execute(CREATE_STUDENTS)
        print("table ready")
    finally:
        connection.close()
```

```text
table ready
table ready
```

Without `IF NOT EXISTS`, the second run would fail: `Table 'students' already exists`. The string in triple quotes (`"""`) lets you write SQL over several lines.
{{< /solution >}}

### Exercise 4: INSERT INTO

Continue: add three students — Dana (24), Avi (31) and Noa (22) — and save. Then count how many rows there are in the table.

**Expected output:**

```text
Rows: 3
```

{{< solution >}}
```python
import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS students (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            age INT NOT NULL,
            email VARCHAR(100) NOT NULL UNIQUE
        )
    """)
    cursor.execute("""
        INSERT INTO students (name, age, email) VALUES
            ('Dana', 24, 'dana@mail.com'),
            ('Avi', 31, 'avi@mail.com'),
            ('Noa', 22, 'noa@mail.com')
    """)
    connection.commit()
    cursor.execute("SELECT * FROM students")
    print("Rows:", len(cursor.fetchall()))
finally:
    connection.close()
```

```text
Rows: 3
```

You don't write the `id` — the database assigns it by itself. `commit()` saves the change in the database.
{{< /solution >}}

### Exercise 5: The database refuses

Insert a student into the table, and then try to insert another student with **the same email**, and a student **with an age of `NULL`**. Catch the error of each attempt and print its message (`error.msg`).

**Expected output:**

```text
Rejected: Duplicate entry 'dana@mail.com' for key 'students.email'
Rejected: Column 'age' cannot be null
```

{{< solution >}}
```python
import mysql.connector

connection = mysql.connector.connect(
    host="localhost", user="root", password="kodkod", database="school")
try:
    cursor = connection.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS students (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            age INT NOT NULL,
            email VARCHAR(100) NOT NULL UNIQUE
        )
    """)
    cursor.execute(
        "INSERT INTO students (name, age, email) VALUES ('Dana', 24, 'dana@mail.com')")
    attempts = [
        "INSERT INTO students (name, age, email) VALUES ('Copy', 20, 'dana@mail.com')",
        "INSERT INTO students (name, age, email) VALUES ('Noa', NULL, 'noa@mail.com')",
    ]
    for sql in attempts:
        try:
            cursor.execute(sql)
        except mysql.connector.IntegrityError as error:
            print("Rejected:", error.msg)
    connection.commit()
finally:
    connection.close()
```

```text
Rejected: Duplicate entry 'dana@mail.com' for key 'students.email'
Rejected: Column 'age' cannot be null
```

The **constraints** we defined in the table are enforced on every insertion — without a single line of checking in the Python code.
{{< /solution >}}

<!-- exercises:end -->