I want to insert the same decimal value into multiple columns in the same PostgreSQL table using this python script to generate some testing data
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)
The error I am getting is this:
[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
Casting to a decimal doesn't work and I reworked it to int and that doesn't work either.
(val, val, val, val,...)needs to be(%s , %s, %s, %s...)per Parameters. @MikeOrganek it just needs to be a sequence of values,listortuplewill work.vals = [payout, payout, ...]is not the problem, it is the other end(val, val...)needs to be(%s, %s...). You could simplify further by using named arguments wherevals = {"val": payout}and(%(val)s, %(val)s, %(val)s ...). Then you would not have to build a list of inputs just the dict and thepayoutvalue would be picked up by%(val)s.valbut 32 columns and 32 items in your list (unless I'm mistaken). That will likely be the next error to pop.