Insert Python Nested Dictionary using Psycopg2-CodePudding

I am trying to insert nested dictionary structure into postgredb. My dictionary is as follows:

dict = {'x': {'2022-02-09': 0.8},'y':{'2022-02-14': 0.9},'z':{'2022-01-14': 0.4}}

and i want to insert into table like this:

a       b     c
x 2022-02-09 0.8
y 2022-02-14 0.9
z 2022-01-14 0.4

My code is follows as :

conn = pg.connect("dbname=foo user=foo_user")
cursor = conn.cursor()

for name , date in dict.items():
cursor.execute(

    """
     INSERT INTO "sometable" ("a","b","c")
              VALUES (%s,%s,%s)

    """, (name, date.keys(),date.values())

)conn.commit()
cursor.close()
conn.close()

when i run the code, i get psycopg2.ProgrammingError: can't adapt type 'dict_values'

What might be the solution to import dict values into postgre by using pyscopg2 adapter ?

CodePudding user response：

I get your approach but you are getting this wrong. When you get date.keys you will get ['2022-02-09', '2022-02-14', '2022-01-14']. However you need to execute them one by one.

Hence here is your should be like

for name , date_info in dict.items():
    for date, value in date_info.items():
        cursor.execute(
        """INSERT INTO "sometable" ("a","b","c") 
           VALUES (%s,%s,%s)""",
           (name, date, value))conn.commit()