Title: Insert same variable into multiple columns in Postgres with python
Tags: python;postgresql
Question: 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.
Comment: @AdrianKlaver I listed it using vals = [payout, etc] isnt that a list?

Comment: @JNevill good catch!

Comment: @AdrianKlaver I am not sure where to update is it in the SQL statement? Or in the cursor statement? If you show a demo I will select you as the correct since I am pretty sure you did get it

Comment: This `(val, val, val, val,...)` needs to be `(%s , %s, %s, %s...)` per [Parameters](https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries). @MikeOrganek it just needs to be a sequence of values, `list` or `tuple` will work.

Comment: `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 where `vals = {"val": payout}` and `(%(val)s,  %(val)s, %(val)s ...)`. Then you would not have to build a list of inputs just the dict and the `payout` value would be picked up by `%(val)s`.

Comment: I believe you only have 31 `val` but 32 columns and 32 items in your list (unless I'm mistaken). That will likely be the next error to pop.

Here is the accepted answer: Change to this:
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)
An alternate form, using named arguments, would be:
vals = {&quot;val&quot;: 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)'''


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 6, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2022:00:08/11/22', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<python><postgresql>', 'Title': 'Insert same variable into multiple columns in Postgres with python', 'ViewCount': 35, 'id': 73323938, 'user_AboutMe': '<p>Hi all I am a technologist, entrepreneur, adventurer, and photographer. Check me out at <a href="http://kyle.sponable.io" rel="nofollow noreferrer">my website</a></p>\n', 'user_AccountId': 2008027.0, 'user_DisplayName': 'Kyle Sponable', 'user_Reputation': 725.0, 'user_Views': 99.0, 'user_id': 1797307.0}}