Аналитика из скрипта и палок: пример генерации PDF-отчета из БД

Как получить статистику по данным и графики из PostgreSQL: пример создания своего PDF-отчета средствами Python-библиотеки matplotlib. Пишем простой скрипт в Google Colab для анализа операционной деятельности интернет-магазина.

Постановка задачи

Сегодня рассмотрим не слишком типичный для работы системного и бизнес-аналитика случай, когда необходимо вручную получить данных из БД, чтобы сделать предложить варианты улучшения бизнеса. Чтобы выполнить такой анализ и сформировать рекомендации, необходимо иметь аналитику, т.е. фактические данные. Причем их получить сразу в агрегированном и презентативном виде, например, PDF-отчет. Как обычно, в качестве примера я возьму базу данных своего ранее спроектированного и реализованного интернет-магазина, которая имеет следующую схему для PostgreSQL:

Предположим, необходимо сделать выводы об операционной деятельности магазина по следующим данным за определенный период:

• количество заказов;
• максимальная, минимальная и средняя сумма чека;
• общая сумма дохода, т.е. выручки от продажи товаров;
• помесячная выручка и количество заказов;
• самые часто покупаемые товары.

Итоговый отчет в виде неизменяемого PDF-файла должен содержать все вышеперечисленные сведения в текстовой и графической формах. Например, для отображения помесячной выручки отлично подходят гистограммы, а для оценки популярности конкретного товара относительно общего числа продаж – круговая диаграмма. Все эти и другие виды графиков поддерживает Python-библиотека matplotlib. Кроме функций для работы с двумерной и трехмерной графикой, она также позволяет формировать PDF-документ с текстовыми, табличными, численными и графическими данными. Поэтому для решаемой задачи будем использовать именно ее, что и рассмотрим далее, сделав небольшой переход из роли аналитика в роль разработчика.

Разработка ТЗ на информационную систему по ГОСТ и SRS

Код курса

TTIS

Ближайшая дата курса

10 ноября, 2025

Продолжительность

22 ак.часов

Стоимость обучения

48 000 руб.

 

Реализация PDF-отчета с matplotlib

Как обычно, напишу и запущу Python-скрипт в Google Colab. Скрипт будет состоять из 3-х ячеек:

• ячейка, выполняемая один раз для импорта пакетов, создания директории для сохранения отчетов и установки значений глобальных переменных;
• ячейка с объявлением функций обращения к БД для выполнения SQL-запросов – разумеется, при отладке эта ячейка выполняется несколько раз, но после того, как достигнута корректная работа всех функций, ее можно запускать однократно;
• ячейка с формированием PDF-отчета по заданному периоду. Поскольку именно эта ячейка генерирует PDF-отчет, она запускается многократно, каждый раз, когда необходимо получить этот документ. Имя файла будет содержать текущую дату.

Python-код выглядит следующим образом:

###############################################ячейка №1 в Google Colab#############################
#импорт пакетов
import matplotlib.pyplot as plt
from matplotlib.backends.backend_pdf import PdfPages
import datetime
import pandas as pd
import psycopg2
import traceback
import threading
from google.colab import files

#############################################
global connection_string #глобальная переменная строка подключения 
connection_string = 'postgres://username:password@v-host.neon.tech/neondb' #ваша строка подключения к БД

#создание директории для сохранения отчетов
!mkdir -p /content/reports

###################################################ячейка №2 в Google Colab########################################################################
# Функция получения помесячной выручки из БД (сумма всех заказов, сгруппированных по месяцам)
def db_read_monthly_income_count(start, finish):
    conn = psycopg2.connect(connection_string)
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT to_char(orders.date, 'Month') AS mes, SUM(orders.sum) AS income, COUNT(orders.sum) AS quantity
            FROM orders
            WHERE EXTRACT(MONTH FROM orders.date) BETWEEN %s::integer AND %s::integer
            GROUP BY mes
            ORDER BY (MIN(orders.date))
        """, (start, finish))
        results = cursor.fetchall()
        return results
    except Exception as e:  # ловить конкретные исключения и сохранять информацию об исключении в переменную 'e'
        traceback.print_exc()
        return 'Ошибка при получении данных из базы данных'
    finally:
        cursor.close() #закрыть курсор, который выполняет запросы
        conn.close()  #закрыть соединение с базой данных

# Функция получения больше всего востребованных товаров из БД (тех товаров во всех заказах, которых купили больше 500 единиц)
def db_read_products_statiscics_count(start, finish):
    conn = psycopg2.connect(connection_string)
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT product.name AS tovar, SUM(order_product.quantity) AS kolichestvo
            FROM order_product
            JOIN product on product.id=order_product.product
            JOIN orders on orders.id=order_product.order
            WHERE (EXTRACT(MONTH FROM orders.date) BETWEEN %s::integer AND %s::integer)
            GROUP BY tovar
            HAVING SUM(order_product.quantity)>500
            ORDER BY kolichestvo
        """, (start, finish))
        results = cursor.fetchall()
        return results
    except Exception as e:  # ловить конкретные исключения и сохранять информацию об исключении в переменную 'e'
        traceback.print_exc()
        return 'Ошибка при получении данных из базы данных'
    finally:
        cursor.close() #закрыть курсор, который выполняет запросы
        conn.close()  #закрыть соединение с базой данных

# Функция получения распределения данных по сумме заказа
def db_read_orders_stat(start, finish):
    conn = psycopg2.connect(connection_string)
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT orders.sum AS summa
            FROM orders
            WHERE EXTRACT(MONTH FROM orders.date) BETWEEN %s::integer AND %s::integer
        """, (start, finish))
        results = cursor.fetchall()
        return results
    except Exception as e:  # ловить конкретные исключения и сохранять информацию об исключении в переменную 'e'
        traceback.print_exc()
        return 'Ошибка при получении данных из базы данных'
    finally:
        cursor.close() #закрыть курсор, который выполняет запросы
        conn.close()  #закрыть соединение с базой данных

###############################################ячейка №3 в Google Colab############################################################################
start = 1 #месяц начала сбора статистики
finish = 12 #месяц окончания сбора статистики

# Создание DataFrame  помесячной выручки и вывод его на экран
df = pd.DataFrame(db_read_monthly_income_count(start, finish), columns=['МЕСЯЦ', 'ДОХОД', 'КОЛИЧЕСТВО ЗАКАЗОВ'])

# Создание DataFrame самых востребованных товаров из БД и вывод его на экран
df2 = pd.DataFrame(db_read_products_statiscics_count(start, finish), columns=['ТОВАР', 'КОЛИЧЕСТВО'])

# Создание DataFrame суммы заказа и вывод его на экран
df3 = pd.DataFrame(db_read_orders_stat(start, finish), columns=['summa'])

# Получаем текущую дату
current_date = datetime.datetime.now()

# Форматируем дату в строку (например, '2023-03-15')
date_str = current_date.strftime('%Y-%m-%d')

# Создание PDF-файла для сохранения графиков и таблиц. В название файла включаем текущую дату
filename = '/content/reports/report_' + date_str + '.pdf'

# Запись данных в PDF-файл
with PdfPages(filename) as pdf:
    # Рассчитаем статистику по суммам заказа
    kolvo=len(df3['summa']) #количество наблюдений
    df_max = df3['summa'].max() #максимум
    df_min=df3['summa'].min() #минимум
    df_mean = df3['summa'].mean() #среднее значение
    df_sum=df3['summa'].sum() #сумма

    # упаковываем статистику в датафрейм для отображения в таблице
    df_stats = pd.DataFrame({
        'Показатель': ['ВСЕГО заказов', 'Максимальный чек', 'Минимальный чек', 'Среднее значение', 'ИТОГО общая сумма дохода'],
        'Значение': [kolvo, df_max, df_min, df_mean, df_sum]
    })

    # Создание фигуры и осей для таблицы
    fig_table, ax_table = plt.subplots(figsize=(8, 3))  # Выберите подходящий размер фигуры

    # Задание заголовка
    ax_table.set_title(f"Статистика по сумме заказов, с {start}-го месяца по {finish}-й месяц")

    # Задание содержимого таблицы со статистикой
    ax_table.axis('tight') #задаем границы области для таблицы так, чтобы они плотно обрамляли содержимое
    ax_table.axis('off') #выключаем отображение осей для таблицы (нет границ и делений)
    table = ax_table.table(cellText=df_stats.values, colLabels=df_stats.columns, loc='center') #задаем содержимое ячеек таблицы, заголовки столбцов и расположение таблицы
    table.set_fontsize(10) #устанавливаем размер шрифта для текста в таблице вручную
    table.scale(1.2, 1.2)  # Можно изменить масштаб таблицы для лучшего отображения

    # Сохранение в pdf и закрытие страницы в файле, чтобы освободить память, связанную с этим объектом Figure в Matplotlib
    pdf.savefig(fig_table)
    plt.close(fig_table)

# подробности по месячной выручке
    # Создание фигуры и осей для таблицы
    fig_table, ax_table = plt.subplots(figsize=(8, 6))  # Выберите подходящий размер фигуры

    # Задание заголовка
    ax_table.set_title(f"Доходы по месяцам, с {start}-го месяца по {finish}-й месяц")

    # Задание содержимого таблицы со статистикой
    ax_table.axis('tight') # задаем границы области для таблицы так, чтобы они плотно обрамляли содержимое
    ax_table.axis('off') # выключаем отображение осей для таблицы (нет границ и делений)
    table = ax_table.table(cellText=df.values, colLabels=df.columns, loc='center') #задаем содержимое ячеек таблицы, заголовки столбцов и расположение таблицы
    table.set_fontsize(10) # устанавливаем размер шрифта для текста в таблице вручную
    table.scale(1.2, 1.2)  # Можно изменить масштаб таблицы для лучшего отображения

    # Сохранение в pdf и закрытие страницы в файле, чтобы освободить память, связанную с этим объектом Figure в Matplotlib
    pdf.savefig(fig_table)
    plt.close(fig_table)

    # Создание фигуры и осей для гистограммы
    fig_hist, ax_hist = plt.subplots(figsize=(14, 6))  # Выберите подходящий размер фигуры

    # Установим метки на оси X с названиями месяцев
    ax_hist.set_xticks(range(1+len(df['МЕСЯЦ'])))

    # Строим столбики гистограммы
    ax_hist.bar(df['МЕСЯЦ'], df['ДОХОД'], width=0.4, edgecolor="white", label='доход за месяц', linewidth=0.7)

    # Рисуем число заказов
    ax_hist.plot(df['МЕСЯЦ'], 5000*df['КОЛИЧЕСТВО ЗАКАЗОВ'], 'r', label='количество заказов (в масштабе x5000)', linewidth=2.0)

    # Задание содержимого таблицы со статистикой
    ax_hist.set_title(f"Доход от заказов и их количество по месяцам, с {start}-го месяца по {finish}-й месяц")

    #Задание осей гистограммы
    ax_hist.set_xlabel('Месяц')
    ax_hist.set_ylabel('Доход')

    # Добавляем легенду на график
    ax_hist.legend()

    #Сохранение в pdf и закрытие страницы в файле, чтобы освободить память, связанную с этим объектом Figure в Matplotlib
    pdf.savefig(fig_hist)
    plt.close(fig_hist)

    # Создание фигуры и осей для таблицы
    fig_table, ax_table = plt.subplots(figsize=(8, 6))  # Выберите подходящий размер фигуры


# для самых часто покупаемых товаров
    #Задание заголовка
    ax_table.set_title(f"Самые часто покупаемые товары, с {start}-го месяца по {finish}-й месяц")
    
    # Задание содержимого таблицы со статистикой
    ax_table.axis('tight') #задаем границы области для таблицы так, чтобы они плотно обрамляли содержимое
    ax_table.axis('off') #выключаем отображение осей для таблицы (нет границ и делений)
    table = ax_table.table(cellText=df2.values, colLabels=df2.columns, loc='center') #задаем содержимое ячеек таблицы, заголовки столбцов и расположение таблицы
    #table.auto_set_font_size(True)
    table.set_fontsize(10) #устанавливаем размер шрифта для текста в таблице вручную
    table.scale(1.2, 1.2)  # Можно изменить масштаб таблицы для лучшего отображения

    #Сохранение в pdf и закрытие страницы в файле, чтобы освободить память, связанную с этим объектом Figure в Matplotlib
    pdf.savefig(fig_table)
    plt.close(fig_table)

    # Создание фигуры и осей для круговой диаграммы
    fig_circle, ax_circle = plt.subplots(figsize=(10, 8)) # 10 - ширина, 8 - высота фигуры в дюймах

    #Задание заголовка
    ax_circle.set_title(f"Самые часто покупаемые товары по месяцам, с {start}-го месяца по {finish}-й месяц")

    # Строим круговую диаграмму с отображением данных
    ax_circle.pie(df2['КОЛИЧЕСТВО'], labels=df2['ТОВАР'], autopct='%1.1f%%', startangle=90)

    # Устанавливаем соотношение сторон диаграммы как 1, чтобы круг выглядел как круг, а не эллипс
    plt.axis('equal')

    #Сохранение в pdf и закрытие страницы в файле, чтобы освободить память, связанную с этим объектом Figure в Matplotlib
    pdf.savefig(fig_circle)
    plt.close(fig_circle)

# Скачиваем файл на локальную машину
files.download(filename)

Результат выглядит довольно симпатично.

Пример итогового PDF-отчета можно посмотреть здесь, а представленный код также доступен в моем Github-репозитории.

Основы архитектуры и интеграции информационных систем

Код курса

OAIS

Ближайшая дата курса

17 ноября, 2025

Продолжительность

25 ак.часов

Стоимость обучения

56 000 руб.

Больше примеров, когда аналитику пригодится умение программировать и проектировать ИС я показываю на моих курсах по архитектуре и интеграции информационных систем в Школе прикладного бизнес-анализа на базе нашего лицензированного учебного центра обучения и повышения квалификации системных и бизнес-аналитиков в Москве:

• Разработка ТЗ на информационную систему
• Основы архитектуры и интеграции информационных систем