Реляционные базы данных

Tinkoff python

Лекция 5

Перминов Сергей

Backend Dialog System

dialog platform for bots

Базы данных - зачем?

Чтобы хранить данные :)

Реляционные и нереляционные.

Писать свою реализацию хранилища сложно и долго.

Реляционные базы данных

 

Используем, когда данные хорошо структурированы, и нам важны связи между ними.

Данные хранятся в таблицах

title created_at deadline done_at
расширить API 25.02 03.03 01.03
обновиться до python 3.8 03.03 15.03 NULL
добавить логов в http-клиент 21.02 NULL NULL

task

title ... author assigned_to project
расширить API ... Даниил Антон chatbot
обновиться до python 3.8 ... Даниил NULL talkbot
name role ...
Даниил project manager ...
Антон backend dev ...

task

user

title state ...
chatbot a|b testing ...
talkbot alpha version ...

project

title ... project project_version
расширить API ... chatbot a|b testing
обновиться до python 3.8 ... talkbot alpha version
добавить логов в http-клиент ... talkbot alpha version
title ... project project_version
расширить API ... chatbot a|b testing
обновиться до python 3.8 ... talkbot production
добавить логов в http-клиент ... talkbot alpha version

One to one

страна - столица

человек - паспортные данные

title author ...
расширить API Даниил ...
обновиться до python 3.8 Даниил ...
добавить логов в http-клиент Антон
name role ...
Даниил project manager ...
Антон backend dev ...

task

user

One to many

title year ...
Джентельмены 2019 ...
1917 2019 ...
Паразиты 2019 ...
login registered_at ...
foo 05.05.2013 ...
bar 26.12.2019 ...

film

user

Many to many

film_rating

film user rating
Джентельмены foo 9
Джентельмены bar 6
1917 bar 8

Первичный ключ

  • позволяет однозначно идентифицировать строку;

  • должен быть отвязан от реальных сущностей.

email password_hash registered_at ...
foo@kek.ru 2cf24dba5f... 05.05.2013 ...
bar@kek.ru 58756879c0... 26.12.2019 ...

Первичный ключ

email password_hash registered_at ...
foo@kek.ru 2cf24dba5f... 05.05.2013 ...
bar@kek.ru 58756879c0... 26.12.2019 ...
email registered_at ... facebook_id
foo@kek.ru 05.05.2013 ... NULL
bar@kek.ru 26.12.2019 ... NULL
NULL 10.03.2020 ... 14446842256
id email ... facebook_id
1 foo@kek.ru ... NULL
2 bar@kek.ru ... NULL
3 NULL ... 14446842256

Да кто такой этот sql?

 

Structured Query Language

 

https://www.w3schools.com/sql/

 

http://www.sql-tutorial.ru/ru/content.html

create table

create table film (
   id serial primary key,
   title varchar not null,
   budget integer default null,
   premiere date not null
);

Колонки типизированы.

  • целые цисла, строки, даты...

  • json, array, enum;

  • ...

  • геометрические примитивы (некоторые БД).

* все примеры на PostgreSQL 11.6

insert

insert into film (title, budget, premiere)
  values ('Джентельмены', 22000000, '2019-12-03');
insert into film (title, budget, premiere)
  values
    ('1917', 100000000, '2019-12-04'),
    ('Паразиты', NULL, '2019-05-21');

select

> select * from film;
 id |    title     |  budget   |  premiere  
----+--------------+-----------+------------
  1 | Джентельмены |  22000000 | 2019-12-03
  2 | 1917         | 100000000 | 2019-12-04
  3 | Паразиты     |           | 2019-05-21
> select title, premiere from film;
    title     |  premiere  
--------------+------------
 Джентельмены | 2019-12-03
 1917         | 2019-12-04
 Паразиты     | 2019-05-21

select

> select * from film where budget > 50000000;
 id | title |  budget   |  premiere  
----+-------+-----------+------------
  2 | 1917  | 100000000 | 2019-12-04
> select * from film order by premiere desc;
 id |    title     |  budget   |  premiere  
----+--------------+-----------+------------
  2 | 1917         | 100000000 | 2019-12-04
  1 | Джентельмены |  22000000 | 2019-12-03
  3 | Паразиты     |           | 2019-05-21

update

> update film
    set budget = budget + 100000
    where id = 2;

UPDATE 1

> select * from film;
 id |    title     |  budget   |  premiere  
----+--------------+-----------+------------
  1 | Джентельмены |  22000000 | 2019-12-03
  3 | Паразиты     |           | 2019-05-21
  2 | 1917         | 101000000 | 2019-12-04

delete

> delete from film where id = 2;
DELETE 1

> select * from film;
 id |    title     |  budget   |  premiere  
----+--------------+-----------+------------
  1 | Джентельмены |  22000000 | 2019-12-03
  3 | Паразиты     |           | 2019-05-21
  2 | 1917         | 101000000 | 2019-12-04

> select * from film;
 id |    title     |  budget  |  premiere  
----+--------------+----------+------------
  1 | Джентельмены | 22000000 | 2019-12-03
  3 | Паразиты     |          | 2019-05-21

index

> select *
    from film
    where premiere > '2019-12-01';

Какова сложность этого запроса - O(?)

Для фильтрации придётся проверить каждую строку таблицы.

index

> create index film_premiere_idx
    on film using btree(premiere);

index

 

  • + ускоряет поиск записей;

  • - замедляет вставку, обновление и удаление записей;

  • - занимает место на диске.

Лишние индексы - плохо!

create table "user" (
   id serial primary key,
   email varchar not null,
   registered_at date default now()
);
insert into "user" (email) values
  ('foo@kek.ru'), ('bar@kek.ru');

Constraint

> select * from "user";
 id |   email    | registered_at 
----+------------+---------------
  1 | foo@kek.ru | 2020-03-07
  2 | bar@kek.ru | 2020-03-07

> insert into "user" (email)
    values ('bar@kek.ru');
> select * from "user";
 id |   email    | registered_at 
----+------------+---------------
  1 | foo@kek.ru | 2020-03-07
  2 | bar@kek.ru | 2020-03-07
  3 | bar@kek.ru | 2020-03-10

Два пользователя с одинаковыми email 💩

create table "user" (
   id serial primary key,
   email varchar not null unique,
   registered_at date default now()
);

unique key

alter table "user" add unique(email);

Мы должны были создать таблицу по-другому:

Или если таблица уже создана:

unique key

> select * from "user";
 id |   email    | registered_at 
----+------------+---------------
  1 | foo@kek.ru | 2020-03-07
  2 | bar@kek.ru | 2020-03-07
  
> insert into "user" (email) values ('bar@kek.ru');

ERROR:  duplicate key value violates unique
        constraint "user_email_key"
> select * from "user";
 id |   email    | registered_at 
----+------------+---------------
  1 | foo@kek.ru | 2020-03-07
  2 | bar@kek.ru | 2020-03-07

> select * from film;
 id |    title     |  budget  |  premiere  
----+--------------+----------+------------
  1 | Джентельмены | 22000000 | 2019-12-03
  3 | Паразиты     |          | 2019-05-21
create table film_rating (
    id serial primary key,
    user_id integer not null,
    film_id integer not null,
    rating integer not null
);
> insert into film_rating
    (user_id, film_id, rating)
    values (1, 1, 7), (9123, 143523, 4);

> select * from film_rating;
 id | user_id | film_id | rating 
----+---------+---------+--------
  1 |       1 |       1 |      7
  2 |    9123 |  143523 |      4

film_rating соединяет несуществующего пользователя с несуществующим фильмом🤦‍♂️

create table film_rating (
    id serial primary key,
    user_id integer not null references "user"(id),
    film_id integer not null references "film"(id),
    rating integer not null
);

foreign key

alter table film_rating
  add constraint film_rating_user_id_fkey
  foreign key (user_id) references "user" (id);
  
-- аналогично для film_id

Мы должны были создать таблицу по-другому:

Или если таблица уже создана:

foreign key

> insert into film_rating
    (user_id, film_id, rating)
    values (1, 1, 7), (9123, 143523, 4);
    
ERROR:  insert or update on table "film_rating"
        violates foreign key constraint
        "film_rating_user_id_fkey"
DETAIL:  Key (user_id)=(9123) is not present
         in table "user".

foreign key

> select * from "user" where id = 1;
 id |   email    | registered_at 
----+------------+---------------
  1 | foo@kek.ru | 2020-03-07

> select * from film_rating  where id = 1;
 id | user_id | film_id | rating 
----+---------+---------+--------
  1 |       1 |       1 |      7

> delete from "user" where id = 1;
ERROR:  update or delete on table "user" violates
        foreign key constraint "film_rating_user_id_fkey"
        on table "film_rating"
DETAIL:  Key (id)=(1) is still referenced from
         table "film_rating".

create table film_rating (
    id serial primary key,
    user_id integer not null references "user"(id)
      on delete cascade,
    film_id integer not null references "film"(id),
    rating integer not null
);

foreign key

alter table film_rating
  drop constraint film_rating_user_id_fkey,
  add constraint film_rating_user_id_fkey
    foreign key (user_id) references "user"(id)
    on delete cascade;

Мы могли бы создать таблицу по-другому:

Или если таблица уже создана:

foreign key

НЕ УДАЛЯЙ ДАННЫЕ КАСКАДОМ

> select * from "user";
 id |   email    | registered_at 
----+------------+---------------
  1 | foo@kek.ru | 2020-03-07
  2 | bar@kek.ru | 2020-03-07

> select * from film;
 id |    title     |  budget  |  premiere  
----+--------------+----------+------------
  1 | Джентельмены | 22000000 | 2019-12-03
  3 | Паразиты     |          | 2019-05-21
> insert into film_rating (user_id, film_id, rating)
    values (2, 3, 14);
> select * from film_rating;
 id | user_id | film_id | rating 
----+---------+---------+--------
  8 |       2 |       3 |     14

14/10 - неужели настолько хороший фильм? 🤔 

create table film_rating (
    id serial primary key,
    user_id integer not null references "user"(id),
    film_id integer not null references "film"(id),
    rating integer not null
      check (rating > 0 and rating < 11)
);
alter table film_rating
  add constraint film_rating_rating_check
  check (rating > 0 and rating < 11);

Мы должны были создать таблицу по-другому:

Или если таблица уже создана:

check constraint

> insert into film_rating (user_id, film_id, rating)
    values (2, 3, 14);

ERROR:  new row for relation "film_rating" violates
        check constraint "film_rating_rating_check"
DETAIL:  Failing row contains (3, 2, 3, 14).

check constraint

create table film_rating (
    id serial primary key,
    user_id integer not null references "user"(id),
    film_id integer not null references "film"(id),
    rating integer not null
      check (rating > 0 and rating < 11)
);

Что ещё может пойти не так?

insert into film_rating (user_id, film_id, rating)
  values (2, 3, 10),
         (2, 3, 10),
         (2, 3, 10);
> select * from film_rating;
 id | user_id | film_id | rating 
----+---------+---------+--------
  3 |       2 |       3 |     10
  4 |       2 |       3 |     10
  5 |       2 |       3 |     10

create table film_rating (
    id serial primary key,
    user_id integer not null references "user"(id),
    film_id integer not null references "film"(id),
    rating integer not null
      check (rating > 0 and rating < 11),
    unique (user_id, film_id)
);
alter table film_rating
  add constraint film_rating_user_id_film_id_key
  unique (user_id, film_id);

Мы должны были создать таблицу по-другому:

Или если таблица уже создана:

Составной unique key

> insert into film_rating (user_id, film_id, rating)
    values (2, 3, 10),
           (2, 3, 10),
           (2, 3, 10);
           
ERROR:  duplicate key value violates unique
        constraint "user_id_film_id_uq"
DETAIL:  Key (user_id, film_id)=(2, 3)
         already exists.

Составной unique key

Index & Constraint

  • Индексы создаём только на те колонки, по которым часто выполняем фильтрацию;

  • Unique key, foreign key, check constraint - создаём так, чтобы гарантировать максимальную корректность данных;

  • Primary key - создаём отдельную "техническую" колонку;

  • Constraint на уровне базы не отменяют проверку данных на уровне приложения;

  • Foreign key должен ссылаться на первичный ключ.

foreign key

> select * from film_rating;
 id | user_email | film_id | rating 
----+------------+---------+--------
  1 | foo@kek.ru |     1   |    7

> select * from film_rating;
 id | user_id | film_id | rating 
----+---------+---------+--------
  1 |       1 |       1 |      7

  • Теперь труднее изменить email;

  • А что, если завтра мы начнём авторизовываться через facebook, и email станет необязательным?

> select * from "user";
 id |   email    | registered_at 
----+------------+---------------
  1 | foo@kek.ru | 2020-03-08
  2 | bar@kek.ru | 2020-03-08

> select * from film;
 id |    title     |  budget   |  premiere  
----+--------------+-----------+------------
  1 | Джентельмены |  22000000 | 2019-12-03
  3 | Паразиты     |           | 2019-05-21
  2 | 1917         | 101000000 | 2019-12-04

> select * from film_rating;
 id | user_id | film_id | rating 
----+---------+---------+--------
  1 |       1 |       1 |      7
  2 |       1 |       3 |     10
  3 |       2 |       1 |      6

> select
      film.id, film.title,
      film_rating.film_id, film_rating.rating
    from film
      join film_rating
        on film.id = film_rating.film_id;

 id |    title     | film_id | rating 
----+--------------+---------+--------
  1 | Джентельмены |       1 |      7
  3 | Паразиты     |       3 |     10
  1 | Джентельмены |       1 |      6

 id |    title     
----+--------------
  1 | Джентельмены 
  3 | Паразиты     
  2 | 1917         
 id | film_id | rating 
----+---------+--------
  1 |       1 |      7
  2 |       3 |     10
  3 |       1 |      6
 id |    title     | film_id | rating 
----+--------------+---------+--------
  1 | Джентельмены |       1 |      7
  3 | Паразиты     |       3 |     10
  1 | Джентельмены |       1 |      6

 id |    title     | film_id | rating 
----+--------------+---------+--------
  1 | Джентельмены |       1 |      7
  3 | Паразиты     |       3 |     10
  1 | Джентельмены |       1 |      6
  2 | 1917         |    NULL |   NULL    

 id |    title     
----+--------------
  1 | Джентельмены 
  3 | Паразиты     
  2 | 1917         
 id | film_id | rating 
----+---------+--------
  1 |       1 |      7
  2 |       3 |     10
  3 |       1 |      6
> select
      film.id, film.title,
      film_rating.film_id, film_rating.rating
    from film
      left join film_rating
        on film.id = film_rating.film_id;

 id |    title     | film_id | rating 
----+--------------+---------+--------
  1 | Джентельмены |       1 |      7
  3 | Паразиты     |       3 |     10
  1 | Джентельмены |       1 |      6
  2 | 1917         |    NULL |    NULL

Full Join

>  select film.id, film.title,
          film_rating.film_id,
          film_rating.rating
      from film cross join film_rating;

 id |    title     | film_id | rating 
----+--------------+---------+--------
  1 | Джентельмены |       1 |      7
  3 | Паразиты     |       1 |      7
  2 | 1917         |       1 |      7
  1 | Джентельмены |       3 |     10
  3 | Паразиты     |       3 |     10
  2 | 1917         |       3 |     10
  1 | Джентельмены |       1 |      6
  3 | Паразиты     |       1 |      6
  2 | 1917         |       1 |      6
>  select film.id, film.title,
          film_rating.film_id,
          film_rating.rating
      from film cross join film_rating;

 id |    title     | film_id | rating 
----+--------------+---------+--------
  1 | Джентельмены |       1 |      7
  3 | Паразиты     |       3 |     10
  1 | Джентельмены |       1 |      6
  2 | 1917         |    NULL |   NULL    

> select
      film.id, film.title,
      film_rating.film_id,
      film_rating.rating
    from film
      left join film_rating
        on film.id = film_rating.film_id;

Как найти фильмы без оценок?

 id |    title     | film_id | rating 
----+--------------+---------+--------
  2 | 1917         |    NULL |   NULL    

> select
      film.id, film.title,
      film_rating.film_id,
      film_rating.rating
    from film as f
      left join film_rating as fr
        on f.id = fr.film_id
    where fr.id is null;

Как найти фильмы без оценок?

> select u.email, f.title, fr.rating
    from "user" u
        join film_rating fr
          on u.id = fr.user_id
        join film f
          on fr.film_id = f.id;


   email    |    title     | rating 
------------+--------------+--------
 foo@kek.ru | Джентельмены |      7
 foo@kek.ru | Паразиты     |     10
 bar@kek.ru | Джентельмены |      6

В запросе может быть много join

> select u.email, f.title, fr.rating
    from "user" u
        join film_rating fr
          on u.id = fr.user_id
        join film f
          on fr.film_id = f.id;

Какова сложность этого запроса - O(?)

SQL - декларативный язык

 

Мы говорим, что хотим сделать, не как это сделать.

> select u.email, f.title, fr.rating
    from "user" u
        join film_rating fr
          on u.id = fr.user_id
        join film f
          on fr.film_id = f.id
    where
      f.premiere > '2019-12-01';

SQL - декларативный язык

  • Сначала приджойнить film_rating к user?

  • Или сначала приджойнить film_rating к film?

  • Или сначала отфильтровать film по premiere?

SQL - декларативный язык

Порядок выполнения запроса зависит от

  • количества записей в таблицах;

  • наличия индексов;

  • внутренних эвристик планировщика запросов.

https://use-the-index-luke.com/

Как именно выполнится запрос? - оператор "explain".

Базы данных - зачем?

ACID в схемах и мемах.

A - атомарность (atomic)

С - согласованность (consistensy)

I - изоляция (isolated)

D - долговечность (durability)

Атомарность (atomic)

Транзакция - набор операций, из которых либо должны быть выполнены все, либо не должна быть выполнена ни одна.

Согласованность (consistensy)

В базе данных отсутствуют противоречия с точки зрения приложения.

Изоляция (isolated)

Конкурирующие за доступ к БД транзакции физически обрабатываются последовательно и изолированно друг от друга, но для приложения это выглядит, как будто они выполняются параллельно.

Долговечность (durability)

Если транзакция завершилась успешно, то изменения будут доступны даже при аппаратном сбое.

Уровни изоляции

 

  • Read uncommited (Чтение незафиксированных данных) - отсутствует в postgres;

  • Read committed (Чтение зафиксированных данных);

  • Repeatable read (Повторяемое чтение);

  • Serializable (Сериализуемость).

 

 

https://habr.com/ru/post/469415/

Уровни изоляции транзакций

 

  • помним, что в базу можеть писать не один процесс;

  • выбираем уровень изоляции, исходя из специфики приложения;

  • минимизируем локи - делаем транзакции как можно менее протяжёнными.

Нормализация

Набор рекомендаций по проектированию БД.

  • исключение избыточности - когда данные хранятся в одном месте, их проще изменять;

  • обеспечение целостности - данные должны быть консистенты;

 

  • не стремимся уменьшить объём данных;

  • не стремимся ускорить работу базы.

Первая нормальная форма (НФ1)

  • значения в колонке должны быть атомарны;

  • колонки не должны дублировать друг друга;

  • каждая запись - представление одной сущности.

 user_id   | watched_films
-----------+--------------
     1     |  12,15,21
     2     |  14,22
  user_id   | film1  |  film2  | film3
------------+--------+---------+------
      1     |   12   |   15    |  21
      2     |   14   |   22    |

НФ1

Каждая запись - представление одной сущности.

 user_id   | film_id |    date
-----------+---------+------------
     1     |  12     | 2019-12-03
     1     |  15     | 2019-10-02
     1     |  21     | 2018-12-21
     2     |  14     | 2020-02-12
     2     |  22     | 2019-12-25

Порядок записей не должен иметь значения.

Важен порядок - заводим явную колонку.

Потенциальный ключ

уникальный и минимальный набор атрибутов, которые описывают хранимую сущность.

НФ2 + НФ3

Храним в таблице только данные, которые зависят только от потенциального ключа целиком.

 user_id | task_id |  task_project| project_state
---------+---------+--------------+---------------
    1    |    1    |    talkbot   |     alpha
    1    |    2    |    talkbot   |     alpha
    1    |    3    |    chatbot   |      a|b
    2    |    4    |    chatbot   |      a|b
    2    |    5    |    chatbot   |      a|b

Проект зависит только от задачи, но не от пользователя.

Назначение задач

Потенциальный ключ

НФ2 + НФ3

 user_id | task_id
---------+--------
    1    |    1   
    1    |    2   
    1    |    3   
    2    |    4   
    2    |    5

Назначение задач

НФ2 + НФ3

 number | title |  deadline | project | project_state
--------+-------+-----------+---------+---------------
  1872  |  ...  | 2020-03-16| talkbot |     alpha
  2012  |  ...  | 2020-03-16| talkbot |     alpha
  1923  |  ...  | 2020-03-17| chatbot |      a|b
   069  |  ...  | 2020-03-16| chatbot |      a|b
  1966  |  ...  | 2020-03-19| chatbot |      a|b

Задачи

Состояние проекта зависит не от потенциального ключа, а от колонки "проект".

НФ2 + НФ3

 number | title |  deadline | project_id 
--------+-------+-----------+------------
  1872  |  ...  | 2020-03-16|      1 
  2012  |  ...  | 2020-03-16|      1 
  1923  |  ...  | 2020-03-17|      2 
   069  |  ...  | 2020-03-16|      2 
  1966  |  ...  | 2020-03-19|      2

Задачи

 project | state
---------+-------
 talkbot | alpha
 chatbot |  a|b

Проекты

Денормализация

Отклонения от нормальных форм ради удобства или скорости работы.

 id |    title     | aver_rating
----+--------------+--------------
  1 | Джентельмены |   8.70
  3 | Паразиты     |   8.06
  2 | 1917         |   8.11

  • считать средний рейтинг фильма долго;

  • супер-точное значение рейтинга не требуется.

 id | user_id | film_id | rating
----+---------+---------+--------
  1 |       1 |       1 |      7
  2 |       1 |       3 |     10
 ...|     ... |     ... |    ...

Нормализация

  • набор рекомендаций по проектированию БД;

  • сначала нормализация, потом денормализация.

Перерыв?

SQLite

база данных в одном файле

  • + не требует усилий по развёртыванию и настройке;

  • + поддержка в python из коробки;

  • -  нельзя одновременно писать из двух процессов;

  • - нельзя работать по сети.

Серверные базы данных

  • Oracle;

  • Microsoft SQL Server;

  • MySQL;

  • Postgres;

  • etc...

Что делаем из python?

 

Вариант 1: работаем с "сырым" sql через драйвер.

raw sql

import sqlite3

conn = sqlite3.connect('example.db')
c = conn.cursor()

c.execute("""
    CREATE TABLE film (
        ID INTEGER PRIMARY KEY,
        title varchar, budget integer, premiere date
    )
""")

c.execute("""
    INSERT INTO film (title, budget, premiere) VALUES
        ('Джентельмены', 22000000, '2019-12-03'),
        ('1917', 100000000, '2019-12-04')
""")

Нет подсветки sql-синтаксиса* 👎

raw sql

res = c.execute("SELECT * FROM film;").fetchall()
for row in res:
    print(row)

# (1, 'Джентельмены', 22000000, '2019-12-03')
# (2, '1917', 100000000, '2019-12-04')

conn.commit()
conn.close()

raw sql

sql-инъекции

film_id = 1

# плохо
cursor.execute(f"""
    SELECT * FROM film WHERE id = {film_id}
""")
film_id = '1; drop table film;'

# плохо
cursor.execute(f"""
    SELECT * FROM film WHERE id = {film_id}
""")

# SELECT * FROM film
#   WHERE id = 1; drop table film;

sqlite эта инъекция не сломает, потому что драйвер не может выполнить больше одного запроса за раз.

raw sql

sql-инъекции

film_id = 1

# плохо
cursor.execute(f"""
    SELECT * FROM film WHERE id = {film_id}
""")

? - placeholder, который сообщает драйверу, что в этом месте будет параметр, который нужно экранировать.

# хорошо
cursor.execute("""
    SELECT * FROM film WHERE id = ?
""", user_id
)

raw sql

Много затрат на построение запроса.

def find_any(columns):
    conditions = [
        f"{column} = '?'"
        for column in columns
    ]
    return ' OR \n'.join(conditions)
 
# field1 = '?' OR 
# field2 = '?' OR 
# field3 = '?'

raw sql

 

Берём драйвер и просто* исполняем запросы.

 

  • + видим конечный sql-запрос;

  • - результаты запросов - простые типы;

  • - мучаемся с шаблонами;

  • - помним про инъекции;

  • - зависим от диалекта sql.

Что делаем из python?

 

Вариант 2: object relation mapping (ORM)

ORM

 

  • Работаем с базой в терминах ООП, а не в терминал реляционной алгебры.

  • Есть query build - не форматируем строки вручную на python.

  • Не зависим от платформы*: сегодня sqlite, завтра postgres.

peewee

from peewee import (
  SqliteDatabase, Model, CharField,
  DateField, AutoField)

db = SqliteDatabase('people.db')


class Film(Model):
    id = AutoField()
    title = CharField()
    premiere = DateField()

    class Meta:
        database = db

        
db.connect()
db.create_tables([Film])

peewee

from datetime import date

film = Film(
    title='Джентльмены',
    premiere=date(2019, 12, 3))
film.save()

loaded_film = (
    Film.select()
    .where(Film.title == 'Джентльмены')
    .get()
)
print(loaded_film.title)
# Джентльмены

django orm

from django.db import models


class Film(models.Model):
    title = models.CharField(max_length=100)
    premiere = models.DateTimeField()

    def __str__(self):
        return self.title

django orm

b = Film(
    title='Джентельмены',
    premiere=date(2019, 12, 3))
b.save()

loaded_film = Film.objects.get(title='Джентельмены')
print(loaded_film)
# Джентельмены

SQLAlchemy

SQLAlchemy

SQLAlchemy

orm

from datetime import date
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Film(Base):
    __tablename__ = 'film'

    id = sa.Column(sa.Integer, primary_key=True)
    title =  sa.Column(sa.String())
    premiere =  sa.Column(sa.Date())


film = Film(
    title='Джентельмены',
    premiere=date(2019, 12, 3))

SQLAlchemy

engine - связь SQLAlchemy с базой

import sqlalchemy as sa

engine = sa.create_engine('sqlite:///foo.db')

# или
# `pip install psycopg2`
db_url = 'postgresql://scott:tiger@localhost:5432/mydatabase'
engine = sa.create_engine(db_url)


Base.metadata.create_all(engine)

SQLAlchemy

session - "близнец" транзакции на уровне python

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

film = Film(
    title='Джентельмены',
    premiere=date(2019, 12, 3))
session.add(film) # insert

film.title = 'new title'
session.add(film) # update

session.delete(film) # delete

# коммитим изменения
session.commit()

# или откатываем изменения
session.rollback()

SQLAlchemy

session нужно закрыть после использования

from contextlib import contextmanager

Session = sessionmaker(bind=engine)


@contextmanager
def create_session(**kwargs):
    new_session = Session(**kwargs)
    try:
        yield new_session
        new_session.commit()
    except Exception:
        new_session.rollback()
        raise
    finally:
        new_session.close()

SQLAlchemy

session - "близнец" транзакции на уровне python

with create_session() as session:
    film = Film(
        title='Джентельмены',
        premiere=date(2019, 12, 3),
    )
  
    session.add(film)

SQLAlchemy

session.query

with create_session() as session:
    test_user = (
        session.query(Film)
        .filter(Film.title == '1917')
        .one()
    )

# SELECT film.id AS film_id,
#        film.title AS film_title,
#        film.premiere AS film_premiere 
# FROM film 
# WHERE film.title = ?

SQLAlchemy

films = (
    session.query(Film)
    .filter(Film.title == 'Жизнь прекрасна')
    .one()
)

# sqlalchemy.orm.exc.NoResultFound:
#   No row was found for one()

SQLAlchemy

метод несколько строк одна строка ни одной строки
all список список из одного объекта пустой список
one исключение объект исключение
first случайный из объектов объект None
one_or_none исключение объект None

SQLAlchemy

with create_session() as session:
    user = User(name='admin')
    session.add(user)


with create_session() as session:
    user = session.query(User).first()
    print(user.name)
    
> 'admin'

SQLAlchemy

with create_session() as session:
    user = session.query(User).first()

...  # что-то ещё
print(user.name)


> Traceback (most recent call last):
>     ...
> sqlalchemy.orm.exc.DetachedInstanceError: ...

SQLAlchemy

with create_session() as session:
    user = session.query(User).first()

...  # что-то ещё
print(user.name)

> Traceback (most recent call last):
>     ...
> sqlalchemy.orm.exc.DetachedInstanceError: ...

транзакция открыта

пытаемся обратиться к атрибуту

другой процесс мог изменить значение в базе

SQLAlchemy

with create_session(expire_on_commit=False) as session:
    user = session.query(User).first()

print(user.name)
> 'admin'

with create_session() as session:
    user = session.query(User).first()
    name = user.name

print(name)
> 'admin'

expire_on_commit - когда мы уверены, что с базой работает только наш процесс, например, в тестах

flush

with create_session() as session:
    user = User(...)
    session.add(user)
    print(user.id)
# None
with create_session() as session:
    user = User(...)
    session.add(user)
    session.flush()
    print(user.id)
# 1

flush - выполнить запрос, но не коммитить транзакцию.

SQLAlchemy

starter pack

engine = sa.create_engine(...)
Session = sessionmaker(bind=engine)
Base = declarative_base()


@contextmanager
def create_session(**kwargs):
    ...


class User(Base):
    __tablename__ = 'user'

    ...


Base.metadata.create_all(engine)

with create_session() as session:
    ...

  • подключение к базе - engine;

  • описываем таблицы - Base;

  • работаем с сессией через контекстный менеджер.

Отношения

class UserRole(Base):
    __tablename__ = 'user_role'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String, unique=True)
    ...


class User(Base):
    __tablename__ = 'user'

    id = sa.Column(sa.Integer, primary_key=True)
    email = sa.Column(sa.String, unique=True)
    role_id = sa.Column(
        sa.Integer, sa.ForeignKey(UserRole.id),
        nullable=False, index=True)
    ...

Отношения

with create_session() as session:
    user = session.query(User).first()
    print(user.role_id)
# 1

Чтобы получить сам объект Role, нужен ещё один запрос в базу?

Отношения

from sqlalchemy import orm as so

class UserRole(Base):
    __tablename__ = 'user_role'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String, unique=True)

    users = so.relationship(
        'User', back_populates='role',
        uselist=True)


class User(Base):
    __tablename__ = 'user'

    id = sa.Column(sa.Integer, primary_key=True)
    email = sa.Column(sa.String, unique=True)
    role_id = sa.Column(
        sa.Integer, sa.ForeignKey(UserRole.id),
        nullable=False, index=True)

    role = so.relationship(
        UserRole, back_populates='users',
        uselist=False)

Отношения

with create_session() as session:
    user = session.query(User).first()
    print(user.role.name)
# admin

Миграции

Изменения схемы базы данных.

 

Для SQLAlchemy:

pip install alembic

 

alembic - инструмент генерации скриптов миграции на основе описания классов.

Миграции

class User(Base):
    __tablename__ = 'user'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String())
    
    # новое поле
    email = sa.Column(sa.String())
alembic revision -m "add user.email column"

Миграции

"""add user.email column
Revision ID: 1975ea83b712
"""

from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = '1975ea83b712'
down_revision = None
branch_labels = None


def upgrade():
    op.add_column('user', sa.Column('email', sa.String())


def downgrade():
    op.drop_column('user', 'email')

Миграции

 

накатываем 🍻 и откатываем

alembic upgrade head / <revision_id>
alembic downgrade <revision_id>
from project.db import Base


@pytest.fixture(autouse=True)
def _init_db():
    Base.metadata.create_all()
    yield
    Base.metadata.drop_all()

Как тестировать

 

  • отдельная тестовая база;

  • создаём данные перед тестом;

  • чистим базу между тестами.

Вопросики

Tinkoff Python 2020 - 5

By Afonasev Evgeniy

Source: persi

Tinkoff Python 2020 - 5

  • 479

More from Afonasev Evgeniy