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:

1
pip3 install psycopg2

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:

1
2
3
4
[
    {"col_name1" : "hello", "col_name2" : 1234},
    {"col_name1" : "world", "col_name2" : 4321}
]

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:

1
sudo -u postgres psql

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

1
psql -U postgres -d template1

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

1
SET ROLE orkb;

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:

1
CREATE DATABASE python_data;

The above execution should return a response of CREATE DATABASE.

Screenshot of a CREATE TABLE example for insert JSON data into PostgreSQL using Python

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:

1
2
3
4
5
6
7
CREATE TABLE json_data (
    id UUID PRIMARY KEY,
    str_col VARCHAR(500),
    int_col SMALLINT,
    bool_col BOOLEAN,
    float_col DECIMAL
);

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:

1
2
3
4
5
           List OF relations
 Schema |   Name    | TYPE  |  Owner  
--------+-----------+-------+----------
 public | json_data | TABLE | postgres
(1 ROW)

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

Screenshot of a CREATE TABLE example for insert JSON data into PostgreSQL using Python

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[
    {
        "id": "cbf071a789d04f25b28b95976611c876",
        "str_col": "data's positronic brain data's positronic brain",
        "int_col": 1435,
        "bool_col": true,
        "float_col": 25.11621924571661
    },
    {
        "id": "9e5a9557bbac4c94bdb7cb8ef845cf29",
        "str_col": "Postgres' psycopg2 adapter examples",
        "int_col": 3774,
        "bool_col": false,
        "float_col": 30.696120169189474
    },
    {
        "id": "ef3051e93c034d16b42d2915d13e4a7a",
        "str_col": "type orkb's tutorials",
        "int_col": 1586,
        "bool_col": false,
        "float_col": 4.443295005013988
    }
]

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:

1
2
3
4
5
# import Python's built-in JSON library
import json, sys

# import the psycopg2 database adapter for PostgreSQL
from psycopg2 import connect, Error

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.