6 СЕМЕСТР / ЛР / ЛР1 / db LR1

import matplotlib
import psycopg2 as psy
import pandas as pd
import matplotlib.pyplot as plt
from matplotlib.dates import DateFormatter


# Подключение к БД
db_connect_kwargs = {
    'dbname': 'musicalgroup',
    'user': 'postgres',
    'password': '12345678qwerty',
    'host': 'localhost',
    'port': '5432'
}

    # Вывод таблицы
try:
    connection = psy.connect(**db_connect_kwargs)
    connection.autocommit = True
    cursor = connection.cursor()

    # Запрос на вывод таблицы
    cursor.execute('SELECT * FROM musicalgroup')
    rows = cursor.fetchall()

    df_musicalgroup = pd.DataFrame(rows)
    print(df_musicalgroup)

    # Запрос 1
    cursor.execute('''SELECT concertname, count(ticketid)
                    FROM ticket
                    JOIN concert USING(concertid)
                    GROUP BY concertname
                    ORDER BY 2 DESC''')
    rows = cursor.fetchall()
    df_zap_1 = pd.DataFrame(rows)
    print(df_zap_1)

    # График запроса 1
    fig, ax = plt.subplots()
    plt.title('Кол-во билетов проданых на концерты')
    ax.set_xlabel('Название концерта')
    ax.set_ylabel('Кол-во билетов')
    ax.xaxis.set_major_formatter(DateFormatter("%d/%m/%y"))
    ax.grid()
    plt.bar(df_zap_1[0], df_zap_1[1])
    plt.tight_layout()
    plt.show()


    # Запрос 2
    cursor.execute('''SELECT OrderDate,
		                count(OrderDate) ticket_count,
		                sum(Price) price_sum,
		                ROUND(avg(Price), 2) avg_price
                    FROM Orders
	                    JOIN ticket USING(TicketID)
		                JOIN location USING(LocationID)
		            GROUP BY OrderDate
		            ORDER BY avg_price DESC''')
    rows = cursor.fetchall()
    df_zap_2 = pd.DataFrame(rows)
    print(df_zap_2)

    # График запроса 2
    fig, ax = plt.subplots()
    plt.title('Объем продаж по дням')
    ax.set_xlabel('Дата')
    ax.set_ylabel('Сумма продаж')
    ax.grid()
    plt.tick_params(axis='x',rotation=45)
    plt.scatter(df_zap_2[0], df_zap_2[2])
    plt.tight_layout()
    plt.show()


    # Запрос 3
    cursor.execute('''SELECT BandName,
	                    count(*) as concert_count
                    FROM MusicalGroup
	                    JOIN Concert USING(MusicalGroupID)
	                    JOIN Contract USING(ConcertID)
                    WHERE ConcertDate BETWEEN '2021-10-15' AND '2021-12-15'
                    GROUP BY BandName''')
    rows = cursor.fetchall()
    df_zap_3 = pd.DataFrame(rows)
    print(df_zap_3)

    # График запроса 3
    fig, ax = plt.subplots()
    plt.title('Доля концертов по группам проведенных с 2021-10-15 по 2021-12-15')
    plt.pie(df_zap_3[1], labels=df_zap_3[0], autopct='%.1f%%')
    plt.tight_layout()
    plt.show()


except Exception as _ex:
    print('[INFO] Error while working with PostgreSQL', _ex)
finally:
    if connection:
        connection.close()
        cursor.close()
        print("[INFO] PostgreSQL connection closed")