Быстрое наполнение базы данных: Python-скрипт INSERT-запросов для PostgreSQL с Faker

ERD, моделирование данных, основы SQL, примеры SQL запросов, ликбез по SQL для бизнес-аналитика с примерами курсы обучение, реляционная модель данных, обучение системных и бизнес-аналитиков, курсы системный и бизнес-анализ, проектирование схемы БД, курсы бизнес-аналитик, основы баз данных, обучение системному и бизнес-анализу, Школа прикладного бизнес-анализа

Сегодня рассмотрим пример проектирования, реализации и наполнения случайными данными реляционной БД PostgreSQL для последующего выполнения аналитических запросов к ней. Для этого я написала набор Python-скриптов, которые генерируют INSERT-запросы к БД со случайными значениями.

Постановка задачи и проектирование модели данных

Рассмотрим пример интернет-магазина, который продает товары от разных поставщиков. Клиент может забрать каждый заказ самостоятельно или оформить платную доставку от магазина. В магазине действует программа лояльности для покупателей с присвоением им различных статусов в зависимости от общей суммы покупок. Помимо внесения информации о заказах, клиентах, товарах, поставщиках и заказах, необходимы ответы на вопросы аналитического характера, например:

  • какие товары продаются чаще всего?
  • сколько заказов собрано, но еще не доставлено?
  • товары каких поставщиков не продаются вообще?
  • сколько заказов были доставлено за эту неделю?
  • каков средний чек покупки в магазине?
  • Каков средний LTV клиента?

Изучив бизнес-контекст, назначение системы и полный список аналитических вопросов, можно составить модель данных. Для этого выделим набор сущностей доменной области с полями:

  • Клиент (Имя, Емейл, Телефон, Статус);
  • Заказ (Дата, Сумма, Статус, Товары, Клиент);
  • Товар (Название, Поставщик, Цена, Количество). Здесь атрибут Количество указывает, сколько единиц Товара есть в магазине.
  • Поставщик (Название, Емейл, Телефон, Адрес);
  • Доставка (Дата, Адрес, Стоимость).

Сделаем первую итерацию проектирования инфологической модели данных, т.е. не привязанной к конкретной СУБД.

проектирование модели данных, .ERD, инфологическая модель
Начало проектирования ER-модели

Эта модель имеет связи много-ко-многим между сущностями Заказ-Товар, поскольку один и тот же товар может встречаться во многих заказах, также как один заказ содержит много разных товаров. Необходимо «развязать» эту связь, сделав дополнительную сущность, которая будет содержать поля Заказ и Товар, ссылающиеся на внешние сущности. Кроме того, эта новая сущность (Товары в Заказе) также будет показывать, сколько единиц конкретного Товара имеется в конкретном Заказе.

проектирование модели данных, .ERD, инфологическая модель
ER-модель вторая версия

Статусы заказов и клиентов выбираются из ограниченного количества значений. Поэтому их целесообразно вынести в отдельные сущности, сделав таким образом нормализацию модели, т.е. приведение ее к нормальной форме. Это позволяет улучшить целостность данных, исключить противоречивость и несогласованность.

 

проектирование модели данных, .ERD, инфологическая модель
ER-модель третья версия

Теперь можно переходить от инфологической модели базы данных к физической, т.е. назначать типы данных каждому атрибуту и привязываться к конкретному решению, т.е. СУБД, которая будет управлять этой БД. Возьмем PostgreSQL – популярную объектно-реляционную СУБД, которая имеет множество встроенных возможностей и дополнительных расширений, отлично подходит как для транзакционных, так и аналитических систем. Чтобы получить из нарисованной схемы DDL-скрипт на создание таблиц БД, все названия сущностей и полей надо написать на английском языке. А также верно проставить типы данных. Например, идентификатор в каждой таблице (id) – это целочисленное поле, которое будет первичным ключом, т.е. уникальным идентификатором строки в таблице. Веб-редактор визуального проектирования drawsql сам подставляет его при создании таблицы. С остальными полями надо поработать вручную. В итоге у меня получилась следующая схема физической модели данных для PostgreSQL. Составим словарь данных для этой модели:

Таблица Назначение Поле Назначение Тип данных
customer Клиент id Уникальный идентификатор, первичный ключ (PRIMARY KEY) int

(целочисленный)

name Имя сhar (символьный)
email Емейл сhar (символьный)
phone Телефон сhar (символьный)
state Ссылка на Статус Клиента, внешний ключ (FOREIGN KEY) int

(целочисленный)

orders Заказ id Уникальный идентификатор, первичный ключ (PRIMARY KEY) int

(целочисленный)

date Дата date (дата YYYY-MM-DD)
sum Сумма Double precision (вещественное двойной точности)
state Ссылка на Статус Заказа, внешний ключ (FOREIGN KEY) int

(целочисленный)

customer Ссылка на Клиент, внешний ключ (FOREIGN KEY) int

(целочисленный)

delivery  Ссылка на Доставку, внешний ключ (FOREIGN KEY) int

(целочисленный)

product Товар id Уникальный идентификатор, первичный ключ (PRIMARY KEY) int

(целочисленный)

name Название сhar (символьный)
provider Ссылка на Поставщик, внешний ключ (FOREIGN KEY) int

(целочисленный)

price Цена Double precision (вещественное двойной точности)
quantity Количество int (целочисленный)
provider Поставщик id Уникальный идентификатор, первичный ключ (PRIMARY KEY) int

(целочисленный)

name Название сhar (символьный)
email Емейл сhar (символьный)
phone Телефон сhar (символьный)
address Адрес text (текстовый)
delivery Доставка id Уникальный идентификатор, первичный ключ (PRIMARY KEY) int

(целочисленный)

date Дата date (дата YYYY-MM-DD)
address Адрес text (текстовый)
price Стоимость Double precision (вещественное двойной точности)
customer_states Статус Клиента id Уникальный идентификатор, первичный ключ (PRIMARY KEY) int

(целочисленный)

name Название сhar (символьный)
order_states Статус Заказа id Уникальный идентификатор, первичный ключ (PRIMARY KEY) int

(целочисленный)

name Название сhar (символьный)
order_product Товары в Заказе id Уникальный идентификатор, первичный ключ (PRIMARY KEY) int

(целочисленный)

order Ссылка на Заказ, внешний ключ (FOREIGN KEY) int

(целочисленный)

product Ссылка на Товар, внешний ключ (FOREIGN KEY) int

(целочисленный)

quantity Количество int (целочисленный)
PostgreSQL, проектирование схемы данных, моделирование данных пример
Физическая модель данных для PostgreSQL

Эта схема была выполнена в онлайн-редакторе https://drawsql.app/ , который позволяет не только визуально спроектировать модель, но и получить DDL-скрипт на создание таблиц:

DDL PostgreSQL
Экспорт DDL-скрипт для PostgreSQL по спроектированной схеме

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

CREATE TABLE "order_states"(
"id" INTEGER NOT NULL,
"name" CHAR(255) NOT NULL
);
ALTER TABLE
"order_states" ADD PRIMARY KEY("id");
CREATE TABLE "product"(
"id" INTEGER NOT NULL,
"name" CHAR(255) NOT NULL,
"provider" INTEGER NOT NULL,
"price" DOUBLE PRECISION NOT NULL,
"quantity" INTEGER NOT NULL
);
ALTER TABLE
"product" ADD PRIMARY KEY("id");
CREATE TABLE "orders"(
"id" INTEGER NOT NULL,
"customer" INTEGER NOT NULL,
"state" INTEGER NOT NULL,
"delivery" INTEGER NOT NULL,
"sum" DOUBLE PRECISION NOT NULL,
"date" DATE NOT NULL
);
ALTER TABLE
"orders" ADD PRIMARY KEY("id");
CREATE TABLE "order_product"(
"id" INTEGER NOT NULL,
"order" INTEGER NOT NULL,
"product" INTEGER NOT NULL,
"quantity" INTEGER NOT NULL
);
ALTER TABLE
"order_product" ADD PRIMARY KEY("id");
CREATE TABLE "customer_states"(
"id" INTEGER NOT NULL,
"name" CHAR(255) NOT NULL
);
ALTER TABLE
"customer_states" ADD PRIMARY KEY("id");
CREATE TABLE "customer"(
"id" INTEGER NOT NULL,
"name" CHAR(255) NOT NULL,
"email" CHAR(255) NOT NULL,
"phone" CHAR(255) NOT NULL,
"state" INTEGER NOT NULL
);
ALTER TABLE
"customer" ADD PRIMARY KEY("id");
CREATE TABLE "delivery"(
"id" INTEGER NOT NULL,
"date" DATE NOT NULL,
"address" TEXT NOT NULL,
"price" DOUBLE PRECISION NOT NULL
);
ALTER TABLE
"delivery" ADD PRIMARY KEY("id");
CREATE TABLE "provider"(
"id" INTEGER NOT NULL,
"name" CHAR(255) NOT NULL,
"phone" CHAR(255) NOT NULL,
"email" CHAR(255) NOT NULL,
"address" TEXT NOT NULL
);
ALTER TABLE
"provider" ADD PRIMARY KEY("id");
ALTER TABLE
"order_product" ADD CONSTRAINT "order_product_order_foreign" FOREIGN KEY("order") REFERENCES "orders"("id");
ALTER TABLE
"product" ADD CONSTRAINT "product_provider_foreign" FOREIGN KEY("provider") REFERENCES "provider"("id");
ALTER TABLE
"orders" ADD CONSTRAINT "orders_delivery_foreign" FOREIGN KEY("delivery") REFERENCES "delivery"("id");
ALTER TABLE
"orders" ADD CONSTRAINT "orders_state_foreign" FOREIGN KEY("state") REFERENCES "order_states"("id");
ALTER TABLE
"customer" ADD CONSTRAINT "customer_state_foreign" FOREIGN KEY("state") REFERENCES "customer_states"("id");
ALTER TABLE
"order_product" ADD CONSTRAINT "order_product_product_foreign" FOREIGN KEY("product") REFERENCES "product"("id");
ALTER TABLE
"orders" ADD CONSTRAINT "orders_customer_foreign" FOREIGN KEY("customer") REFERENCES "customer"("id");

Однако, просто создать таблицы в БД недостаточно, их надо наполнить данными, чтобы делать SQL-запросы к ним. Заполнять 8 таблиц вручную довольно долго, поэтому я написала Python-скрипт, который будет наполнять таблицы случайными данными.

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

Код курса
TTIS
Ближайшая дата курса
3 июня, 2024
Продолжительность
12 ак.часов
Стоимость обучения
27 000 руб.

Пишем Python-скрипт с INSERT-запросами

Разумеется, сначала я хотела найти готовый инструмент, который будет наполнять мои таблицы данными. Но существующие сервисы предлагают лишь сгенерировать датасет по своей структуре, например, получить 100 строк с названиями компаний, именами людей, емейлами, телефонами и т.д. Синтаксический разбор внешней схемы данных они не делают. Поэтому пришлось изобретать свой велосипед писать собственный код.

Сперва я хотела написать полноценную программу, которая будет на вход принимать DDL-скрипт, определять таблицы и столбцы в них, включая типы данных, и выдавать DML-скрипт с INSERT-запросами для заполнения базы данных. Однако, оказалось это не так просто из-за ограничений, связанных с внешними ключами. Поэтому я решила сделать следующие заготовки, которыми вы тоже можете воспользоваться для наполнения своих БД, предварительно поменяв структуру таблиц и столбцов.

Начнем с наполнения таблиц-справочников со статусами клиентов:

#скрипт наполнения простой таблицы-справочника из ограниченного количества значений
# задаем название таблицы
table_name = 'customer_states'

# задаем столбцы таблицы
column_names = ('id', 'name')

#создаем список данных - справочник значений
states = ['бронзовый', 'серебряный', 'золотой', 'VIP']

# задаем количество строк таблицы, которое хотим заполнить - для таблицы-справочника это количество значений списка данных
k = len(states)

# генерация и запись DML-скрипта в файл
filename = f'{table_name}.sql' # формируем название выходного файла
with open(filename, 'w') as f: #открытие файла для записи
    # формируем строку с названиями столбцов
    columns_str = ', '.join(f'"{column}"' for column in column_names)
    for i in range(k):
        # генерируем значения для каждого столбца       
        # формируем список со значениями
        values = [i+1, f"'{states[i]}'"]
        # формируем строку со значениями
        values_str = ', '.join(str(value) for value in values)
        # формируем и записываем DML-запрос в файл
        insert_query = f'INSERT INTO "{table_name}" ({columns_str}) VALUES ({values_str});\n'
        print(insert_query) #вывод на консоль - для проверки и отладки
        f.write(insert_query) #запись в файл
    f.close() #закрытие файла

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

Этот скрипт не только выводит INSERT-запросы на заполнение таблицы со статусами клиентов данными, но и сохраняет файл с расширением .sql и названием таблицы, заданной в переменной table_name. Запустим скрипт в интерактивной среде Google Colab:

Insert into postgresql python colab
Запуск в Colab Python-кода для получения DML-скрипта с INSERT-запросами

Получим следующий DML-файл:

INSERT INTO "customer_states" ("id", "name") VALUES (1, 'бронзовый');
INSERT INTO "customer_states" ("id", "name") VALUES (2, 'серебряный');
INSERT INTO "customer_states" ("id", "name") VALUES (3, 'золотой');
INSERT INTO "customer_states" ("id", "name") VALUES (4, 'VIP');

Аналогичным образом я получила DML-скрипты для таблицы со статусами заказов, изменив значения списка данных и название таблицы. Для таблицы с доставкой, которая включает адрес, пришлось немного модифицировать скрипт. Чтобы генерировать случайный адрес, я использовала Python-библиотеку Faker с русской локализацией. Код для получения INSERT-запросов для таблицы Доставка выглядит так. Сперва установим необходимые библиотеки и модули:

#############################ячейка №1 в Google Colab - установка библиотек и импорт модулей######################## 
!pip install Faker

import random
import datetime
from faker import Faker
from google.colab import files

from faker.providers.address.ru_RU import Provider

Затем напишем сам скрипт наполнения таблицы данными:

#############################ячейка №2 в Google Colab - скрипт наполнения случайными данными таблицы с датой и адресом

# Создание объекта Faker с локализацией для России
fake = Faker('ru_RU')
fake.add_provider(Provider)

# задаем названиие таблицы
table_name = 'delivery'

# задаем столбцы таблицы
column_names = ('id', 'date', 'address', 'price')

# задаем количество строк таблицы, которое хотим заполнить
k = 100

# генерация и запись DML-скрипта в файл
filename = f'{table_name}.sql' # формируем название выходного файла
with open(filename, 'w') as f: #открытие файла для записи
    # формируем строку с названиями столбцов
    columns_str = ', '.join(f'"{column}"' for column in column_names)
    for i in range(k):
      # генерируем значения для каждого столбца

        # генерируем случайную дату в диапазоне
        start_date = datetime.date(2023, 1, 1)
        end_date = datetime.date(2023, 12, 31)
        random_date = start_date + datetime.timedelta(days=random.randint(0, (end_date - start_date).days))
        # преобразуем дату в строку в формате "год-месяц-день"
        #для строковых значений обязательно f"'{}'"
        date = f"'{random_date.strftime('%Y-%m-%d')}'"
        
        #генерируем случайное значение адреса
        address=f"'{fake.address()}'" 
        #генерируем для суммы денег за доставку случайное вещественное число от 0 до 1000 с двумя знаками после запятой  
        price=round(fake.pyfloat(right_digits=2, min_value=0, max_value=1000), 2) 
        
        # формируем список со значениями
        values = [i+1, date, address, price]
        # формируем строку со значениями
        values_str = ', '.join(str(value) for value in values)
        # формируем и записываем DML-запрос в файл
        insert_query = f'INSERT INTO "{table_name}" ({columns_str}) VALUES ({values_str});\n'
        print(insert_query) #вывод на консоль - для проверки и отладки
        f.write(insert_query) #запись в файл
    f.close() #закрытие файла

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

Выполнение этого кода загрузит на ваш компьютер файл с INSERT-запросами, которые можно скопировать и выполнить в интерактивной среде работы с базами данных https://sqliteonline.com/. Все DML и DDL-скрипты для наполнения БД доступны в этой папке моего Github-репозитория

Аналогичным образом я наполнила остальные таблицы. После этого можно их запрашивать, отрабатывая навык написания SQL-запросов на собственной базе данных.

DML PostgreSQL online
Запуск полученного DML-скрипта

Например, следующий запрос показывает, сколько клиентов у магазина в каком статусе лояльности:

SELECT count(*) AS "Количество клиентов", 
customer_states.name AS "Статус клиентов"
from customer
JOIN customer_states 
on customer_states.id=customer.state
GROUP BY customer_states.name
ORDER BY "Количество клиентов"
обучение SQL и БД основы для аналитика, основы баз данных с примерами для начинающих, основы SQL для новичков примеры
Пример SQL-запроса к созданной и наполненной БД

Разумеется, этот пример охватывает не все этапы проектирования базы данных. В частности, здесь я намеренно не стала упоминать про индексы — расскажу в следующий раз. Однако, надеюсь, этот небольшой пример позволил понять некоторые принципы проектирования физической модели данных и ее использования.

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

Код курса
OAIS
Ближайшая дата курса
20 мая, 2024
Продолжительность
12 ак.часов
Стоимость обучения
27 000 руб.

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

 

Я даю свое согласие на обработку персональных данных и соглашаюсь с политикой конфиденциальности.

Добавить комментарий

Поиск по сайту

Напишите нам, мы онлайн!