Insert a nested Json into MySQL table

Question

I want to move data from DocumentDb hosted on Amazon into MySQL table(avoid duplicate insert so I am using 'INSERT IGNORE INTO'). The data in the DocumentDb looks like:

   [{
    "_id": {
        "$oid": "5f0e2c96eebd1c040a42523c"
    },
    "packNumber": 324,
    "DMMMeasuredCurrent": 1.75,
    "BMUReportedCurrent": 1.76,
    "error": 0.5,
    "operator": "Abc",
    "notes": "na",
    "reworks": [],
    "createdAt": {
        "$date": 1594764438975
    },
    "updatedAt": {
        "$date": 1594764438975
    },
    "__v": 0
}, , {
    "_id": {
        "$oid": "5f7b390701476b835e4379dd"
    },
    "packNumber": 420,
    "DMMMeasuredCurrent": 1.75,
    "BMUReportedCurrent": 1.74,
    "error": 0.5,
    "operator": "xyz",
    "notes": "l",
    "reworks": [],
    "createdAt": {
        "$date": 1601911047462
    },
    "updatedAt": {
        "$date": 1601911047462
    },
    "__v": 0
}]

I created the Table in MySql as below: create table auxcurrents1

(
BMUReportedCurrent varchar(50),
DMMMeasuredCurrent varchar(50),
notes   varchar(500),
packNumber  varchar(50),
__v varchar(50),
createdAt   varchar(50),
updatedAt   varchar(50),
operator    varchar(50),
idno    varchar(50),
reworks varchar(50)
)

How can I insert the data I am getting from DocumentDb to MySQL table? The following code, the snippet is what I have tried so far, but it didn't insert the data inside the MySQL table.

    import pymongo
    import sys
    from bson.json_util import dumps, loads
    from bson import json_util
    import mysql.connector
    from mysql.connector import connection
    #import MySQLdb
    import json
    from pandas.io import sql
    from sqlalchemy import create_engine
    import pandas as pd
    from pandas.io.json import json_normalize
    
    client = pymongo.MongoClient('mongodb://user:passwrd@host:27017/?ssl=**=.pemFile
    print('DocumentDb connected')
    
    
    #Create MySQL Connection
    mysqlConnection = mysql.connector.connect(host='host',database='db', user='user', password='passwrd',port=3306)
    mysqlCursor = mysqlConnection.cursor()
    print('MySQL Connection Established')
    
    
    #Specify the database to be used
    db = client.everestdocumentdb
    col=db.auxcurrents.find()
    print('The next line will print col')
    print(json_util.dumps(col))
    
    
    #Insert Into MySQL
    rows = ("INSERT IGNORE INTO table1 VALUES(%s)")
    mysqlCursor.executemany(rows,test)
    mysqlConnection.commit()

    print('Ran the db.execute command')

Any help is much appreciated. Thanks in advance.

Answer 1

The more obvious problem here is you didn't set test anywhere before the row mysqlCursor.executemany(rows, test)

But the main point is to recall MySql is, unlike MongoDB, a relational database so you can't just insert a JSON on it. You can't do something like INSERT INTO myTable VALUES (myJson) expecting that each JSON field will populate the correct column in the table.

To do what you want, first, you need to convert your JSON to a list of dictionaries and then convert it to a list of lists. Something like that:

import json

with open("a.json") as data:
    dictionary = json.load(myJsonString)

rows = []
for field in dictionary:
    rows.append((
        field["_id"]["$oid"],
        field["packNumber"],
        field["DMMMeasuredCurrent"],
        #...other columns
        field["__v"]
    ))

print(rows)

Now you can use

sql = ("INSERT IGNORE INTO table1 VALUES(%s, %s, %s,...)")
mysqlCursor.executemany(sql,rows)

(Notice you need a %s for each column)

Besides that, you need to guarantee that the field order match with the columns order of database or (the better option IMO) explicitly declare the column names in the INSERT:

INSERT INTO table Col1, Col2, Col3,...,ColN VALUES (%s, %s, %s,...,%s)")

Finally, you may need to have to work the field reworks once you are converting a list to a String and also the date fields that may need an explicit conversion.

That is all. I hope I have helped a bit.