How to create a new table in Postgresql from a .json file using Python and psycopg2?

Question

I am new to postgresql. I would like to insert information from .json and create a new table in Postgresql using python/psycopg2. I have looked over some StackOverflow posts and psychopg2 documentation without getting much further. The closest question is here, from which I derived the following:

The test .json file is as follows (which only has 1-level i.e. no nested .json structure):

[{"last_update": "2019-02-01"}]

Attempted python code:

import psycopg2
from psycopg2.extras import Json
from psycopg2 import Error
from unipath import Path
import io

def insert_into_table(json_data):
    try:
        with psycopg2.connect(  user = "thisuser",
                                password = "somePassword",
                                host = "127.0.0.654165",
                                port = '5455',
                                database = "SqlTesting") as conn:

            cursor = conn.cursor()
            read_json = io.open(data_path, encoding='utf-8')
            read_json_all = read_json.readlines()

            query = "INSERT INTO new_table VALUES (%s)"
            cursor.executemany(query, (read_json_all,))
            conn.commit()

            print("Json data import successful")

    except (Exception, psycopg2.Error) as error:
        print("Failed json import: {}".format(error))

insert_into_table(data_path)

The above code didn't work regardless whether new_table didn't exist or if it was created manually as a place-holder.

Rather, it produced the following error message:

Failed json import: relation "new_table" does not exist
LINE 1: INSERT INTO new_table VALUES ('[{"last_update": "2019-02-01"...

During debugging, I saw:

for i in read_json:
    print (i)

# will result
# [{"last_update": "2019-02-01"}]

And

print (read_json_all)

# Will result
# ['[{"last_update": "2019-02-01"}]']

B Troy · Accepted Answer · 2019-02-20 06:40:14Z

1

I think you might want to use sqlalchemy to put your data into the postgres DB. Below, I used a very simple json file, and created a Pandas DataFrame. I then used sqlalchemy to place it into the DB. Check the code here. It should get you where you want to go.

import psycopg2
import pandas as pd
import sqlalchemy
from sqlalchemy import create_engine
import json

from pandas.io.json import json_normalize
with open('example_1.json') as data_file:    
    d = json.load(data_file)

def create_table():
   conn=psycopg2.connect("dbname='SqlTesting' user='thisuser' password='somePassword' host='localhost' port='5432' ") 
   cur=conn.cursor() 
   cur.execute("CREATE TABLE IF NOT EXISTS new_table (color TEXT, fruit TEXT, size TEXT)")  
   conn.commit() 
   conn.close() 

create_table()

df = json_normalize(d)

engine = create_engine("postgresql+psycopg2://thisuser:somePassword@localhost:5432/SqlTesting")
df.to_sql("new_table", engine, index=False, if_exists='append')
print("Done")

edited Feb 20, 2019 at 6:40

answered Feb 20, 2019 at 6:26

B Troy

1911 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

KubiK888 Over a year ago

Thank you, but the objective is to import json to postgresql in one step only, unless it is not possible.

KubiK888 Over a year ago

I have read through more material, and it seems your solution is one of the more efficient ways to insert data, will accept as answer. Thanks.

rubynorails Over a year ago

Apparently, in pandas v1.0.0, pandas.io.json.json_normalize was deprecated and relocated to pandas namespace itself at pandas.json_normalize. I haven't tested this, so I don't want to edit your answer, but I do know that it will not work as-is right now. The correct import statement should be from pandas import json_normalize.

Collectives™ on Stack Overflow

How to create a new table in Postgresql from a .json file using Python and psycopg2?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related