Converting a dictionary where each value is a list and each item in that list is another dictionary-CodePudding

I have a dictionary where each key is an id number and each value is a list of dictionaries, so it looks something like this: {'id number':[{'key1a': 'value1a', 'key2a': 'value2a', 'key3a': 'value3a'},{'key1b': 'value1b', 'key2b': 'value2b', 'key3b': 'value3b'}], 'id number'...etc.} The dictionary keys within the list are always the same and there are always 3 items in these dictionaries, however, the length of these lists vary.,

What I am trying to do is input this dictionary into PostgreSQL with psycopg2. From what I see from the documentation, it is best to input as tuples, so I am trying to convert this information into a list of tuples. The trick is, I need the ID key to remain the same, as the first item in the tuple, for each value dictionary item it is paired with.

So the list of tuples should look like this [(10001, 1a, 2a, 3a), (10001, 1b, 2b, 3b), (10002,...), etc. so that the table would look like the one below, if I'm not mistaken.

ID	value1	value2	value3
10001	a	a	a
10001	b	b	b

I've tried iterating like so: for key in dict: for i in key: print(i) (with the right indention), but this just prints the actual characters in the id. Any help would be appreciated. I'm probably making it more complicated than I need to.

CodePudding user response：

As far as I've understood the problem statement, Here's an example code for it. I've taken your sample input as a refernce and its coded in python.

    d={111:[{'key1a': 'value1a', 'key2a': 'value2a', 'key3a': 'value3a'},{'key1b': 'value1b', 'key2b': 'value2b', 'key3b': 'value3b'}],222:[{'key1a': 'value1a', 'key2a': 'value2a', 'key3a': 'value3a'},{'key1b': 'value1b', 'key2b': 'value2b', 'key3b': 'value3b'}]}
result = [(int(key), *item.values()) for key, value in d.items() for item in value]
print(result)

OUTPUT : [(111, 'value1a', 'value2a', 'value3a'), (111, 'value1b', 'value2b', 'value3b'), (222, 'value1a', 'value2a', 'value3a'), (222, 'value1b', 'value2b', 'value3b')]

CodePudding user response：

Modify dict to get list of lists.

d = {'10001':[{'key1': 'a', 'key2': 'a', 'key3': 'a'},{'key1': 'b', 'key2': 'b', 'key3': 'b'}],
     '10002': [{'key1': 'a2', 'key2': 'a2', 'key3': 'a2'},{'key1': 'b2', 'key2': 'b2', 'key3': 'b2'}, {'key1': 'c2', 'key2': 'c2', 'key3': 'c2'}]}

row_list = []
for id in d:
    for item in d[id]:
        item_list = []
        item_list.append(int(id))
        for ky in item:
            item_list.append(item[ky])
        row_list.append(item_list)
row_list
[[10001, 'a', 'a', 'a'],
 [10001, 'b', 'b', 'b'],
 [10002, 'a2', 'a2', 'a2'],
 [10002, 'b2', 'b2', 'b2'],
 [10002, 'c2', 'c2', 'c2']]

Insert data into table

create table val (id integer, value1 varchar, value2 varchar, value3 varchar);

import psycopg2

con = psycopg2.connect(dbname="test", host='localhost', user='postgres', port=5432)
cur = con.cursor()
cur.executemany('insert into val values(%s, %s, %s, %s)', row_list)
con.commit()

select * from val;
  id   | value1 | value2 | value3 
------- -------- -------- --------
 10001 | a      | a      | a
 10001 | b      | b      | b
 10002 | a2     | a2     | a2
 10002 | b2     | b2     | b2
 10002 | c2     | c2     | c2

The above uses executemany which does not scale well. If you are going to be inserting large numbers of values then:

from psycopg2.extras import execute_batch
execute_batch(cur, 'insert into val values(%s, %s, %s, %s)', row_list)
con.commit()

This uses execute_batch from Fast execution helpers which inserts(in this case) the data in batches instead of iterating over the data as executemany does.