Подключение, таблица и вставка данных: Connection, CREATE TABLE и INSERT

В этом уроке#

Урок состоит из двух глав — в том же порядке, что и в презентации:

1. Глава 1 — открытие соединения, проверка, что соединение работает, и правильное закрытие
2. Глава 2 — базовый SQL: CREATE TABLE и INSERT

Глава 1: Открытие соединения, проверка, что оно работает, и правильное закрытие#

Что мы изучим?#

Жизненный цикл любой работы с базой данных:

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

• Соединение (Connection) и курсор (Cursor): что такое каждый из них.
• Проверочный запрос (Health Check): «база вообще отвечает?»
• Правильное закрытие, даже при ошибке.
• get_connection() — одна функция, которой будет пользоваться весь проект.

Зачем это нужно?#

Каждая функция CRUD и каждый Endpoint сервера (тема API студентов) начинается с открытия соединения и заканчивается его закрытием. Незакрытое соединение — классический баг: каждое соединение с MySQL занимает ресурсы — и у нас, и на сервере, — а у MySQL ограниченное число одновременных соединений. На сервере, который обрабатывает тысячи запросов, через какое-то время соединения заканчиваются (Too many connections), и всё застревает. Это ровно как открытые файлы в уроке о файлах, только в масштабе сервера.

Объяснение с основ#

Connection — открытая линия#

Соединение (Connection) = объект, который представляет открытый канал между нашим кодом и сервером MySQL. Через него создают курсор, через него подтверждают изменения (commit, позже в этой теме) и через него закрывают. Пока оно открыто, оно занимает ресурсы.

Cursor#

Курсор (Cursor) = объект, который выполняет запрос и хранит его результаты и позволяет читать их строка за строкой. Представьте его как палец, который идёт по результатам запроса.

Его создают из соединения и выполняют через него:

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

Чтение результатов#

Каждая строка — tuple (вспомните кортежи!): ("Dana", 24). (Позже в этой теме научимся получать строки в виде dict.)

Проверочный запрос (Health Check)#

Чтобы проверить, что соединение действительно работает (а не только что объект создан), выполняют простой запрос, который не зависит ни от одной таблицы:

SELECT 1
SELECT VERSION()

Если ответ пришёл, база жива и отвечает. Именно так работает Endpoint /health настоящих серверов (тема бэкенда и FastAPI).

Правильное закрытие — try/finally#

Что будет, если запрос упадёт после того, как мы открыли соединение?

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

Ошибка останавливает функцию, и close не выполняется. Решение, которое мы изучили в теме исключений: finally, который выполняется всегда:

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

Синтаксис#

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

Первый пример кода#

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

Вывод:

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

Разбор кода построчно#

Ещё один пример#

Ошибка посередине и доказательство, что finally закрывает. А также: что происходит, когда пытаются использовать закрытое соединение.

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)

Вывод:

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

Разбор:

• mysql.connector.Error — базовый класс всех ошибок драйвера (тема ООП: иерархия исключений!). ProgrammingError (ошибка в SQL), IntegrityError (нарушение ограничения, позже) и OperationalError (проблема с соединением) — его подклассы.
• В запросе с опечаткой (SELEC) ошибка была поймана, и finally всё равно закрыл соединение.
• 1064 — номер ошибки MySQL для синтаксической ошибки, а near 'SELEC 1' говорит точно, где проблема. Это синтаксическая ошибка SQL (та же идея, что в Python, другой язык).
• Использование соединения после close → MySQL Connection not available. Соединение «мертво», и нужно открыть новое.

get_connection — одна точка подключения#

В настоящем проекте соединение нужно каждой функции. Вместо того чтобы писать пять данных для подключения в десятках мест, пишут одну функцию (DRY). Это будет основа файла db.py во всех этих темах:

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 возвращает dict. В теме API студентов её можно подключить к Endpoint-у GET /health, и FastAPI превратит её результат в JSON.

Типичная ошибка#

Ошибка 1: close без finally. Работает на «счастливом пути», но каждая ошибка посередине оставляет соединение открытым. На сервере после нескольких сотен неудачных запросов MySQL отказывает в новых соединениях: Too many connections.

Ошибка 2: забыть [0].

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

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

fetchone() возвращает строку (tuple), даже когда в ней один столбец. Само значение = [0].

Ошибка 3: fetchone(), когда результатов нет. fetchone() возвращает None, если строки нет, и тогда None[0] → TypeError: 'NoneType' object is not subscriptable. Позже в этой теме (поиск по id) мы всегда будем проверять if row is None.

Ошибка 4: новый запрос до того, как прочитаны все результаты.

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

В MySQL результаты приходят с сервера постепенно, и новый запрос не может уйти, пока предыдущий не прочитан до конца. Когда ожидаете несколько строк — fetchall(). Когда нужна одна строка — фильтруйте через WHERE, чтобы вернулась одна строка.

Итоги#

• Цикл: открытие (connect) → курсор (cursor()) → запрос (execute) → чтение (fetchone/fetchall) → закрытие (close).
• Connection = канал к серверу базы данных. Cursor = выполняет запрос и хранит результаты.
• fetchone() = одна строка (tuple) или None. fetchall() = список tuple. Одно значение: cursor.fetchone()[0].
• Проверочный запрос: SELECT 1 / SELECT VERSION(). Основа для /health.
• Всегда try/finally + close().
• Одна get_connection() на весь проект (DRY). Ошибки: mysql.connector.Error и её подклассы.

Официальная документация#

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

Глава 2: Базовый SQL — CREATE TABLE и INSERT#

Что мы изучим?#

Первые две команды SQL:

В этом уроке мы сосредоточимся на самом SQL и будем выполнять его в Database Client в VS Code. В конце увидим, как выполнить тот же SQL из Python. (В следующих уроках научимся работать из Python правильно — параметры, commit и результаты.)

Зачем это нужно?#

База school работает в контейнере — но она пуста. Таблица — это «ящик», где будут лежать данные, а INSERT — первое действие в любой системе: регистрация пользователя, добавление товара, оформление заказа. В API, который мы построим в теме API студентов, POST /students = INSERT INTO students.

Объяснение с основ#

Структура команды SQL#

• Ключевые слова вроде CREATE, TABLE, INSERT, INTO, VALUES по соглашению пишут заглавными буквами, чтобы отличать их от имён. (SQL не различает регистр ключевых слов, но так читается лучше.)
• Имена таблиц и столбцов: snake_case, строчными буквами, а для таблиц обычно во множественном числе (students).
• Строки в SQL пишут в одинарных кавычках: 'Dana'.
• Команда заканчивается ; (обязательно, когда выполняете несколько команд подряд, и всегда рекомендуется).

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

Каждая строка в скобках = столбец: <name> <TYPE> <constraints>, через запятую. (После последнего столбца запятой нет!)

IF NOT EXISTS#

CREATE TABLE students падает, если таблица уже существует. На сервере, который каждый день запускается заново, нужна команда, которую безопасно выполнять повторно:

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

«Создай таблицу, только если её ещё нет». Если она есть, ничего не происходит, и ошибки нет. (Обратите внимание: если таблица существует с другой структурой, она не изменится.)

INSERT INTO#

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

• INSERT INTO <table> — в какую таблицу.
• (name, age, email) — какие столбцы вы заполняете и в каком порядке.
• VALUES (...) — значения, точно в том же порядке.
• id нет в списке — его сам заполняет AUTO_INCREMENT.

Можно добавить несколько строк одной командой:

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

Синтаксис#

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');

Как выполнять SQL в Database Client:

1. Создайте новый файл с расширением .sql, например school.sql.
2. Вверху файла выберите соединение и базу school (или: правый щелчок по school в дереве → Open Query).
3. Напишите команды, выделите их и нажмите Run (или Ctrl+Enter).
4. В дереве сбоку нажмите Refresh — и таблица появится. Щелчок по ней открывает строки как электронную таблицу.

Первый пример кода#

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;

Выполните в Database Client. Результат SHOW TABLES:

Tables_in_school
----------------
students

Таблица существует! А в дереве сбоку появилась students с четырьмя столбцами.

Разбор кода построчно#

Выполните ту же команду снова: ошибки нет. Благодаря IF NOT EXISTS.

Ещё один пример#

Добавляем строки и смотрим, что происходит при нарушении ограничений. Добавьте в school.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;

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

База сама выдала id: 1, 2, 3. (SELECT * = «покажи всё» — в следующем уроке изучим его подробно.)

Теперь нарушим ограничения — выполняйте каждую команду отдельно:

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

Мы запросили 3 столбца, но дали только 2 значения. Число значений должно совпадать с числом столбцов.

Разбор:

• Duplicate entry ... for key 'students.email' — email Даны уже существует. База отказала, и строка не добавилась.
• Field 'age' doesn't have a default value — мы не заполнили age, а он обязательный (NOT NULL, без значения по умолчанию).
• Число в начале каждого сообщения (1062, 1364, 1136) — код ошибки MySQL, по нему можно искать в интернете.
• Из Python нарушение UNIQUE приходит как IntegrityError (Integrity = целостность данных). Мы поймаем его позже в этой теме и будем возвращать 409 из API в теме API студентов.
• Данные остались допустимыми. В этом сила ограничений: даже если в коде есть баг, база соблюдает правила.

Тот же SQL — из Python#

На сервере мы не будем выполнять SQL вручную — его будет выполнять код. Вот как те же CREATE TABLE и INSERT выглядят из 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()

• """...""" — строка на несколько строк, удобна для длинного SQL.
• cursor.execute(...) выполняет одну команду за вызов.
• connection.commit() — сохраняет вставку в базе. Без commit строка исчезает при закрытии соединения. (В уроке «запросы из Python» расскажем подробнее.)

Типичная ошибка#

Ошибка 1: двойные кавычки для строк.

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

В стандартном SQL двойные кавычки — для имён (таблиц и столбцов), а одинарные — для строк. MySQL «прощает» и принимает это, но другие базы (PostgreSQL) выдадут ошибку. Для строк всегда '...'.

Ошибка 2: запятая после последнего столбца.

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

Ошибка 3: забыть список столбцов в INSERT.

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

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

Без списка столбцов SQL ожидает значение для каждого столбца, включая id, и в порядке таблицы. Всегда пишите список столбцов явно. Это ещё и устойчиво к будущим изменениям таблицы.

Ошибка 4: изменить CREATE TABLE IF NOT EXISTS и ожидать, что таблица обновится. Вы добавили в команду столбец city и выполнили её: ничего не произошло, потому что таблица уже существует. На этапе обучения простое решение: удалить таблицу — DROP TABLE students; — и выполнить снова. (Это удалит и все строки! В настоящих системах используют миграции (Migrations) — инструменты, которые меняют структуру существующей таблицы без потери данных. Тема на будущее.)

Итоги#

• CREATE TABLE IF NOT EXISTS name (col TYPE constraints, ...); — создаёт структуру. IF NOT EXISTS = безопасно запускать повторно (но существующую структуру не обновляет).
• INSERT INTO name (cols) VALUES (...); — добавляет строку. Всегда со списком столбцов. id с AUTO_INCREMENT заполняется сам.
• Строки в SQL — в одинарных кавычках '...'. После последнего столбца запятой нет.
• Нарушение ограничения → ошибка с кодом (1062 — дубликат, 1364 — отсутствует обязательное поле). Строка не добавляется, и данные остаются допустимыми.
• SQL выполняют в Database Client, а из Python — через cursor.execute(...) и commit().

Официальная документация#

• MySQL — CREATE TABLE
• MySQL — INSERT

Практика#

Упражнения опираются только на то, что пройдено в этом уроке и в предыдущих. Сначала попробуйте сами, и только потом открывайте решение.

Упражнение 1: Проверка здоровья (Health Check)#

Напишите код, который подключается к базе school, выполняет два проверочных запроса — SELECT 1 + 1 и SELECT 'ok', — печатает результаты и в любом случае закрывает соединение.

Ожидаемый вывод:

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

Упражнение 2: Connection и Cursor#

1. В чём разница между Connection и Cursor?
2. В чём разница между fetchone() и fetchall()?

Упражнение 3: CREATE TABLE IF NOT EXISTS#

Напишите код на Python, который создаёт таблицу students (id, name, age, уникальный email) в базе school, только если её нет. Выполните создание дважды и покажите, что ошибки нет.

Ожидаемый вывод:

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

Упражнение 4: INSERT INTO#

Продолжение: добавьте трёх студентов — Дану (24), Ави (31) и Ноа (22) — и сохраните. Затем посчитайте, сколько строк в таблице.

Ожидаемый вывод:

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

Упражнение 5: База отказывает#

Вставьте студента в таблицу, а затем попытайтесь вставить другого студента с тем же email и студента с возрастом NULL. Поймайте ошибку каждой попытки и напечатайте её сообщение (error.msg).

Ожидаемый вывод:

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