0

Team, I have a python nested dict object that I created after iterating over several files. I then converted it to json str with json_dumps then i passed it to pandas creating dataframe. Now, I uploaded it to database but my database is missing the rows.

python nested dictionary nested_dict

 {'av1':{'parent_child1': {'cpu': 3, 'server': 2, 'mixer': 3}, 'parent_child2': {'server': 9, 'mixer': 1, 'cpu': 5}, 'parent_child3': {'mixer': 4, 'server': 4, 'cpu': 6}, 'parent_child4': {'cpu': 2, 'mixer': 1, 'server': 3}, 'parent_child5': {'cpu': 224, 'server': 620, 'mixer': 131}}}}

json_dumps of nested_dict[av1])

 {"parent_child1": {"cpu": 3, "server": 2, "mixer": 3}, "parent_child2": {"server": 9, "mixer": 1, "cpu": 5}, "parent_child3": {"mixer": 4, "server": 4, "cpu": 6}, "parent_child4": {"cpu": 2, "mixer": 1, "server": 3}, "parent_child5": {"cpu": 224, "server": 620, "mixer": 131}}}

manual representation is

{
  “av1”: {
    “parent_child1”: {
      "cpu": 3,
      "server": 2,
      "mixer": 3
    },
    "parent_child2”: {
      "server": 9,
      "mixer": 1,
      "cpu": 5
    },
    "parent_child3”: {
      "mixer": 4,
      "server": 4,
      "cpu": 6
    },
    "parent_child4”: {
      "cpu": 2,
      "mixer": 1,
      "server": 3
    },
    "parent_child5”: {
      "cpu": 224,
      "server": 620,
      "mixer": 131
    }
  }
}

 df1 = pd.read_json.format_dict())
 df2=(df1['av1'].apply(pd.Series))

before pushing to Database below is dataframe which i converted with pandas

print(df1)


               parent_child1  parent_child2  parent_child3  parent_child4  parent_child5
server                   3              5              6              2              224
cpu                  2              9              4              3              620
mixer                3              1              4              1              131

So I need help push the dataframe as is such that I retain the servers too server,mixer,cpu but below shows am loosing the server,mixer,cpu and it is replaced by 0,1,2.

above parent_child1 is a column and server is row. but below I lost all servers. any hint how can i push them to table and then I should be able to run query like

select parent1_child1 from tble_db where row=server

print(df2) output

      parent_child1  parent_child2  parent_child3  parent_child4  parent_child5
0           3         5          6         2       224
1           2         9          4         3       620
2           3         1          4         1       131
AhmFM
  • 1,552
  • 3
  • 23
  • 53
  • I would skip the pandas conversion and parse the json into a database structure. You need to make a call how much you want to normalise your data structure in mysql. Will you create 1 table with a json column for children or will you create two tables and relate parent and child entries or will you create just a children table that has parent as a column as parent does not seem to have additional properties. – Shadow Sep 28 '22 at 19:23
  • very good probe. yeah: just a children table that has parent as a column as parent does not seem to have additional properties – AhmFM Sep 28 '22 at 19:25
  • OK, so where are you stuck with this? You seem to need two loops to generate the insert statements. – Shadow Sep 28 '22 at 19:44
  • am stuck at `print(df2)` if you see my rows are lost. `server,mixer,cpu` each child has unique has unique of those values. – AhmFM Sep 28 '22 at 20:21
  • Do not use pandas. Work with your json file. Or you need to change how you convert your json to pandas, but then your question has nothing to do with mysql. – Shadow Sep 28 '22 at 22:07
  • https://stackoverflow.com/questions/21104592/json-to-pandas-dataframe – Shadow Sep 28 '22 at 22:16

0 Answers0