在Postgres和python中将相同的变量插入多个列中

Question

我希望使用这个python脚本将相同的十进制值插入同一个PostgreSQL表中的多个列中，以生成一些测试数据。

 def insert_payout(payout):

     vals = [payout, payout, payout, payout, payout, payout, payout, payout, payout, payout, payout, payout,
        payout, payout, payout, payout, payout, payout, payout, payout, payout, payout, payout, payout,
        payout, payout, payout, payout, payout, payout, payout, payout] #works

       sql = '''INSERT INTO test_db.public.test_node
       (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26, col27, col28, col29, col30, col31, col32)
  VALUES
       (val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val, val ) '''

  conn = None
 
     print(vals)#remove after testing
     try:
         # read database configuration
         params = setparams()#managed elsewhere just connects to the db working
         # connect to the PostgreSQL database
         conn = psycopg2.connect(**params)
         # create a new cursor
         cur = conn.cursor()
         # execute the INSERT statement
         cur.execute(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(3.3)

我得到的错误是：

  [3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3]
   not all arguments converted during string formatting 

转换为十进制不起作用，我将其重新加工为int，这也不起作用。

Answer 1

改为：

def insert_payout(payout):

     vals = [payout, payout, payout, payout, payout, payout, payout, payout, payout, payout, payout, payout,
        payout, payout, payout, payout, payout, payout, payout, payout, payout, payout, payout, payout,
        payout, payout, payout, payout, payout, payout, payout, payout] #works

       sql = '''INSERT INTO test_db.public.test_node
       (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26, col27, col28, col29, col30, col31, col32)
  VALUES
       (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) '''

  conn = None
 
     print(vals)#remove after testing
     try:
         # read database configuration
         params = setparams()#managed elsewhere just connects to the db working
         # connect to the PostgreSQL database
         conn = psycopg2.connect(**params)
         # create a new cursor
         cur = conn.cursor()
         # execute the INSERT statement
         cur.execute(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(3.3)

使用命名参数的另一种形式是：

vals = {"val": payout}
sql = '''INSERT INTO test_db.public.test_node
       (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26, col27, col28, col29, col30, col31, col32)
  VALUES
      (%(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, 
%(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, 
%(val)s,%(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s, %(val)s)'''