Migrating Data from MongoDB to Postgres Using Python Script. Able to load the data in Postgres but only in 1st Column in JSON Format

Viewed 414

I loaded the data from Mongo to Postgres with python script but, I am not able to place the values in with respective columns of the Postgres. Data is in JSON Format.

import import psycopg2
from pymongo import MongoClient
from decimal import Decimal
from bson.decimal128 import Decimal128
from bson.objectid import ObjectId
from datetime import datetime
import time

class JSONEncoder(json.JSONEncoder):
    def default(self, o):
        if isinstance(o, ObjectId):
            return str(o)
        if isinstance(o, Decimal):
            return float(o)
        if isinstance(o, Decimal128):
            return float(str(o))
        if isinstance(o, datetime):
            return str(o)
        return json.JSONEncoder.default(self, o)

client = MongoClient("mongodb+srv://reporting:reporting123@miramira.ntara.mongodb.net")
db=client.Bookstore
col=db.Book
mdbcur=col.find()
with psycopg2.connect(host="localhost",
    database="Book",
    user="postgres", 
    password="root") as pgconn:
    pgcur=pgconn.cursor()

    for doc in mdbcur:
        print(doc)
        data=json.dumps(doc, cls=JSONEncoder)
        query_sql = """insert into booktable VALUES ('""" + data + """'::jsonb)"""
        pgcur.execute(query_sql)
        pgconn.commit()
        time.sleep(1)
    pgcur.close()
    mdbcur.close()

I need to load the data in the string format in each respective column of Postgres

As you can see in the picture the way how data is loaded in the id column in JSON format

0 Answers
Related