Python.
И СУБД.
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/fz/images/454358/python.png)
Лекция #8
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Почему важно отмечаться на лекции?
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848927/jupyter-logo.png)
notebook.ipynb
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3234297/stop-sign-png-26.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Рейтинг популярности
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3234309/Screen_Shot_2016-11-16_at_00.08.29.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Где хранить данные?
- На диске (HDD, SSD)
- В памяти (in-memory)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3258208/HDD_VS_RAM.jpg)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Модель хранения данных
- Реляционные бд
- Документ-ориентированные
- Key-value хранилища
- Графовые базы данных
- Time series
- Поисковые движки
- Другие
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
CAP-теорема
- Consistency
- Availability
- Partition tolerance
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3259342/download.jpeg)
Определение
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3248562/main-qimg-aa5066d5432bb68e059cff414ed115d9.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Общие понятия
- Язык запросов
- Индексы
- Транзакции
- ACID
- Миграции
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Способы масштабирования
- Вертикальное
- Горизонтальное
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3258205/png_base6468d434762ea2589e.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Репликация
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3250326/Screen_Shot_2016-11-20_at_12.20.27.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Шардинг
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3255408/Screen_Shot_2016-11-21_at_18.00.50.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Популярность
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3234490/Screen_Shot_2016-11-16_at_01.07.06.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Реляционные бд
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3234402/Screen_Shot_2016-11-16_at_00.33.37.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Таблицы, столбцы, строки
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3255289/Screen_Shot_2016-11-21_at_17.39.55.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
SQL
SELECT * FROM users;
INSERT INTO users (name, address)
VALUES ('Alexander','Stavanger, Norway');
UPDATE users SET address="Moscow, Russia"
WHERE name="Alexander";
DELETE FROM users WHERE name="Alexander";
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Первичные ключи
(Primary Key)
CREATE TABLE users (
id int NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
address varchar(255),
PRIMARY KEY (id)
);
Уникально определяет каждую запись в таблице
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Внешние ключи (Foreign Key)
Основной примитив отношений - поле, ссылающееся на первичный ключ другой таблицы
CREATE TABLE City
(
id INTEGER NOT NULL PRIMARY KEY,
name CHAR(40)
)
CREATE TABLE Street
(
id INTEGER NOT NULL PRIMARY KEY,
name CHAR(40),
id_city INTEGER NOT NULL FOREIGN KEY REFERENCES City(id)
)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Отношения (relations)
- Один к одному (One to One)
- Один ко многим/многие к одному (One to Many/Many to One)
- Многие ко многим (Many to Many)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
JOINS
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3234409/sql-joins.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3250341/Screen_Shot_2016-11-20_at_12.33.53.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3250345/Screen_Shot_2016-11-20_at_12.35.34.png)
user:
course:
https://www.sitepoint.com/understanding-sql-joins-mysql-database/
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
INNER JOIN (JOIN)
SELECT user.name, course.name
FROM `user`
INNER JOIN `course` on user.course = course.id;
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3250355/Screen_Shot_2016-11-20_at_12.45.03.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3250361/join-inner.gif)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
LEFT JOIN
SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id;
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3250356/Screen_Shot_2016-11-20_at_12.46.42.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3250363/join-left.gif)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Python – работа с реляционными бд
- Нативные коннекторы (mysqlclient, sqlite3, psycopg2). DB API 2.0
- ORM (SQLAlchemy, Peewee, Django ORM)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3248683/lkG9Vm8.jpg)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Document-oriented
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3234444/Screen_Shot_2016-11-16_at_00.48.30.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Преимущества
- Отсутствие схемы (хранение только нужных атрибутов, гибкость)
- Возможность быстрой записи
- Проще шардировать данные
Эти преимущества могут быть и недостатками - "know your business"
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
PyMongo
from pymongo import MongoClient
client = MongoClient()
db = client.test
result = db.restaurants.insert_one(
{
"address": {
"street": "2 Avenue",
"zipcode": "10075",
"building": "1480",
"coord": [-73.9557413, 40.7720266]
},
"grades": ["A", "B"]
}
)
cursor = db.restaurants.find({"address.zipcode": "10075"})
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Поисковые движки
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3250451/Screen_Shot_2016-11-20_at_13.29.10.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Time-series
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3250462/Screen_Shot_2016-11-20_at_13.31.03.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Отправка в Graphite
import socket
import time
CARBON_SERVER = '127.0.0.1'
CARBON_PORT = 2003
ts = int(time.time()
message = 'passkeeper.response_time 420 %d\n' % ts)
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.connect((CARBON_SERVER, CARBON_PORT))
sock.sendall(message)
sock.close()
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Grafana
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3259195/nice_dashboard.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Column-oriented
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3250403/Screen_Shot_2016-11-20_at_13.12.11.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Преимущества
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3259127/Screen_Shot_2016-11-22_at_14.17.12.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3259181/Screen_Shot_2016-11-22_at_14.36.19.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3259184/Screen_Shot_2016-11-22_at_14.36.55.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
cassandra-driver
from cassandra.cluster import Cluster
from cassandra.policies import RoundRobinPolicy
from cassandra.query import SimpleStatement
cluster = Cluster(
['10.1.1.3', '10.1.1.4', '10.1.1.5'],
load_balancing_policy=RoundRobinPolicy(),
port=9042)
session = cluster.connect('mykeyspace')
rows = session.execute('SELECT name, age, email FROM users')
for user_row in rows:
print user_row.name, user_row.age, user_row.email
query = SimpleStatement(
"INSERT INTO users (name, age) VALUES (%s, %s)",
consistency_level=ConsistencyLevel.QUORUM)
session.execute(query, ('John', 42))
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Key-value
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3234452/Screen_Shot_2016-11-16_at_00.51.18.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Другие интересные бд
- RocksDB
- CockroachDB
- Tarantool
- Aerospike
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Python – работа с NoSQL бд
Свой коннектор для каждой базы данных, никакой абстракции
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/3250526/3993521.jpg)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/93691/images/2848305/technopark-mail-mgtu.png)
Спасибо!
Python_10
By Emelin Alexander
Python_10
- 1,038