Insert JSON Data into PostgreSQL using Python (Part 1)

Introduction

Python has its own built-in JSON library that not only creates JSON-compliant strings from almost any kind of Python object, but can also take a JSON string and convert it into Python objects. This article series will show you how to use Python to generate an SQL-compliant string that can insert some JSON data into Postgres when executed.

Prerequisites

You should have Python 3 installed and you’ll need to install the psycopg2 adapter for Postgres using the PIP package manager for Python if you haven’t already. Type pip3 -V into a terminal or command prompt window to verify that PIP is installed, and then use the following command to install the adapter:

You’ll also need to have a Postgres server running, and you’ll need to create a database and table for the JSON data. You can use the psql command line interface for Postgres to create them, and we’ll go over that in the next section if you haven’t done so already.

JSON data for Postgres

The last thing you’ll need is some JSON data you can use to test Python code on. The data should have JSON objects, with key-value pairs of data, nested inside of an array. Each JSON object, delimited by commas, will represent a single record for the table’s data, and the keys represent the column names, so all of their keys need to match:

Connect to psql

Try the psql command in your terminal prompt to connect to the CLI for Postgres. If you get a role-does-not-exist error you may need to explicitly connect to psql using the built-in superuser postgres:

You can also connect to a built-in database, like template1, and then create a new database once you’ve connected to psql:

Once you’re connected you can use the SET ROLE statement to switch to another user if you’d like:

If the role exists and is accessible the above command should return SET as a response.

Create a database for Postgres

Once you’re inside psql you should use the \list command to see all of the databases for your current user or role. If the desired database doesn’t exist you can execute an SQL statement to create one like with the following example:

The above execution should return a response of CREATE DATABASE.

Create a table in Postgres

Once you’ve created the database you can connect to it with the \c command followed by the database name, and then you should create a table for the database like so:

Executing the above SQL statement should return a response of CREATE TABLE. If you’d like to verify that the table was created use the \dt command and it should return a table that looks something like this:

Once you’re finished with psql you can just use the \q command to exit out of the command line interface.

Create a JSON file

If you don’t have any JSON data you can use the following JSON array as test data, and it conforms to the column names and constraints of the CREATE TABLE SQL statement from earlier:

NOTE: Make sure to save the data in a file that uses an extension like .json or .txt.

Use Python to parse JSON

At this point we’re ready to put together some Python code that can read and parse the JSON data.

Create a Python script

You’ll need to use a text editor or IDE with Python syntax to create a new Python script with the .py file extension. At the top of the script make sure to import the necessary library. In this case we’ll need Python’s json library as well a psycopg2 if you’re planning to execute the SQL string at the time of concatenation:

Python’s json library has two method calls you can use to read JSON data. The load() method allows you to deserialize the JSON data by loading it from a file whereas the loads() method (with an “s”) simply reads a Python string, from within the script itself, and converts it into a Python-compatible dict object or list of dictionary objects.

Conclusion

Now you should have the packages installed, the JSON data created, and everything you need to read the JSON data in a Python script. Check out part two of this series to see another example of how to parse and modify Python objects in order to transform them into a legitimate INSERT INTO SQL statements.