How to insert JSON file data in to table

Question

I have a sample json file named a.json

The json data in a.json is as:

{
    "a cappella": {
        "word": "a cappella",
        "wordset_id": "5feb6f679a",
        "meanings": [
            {
                "id": "492099d426",
                "def": "without musical accompaniment",
                "example": "they performed a cappella",
                "speech_part": "adverb"
            },
            {
                "id": "0bf8d49e2e",
                "def": "sung without instrumental accompaniment",
                "example": "they sang an a cappella Mass",
                "speech_part": "adjective"
            }
        ]
    },
    "A.D.": {
        "word": "A.D.",
        "wordset_id": "b7e9d406a0",
        "meanings": [
            {
                "id": "a7482f3e30",
                "def": "in the Christian era",
                "speech_part": "adverb",
                "synonyms": [
                    "AD"
                ]
            }
        ]
    },.........
}

As suggested in my previous question I am looking on how to insert this data in to tables

Word: [word, wordset_id]
Meaning: [word, meaning_id, def, example, speech_part
Synonym: [word, synonym_word]

I tried reading file as:

import json
with open('a.json') as f:
    d = json.load(f)

when I tried printing all words as:

for word in d:
    print(word)

I got all words, but failed to get wordset_id for the same.

How can I insert the word and wordset_id in to the table word for the json format as above?

DBconnection as:

from flask import Flask
from flaskext.mysql import MySQL

app = Flask(__name__)
mysql = MySQL()
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'root'
app.config['MYSQL_DATABASE_DB'] = 'wordstoday'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

conn = mysql.connect()
cursor =conn.cursor()

Shubham Bakshi · Accepted Answer · 2019-12-27 07:16:50Z

1

When you try to execute code:

for word in d:
    print(word)

It will only print the keys of the json object, not complete value. Instead, you can try doing something like this,

for word in d:
    word_obj = d[word]
    wordset_id = word_obj['wordset_id']

    sql = "INSERT INTO Word (word, wordset_id) VALUES (%s, %s)"
    values = (word, wordset_id)
    cursor.execute(sql, values)

    meaning_obj_list = d[word]['meanings']
    for meaning_obj in meaning_obj_list:
        meaning_id = meaning_obj['id']
        definition = meaning_obj['def']
        example = meaning_obj.get('example', None)   # since it is not guaranteed that "example" key will be present in the data, it is safer to extract the value this way
        speech_part = meaning_obj['speech_part']

        sql = "INSERT INTO Meaning (word, meaning_id, def, example, speech_part) VALUES (%s, %s, %s, %s, %s)"
        values = (word, meaning_id, definition, example, speech_part)
        cursor.execute(sql, values)

    db.commit()

Also, refrain from using the keys names such as def as this is a keyword in python.

edited Dec 27, 2019 at 7:16

answered Dec 27, 2019 at 7:05

Shubham Bakshi

1567 bronze badges

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

6 Comments

KcH Over a year ago

Thanks a ton mate , I would try the same and would ask you back for any more queries

KcH Over a year ago

The only typo I found is values for val?

KcH Over a year ago

I guess I am wrong with connecting my DB too, would you show me your way if possible?

Shubham Bakshi Over a year ago

@Codenewbie you can add the code you are trying..I have some work now..will check it once I get free.

KcH Over a year ago

solved it mate by replacing db.commit() with conn.commit(), does it take hell of time to insert say above 10000 records?

|

Collectives™ on Stack Overflow

How to insert JSON file data in to table

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related