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
.
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.
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