How to Load Data(JSON and TSV FORMATS) into PostgresDB using Python

#Import Libraries 

import psycopg2

import json

import os

import glob

import pandas as pd

import subprocess

from sqlalchemy import create_engine

#Establishing the  Connection to PostgresDB

conn = psycopg2.connect(user = "xxxx",

                         password = "xxxxx",

                         host = "127.0.0.1",

                         port = "5432",

                         database = "DB"

                        )

filenames = glob.glob('path/*.json')

df = []

for file in filenames:

    data = pd.read_json(file, lines=True)  (For JSON)

     data = pd.read_csv(file,'\t')  (For Tsv)

    df.append(data)

result = pd.concat(df)

 

engine = create_engine(("postgres://user:passwd@host:port/DB"))

connection = engine.connect()

#LOADING DATA INTO POSTGRESDB

table_name = 'xxx'

schema_name = 'xxx'

result.to_sql(table_name, connection,schema_name, method='multi', index=False, if_exists='append')

conn.commit()

print("connection is closed") 

 

#GENERATING .SQL FILES FOR POSTGRESDB

def backup_postgres_db(host, database_name, port, user, password, dest_file):

    process = subprocess.Popen(

    ['pg_dump',

    '--dbname=postgresql://{}:{}@{}:{}/{}'.format(user, password, host, port, database_name),

    '-f', dest_file,

     ],

    stdout=subprocess.PIPE

    )

   output = process.communicate()[0]

    return output

backup_postgres_db('127.0.0.1', 'db', 'port', 'xxxx', 'xxxx', path/filename.sql')