Connecting, a Table and Inserting Data: Connection, CREATE TABLE and INSERT

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:

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:

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

Reading results#

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

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:

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?

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:

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

Syntax#

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:

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:

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#

Another example#

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

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:

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:

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())

{'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].

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

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.

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

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.

Official documentation#

• Connector/Python — Connecting to MySQL
• MySQLConnection.is_connected()
• MySQLConnection.close()

Chapter 2: Basic SQL — CREATE TABLE and INSERT#

What will we learn?#

The first two commands in SQL:

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#

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:

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#

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:

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

Syntax#

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:

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:

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#

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:

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;

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:

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

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

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

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

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

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:

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.

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.

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

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.

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

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().

Official documentation#

• MySQL — CREATE TABLE
• MySQL — INSERT

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:

The database computed: 2
Status: ok
Connection closed

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")

The database computed: 2
Status: ok
Connection closed

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()?

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:

table ready
table ready

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()

table ready
table ready

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:

Rows: 3

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()

Rows: 3

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:

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

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()

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