Лекция 5
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 |
title | ... | author | assigned_to | project |
---|---|---|---|---|
расширить API | ... | Даниил | Антон | chatbot |
обновиться до python 3.8 | ... | Даниил | NULL | talkbot |
name | role | ... |
---|---|---|
Даниил | project manager | ... |
Антон | backend dev | ... |
title | state | ... |
---|---|---|
chatbot | a|b testing | ... |
talkbot | alpha version | ... |
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 |
title | author | ... |
---|---|---|
расширить API | Даниил | ... |
обновиться до python 3.8 | Даниил | ... |
добавить логов в http-клиент | Антон |
name | role | ... |
---|---|---|
Даниил | project manager | ... |
Антон | backend dev | ... |
title | year | ... |
---|---|---|
Джентельмены | 2019 | ... |
1917 | 2019 | ... |
Паразиты | 2019 | ... |
login | registered_at | ... |
---|---|---|
foo | 05.05.2013 | ... |
bar | 26.12.2019 | ... |
film | user | rating |
---|---|---|
Джентельмены | foo | 9 |
Джентельмены | bar | 6 |
1917 | bar | 8 |
password_hash | registered_at | ... | |
---|---|---|---|
foo@kek.ru | 2cf24dba5f... | 05.05.2013 | ... |
bar@kek.ru | 58756879c0... | 26.12.2019 | ... |
password_hash | registered_at | ... | |
---|---|---|---|
foo@kek.ru | 2cf24dba5f... | 05.05.2013 | ... |
bar@kek.ru | 58756879c0... | 26.12.2019 | ... |
registered_at | ... | facebook_id | |
---|---|---|---|
foo@kek.ru | 05.05.2013 | ... | NULL |
bar@kek.ru | 26.12.2019 | ... | NULL |
NULL | 10.03.2020 | ... | 14446842256 |
id | ... | facebook_id | |
---|---|---|---|
1 | foo@kek.ru | ... | NULL |
2 | bar@kek.ru | ... | NULL |
3 | NULL | ... | 14446842256 |
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 into film (title, budget, premiere)
values
('Джентельмены', 22000000, '2019-12-03'),
('1917', 100000000, '2019-12-04'),
('Паразиты', NULL, '2019-05-21');
> 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 max(premiere) from film;
max
------------
2019-12-04
> select * from film
where premiere = (select max(premiere) from film);
id | title | budget | premiere
----+-------+-----------+------------
2 | 1917 | 100000000 | 2019-12-04
> 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 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 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
> select *
from film
where premiere > '2019-12-01';
> create index film_premiere_idx
on film using btree(premiere);
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');
> 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
create table "user" (
id serial primary key,
email varchar not null unique,
registered_at date default now()
);
alter table "user" add unique(email);
> 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
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
);
alter table film_rating
add constraint film_rating_user_id_fkey
foreign key (user_id) references "user" (id);
-- аналогично для film_id
> 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".
> 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
);
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;
> 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
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);
> 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).
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);
> 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.
> 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
> 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
f.title, fr.rating,
fr.film_id, fr.rating
from film as f
join film_rating as fr
on f.id = fr.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
----+--------------
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
2 | 1917 | NULL | NULL
id | title
----+--------------
1 | Джентельмены
3 | Паразиты
2 | 1917
id | film_id | rating
----+---------+--------
1 | 1 | 7
2 | 3 | 10
3 | 1 | 6
> select
f.title, fr.rating,
fr.film_id, fr.rating
from film as f
left join film_rating as fr
on f.id = fr.film_id;
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
2 | 1917 | NULL | NULL
> select f.id, f.title,
fr.film_id, fr.rating
from film f cross join film_rating fr;
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 f.id, f.title,
fr.film_id, fr.rating
from film f cross join film_rating fr;
id | title | film_id | rating
----+--------------+---------+--------
1 | Джентельмены | 1 | 7
3 | Паразиты | 3 | 10
1 | Джентельмены | 1 | 6
2 | 1917 | NULL | NULL
> select
f.title, fr.rating,
fr.film_id, fr.rating
from film as f
left join film_rating as fr
on f.id = fr.film_id;
id | title | film_id | rating
----+--------------+---------+--------
2 | 1917 | NULL | NULL
> select
f.title, fr.rating,
fr.film_id, fr.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
> 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;
> 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';
ACID в схемах и мемах.
Если транзакция завершилась успешно, то изменения будут доступны даже при аппаратном сбое.
> select * from accounts;
id | login | balance
----+-------+----------
1 | petya | 2000
> begin;
> update accounts
set balance = balance + 500
where id = 1;
> select * from accounts;
id | login | balance
----+-------+----------
1 | petya | 2500
> commit;
> begin;
> select * from accounts;
id | login | balance
----+-------+----------
1 | petya | 2000
> select * from accounts;
id | login | balance
----+-------+----------
1 | petya | 2500
> select * from accounts;
id | login | balance
----+-------+----------
1 | petya | 2000
> begin;
> update accounts
set balance = balance + 500
where id = 1;
> select * from accounts;
id | login | balance
----+-------+----------
1 | petya | 2500
> commit;
> begin;
> select * from accounts;
id | login | balance
----+-------+----------
1 | petya | 2000
> select * from accounts;
id | login | balance
----+-------+----------
1 | petya | 2000
> select * from accounts;
id | login | balance
----+-------+----------
1 | petya | 2000
> begin;
> update accounts
set balance = balance + 500
where id = 1;
> select * from accounts;
id | login | balance
----+-------+----------
1 | petya | 2500
> begin;
> update accounts
set balance = balance + 500
where id = 1;
> select * from accounts;
id | login | balance
----+-------+----------
1 | petya | 2000
> begin;
> update accounts
set balance = balance + 500
where id = 1;
> select * from accounts;
id | login | balance
----+-------+----------
1 | petya | 2500
> commit;
> begin;
> update accounts
set balance = balance + 500
where id = 1;
ERROR: could not serialize
access due to concurrent update
id | user_id | watched_films
----+------------+--------------
1 | 1 | 12,15,21
2 | 2 | 14,22
id | user_id | film1 | film2 | film3
----+------------+--------+---------+------
1 | 1 | 12 | 15 | 21
2 | 2 | 14 | 22 |
id | user_id | film_id | date
----+------------+---------+------------
1 | 1 | 12 | 2019-12-03
2 | 1 | 15 | 2019-10-02
3 | 1 | 21 | 2018-12-21
4 | 2 | 14 | 2020-02-12
5 | 2 | 22 | 2019-12-25
id | user_id | task_id | task_project| project_state
----+---------+---------+--------------+---------------
1 | 1 | 1 | talkbot | alpha
2 | 1 | 2 | talkbot | alpha
3 | 1 | 3 | chatbot | a|b
4 | 2 | 4 | chatbot | a|b
5 | 2 | 5 | chatbot | a|b
Потенциальный ключ
id | user_id | task_id
----+---------+--------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 2 | 4
5 | 2 | 5
id | number | title | deadline | project | project_state
------+--------+-------+-----------+---------+---------------
1 | 1872 | ... | 2020-03-16| talkbot | alpha
2 | 2012 | ... | 2020-03-16| talkbot | alpha
3 | 1923 | ... | 2020-03-17| chatbot | a|b
4 | 069 | ... | 2020-03-16| chatbot | a|b
5 | 1966 | ... | 2020-03-19| chatbot | a|b
id | number | title | deadline | project | project_state
------+--------+-------+-----------+---------+---------------
1 | 1872 | ... | 2020-03-16| talkbot | alpha
2 | 2012 | ... | 2020-03-16| talkbot | alpha
3 | 1923 | ... | 2020-03-17| chatbot | a|b
4 | 069 | ... | 2020-03-16| chatbot | a|b
5 | 1966 | ... | 2020-03-19| chatbot | a|b
id | number | title | deadline | project_id
------+--------+-------+-----------+------------
1 | 1872 | ... | 2020-03-16| 1
2 | 2012 | ... | 2020-03-16| 1
3 | 1923 | ... | 2020-03-17| 2
4 | 069 | ... | 2020-03-16| 2
5 | 1966 | ... | 2020-03-19| 2
id | project | state
----+---------+-------
1 | talkbot | alpha
2 | 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
...| ... | ... | ...
соединение расходует ресурсы базы данных;
количество одновременно открытых соединений ограничено;
соединения можно переиспользовать как на стороне БД (например, pgbouncer), так и на стороне приложения.
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')
""")
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()
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 эта инъекция не сломает, потому что драйвер не может выполнить больше одного запроса за раз.
film_id = 1
# плохо
cursor.execute(f"""
SELECT * FROM film WHERE id = {film_id}
""")
? - placeholder, который сообщает драйверу, что в этом месте будет параметр, который нужно экранировать.
# хорошо
cursor.execute("""
SELECT * FROM film WHERE id = ?
""", user_id
)
def find_any(columns):
conditions = [
f"{column} = '?'"
for column in columns
]
return ' OR \n'.join(conditions)
# field1 = '?' OR
# field2 = '?' OR
# field3 = '?'
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])
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)
# Джентльмены
from django.db import models
class Film(models.Model):
title = models.CharField(max_length=100)
premiere = models.DateTimeField()
def __str__(self):
return self.title
b = Film(
title='Джентельмены',
premiere=date(2019, 12, 3))
b.save()
loaded_film = Film.objects.get(title='Джентельмены')
print(loaded_film)
# Джентельмены
не только 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))
engine = sa.create_engine('sqlite:///foo.db')
# или
# `pip install psycopg2`
db_url = 'postgresql://scott:tiger@localhost:5432/mydatabase'
engine = create_engine(db_url)
Base.metadata.create_all(engine)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
session.add(film1) # insert
session.delete(film2) # delete
film3.title = 'new title'
session.add(user3) # update
# коммитим изменения
session.commit()
# или откатываем изменения
session.rollback()
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()
with create_session() as session:
film = Film(
title='Джентельмены',
premiere=date(2019, 12, 3),
)
session.add(film)
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 = ?
films = (
session.query(Film)
.filter_by(title='1917')
.all()
)
films = (
session.query(Film)
.filter(Film.title == '1917')
.all()
)
films = (
session.query(Film)
.filter(Film.title == 'Жизнь прекрасна')
.one()
)
# sqlalchemy.orm.exc.NoResultFound:
# No row was found for one()
метод | несколько строк | одна строка | ни одной строки |
---|---|---|---|
all | список | список из одного объекта | пустой список |
one | исключение | объект | исключение |
first | случайный из объектов | объект | None |
one_or_none | исключение | объект | None |
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'
with create_session() as session:
user = session.query(User).first()
... # что-то ещё
print(user.name)
> Traceback (most recent call last):
> ...
> sqlalchemy.orm.exc.DetachedInstanceError: ...
with create_session() as session:
user = session.query(User).first()
... # что-то ещё
print(user.name)
> Traceback (most recent call last):
> ...
> sqlalchemy.orm.exc.DetachedInstanceError: ...
транзакция открыта
пытаемся обратиться к атрибуту
другой процесс мог изменить значение в базе
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 - когда мы уверены, что с базой работает только наш процесс, например, в тестах
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
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:
...
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
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
class Film(Base):
__tablename__ = 'film'
id = sa.Column(sa.Integer, primary_key=True)
...
viewers = so.relationship(
'User', secondary=lambda: user_to_film,
back_populates='films')
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, primary_key=True)
...
films = so.relationship(
Film, secondary=lambda: user_to_film,
back_populates='viewers')
user_to_film = sa.Table('user_to_film', Base.metadata,
sa.Column('user_id', sa.Integer, sa.ForeignKey(User.id)),
sa.Column('film_id', sa.Integer, sa.ForeignKey(Film.id)),
)
with create_session() as session:
film: Film = session.query(Film).first()
user = session.query(User).get(1)
film.viewers.append(user)
with create_session() as session:
user: User = session.query(User).get(1)
print(user.films)
# [<Film object at...>]
class FilmRating(Base):
__tablename__ = 'film_rating'
id = sa.Column(sa.Integer, primary_key=True)
user_id = sa.Column(sa.Integer, sa.ForeignKey(User.id))
film_id = sa.Column(sa.Integer, sa.ForeignKey(Film.id))
rating = sa.Column(
sa.Integer,
sa.CheckConstraint('rating > 0 AND rating < 11'))
viewer = so.relationship(User, back_populates='films')
film = so.relationship(Film, back_populates='viewers')
class Film(Base):
__tablename__ = 'film'
id = sa.Column(sa.Integer, primary_key=True)
...
viewers = so.relationship(
'FilmRating',
back_populates='film')
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, primary_key=True)
...
films = so.relationship(
'FilmRating',
back_populates='viewer')
class FilmRating(Base):
...
viewer = so.relationship(User, back_populates='films')
film = so.relationship(Film, back_populates='viewers')
with create_session() as session:
film = session.query(Film).first()
user = session.query(User).get(1)
film_rating = FilmRating(
film=film, viewer=user, rating=8)
with create_session() as session:
user: User = session.query(User).get(1)
for r in user.films:
print(f'{r.film.title}: {r.rating}')
# Джентельмены: 8
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()
FROM alpine
RUN apk update && \
apk add --update python3-dev py3-pip && \
pip3 install --upgrade pip
COPY requirements.txt /tmp/requirements.txt
RUN pip3 install -r /tmp/requirements.txt
WORKDIR /server
COPY ./server /server
CMD ["gunicorn", "app:app", "-b", "0.0.0.0:8000"]
class ImageV1: # docker build -> image:v1
...
container1_v1 = ImageV1() # docker run image:v1
container2_v1 = ImageV1() # docker run image:v1
...
# some time later
class ImageV2: # docker build -> image:v2
...
container1_v2 = ImageV2() # docker run image:v2
container2_v2 = ImageV2() # docker run image:v2
...
docker run --help
# запускаем:
docker run -p 5432:5432 postgres:11.6
# посмотреть, какие контейнеры сейчас подняты:
docker ps
# подключаемся к базе из терминала:
docker exec -it <container_name> psql -U postgres
инструмент для управления несколькими связанными контейнерами: например, контейнер с веб-сервисом и контейнер с базой данных.
version: '2.4'
web-app:
image: my_image_name:version
environment:
- DB_URL=postgresql://postgres:postgres@db/postgres
ports:
- 5000:5000
depends_on:
- db
db:
image: postgres:11.6
environment:
- POSTGRES_DB=postgres
- POSTGRES_PASSWORD=password
ports:
- 5432:5432
docker-compose.yaml