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.

INSERT IGNORE INTO table SET ?

Ravi
– Ravi

2020-10-06 19:38:26 +00:00
Commented Oct 6, 2020 at 19:38 — Ravi
– Ravi, Commented Oct 6, 2020 at 19:38
@aRvi how to use? Can you modify it as per my code?

Shrads
– Shrads

2020-10-06 19:44:37 +00:00
Commented Oct 6, 2020 at 19:44 — Shrads
– Shrads, Commented Oct 6, 2020 at 19:44

Cristiano · Accepted Answer · 2020-10-15 01:31:34Z

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.

edited Oct 15, 2020 at 1:31

answered Oct 6, 2020 at 21:24

Cristiano

1342 silver badges4 bronze badges

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

4 Comments

Shrads Over a year ago

Thanks for the detailed reply. I have now understood the logic based on your explanation. But it is giving an error: field["_id"]["$oid"], TypeError: 'ObjectId' object is not subscriptable.. Can you help me how to fix this?

Cristiano Over a year ago

That means the object is not a container of objects like a list. You need to figure out what type of object field is, and if it is a list as expected, then see if field["_id"] is also a list. Did you used json.load ?

Shrads Over a year ago

Yes, I used json.loads

Cristiano Over a year ago

My bad @Shrads. My example was incomplete. json.load expect a file stream, not a string. I fixed my answer to reflect that. You can use a string as well instead of a file, but in that case, you need to use 'json.loads' (with an "s" at the end).

Collectives™ on Stack Overflow

Insert a nested Json into MySQL table

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related