Тестовое задание

Необходимо разработать некоторые части приложения для учета книг в библиотеке. Описание данных, с которыми будет работать приложение – ниже.

Приложение для учета книг в библиотеке должно:

Хранить названия книг, ФИО авторов, наименования издательств, год издания.
Учитывать имеющиеся в библиотеке экземпляры конкретной книги.
Учитывать студентов, которым выдавалась конкретная книга. При каждой выдаче книги студенту, фиксируется дата выдачи. При возврате – дата возврата книги.

Задания:

Опишите модель данных (в любом удобном для вас представлении) для обслуживания библиотеки. Это может быть описание таблиц с типами данных, диаграмма – что угодно.

Указанную модель данных удобно представить в виде следующей ER-диаграммы:

Создадим базу данных на основе данной модели:

import psycopg2 as pg
import pandas as pd

class DBexecutor:
    
    def __init__(self, params):
        self.params = params
        self.conn, self.curs = self.connect_db()
        
    def connect_db(self):
        try:
            conn = pg.connect(**self.params)
            conn.set_session(autocommit=True)
            if conn:
                curs = conn.cursor()
                return conn, curs
        except Exception:
            print('Ошибка подключения к БД')
            return None, None
        
    def __del__(self):
        if self.conn:
            self.curs.close()
            self.conn.close()

params = {
    'dbname': 'library',
    'user': 'postgres',
    'password': ''
        }

db = DBexecutor(params)

db_schema = [
            """CREATE TABLE first_name (id serial PRIMARY KEY, first_name varchar(100));""",
            """CREATE TABLE middle_name (id serial PRIMARY KEY, middle_name varchar(100));""",
            """CREATE TABLE last_name (id serial PRIMARY KEY, last_name varchar(100));""",
            """CREATE TABLE faculty (id serial PRIMARY KEY, faculty varchar(100));""",
            """CREATE TABLE book_name (id serial PRIMARY KEY, book_name varchar(100));""",
            """CREATE TABLE publishing (id serial PRIMARY KEY, publishing varchar(100));""",
            """CREATE TABLE student (id serial PRIMARY KEY, first_name_id 
                INTEGER REFERENCES first_name(id), middle_name_id 
                INTEGER REFERENCES  middle_name(id), last_name_id 
                INTEGER REFERENCES  last_name(id), faculty_id 
                INTEGER REFERENCES  faculty(id));""",
            """CREATE TABLE author (id serial PRIMARY KEY, first_name_id 
                INTEGER REFERENCES first_name(id), middle_name_id 
                INTEGER REFERENCES  middle_name(id), last_name_id 
                INTEGER REFERENCES  last_name(id));""",
            """CREATE TABLE books (id serial PRIMARY KEY, name_id 
            INTEGER REFERENCES book_name(id), author_id 
            INTEGER REFERENCES  author(id), publishing_id 
            INTEGER REFERENCES  publishing(id), year date);""",
            """CREATE TABLE books_students (id serial PRIMARY KEY, book_id 
            INTEGER REFERENCES books(id), student_id 
            INTEGER REFERENCES  student(id), date_of_issue date, date_of_return date);"""
            ]

for query in db_schema:
    db.curs.execute(query)

Наполнение базы данных происходит из файлов authors, books, publishing и faculty, соответствующий код приведен в файле parse.py

Напишите SQL-запрос, который бы возвращал самого популярного автора за год. Запрос должен основываться на модели данных, которую вы описали в задании 1.

Запишем указанный запрос и, для наглядности, выполним его средствами Python:

df = pd.read_sql("""
                select first_name, middle_name, last_name
                from books_students bs
                inner join books b on book_id = b.id
                inner join author a2 on author_id = a2.id
                inner join first_name fn on first_name_id = fn.id
                inner join middle_name mn on middle_name_id = mn.id
                inner join last_name ln2 on last_name_id = ln2.id
                where bs.date_of_issue between '2019-01-01' and '2019-12-31'
                group by first_name, middle_name, last_name 
                order by count(bs.id) desc
                limit 1;
                """, db.conn)

df

	first_name	middle_name	last_name
0	Моисей	Павлович	Венгров

Определите понятие «злостный читатель». Предложите алгоритм для поиска самого злостного читателя библиотеки. На любом языке программирования опишите алгоритм поиска такого читателя. Алгоритм должен основываться на модели данных, которую вы описали в задании 1.

Определим понятие «злостный читатель» - пусть «злостным» является такой читатель, который с начала года допускал задержку возврата книг, в среднем, более чем на 14 дней. Максимально допустимый срок возврата книг примем равным 30 дням. Выполним запрос для поиска таких читателей в БД:

db = DBexecutor(params)

df = pd.read_sql("""
                select first_name, middle_name, last_name, faculty 
                from books_students bs 
                inner join student s on student_id = s.id
                inner join first_name fn on first_name_id = fn.id
                inner join middle_name mn on middle_name_id = mn.id
                inner join last_name ln2 on last_name_id = ln2.id
                inner join faculty f on faculty_id = f.id
                where bs.date_of_issue between '2020-01-01' and '2020-06-26'
                group by first_name, middle_name, last_name, faculty 
                having sum(date_of_return - date_of_issue - 30) / count(bs.id) > 14
                order by last_name::bytea;
                """, db.conn)
df

	first_name	middle_name	last_name	faculty
0	Вениамин	Магафурович	Айпин	Факультет радиотехники и электроники (РЭФ)
1	Абрам	Трифонович	Амусин	Физико-технический факультет (ФТФ)
2	Владимир	Вадимовна	Балков	Факультет гуманитарного образования (ФГО)
3	Татьяна	Лазаревич	Белецкий	Факультет гуманитарного образования (ФГО)
4	Валерий	Максович	Гейченко	Факультет энергетики (ФЭН)
5	Андрей	Матвеевич	Иванченко	Факультет летательных аппаратов (ФЛА)
6	Виталий	Семёновна	Мартынов	Факультет гуманитарного образования (ФГО)
7	Кронид	Прокопьевич	Оношко	Факультет бизнеса (ФБ)
8	Рувим	Бертильевич	Пельский	Факультет прикладной математики и информатики ...
9	Рувим	Фомич	Писахов	Факультет мехатроники и автоматизации (ФМА)
10	София	Арсеньевич	Черман	Механико -технологический факультет (МТФ)

В с помощью данного запроса мы объединяем таблицы books_students, student, first_name, middle_name, last_name и faculty, группируем записи по ФИО студентов, для каждой полученной группы выполняем расчет отношения числа дней просрочки к числу записей о получении и возврате книги и фильтруем полученный результат, отсекая значения менее 14.

Аналогичного результата мы можем достичь запросив данные из БД и выполнив дальнейшую обработку средствами Python:

import datetime

db = DBexecutor(params)

df = pd.read_sql("""
                select * 
                from books_students bs 
                inner join student s on student_id = s.id
                inner join first_name fn on first_name_id = fn.id
                inner join middle_name mn on middle_name_id = mn.id
                inner join last_name ln2 on last_name_id = ln2.id
                inner join faculty f on faculty_id = f.id
                """, db.conn)
data = df[['date_of_issue', 'date_of_return', 'first_name', 'middle_name', 'last_name', 'faculty']]
filter_after = data['date_of_issue'] >= datetime.date(2020, 1, 1)
filter_before = data['date_of_issue'] <= datetime.date(2020, 6, 26)
data = data.loc[filter_before & filter_after]

data['delta'] = data['date_of_return'] - data['date_of_issue'] - pd.Timedelta(30, unit='d')
group_data = data.groupby(['first_name', 'middle_name', 'last_name', 'faculty']).agg(['sum', 'count']).reset_index()
group_data['av_delay'] = group_data[['delta'][0]]['sum'] / group_data[['delta'][0]]['count']
filter_delay = group_data['av_delay'].dt.round(freq = 'D') > pd.Timedelta(14, unit='d')
group_data.loc[filter_delay].sort_values('last_name').reset_index()[['first_name', 'middle_name', 'last_name', 'faculty']]

	first_name	middle_name	last_name	faculty

0	Вениамин	Магафурович	Айпин	Факультет радиотехники и электроники (РЭФ)
1	Абрам	Трифонович	Амусин	Физико-технический факультет (ФТФ)
2	Владимир	Вадимовна	Балков	Факультет гуманитарного образования (ФГО)
3	Татьяна	Лазаревич	Белецкий	Факультет гуманитарного образования (ФГО)
4	Валерий	Максович	Гейченко	Факультет энергетики (ФЭН)
5	Андрей	Матвеевич	Иванченко	Факультет летательных аппаратов (ФЛА)
6	Виталий	Семёновна	Мартынов	Факультет гуманитарного образования (ФГО)
7	Кронид	Прокопьевич	Оношко	Факультет бизнеса (ФБ)
8	Рувим	Бертильевич	Пельский	Факультет прикладной математики и информатики ...
9	Рувим	Фомич	Писахов	Факультет мехатроники и автоматизации (ФМА)
10	София	Арсеньевич	Черман	Механико -технологический факультет (МТФ)

del db

homutovan / my_library Goto Github PK

my_library's Introduction

Тестовое задание

Задания:

my_library's People

Contributors

Watchers

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent