PostgreSQL — Generating Fake Data

One of the main problems that I faced in the initial stages of trying to learn with data was getting the data needed. If we need a million rows, we cannot create that data manually. If we try to download it from somewhere, we will have to clean up the data which is also sort of a hectic task. In this article, I will share with you the script I use to create random data as and when I need it.

Prerequisites

1. Readiness to learn stuff 😉
2. Python 3.7+ (I used 3.10 for the script) — Refer to this documentation to install using pyenv
3. PostgreSQL (with psql) — Refer to the official PostgreSQL Documentation for the installation

I will be using the commands python and pip, but please use python3 or pip3 if your installation requires it. Also the commands are from the perspective of running from Mac (Mostly can be used for Linux based distros as well).

Setup

First, let’s set the things needed for us to run the scripts.

Clone the repository:

Clone the repository in the directory where you want to store it:

cd ~/path-to-dir
git clone https://github.com/NichuSPN/Data-Faker-Python.git

Setup terminal for Python:

Navigate to the Data-Faker-Python directory:

cd ~/path-to-dir/Data-Faker-Python

Install the required packages from requirements.txt:

pip install -r requirements.txt

Let’s call this terminal “Terminal Python 🐍”

Setup terminal for SQL:

Navigate to the Data-Faker-Python/data directory:

cd ~/path-to-dir/Data-Faker-Python/data

Login to PostgreSQL using the psql command:

psql <database-name> -U <user-name> -h <host> -p <port> 

Let’s call this terminal “Terminal SQL”

Code Walkthrough

Let’s walk through the code in the repository. From an overall view, there are 2 steps to creating data in PostgreSQL. We use a Python script to generate the data in CSV format and then we use an SQL script to get this CSV data into the required table. Let’s look at both scripts one by one.

Python Script

First, let’s start with the imported modules

from faker import Faker
import csv, random

faker is a library used to generate fake data. We have only touched the surface of this library in this script and there is more to this so read from this documentation to learn more about this.

Get Nishaanth S P’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

csv is a module that we use to write the generated data into CSV and random is a module that we use to generate random values for a few fields.

CSV_FILE_NAME = 'data/employees.csv'
COLUMNS = ['name', 'age', 'email', 'department', 'salary']
ROWS = 100

There are the 3 variables that need to be initialized.

• CSV_FILE_NAME — name of the CSV file. Make sure it is stored in the data folder for the SQL script to work as usual.
• COLUMNS — List for header in the CSV file to be generated
• ROWS — This defines the number of rows to be generated

Create a Faker object to be used to generate a fake name and email

fake=Faker()

Open the CSV file, create a writer object, and write the headers for the CSV

with open(CSV_FILE_NAME, 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(COLUMNS)

For ROWS numbers, generate the data needed, and write each row into the CSV using the writer object created

for i in range(ROWS):  
  name = fake.name()
  age = random.randint(22, 50)
  email = fake.email()
  department = random.choice(DEPARTMENTS)
  salary = random.randint(10000, 200000)
  writer.writerow([name, age, email, department, salary])

For name and email we use faker. For age and salary we use random.randint function and for departments we use random.choice to pick random department names from the list of values for the department.

After writing the row into the CSV file with the values generated, use writerow function of CSV writer object.

SQL Script

The objective of this script is to create a table employees with the data generated with the Python Script.

begin;

begin used to start a transaction. This makes sure that all the queries in the transaction run successfully or all fail together. To get more context on the same, read this article 😌.

create table employees_temp(name text, 
  age int, 
  email text, 
  department text, 
  salary bigint);

create table employees(name text, 
  age int, 
  email text, 
  department text, 
  salary bigint,
  primary key (name, email));

We create 2 tables one main table with a primary key constraint and a temp table without a primary key constraint. The reason for this is while we copy from CSV to a table, we cannot handle conflicts caused by constraints. To avoid that we add the data to a temp table first and then port data from that table to the next one using insert where we can handle conflicts

\copy employees_temp(name, age, email, department, salary) from 'employees.csv' with delimiter ',' csv header;

\copy command is used to copy data from a file to a table. Here we copy data from the CSV file we generated using the Python script to the temp table we created.

insert into employees (name, age, email, department, salary)
select name, age, email, department, salary from employees_temp
on conflict (name, email) do nothing;

From the temp table we write the data into the main table using insert-select and on conflict ignore the incoming row

drop table employees_temp;

Drop the temporary table we created.

commit;

Commit the changes to the database if it has passed through all these queries successfully. If any SQL statement errors out in the transaction PostgreSQL automatically runs ROLLBACK to rollback the changes done

Now that you know what these script mean, make the changes required for your use case and keep it ready

How to run?

Make sure you completed the steps in the Setup section before jumping in here

In Terminal Python 🐍 run,

python main.py

This runs the Python script and generates the CSV in the data folder

In Terminal SQL run,

\i script.sql

This runs the SQL script and ports the data from the generated CSV into the table that we want the data in.

Conclusion

By leveraging libraries such as Faker and following the structured setup process, we can quickly populate our tables with realistic data, making it invaluable for testing and development purposes. This approach not only saves time but also ensures that our applications can handle real-world data scenarios effectively.

So, the next time you need to fill your database with data, remember: with Python and PostgreSQL, faking it has never been more real! 🐍✨

Reference

https://github.com/NichuSPN/Data-Faker-Python/blob/main/README.md