МИНИСТЕРСТВО НАУКИ И ВЫСШЕГО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ
федеральное государственное автономное образовательное учреждение высшего образования
«САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ АЭРОКОСМИЧЕСКОГО ПРИБОРОСТРОЕНИЯ»
ИНСТИТУТ НЕПРЕРЫВНОГО И ДИСТАНЦИОННОГО ОБРАЗОВАНИЯ
КАФЕДРА 41
|
ОЦЕНКА
ПРЕПОДАВАТЕЛЬ
канд.тех.наук, доцент |
|
|
|
Е. Л. Турнецкая |
должность, уч. степень, звание |
|
подпись, дата |
|
инициалы, фамилия |
ОТЧЕТ О ЛАБОРАТОРНОЙ РАБОТЕ №7
|
СВЯЗЬ СУБД POSTGRESQL И PYTHON
|
по дисциплине: Базы данных |
РАБОТУ ВЫПОЛНИЛ
СТУДЕНТ ГР. № |
Z9411 |
|
|
|
Р. С. Кафка |
|
номер группы |
|
подпись, дата |
|
инициалы, фамилия |
Студенческий билет № |
2019/3603 |
|
|
|
|
Санкт-Петербург 2023
СОДЕРЖАНИЕ
1. Цель работы 3
2. Вариант задания 3
3. Ход работы 4
3.1. Подключение к СУБД PostgreSQL 4
3.2. Создание новой таблицы с помощью Python и заполнение её данными 5
3.3. Запросы в Python 8
3.4. Создание функции select_data 12
3.5. Создание собственной пользовательской функции 14
ЗАКЛЮЧЕНИЕ 16
СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ 17
ПРИЛОЖЕНИЕ А 18
Цель работы
Произвести связь базы данных в PostgreSQL и Python, изучить операции по манипулированию с данными БД, а также созданию простейших пользовательских функций.
Вариант задания
В качестве темы работы используется БД, созданная в предыдущей ЛР (employees, departments, jobs).
Схема данных представлена на рисунке 1.
Рисунок
1 – Схема данных
Вариант задания №9.
Формулировка запросов:
Выяснить, сотрудники какой профессии получают зарплату меньше 2500.
Вывести сотрудников и их начальников (в одном столбце расположены сотрудники, во втором – их начальники).
Ход работы
Подключение к субд PostgreSql
С помощью кода, представленного в листинге 1, было успешно установлено подключение к СУБД PostgreSQL, вывод свойства соединения и версии СУБД. Результат представлен на рисунке 2.
Листинг 1 – Соединение Python с БД в PostgreSQL и вывод свойств
-
import pandas as pd
import psycopg2
# Подключение к базе данных:
connection = psycopg2.connect(database="students",
user="postgres",
password="123",
host="127.0.0.1",
port="5432")
cursor = connection.cursor() # курсор для выполнения операций с БД
print(connection.get_dsn_parameters(), "\n") # вывод свойства соединения
cursor.execute("SELECT version();") # выполнение запроса к БД
version_ps = cursor.fetchone() # получение результата запроса
print("Вы подключены к - ", version_ps, "\n")
cursor.close()
connection.close()
Рисунок
2 – Соединение Python с БД в PostgreSQL и вывод
свойств
Создание новой таблицы с помощью Python и заполнение её данными
С помощью кода, указанного в листинге 2, создал новую таблицу locations.
Листинг 2 – Создание таблицы locations
-
cursor.execute('''CREATE TABLE if not exists public.locations
(location_id int PRIMARY KEY,
city varchar(30),
postal_code varchar(12)
); ''')
connection.commit()
Проверил, что таблица появилась в СУБД PostgreSQL (рисунок 3).
Рисунок
3 – Создание таблицы locations
Заполнил таблицу данными, представленными в листинге 3 и вывел результат.
Листинг 3 – Заполнение таблицы locations данными
-
cursor.execute('''
INSERT INTO public.locations VALUES ( 1,'Roma', '00989');
INSERT INTO public.locations VALUES ( 2,'Venice','10934');
INSERT INTO public.locations VALUES ( 3,'Tokyo', '1689');
INSERT INTO public.locations VALUES ( 4,'Hiroshima','6823');
INSERT INTO public.locations VALUES ( 5,'Southlake', '26192');
INSERT INTO public.locations VALUES ( 6,'South San Francisco', '99236');
INSERT INTO public.locations VALUES ( 7,'South Brunswick','50090');
INSERT INTO public.locations VALUES ( 8,'Seattle','98199');
INSERT INTO public.locations VALUES ( 9,'Toronto','M5V 2L7');
INSERT INTO public.locations VALUES ( 10,'Whitehorse','YSW 9T2');
''')
connection.commit()
Проверил, что таблица также заполнена в СУБД PostreSQL (рисунок 4).
Рисунок
4 – Заполнение таблицы locations
данными
Добавил в таблицу employees столбец «location_id», добавил связь с таблицей locations, заполнил столбец «location_id» данными. Код представлен в листинге 4.
Листинг 4 – Добавление столбца в таблицу с сотрудниками и заполнение данными
-
import psycopg2
import random
connection = psycopg2.connect(database="students",
user="postgres",
password="123",
host="127.0.0.1",
port="5432")
cursor = connection.cursor()
# Add location_id column to employees table
cursor.execute("ALTER TABLE public.employees ADD COLUMN location_id int REFERENCES public.locations (location_id)")
# Fetch all location_id from locations table
cursor.execute("SELECT location_id FROM public.locations")
location_ids = cursor.fetchall()
location_ids = [location_id[0] for location_id in location_ids]
# Update location_id for each employee randomly
for i in range(1, 54):
cursor.execute(f"UPDATE public.employees SET location_id={random.choice(location_ids)} WHERE employee_id={i}")
connection.commit()
cursor.close()
connection.close()
Код подключается к базе данных PostgreSQL "students" и добавляет к таблице "employees" столбец «location_id». Между таблицами "employees" и "locations" определяется связь "location_id", где "location_id" в таблице " employees " связан с "location_id" в таблице "locations".
Затем код фиксирует все возможные "location_id" из таблицы «locations», сохраняет их. После вызывается цикл по всем сотрудникам (их 53 штуки) и каждому сотруднику присваивается случайная локация, код заполняет столбец "location_id" таблицы "employees" случайными данными от 1 до 10 (количество мест) с помощью функции random.randint().
Результат работы представлен на рисунке 5.
Рисунок
5 – Добавление столбца в таблицу с
сотрудниками и заполнение данными