Установка и запуск PostgreSQL в Jupyter Notebook
| Навигация по SQL |
Базы данных хранят и обрабатывают огромный объем информации. Также базы данных играют важную роль в безопасности хранения и конфиденциальности этих данных. По структуре организации базы данных делятся на реляционные и нереляцонные. Реляционные базы данных хранят структурированные данные в таблицах. Каждая строка в такой таблице состоит из определенного количества столбцов фиксированного размера и описывает определенный объект. Для работы с реляционными базами данных используется язык структурированных запросов SQL (Structured Query Language).
Реляционные системы управления базами данных хранят данные в таблицах и определяют отношения между таблицами. Язык SQL практически везде используется для работы с этими данными, и выполнение запросов, извлекающих информацию по заданному критерию. Наиболее популярны системы управления данными с открытым кодом это SQLite, MySQL, PostgreSQL, MarinaDB. Любой желающий может установить и свободно пользоваться ими. Для всех перечисленных баз данных существует поддержка Python. Во всех дальнейших примерах я буду использовать PostgreSQL.
| Содержание страницы: |
1. Установка PostgreSQL
PostgreSQL — это мощная система объектно-реляционных баз данных с открытым исходным кодом, которая использует и расширяет язык SQL в сочетании со многими функциями, позволяющими безопасно хранить и масштабировать самые сложные рабочие нагрузки данных. Разрабатывается ужу более 35 лет и заслуживает прочную репутацию за надежность, надежность функций и производительность. Скачать PostgreSQL с официального сайта абсолютно бесплатно - https://www.postgresql.org/. При первой установке будет создана база данных и создан пользователь с паролем.
Так как после установки база данных PostgreSQL не содержит данных, установим демонстрационную базу данных, скаченную с официального сайта СУБД Postgres Pro. В качестве предметной области выбраны авиаперевозки по России. Скачать эту базу данных вы можете по этой ссылке. Далее во всех примерах используются данные по полетам за год из этой базы данных.
Скачиваем файл с базой данных в любую папку. К примеру:
C:\Users\Desktop\postgres
Затем запускаем командную строку Windows(cmd) и переходим в папку с базой данных:
Microsoft Windows [Version 10.0.19044.2130]
(c) Корпорация Майкрософт (Microsoft Corporation). Все права защищены.
C:\Users\>cd C:\Users\Desktop\postgres
C:\Users\Desktop\postgres>
Далее выполняем команду для загрузки базы данных из sql-файла:
"C:\Program Files\PostgreSQL\14\bin\psql" -U *ИМЯ ПОЛЬЗОВАТЕЛЯ* -f demo_big.sql
-U – имя пользователя, которое указывали при установке
-f - название файла с базой данных
Затем нужно будет ввести пароль пользователя, и установка начнется.
В конце установки вы должны увидеть:
........
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
REFRESH MATERIALIZED VIEW
База данных установлена.
2. Диаграмма схемы данных
Скачанная с сайта СУБД Postgres Pro база данных Bookings состоит из 8 таблиц, связанные между собой. Все таблицы связаны между собой по первичным или внешним ключам. Диаграмма представлена ниже:
Основной сущностью является бронирование (bookings). В одно бронирование можно включить несколько пассажиров, каждому из которых выписывается отдельный билет (tickets). Билет имеет уникальный номер и содержит информацию о пассажире. Как таковой пассажир не является отдельной сущностью. Как имя, так и номер документа пассажира могут меняться с течением времени, так что невозможно однозначно найти все билеты одного человека; для простоты можно считать, что все пассажиры уникальны. Билет включает один или несколько перелетов (ticket_flights). Несколько перелетов могут включаться в билет в случаях, когда нет прямого рейса, соединяющего пункты отправления и назначения (полет с пересадками), либо когда билет взят «туда и обратно». В схеме данных нет жёсткого ограничения, но предполагается, что все билеты в одном бронировании имеют одинаковый набор перелетов. Каждый рейс (flights) следует из одного аэропорта (airports) в другой. Рейсы с одним номером имеют одинаковые пункты вылета и назначения, но будут отличаться датой отправления. При регистрации на рейс пассажиру выдаётся посадочный талон (boarding_passes), в котором указано место в самолете. Пассажир может зарегистрироваться только на тот рейс, который есть у него в билете. Комбинация рейса и места в самолете должна быть уникальной, чтобы не допустить выдачу двух посадочных талонов на одно место. Количество мест (seats) в самолете и их распределение по классам обслуживания зависит от модели самолета (aircrafts), выполняющего рейс. Предполагается, что каждая модель самолета имеет только одну компоновку салона. Схема данных не контролирует, что места в посадочных талонах соответствуют имеющимся в самолете (такая проверка может быть сделана с использованием табличных триггеров или в приложении).
Описание таблицы взято с сайта СУБД Postgres Pro.
3. Подключение к PostgreSQL через Jupyter Notebook
При обучении или анализе данных с БД можно работать различными способами. Одним из таких способов является работа с помощью Jupyter Notebook. Jupyter Notebook – это легкий и удобный инструмент для работы с любыми данными, в том числе изображениями. После подключения БД в Jupyter Notebook вы можете в нем редактировать запросы, анализировать таблицы и строить различные графики, не вводя код по много раз. Jupyter Notebook поддерживает большинство языков программирования.
Для подключения к БД понадобится установить библиотеки psycopg2 и pandas. Импортируем их в начале:
import psycopg2 as ps
import pandas as pd
Создаем подключение:
conn = ps.connect(host="localhost", port = 5432, database="demo", user="******", password="*****", options="-c search_path=bookings")
host - расположение БД, если на вашем компьютере, выбираете localhost
port - при установке БД в основном сразу забивается 5432
database - имя вашей БД
user и password - данные вашего пользователя
options - этим атрибутом мы изменяем конфигурационный параметр search_path. Так все таблицы БД находятся в схеме bookings, то нам бы пришлось при каждом запросе прописывать адрес bookings.flights или bookings.aircrafts. После изменения, мы сможем напрямую обращаться к этим таблицам.
Теперь можно работать с этой БД. В примере выведем таблицу flights:
df_flights = pd.read_sql("SELECT * FROM flights LIMIT 5", con=conn)
df_flights
| flight_id | flight_no | scheduled_departure | scheduled_arrival | departure_airport | arrival_airport | status | aircraft_code | actual_departure | actual_arrival | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | PG0403 | 2016-08-11 07:25:00+00:00 | 2016-08-11 08:20:00+00:00 | DME | LED | Arrived | 321 | 2016-08-11 07:29:00+00:00 | 2016-08-11 08:24:00+00:00 |
| 1 | 2 | PG0404 | 2016-08-11 15:05:00+00:00 | 2016-08-11 16:00:00+00:00 | DME | LED | Arrived | 321 | 2016-08-11 15:11:00+00:00 | 2016-08-11 16:06:00+00:00 |
| 2 | 3 | PG0405 | 2016-08-11 05:35:00+00:00 | 2016-08-11 06:30:00+00:00 | DME | LED | Arrived | 321 | 2016-08-11 05:38:00+00:00 | 2016-08-11 06:33:00+00:00 |
| 3 | 4 | PG0402 | 2016-04-10 08:25:00+00:00 | 2016-04-10 09:20:00+00:00 | DME | LED | Arrived | 321 | 2016-04-10 08:30:00+00:00 | 2016-04-10 09:26:00+00:00 |
| 4 | 5 | PG0403 | 2016-04-10 07:25:00+00:00 | 2016-04-10 08:20:00+00:00 | DME | LED | Arrived | 321 | 2016-04-10 07:28:00+00:00 | 2016-04-10 08:22:00+00:00 |
Выведем таблицу aircrafts:
df_aircraft = pd.read_sql("SELECT * FROM aircrafts LIMIT 5", con=conn)
df_aircraft
| aircraft_code | model | range | |
|---|---|---|---|
| 0 | 773 | Boeing 777-300 | 11100 |
| 1 | 763 | Boeing 767-300 | 7900 |
| 2 | SU9 | Sukhoi SuperJet-100 | 3000 |
| 3 | 320 | Airbus A320-200 | 5700 |
| 4 | 321 | Airbus A321-200 | 5600 |
С помощью Jupyter Notebook все запросы легко редактировать и добавлять различные функции.