I am trying to generate some sql for a python update script I want to make it so it reads in a json file and then extracts columns name

 def insert_payout(payout, items, node_name):#TODO add data source in pycharm

       vals = {"val": payout}
       colDict = json.loads(items)
       colList = list(nftDict.keys())
       table = 'test.public.'   node_name
       col_list = []
       for i in range(len(colList)):
           col_list.append(str({colList[i]}))

       base_sql = sql.SQL(
           '''INSERT INTO {} ({}) 
           VALUES ({})
           '''). \
           format(sql.Identifier(table),
                sql.SQL(", ").join(map(sql.Identifier, col_list)),
                sql.SQL(', ').join(sql.Placeholder() * len(col_list))
                )
     

      conn = None

      try:
          # read database configuration
         params = setparams()
         # connect to the PostgreSQL database
          conn = psycopg2.connect(**params)
         # create a new cursor
          cur = conn.cursor()
         # execute the INSERT statement
         #cur.execute(base_sql, vals)
         # commit the changes to the database
         conn.commit()
         # close communication with the database
          cur.close()
      except (Exception, psycopg2.DatabaseError) as error:
         print(error)
     finally:
         if conn is not None:
             conn.close()


  if __name__ == '__main__':
          
         insert_payout(7.4, item_json, 'test_write_table')

Error is

 TypeError: Composed elements must be Composable, got 'N' instead

New Error is:

  "ItemUniqueName31": [
    "Item_ID_Number31",
    "<something31>",
    "BooleanActive"
],
"ItemUniqueName32": [
    "Item_ID_Number32",
    "<something32>",
    "BooleanActive"
]
 }


 dict is not a sequence

And I am not adding the operator correctly I used this answer as a guide once I get this working I am going to work in the vals

CodePudding user response：

Simpler solution:

table = "test_table"
colList = ['test', 'test2', 'test3']
base_sql = sql.SQL(
         '''INSERT INTO {} ({}) 
         VALUES ({})
         '''). \
         format(sql.Identifier(table),
                sql.SQL(", ").join(map(sql.Identifier, colList)),
                sql.SQL(', ').join(sql.Placeholder() * len(colList))
                )

print(base_sql.as_string(con))                                                                                                                                            
INSERT INTO "test_table" ("test", "test2", "test3") 
         VALUES (%s, %s, %s)