Глава 2. Создание и наполнение базы данных

from sqlalchemy import create_engine
from sqlalchemy.engine import URL

# Формируем структуру подключения к БД
connection_url = URL.create(
    drivername="mysql+pymysql",
    host="localhost",
    port=3306,
    database="sakila",
    username="root",
    password="********",
)
# Создаем движок подключения Engine и передаем в него готовый объект URL
engine = create_engine(connection_url)

# Активируем магические команды JupySQL в текущем ноутбуке
%load_ext sql

# Задать лимит отображения количества строк ( default=10, None осторожно)
%config SqlMagic.displaylimit = 100
# Отключить вывод строки подключения 'Running query in 'mysql+pymysql...'
%config SqlMagic.displaycon = False
# Отключить вывод количества затронутых строк `10 rows affected.`
# %config SqlMagic.feedback = False

# Передаем наш движок в JupySQL
%sql engine

# Вывод подтверждения
print("SQLAlchemy - подключение создано")
print("JupySQL - успешно подключен через SQLAlchemy Engine!")

SQLAlchemy - подключение создано
JupySQL - успешно подключен через SQLAlchemy Engine!

Типы данных

# Символьный (текстовый, строковый)

| Тип        | Описание                | Макс байт     |
| ---------- | ----------------------- | ------------- |
| CHAR       | Постоянная длина        | 255           |
| VARCHAR    | Переменная длина        | 65 535        |
| mediumtext |                         | 16 777 215    |
| longtext   |                         | 4 294 967 295 |
| ENUM       | Проверочное ограничение |               |

# Целочисленный

| Тип       | Знаковый диапазон                  | UNSIGNED              |
| --------- | ---------------------------------- | --------------------- |
| INT       | от -2 147 483 648 до 2 147 483 647 | от 0 до 4 294 967 295 |
| BIGINT    | от -2'63 до 2'63-1                 | от 0 до 2'64-1        |
| mediumint | от -8 388 608 до 8 388 607         | от 0 до 16 777 215    |
| smallint  | от -32 768 до 32 767               | от 0 до 65 535        |
| tinyint   | от -128 до 127                     | от 0 до 255           |

# C плавающей точкой

| Тип          |                           |
| ------------ | ------------------------- |
| FLOAT(p, s)  | ~ 7 знаков после запятой  |
| double(p, s) | ~ 15 знаков после запятой |

- `p` (precision) – точность: общее количество допустимых цифр слева и справа от десятичной точки;
- `s` (scale) – масштаб: количество допустимых цифр справа от десятичной точки

# Временной

| Тип       | Формат              |
| --------- | ------------------- |
| date      | YYYY-MM-DD          |
| datetime  | YYYY-MM-DD HH:MI:SS |
| timestamp | YYYY-MM-DD HH:MI:SS |
| year      | YYYY                |
| time      | HHH:MI:SS           |

Создание таблицы¶

Таблица person

| Столбец     | Тип                | Допустимые значения |
| ----------- | ------------------ | ------------------- |
| person_id   | int(unsigned)      |                     |
| first_name  | varchar(20)        |                     |
| last_name   | varchar(20)        |                     |
| eye_color   | char(2)            | BL, BR, GR          |
| birth_date  | date               |                     |
| street      | varchar(30)        |                     |
| city        | varchar(20)        |                     |
| state       | varchar(20)        |                     |
| country     | varchar(20)        |                     |
| postal_code | varchar(20)        |                     |

Заменил smallint –> int

Магические ячейки %%sql не поддерживают на сайте подсветку синтаксиса MySQL

В принципе их первичная роль подсвечивать синтаксиc в блокнотах Jupyter. Для этого они были выбраны и отлично справляются со своей ролью.

Безусловно, было неожиданным откровением обнаружить отсутствие подсветки синтаксиса на сайте.
Но в конце-концов сайт вторичен.

%%sql
CREATE TABLE person(
    person_id INT UNSIGNED,
    fname VARCHAR(20),
    lname VARCHAR(20),
    eye_color ENUM('BR', 'BL', 'GR'),
    birth_date DATE,
    street VARCHAR(30),
    city VARCHAR(20),
    state VARCHAR(20),
    country VARCHAR(20),
    postal_code VARCHAR(20),
    CONSTRAINT pk_person PRIMARY KEY (person_id)
);

Loading...

Далее в этой главе в качестве эксперимента

Все исполняемые ячейки %%sql с сайта будут скрыты, а для красивого и читаемого отображения SQL-запросов будут добавлены обычные Markdown-блоки с корректной подсветкой синтаксиса.

Этот эксперимент честно, с интересом и любопытством отработаю в данной вводной главе.
Но в последующих главах с упражнениями тратить время на дублирование и сокрытие ячеек %%sql уже не буду. Причина простая:

цель (первична) – исследование SQL в рамках выбранной книги и инструментов;
сайт (вторичен) – можно сказать “сопутствующий ущерб” в силу природной любознательности.

CREATE TABLE person(
    person_id INT UNSIGNED,
    fname VARCHAR(20),
    lname VARCHAR(20),
    eye_color ENUM('BR', 'BL', 'GR'),
    birth_date DATE,
    street VARCHAR(30),
    city VARCHAR(20),
    state VARCHAR(20),
    country VARCHAR(20),
    postal_code VARCHAR(20),
    CONSTRAINT pk_person PRIMARY KEY (person_id)
);

Best practice

При создании таблицы best practice сразу включать функцию авто-инкремента для столбца первичного ключа INT PRIMARY KEY AUTO_INCREMENT

CREATE TABLE person(
    person_id INT PRIMARY KEY AUTO_INCREMENT,
    fname VARCHAR(20),
    lname VARCHAR(20),
    -- ...
    postal_code VARCHAR(20)
);

DESC person;

Loading...

CREATE TABLE favorite_food(
    person_id INT UNSIGNED,
    food VARCHAR(20),
    CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
    CONSTRAINT fk_favorite_food FOREIGN KEY (person_id) REFERENCES person (person_id)
);

%%sql
CREATE TABLE favorite_food(
    person_id INT UNSIGNED,
    food VARCHAR(20),
    CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
    CONSTRAINT fk_favorite_food FOREIGN KEY (person_id) REFERENCES person (person_id)
);

Loading...

DESC favorite_food;

Loading...

Заполнение и изменение таблиц¶

Генерация данных числовых ключей¶

ALTER TABLE person MODIFY person_id INT UNSIGNED AUTO_INCREMENT;

RuntimeError: (pymysql.err.OperationalError) (1833, "Cannot change column 'person_id': used in a foreign key constraint 'fk_favorite_food' of table 'sakila.favorite_food'")
[SQL: ALTER TABLE person MODIFY person_id INT UNSIGNED AUTO_INCREMENT;]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Следуя за автором получаем ошибку

СУБД (MySQL) не позволяет изменять свойства колонки, если на неё уже ссылается внешний ключ (FK) из другой таблицы. Когда пытаемся добавить AUTO_INCREMENT к person_id в таблице person, база данных блокирует это действие, так как таблица favorite_food уже жестко связана с исходным определением этого поля.

Самый простой и быстрый способ в MySQL – временно отключить проверку внешних ключей на время выполнения команды.

-- 1. Временно отключаем проверку внешних ключей
SET FOREIGN_KEY_CHECKS = 0;

-- 2. Выполняем изменение (добавляем AUTO_INCREMENT)
ALTER TABLE person MODIFY person_id INT UNSIGNED AUTO_INCREMENT;

-- 3. Включаем проверку внешних ключей обратно
SET FOREIGN_KEY_CHECKS = 1;

Best practice

При создании таблицы сразу указывать AUTO_INCREMENT для PK:

person_id INT PRIMARY KEY AUTO_INCREMENT

SET FOREIGN_KEY_CHECKS = 0;

Loading...

ALTER TABLE person MODIFY person_id INT UNSIGNED AUTO_INCREMENT;

Loading...

SET FOREIGN_KEY_CHECKS = 1;

Loading...

DESC person;

Loading...

Добавление данных¶

INSERT INTO person
    (person_id, fname, lname, eye_color, birth_date)
VALUES (null, 'William', 'Turner', 'BR', '1972-05-27');

Loading...

SELECT person_id, fname, lname, eye_color, birth_date
FROM person;

Loading...

SELECT person_id, fname, lname, eye_color, birth_date
FROM person
WHERE person_id = 1;

Loading...

SELECT person_id, fname, lname, eye_color, birth_date
FROM person
WHERE lname = 'Turner';

Loading...

INSERT INTO favorite_food (person_id, food)
VALUES (1, 'pizza');

Loading...

INSERT INTO favorite_food (person_id, food)
VALUES (1, 'cookies');

Loading...

INSERT INTO favorite_food (person_id, food)
VALUES (1, 'nachos');

Loading...

SELECT food
FROM favorite_food
WHERE person_id = 1
ORDER BY food;

Loading...

INSERT INTO person
    (person_id, fname, lname, eye_color, birth_date,
    street, city, state, country, postal_code)
VALUES
    (null, 'Susan', 'Smith', 'BL', '1975-11-02',
    '23 Maple St.', 'Arlington', 'VA', 'USA', '20220');

%%sql
INSERT INTO person
    (person_id, fname, lname, eye_color, birth_date,
    street, city, state, country, postal_code)
VALUES
    (null, 'Susan', 'Smith', 'BL', '1975-11-02',
    '23 Maple St.', 'Arlington', 'VA', 'USA', '20220');

Loading...

SELECT person_id, fname, lname, birth_date
FROM person;

Loading...

Генерация в XML

Приведенный в книге код принадлежит и выполняется в Microsoft SQL Server:

SELECT * FROM favorite_food
FOR XML AUTO, ELEMENTS

Мы работаем в MySQL, где синтаксис для работы с XML совершенно другой. К тому же, в современных версиях MySQL для работы со структурами данных гораздо чаще используется формат JSON, т.к. встроенная поддержка XML в MySQL довольно ограничена.

Выгрузка в JSON (рекомендуемая для MySQL)

%%sql
SELECT JSON_OBJECT('person_id', person_id, 'food', food) AS json_result 
FROM favorite_food;

Что дальше?

После выполнения запроса данные существуют только в оперативной памяти. Можем перехватить их и передать напрямую в Python, чтобы сохранить их в файл (JSON, XML, CSV) или использовать для анализа.

# 1. Превращаем результат SQL-запроса в DataFrame
df = _.DataFrame()

# 2. Извлекаем только колонку с JSON и сохраняем в файл
with open('favorite_food_output.json', 'w', encoding='utf-8') as f:
    # Соединяем все строки в один красивый JSON-массив
    json_string = "[" + ",".join(df['json_result']) + "]"
    f.write(json_string)

print("Файл favorite_food_output.json успешно сохранен!")

После выполнения файл favorite_food_output.json появится в левой панели Jupyter Lab (File Browser) в текущей папке.

Или в дочерней, если сразу указать относительный путь к заранее созданной папке:

with open('data/favorite_food_output.json', 'w', encoding='utf-8') as f:

Изменение данных¶

UPDATE person
SET street = '1225 Tremont St.',
    city = 'Boston',
    state = 'MA',
    country = 'USA',
    postal_code = '02138'
WHERE person_id = 1;

Loading...

SELECT * FROM person;

Loading...

Удаление данных¶

DELETE FROM person
WHERE person_id = 2;

Loading...

SELECT * FROM person;

Loading...

Когда хорошие инструкции становятся плохими¶

Не уникальный первичный ключ¶

INSERT INTO person
    (person_id, fname, lname, eye_color, birth_date)
VALUES (1, 'Charles', 'Fulton', 'GR', '1968-01-15');

RuntimeError: (pymysql.err.IntegrityError) (1062, "Duplicate entry '1' for key 'person.PRIMARY'")
[SQL: INSERT INTO person
    (person_id, fname, lname, eye_color, birth_date)
VALUES (1, 'Charles', 'Fulton', 'GR', '1968-01-15');]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

Несуществующий внешний ключ¶

INSERT INTO favorite_food (person_id, food)
VALUES (999, 'lasagna');

RuntimeError: (pymysql.err.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`sakila`.`favorite_food`, CONSTRAINT `fk_favorite_food` FOREIGN KEY (`person_id`) REFERENCES `person` (`person_id`))')
[SQL: INSERT INTO favorite_food (person_id, food)
VALUES (999, 'lasagna');]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

Нарушения значения столбцов¶

UPDATE person
SET eye_color = 'ZZ'
WHERE person_id = 1;

RuntimeError: (pymysql.err.DataError) (1265, "Data truncated for column 'eye_color' at row 1")
[SQL: UPDATE person
SET eye_color = 'ZZ'
WHERE person_id = 1;]
(Background on this error at: https://sqlalche.me/e/20/9h9h)

Некорректное преобразование данных¶

UPDATE person
SET birth_date = 'DEC-21-1980'
WHERE person_id = 1;

RuntimeError: (pymysql.err.OperationalError) (1292, "Incorrect date value: 'DEC-21-1980' for column 'birth_date' at row 1")
[SQL: UPDATE person
SET birth_date = 'DEC-21-1980'
WHERE person_id = 1;]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

UPDATE person
SET birth_date = str_to_date('DEC-21-1980', '%b-%d-%Y')
WHERE person_id = 1;

Loading...

SELECT person_id, fname, lname, birth_date
FROM person;

Loading...

CheatSheet для преобразования строк в дату и время

%а Краткое имя дня недели - Sun, Mon, ...
%b Краткое имя месяца — Jan, Feb, ...
%с Числовое значение месяца (0..11)
%d Числовое значение дня месяца (00..31)
%f Число микросекунд (000000..999999)
%Н Час дня в 24-часовом формате (00..23)
%h Час дня в 12-часовом формате (01..12)
%i Минуты в часе (00..59)
%j День года (001..366)
%М Полное имя месяца (January..December)
%m Числовое значение месяца
%р AM или РМ
%s Число секунд (00..59)
%W Полное имя дня недели (Sunday..Saturday)
%w Числовое значение дня недели (0=Sunday..6=Saturday)
%Y Значение года (четыре цифры)

База данных Sakila¶

SHOW TABLES;

Loading...

DROP TABLE favorite_food;

Loading...

DROP TABLE person;

Loading...

SHOW FULL TABLES;

Loading...

DESC customer;

Loading...

Глава 2. Создание и наполнение базы данных

Abstract¶

Создание таблицы¶

Заполнение и изменение таблиц¶

Генерация данных числовых ключей¶

Добавление данных¶

Изменение данных¶

Удаление данных¶

Когда хорошие инструкции становятся плохими¶

Не уникальный первичный ключ¶

Несуществующий внешний ключ¶

Нарушения значения столбцов¶

Некорректное преобразование данных¶

База данных Sakila¶