CODEWITHSUNDEEP
sqljsonpython-3.xpostgresqlpsycopg2
KubiK888
KubiK888

Reputation: 4723

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

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"}]']

Upvotes: 1

Views: 2263

Answers (1)

B Troy
B Troy

Reputation: 181

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")

Upvotes: 1

Related Questions