Создание тестовых данных PostgreSQL с SQL, PL / PGSQL и Python

После изучения различных способов Загрузите тестовые данные в PostgreSQL для моего последнего поста в блоге Я хотел погрузиться в разные подходы для Генерация Тестовые данные для PostgreSQL. Создание тестовых данных, а не использование статических данных, созданных вручную, может быть ценной по нескольким причинам:

• Написание логики для генерации данных тестовых данных заставляет вас второй взгляд на вашу модель данных и рассмотреть, какие значения разрешены, а какие значения являются краевыми случаями.
• Инструменты для генерации тестовых данных облегчают настройку данных на тест. Я бы сказал, что это лучше, чем альтернативы (а) данных, создавающих руку, или (б) попытку поддерживать один набор данных, который используется во всем наборе тестирования. Первый вариант утомительно, а второй вариант может быть хрупким. В качестве примера, если вы проверяете веб-сайт электронной коммерции, и ваш тестовый набор использует жестко закодированные детали продукта и деактивируют продукт в вашем тестовом наборе данных, вызывает многие тесты, чтобы неожиданно выйти из строя, то эти тесты были зависит от предварительного условия, что оказалось удовлетворенным в вашем тестовом наборе данных. Генерация данных за тест может принимать такие предварительные условия более явными и четкими, особенно для коллег, которые наследуют ваши тесты и тестовые данные в будущем.
• Если у вас уже нет большого набора данных из производственной среды или партнерской компании, которую вы можете использовать (надеюсь, после анонимизации!), Генерирование тестовых данных – единственный способ получить большие наборы данных для сравнительного анализа и тестирования нагрузки.

Подобно предыдущей статье, если вы используете библиотеку объекта-реляционного сопоставления (ORM), то вы, вероятно, создаете и сохраняете объекты в базу данных, используя ORM или используете ORM для сброса и восстановления приспособлений данных, используя JSON или CSV. Если вы не используете ORM, подходы в этой статье могут дать некоторое обучение или вдохновение для того, как вы можете лучше всего генерировать данные для вашей конкретной ситуации тестирования.

Похож на Предыдущая статья , вы можете следить с помощью Docker и скриптов в подпапке нашего репо блога Tangram Vision Blog: https://gitlab.com/tangram-vision-oss/tangram-visions-blog/-/tree/main/2021.04.30_generatingtestdatainpostgresql.

В отличие от предыдущей статьи, я предоставил DockerFile добавить Python в Postgres Docker Image, чтобы мы могли запустить Python внутри базы данных PostgreSQL. Как описано в Repo Readme, вы можете построить изображение Docker и запустить примеры с:

docker build . --tag=postgres-test-data-blogpost

# The base postgres image requires a password to be set, but we'll just be
# testing locally, so no need to set a strong password.
docker run --name=postgres --rm --env=POSTGRES_PASSWORD=foo \
    --volume=$(pwd)/schema.sql:/docker-entrypoint-initdb.d/schema.sql \
    --volume=$(pwd):/repo \
    postgres-test-data-blogpost -c log_statement=all

REPO содержит различные файлы, которые начинаются с Add-data- которые демонстрируют различные способы загрузки и генерации тестовых данных. После того, как Postgres Docker контейнер работает, вы можете запустить Add-data- Файлы в новом окне терминала с помощью команды, как:

docker exec --workdir=/repo postgres \
    psql --host=localhost --username=postgres \
         --file=add-data-insert-random.sql

Если вы хотите интерактивно ткать вокруг базы данных с PSQL , использовать:

docker exec --interactive --tty postgres \
    psql --host=localhost --username=postgres

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

• Музыкальные художники имеют имя
• Художник может иметь много альбомов (один ко многим), которые имеют название и дата выпуска
• Жанры имеют имя
• Альбомы могут принадлежать многим жанрам (многозначным)

Образец схемы, связанные с музыкальными артистами, альбомы и жанры.

Использование статических наборов данных имеет преимущества (вы точно знаете, какие данные в вашей базе данных), но они могут быть утомительными для обслуживания со временем и нецелесообразными для создания, если вам нужно много данных (например, для тестирования нагрузки или нагрузки). Генерация данных – это альтернативный подход, который позволяет определить, как данные должны смотреть в одном месте, а затем генерировать и использовать столько данных, сколько вам нравится.

Существует несколько различных инструментов для генерации тестовых данных, которые стоит изучить, с простого OL ‘SQL к языкам программирования на высоком уровне, как Python.

SQL

Если вы как я, возможно, вы, возможно, начали эту статью, не ожидаю, что SQL будет способным создавать тестовые данные. С [Generate_Series] (https://www.postgresql.org/docs/current/functions-srf.html) и [Random] (https://www.postgresql.org/docs/current/functions-math.html#functions-math-random-table) и немного творчества Однако SQL хорошо оснащен для создания различных данных.

Чтобы создать 5 артистов с 8 случайными шестнадцатеричными символами для их имен, вы можете сделать следующее:

INSERT INTO artists (name)
SELECT substr(md5(random()::text), 1, 8) FROM generate_series(1, 5) as _g;

Если вы хотите использовать случайные слова вместо случайных символов Hex, вы можете выбрать слова из словаря системного словаря. Я скопировал Ubuntu’s Американский-английский Список слов в /usr/share/dict/words В документе Docker, поэтому нам просто нужно загрузить его и выбирать слова случайным образом:

-- Temporary tables are only accessible to the current psql session and are
-- dropped at the end of the session.
CREATE TEMPORARY TABLE words (word TEXT);

-- The WHERE clauses excludes possessive words (almost 30k of them!)
COPY words (word) FROM '/usr/share/dict/words' WHERE word NOT LIKE '%''%';

-- Randomly order the table and pick the first result
SELECT * FROM words ORDER BY random() LIMIT 1;

Нет шутки, первое слово, которое приведенное выше запрос вернулась для меня, было «Браво». Я не знаю, следует ли поощрять или проплеститься.

На отдельном примечании словарь содержит слова, которые могут быть оскорбительными и неуместными в некоторых настройках. Если вы получаете тестовые данные из словаря и не хотите, чтобы эти слова появлялись в вашей следующей демонстрации для клиентов/боссов, обязательно примите соответствующие меры предосторожности!

Во всяком случае, двигаясь на … Используя эти инструменты (и еще несколько), мы можем создавать интересные данные тестирования для всех наших таблиц. Комментарии к коду ниже объяснения дополнительных функций и используемых методов.

-- Excerpt from add-data-insert-random.sql in the sample code repo

-- Use 8 random hex chars as the genre name.
INSERT INTO genres (name)
SELECT substr(md5(random()::text), 1, 8) FROM generate_series(1, 5) AS _g;

INSERT INTO artists (name)
SELECT
  -- Pick one random word as the artist name.
  (SELECT * FROM words ORDER BY random() LIMIT 1)
FROM generate_series(1, 4) AS _g;

INSERT INTO albums (artist_id, title, released)
SELECT
  -- Select a random artist from the artists table.
  -- NOTE: random() is only evaluated once in this subquery unless it depends on
  -- the outer query, hence the "_g*0" after random().
  (SELECT id FROM artists ORDER BY random()+_g*0 LIMIT 1),

  -- Select the first 1-3 rows after randomly sorting the word list, then join
  -- them with spaces between each word and capitalize the first letter of each
  -- word.
  initcap(array_to_string(array(
    SELECT * FROM words ORDER BY random()+_g*0 LIMIT ceil(random() * 3)
  ), ' ')),

  -- Subtract between 0-5 years from today as the album release date.
  (now() - '5 years'::interval * random())::date
FROM generate_series(1, 8) AS _g;

-- Assign a random album a random genre. Repeat 10 times.
INSERT INTO album_genres (album_id, genre_id)
SELECT
  (SELECT id FROM albums ORDER BY random()+_g*0 LIMIT 1),
  (SELECT id FROM genres ORDER BY random()+_g*0 LIMIT 1)
FROM generate_series(1, 10) AS _g
-- If we insert a row that already exists, do nothing (don't raise an error)
ON CONFLICT DO NOTHING;

Но это не все! Мы можем определить функции в SQL для повторного использования логики-если мы хотим жанры, имена артистов и названия альбомов, чтобы все были случайными словами, тогда мы можем перемещать случайное слово в функцию и использовать их во многих местах:

-- Excerpt from add-data-insert-random-function.sql in the sample code repo
CREATE OR REPLACE FUNCTION generate_random_title(num_words int default 1) RETURNS text AS $$
  SELECT initcap(array_to_string(array(
    SELECT * FROM words ORDER BY random() LIMIT num_words
  ), ' '))
$$ LANGUAGE sql;

INSERT INTO genres (name)
SELECT generate_random_title()
FROM generate_series(1, 5) AS _g;

INSERT INTO artists (name)
-- Generate 1-2 random words as the artist name.
SELECT generate_random_title(ceil(random() * 2 + _g * 0)::int)
FROM generate_series(1, 4) AS _g;

-- ...

PL/PGSQL.

Если декларативный стиль SQL неловко/сложно, мы можем обратиться в PL/PGSQL Для создания тестовых данных в PostgreSQL с использованием более процедурного/императивного стиля программирования. PL/PGSQL Предоставляет знакомые концепции программирования, такие как переменные, условные петли, возвратные заявления и обработка исключений.

Чтобы продемонстрировать некоторые из чего можно сделать PL/PGSQL, давайте укажем еще несколько требований к нашим сгенерированным данным – примерно половину наших художников должны иметь имена, начиная с «DJ», и все альбомы DJ Artists должны принадлежать к «электронному» жанру. Эта реализация может выглядеть как:

-- Excerpt from add-data-plpgsql-insert.sql in the sample code repo
DO $$
DECLARE
  -- Declare (and optionally assign) variables used in the below code block.
  genre_options text[] := array['Hip Hop', 'Jazz', 'Rock', 'Electronic'];
  artist_name text;
  dj_album RECORD;
BEGIN
  -- Convert each array option into a row and insert them into genres table.
  INSERT INTO genres (name) SELECT unnest(genre_options);

  FOR i IN 1..8 LOOP
    SELECT generate_random_title(ceil(random() * 2)::int) INTO artist_name;
    -- About 50% of the time, add 'DJ ' to the front of the artist's name.
    IF random() > 0.5 THEN
      artist_name = 'DJ ' || artist_name;
    END IF;
    INSERT INTO artists (name)
    SELECT artist_name;
  END LOOP;

  -- ...

  -- Ensure all albums by a 'DJ' artist belong to the Electronic genre.
  FOR dj_album IN
    SELECT albums.* FROM albums
    INNER JOIN artists ON albums.artist_id = artists.id
    WHERE artists.name LIKE 'DJ %'
  LOOP
    RAISE NOTICE 'Ensuring DJ album % belongs to Electronic genre!', quote_literal(dj_album.title);
    INSERT INTO album_genres (album_id, genre_id)
    SELECT dj_album.id, (SELECT id FROM genres WHERE name = 'Electronic')
    -- If we insert a row that already exists, do nothing (don't raise an error)
    ON CONFLICT DO NOTHING;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Как вы можете видеть в приведенном выше фрагменте кода, PL/PGSQL позволяет нам:

• Условия теста с Если утверждения (которые могут иметь ELSIF и остальные блоки или попеременно быть представлены с помощью случаев отчетов),
• Петля по ряду целых чисел с Для я в 1..8 петли (который может петлю в обратном или с шагом),
• Петля над строками из запроса как в Для DJ_ALBUM в ... Пример выше,
• Печать полезных операторов журнала с Поднять С
• И сделать все вышеперечисленное в исполнителе , Поскольку клиент может отправлять весь код код на сервер, чтобы выполнить, а не сериализующую и отправку каждого оператора на сервер один за раз, когда он будет с RAW SQL.

Для гораздо больше для Узнайте о PL/PGSQL

Используя Python

PL/PGSQL не единственный процедурный язык, доступный с PostgreSQL, он также поддерживает Python! Процедурный язык Python, plypython3u Для Python 3, «ненадежна» (следовательно, u в конце имени), что означает, что вы должны быть суперпользовательским для создания функций, и код Python может получить доступ и сделать все возможное, чтобы суперзвук мог. К счастью, мы генерируем тестовые данные в непроизводственных средах, поэтому Python является приемлемым вариантом, несмотря на эти проблемы безопасности.

Использовать plypython3u нам нужно установить Python3. и postgresql-plypython3- $ pg_major Пакеты системы и создайте расширение в скрипте SQL с помощью команды ниже. Я уже взял эти шаги для скрипта Docker Image и Scrpyphon в репо Code Code.

CREATE EXTENSION IF NOT EXISTS plpython3u;

Основное отличие, чтобы осознавать при использовании Python в PostgreSQL, заключается в том, что все доступ к базе данных происходит через заблуждение Модуль, который автоматически импортируется в plypython3u блоки. Следующий пример должен помочь уточнить некоторые основы использования plypython3u и ПЛЮТ Модуль:

-- Excerpt from add-data-plpython-intro.sql in the sample code repo
DO $$
    print("Print statements don't appear anywhere!")

    # Manually convert value to string, quote it, and interpolate
    artist_name = plpy.quote_nullable("DJ Okawari")
    returned = plpy.execute(f"INSERT INTO artists (name) VALUES ({artist_name})")
    plpy.info(returned)  # Outputs the next line
    # INFO:  

    # Let PostgreSQL parameterize the query
    artist_name = "Ella Fitzgerald"
    plan = plpy.prepare("INSERT INTO artists (name) VALUES ($1) RETURNING *", ["text"])
    returned = plan.execute(plan, [artist_name])
    plpy.info(returned)  # Outputs the next line
    # INFO:  

    returned = plpy.execute("SELECT * FROM artists")
    plpy.info(returned)  # Outputs the next line
    # INFO:  
$$ LANGUAGE plpython3u;

Вот самые важные идеи из вышеуказанного кода:

• Вы не можете распечатать информацию о отладке с оператором Python Print, вам нужно использовать Методы регистрации доступны в модуле установленного узла (например, информация , Предупреждение , Ошибка )
• [ПЛЮС .EXECUTE Функция] ( https://www.postgresql.org/docs/12/plpython-database.html ) может выполнить простую строку в качестве запроса. Если вы интерполизируете переменные в запрос, вы несете ответственность за преобразование значения переменной в строку и правильно цитируя Это.
• Поочередно, используйте Plan.Prepare тогда Plan.execute Чтобы подготовить и выполнить запрос, который позволяет вам оставить преобразование данных и процитировать до PostgreSQL. В качестве бонуса вы можете сохранить планы, чтобы база данных была только проанализировать строку запроса и сформулировать план выполнения один раз.
• Возвращаемое значение Plypy.execute могу сказать вам Статус Из запроса, сколько рядов было вставлено или возвращено, и сами строки.

Теперь, когда у нас есть понимание того, как использовать Python в PostgreSQL, давайте применим его к созданию тестовых данных для нашей выборки схемы. Хотя мы могли бы перевести код PL/PGSQL PL/PGSQL в Python в Python с очень немногими изменениями, это не будет выгодно извлечь выгоду из самых больших преимуществ использования Python – множества стандартных и сторонних библиотек.

Faker Package.

Faker это пакет Python, который предоставляет многие помощники для создания поддельных данных. Вы можете генерировать реалистичные первые и фамилии, адреса, электронные письма, URL, названиям работы, названия компаний и многое другое. Faka также поддерживает генерацию Случайные слова и предложения и генерируя случайные данные по многим различным типам данных (числа, строки, даты, JSON и более). Использование Faker является простым:

-- Excerpt from add-data-plpython-faker.sql in the sample code repo
DO $$
    from random import randint, choice
    from faker import Faker

    fake = Faker()

    for _ in range(6):
        plan = plpy.prepare("INSERT INTO artists (name) VALUES ($1)", ["text"])
        plan.execute([fake.name()])

    # Alternately, we could add "RETURNING artist_id" to the above query and
    # save those values to avoid making this extra query for all artist_ids
    artist_ids = [row["artist_id"] for row in plpy.execute("SELECT artist_id FROM artists")]
    for _ in range(10):
        title = " ".join(word.title() for word in fake.words(nb=randint(1, 3)))
        plan = plpy.prepare(
            "INSERT INTO albums (artist_id, title, released) VALUES ($1, $2, $3)",
            ["int", "text", "date"],
        )
        plan.execute([choice(artist_ids), title, fake.date()])

    # ...
$$ LANGUAGE plpython3u;

Модуль DataClasses

Если вы предпочитаете создавать объекты Python, чтобы представлять строки из ваших разных таблиц, вы можете использовать различные разные пакеты, такие как attrs , factory_boy или встроенный модуль Dataclasses Отказ Эти пакеты позволяют объявить поле на стол столбец в таблице и ассоциировать типы данных и фабрики для генерации тестовых данных.

Обратите внимание, что если вы пройдете очень далеко по этому пути представления рядов в виде объектов Python, вы найдете себя повторно созданию множества функциональности ORM. В этом случае вы, вероятно, должны просто использовать ORM!

Вот пример того, как вы можете использовать модуль Dataclasses для генерации данных тестирования для нашей схемы образца:

-- Excerpt from add-data-plpython-dataclasses.sql in the sample code repo
DO $$
    from dataclasses import dataclass, field
    import datetime
    from random import randint, choice
    from typing import List, Any, Type, TypeVar

    from faker import Faker

    T = TypeVar("T", bound="DataGeneratorBase")
    fake = Faker()

    # This is a useful base class for tracking instances so we can use them in
    # relationships (picking a random artist or genre to foreign key to).
    class DataGeneratorBase:
        def __new__(cls: Type[T], *args: Any, **kwargs: Any) -> T:
            "Track class instances in a list on the class"
            instance = super().__new__(cls, *args, **kwargs)  # type: ignore
            if "instances" not in cls.__dict__:
                cls.instances = []
            cls.instances.append(instance)
            return instance

    @dataclass
    class Genre(DataGeneratorBase):
        genre_id: int = field(init=False)
        name: str = field(default_factory=fake.street_name)

    @dataclass
    class Artist(DataGeneratorBase):
        artist_id: int = field(init=False)
        name: str = field(default_factory=fake.name)

    @dataclass
    class Album(DataGeneratorBase):
        album_id: int = field(init=False)
        artist: Artist = field(default_factory=lambda: choice(Artist.instances))
        title: str = field(
            default_factory=lambda: " ".join(
                word.title() for word in fake.words(nb=randint(1, 3))
            )
        )
        released: datetime.date = field(default_factory=fake.date)
        genres: List[Genre] = field(
            # Use Faker to pick a list of genres to avoid duplicates
            default_factory=lambda: fake.random_elements(Genre.instances, length=randint(0, 3), unique=True)
        )

    for _ in range(6):
        g = Genre()
        # "RETURNING id" lets us get the database-generated and store it on the
        # Python object for later reference without needing to issue additional
        # queries.
        plan = plpy.prepare(
            "INSERT INTO genres (name) VALUES ($1) RETURNING genre_id", ["text"]
        )
        g.genre_id = plan.execute([g.name])[0]["genre_id"]
    for _ in range(6):
        artist = Artist()
        plan = plpy.prepare(
            "INSERT INTO artists (name) VALUES ($1) RETURNING artist_id", ["text"]
        )
        artist.artist_id = plan.execute([artist.name])[0]["artist_id"]
    for _ in range(8):
        album = Album()
        plan = plpy.prepare(
            "INSERT INTO albums (artist_id, title, released) VALUES ($1, $2, $3) RETURNING album_id",
            ["int", "text", "date"],
        )
        album.album_id = plan.execute(
            [album.artist.artist_id, album.title, album.released]
        )[0]["album_id"]

        # Insert album_genres rows
        for g in album.genres:
            plan = plpy.prepare(
                "INSERT INTO album_genres (album_id, genre_id) VALUES ($1, $2)",
                ["int", "int"],
            )
            plan.execute([album.album_id, g.genre_id])
$$ LANGUAGE plpython3u;

Приведенный выше фрагмент определяет классы для каждой основной таблицы в нашем примере схемы: жанр, артист и альбом. Затем он определяет поля для каждого столбца вместе с Default_factory Функция, которая говорит Python (или пакет Faker, во многих случаях) Как генерировать подходящие тестовые данные. Я сделал класс альбома «владельцами» многих ко многим отношениям с жанрами, поэтому, когда создан альбом, он автоматически выбирает 0-3 существующих жанров для ассоциирования с во время инициализации.

Вторая половина кода передает объекты Python в запросы SQL вставки, возвращая идентификаторы первичных ключей (которые не создавали при создании объекта, из-за init = Ложь полевой аргумент), поэтому они могут быть сохранены на объектах и использованы позже при настройке внешних ключей. Это подчеркивает трудность с выполнением такого рода объекта-реляционного отображения самостоятельно – вы должны понять зависимости между вашими типами данных и обеспечить заказа (в Python и SQL) Так что у вас есть идентификаторы, созданные базы данных в нужное время. Это может быть немного утомительным и грязным, особенно если у вас есть круговые зависимости или самосвязанные отношения в ваших таблицах.

Импорт внешнего .py. Файлы

Если ваша модель данных или код генерации данных начнут стать сложными, может быть раздражает, что в файлах SQL есть много кода Python-ваш IDE не захочет прокинуть, проверить тип и автоматическое вкладывание кода Python! К счастью, вы можете сохранить код Python во внешнем Файлы, которые вы импортируете и выполняете изнутри plypython3u Блок, используя технику, показанную ниже:

-- Excerpt from add-data-plpython-external-pyfile.sql in the sample code repo
DO $$
    import importlib.util

    # The second argument is the filepath on the server (inside the container)
    spec = importlib.util.spec_from_file_location("add_test_data", "/repo/add_test_data.py")
    add_test_data = importlib.util.module_from_spec(spec)
    spec.loader.exec_module(add_test_data)
    add_test_data.main(plpy)
$$ LANGUAGE plpython3u;

add_test_data.py . Файл может выглядеть так же, как тело plypython3u Блок из предыдущего примера, но вам нужно будет обернуть нижнюю половину (что использует плп для запуска запросов) в функции, которая принимает ПЛЮТ как аргумент, так выглядит:

# Excerpt from add_test_data.py in the sample code repo

# ...
def main(plpy: Any) -> None:
    for _ in range(6):
        g = Genre()
    # ...

Другие (доверенные) способы использования Python

Я хочу кратко затронуть два способа использования Python снаружи PostgreSQL – Запуск Python внешне может быть предпочтительным, если вы хотите или нужно избегать ненадежного характера plypython3u Отказ Эти подходы позволяют поддерживать свой код Python полностью независимым от базы данных, что может быть полезно для воспроизведения и ремонтопригодности.

• Вы можете использовать сценарии Python для создания тестовых данных в файлы CSV, а затем загрузить их в PostgreSQL с Копировать команду Отказ Однако с таким подходом вы, вероятно, в конечном итоге с помощью многоступенчатого процесса для генерации и нагрузки тестовых данных. Если вы вызываете сценарий Python (который выводит CSV) в команде SQL Copy, то вы не можете заполнить несколько таблиц одной командой. Если вы используете несколько команд SQL Copy, она становится запутанной для эталонных идентификаторов в таблицах (внешних клавишах) в нескольких выполнениях сценариев Python. Оставшийся разумный подход представляет собой многоступенчатый: запустить скрипт Python, который сохраняет несколько файлов CSV на диск (по одному на таблицу базы данных), а затем запустить команду SQL Copy Come CSV для загрузки данных.
• Вы можете запустить сценарии Python, которые подключаются к PostgreSQL через клиентскую библиотеку, такую как psycopg2 Отказ Пакет PSYCOPG2 используется многими ORM, такими как Django Orm и Sqlalchemy, но он не налагает никаких ограничений на то, как вы обрабатываете свои данные – он просто дает интерфейс Python для подключения к PostgreSQL, отправке команд SQL и получения результатов Анкет

Спасибо за то, что присоединились ко мне в этом исследовании данных о загрузке тестов (в предыдущем сообщении в блоге ) и создание тестовых данных для PostgreSQL! Мы опробовали различные подходы и получили некоторые практические практики с кодом – я надеюсь, что это поможет вам понять, как использовать эти разные подходы, взвесить их компромиссы и выбирать, какой подход имеет наибольшее значение для вашей команды и проекта.

Если у вас есть какие-либо предложения или исправления, пожалуйста, дайте мне знать или Отправь нас Tweet И если вам интересно узнать больше о том, как мы улучшаем датчики восприятия, посетите нас в Tangram Vision Отказ

Оригинал: “https://dev.to/tangramvision/creating-postgresql-test-data-with-sql-pl-pgsql-and-python-efj”