Writing JSON to SQLite

Question

I'm trying to save a JSON file to a database. I have very little experience but after doing a little searching, I found this code

import json
import sqlite3

JSON_FILE = "some.json"
DB_FILE = "some.db"

traffic = json.load(open(JSON_FILE))
conn = sqlite3.connect(DB_FILE)

foo = traffic[0]["foo"]
bar = traffic[0]["bar"]

data = [foo, bar]

c = conn.cursor()
c.execute('create table table_name (foo, bar)')
c.execute('insert into table_name values (?,?)', data)

conn.commit()
c.close()

However, I'm thrown an error KeyError: 0.

The JSON that I'm testing with is

{
   "name":"",
   "children":[
      {
         "name":"Level 1",
         "children":[
            {
               "name":"Level 2",
               "children":[
                  {
                     "name":"Level 3",
                     "children":[
                        {
                           "name":"Level 4",
                           "children":[
                              {
                                 "name":"Speed",
                                 "children":null,
                                 "id":6
                              }
                           ],
                           "id":5
                        }
                     ],
                     "id":4
                  }
               ],
               "id":3
            }
         ],
         "id":2
      },
      {
         "name":"Level 1",
         "children":[
            {
               "name":"Level 2",
               "children":[
                  {
                     "name":"Level 3",
                     "children":[
                        {
                           "name":"Level 4",
                           "children":[
                              {
                                 "name":"Cost",
                                 "children":null,
                                 "id":11
                              }
                           ],
                           "id":10
                        }
                     ],
                     "id":9
                  }
               ],
               "id":8
            }
         ],
         "id":7
      },
      {
         "name":"Level 1",
         "children":[
            {
               "name":"Level 2",
               "children":[
                  {
                     "name":"Level 3",
                     "children":[
                        {
                           "name":"Level 4",
                           "children":[
                              {
                                 "name":"Manufacturability",
                                 "children":null,
                                 "id":16
                              }
                           ],
                           "id":15
                        }
                     ],
                     "id":14
                  }
               ],
               "id":13
            }
         ],
         "id":12
      }
   ],
   "id":1
}

How exactly would I write this JSON, or a similar one, to a database?

Answer 1

The code you've shown creates a database with one table called "table_name" containing two columns, "foo" and "bar." This structure does not match the JSON file you are trying to store.

You will need to change the SQL queries to create a database schema that better represents the JSON structure you are trying to store.

For example, you could create a table with three columns, defined in the DDL below:

CREATE TABLE table_name (
    id     INTEGER PRIMARY KEY,
    name   VARCHAR,
    parent INTEGER
);

Your JSON is hierarchical, which causes some issues with database design. While self-referencing tables are a contested issue, they would be able to store your JSON and maintain the structure.

If you want to simply store the JSON string in a database, you could do something like this:

CREATE TABLE table_name (
    id              INTEGER PRIMARY KEY,
    json_contents   VARCHAR
);