Generate Postgres Test Data with Python (Part 2)

Introduction

In the first part of this article series we showed you how to setup a Python script that will create randomized Postgres data to use in conjunction with the INSERT INTO SQL statement. Here is the link to Part 1.

The last part of this installment will show you how to format the JSON data created by the script so that you can easily insert some test data into a PostgreSQL table.

Creating Postgres data

In the last article we declared a Python function called create_postgres_json() that randomly generates data and puts them into a list of dict objects meant to represent a JSON array of potential Postgres data.

The following line of code is an example of how you can call the function and specify, with an integer argument, the total number of records you’d like to generate:

NOTE: The above function call should return a Python list object containing dict “records” for Postgres. To get the complete code for that function you can visit the series’ first article, or scroll down to the end of this article.

Python dict to JSON

You can pass the list of dictionary objects (json_records) to the json.dumps() method to convert the Python object into a JSON string:

You should be able to convert the string back to a JSON-compliant dictionary object using the following syntax:

If it raises an error then there’s a problem with the data, or you’re passing the wrong type of data to the method call. The json.loads() method will only accept JSON-compatible data such as a dictionary or a list of dictionary objects.

Create a Postgres SQL string

The last step is to create a function that converts the data into a valid SQL string that is able to insert documents into a Postgres table.

Define another function that can accept a Python list of dictionary objects, and a string for the Postgres table name, as arguments:

NOTE: Naming conventions for Postgres objects are lowercase strings that use underscores (_) instead of spaces, dashes, or hyphens. The above code will ensure that your Postgres column names conform to these conventions.

Now declare a string for the Postgres INSERT INTO SQL statement:

Then declare a list for the records and enumerate() over the list of dictionary objects containing the randomized data:

Fix the Postgres record data

Now you’ll need to iterate or enumerate over the values for each record and fix them so that Postgres won’t raise any syntax errors when you attempt to execute the SQL string:

NOTE: All strings in Postgres must be enclosed within single quotation marks, so the above code checks the JSON (dict) object for any Python string types (str), and then use replaces any single quotes within the string for an escape sequence that will allow for apostrophes in the data.

Append the Postgres records to the string

The last step is to append each record to the list and then iterate over the records one last time in order to append parenthesis around each record string before completing the INSERT INTO statement:

Create the ‘INSERT INTO’ SQL string

The next bit of code will dynamically pass the Postgres table name to the script, using a command line argument, while running the Python script:

NOTE: The sys.argv list must have a length that is greater than one because the first element in the list is just a string of the script’s name, so you’ll need to access all of the subsequent elements to get the actual system arguments passed by the user.

Call the create_insert_records() function that we created earlier and make sure to pass the list of dict objects to it, as well as the table name string:

If you don’t want to use Python and psycopg2 to insert the records, then just save your code as-is, execute the script with python3, and copy the SQL string output in your terminal or command prompt window so that you can paste the results into that particular database’s psql interface.

However, if you would like to have the psycopg2 adapter insert the records automatically, you’ll have to call the function and then pass the returned string to a psycopg2 cursor object.

The following code does this within a try-catch indentation block in case there are any raised exceptions or SQL syntax errors in the string:

Execute the Python script

Make sure to save your script, and then use the python3 command (followed by your script’s name) to execute the Python code and it should generate and insert the randomized Postgres data. The following example will instruct Python to create data for a table named python_test:

You can also just run it with python3 my_script.py and it will attempt to insert the data into the Postgres table name hard coded into the script.

Conclusion

If everything works as expected you should be able to access psql and have it return the inserted records using the SELECT * FROM python_test; SQL statement (make sure to type q to close the full screen viewer).

Since the function uses randomly generated UUIDs for the records’ IDs, you can run the script multiple times and it should just insert more records without having it return a duplicate key SQL error.

DISCLAIMER: This code should be used for development and experimental purposes only. Running automated scripts like this on production servers could potentially leave your data vulnerable to security risks and SQL injection attacks.

We’ve covered how to create randomized JSON data, and then format it to be an SQL-compliant INSERT INTO string. You should now have some test data to play around with inside of your PostgreSQL table simply by running a Python script!

Just the Code