Postgresql add values to a column with a for loop (on Python)

Question

I've added a new column to my postgresql table. Now I want to add values to this new added column. The only commands I found allow adding values but not in chosen rows of the column. What I'm tryin to have is:

cursor.execute('SELECT Column_name FROM Table_name')
rows = cursor.fetchall()
cursor.execute('ALTER TABLE Table_name ADD NewColumn_name type')
for row in rows:
     #Here I want to have a commands that adds a value (result of a function: func(row)) to every row of the new column, one by one something like: 

     NewColumn_name[i] = func(row)
     i = i+1

What is the primary key or unique column?

Clodoaldo Neto
– Clodoaldo Neto

2017-08-14 12:34:59 +00:00
Commented Aug 14, 2017 at 12:34 — Clodoaldo Neto
– Clodoaldo Neto, Commented Aug 14, 2017 at 12:34
What does func do?

Clodoaldo Neto
– Clodoaldo Neto

2017-08-14 12:36:47 +00:00
Commented Aug 14, 2017 at 12:36 — Clodoaldo Neto
– Clodoaldo Neto, Commented Aug 14, 2017 at 12:36

Laposhasú Acsa · Accepted Answer · 2017-08-16 09:31:00Z

0

You need to execute UPDATE statements in your loop's body. Instead of this:

 NewColumn_name[i] = func(row)

try something like this (documentation):

cursor.execute('UPDATE Table_name SET NewColumn_name = %s WHERE id = %s', (func(row),row[0]);

Assuming the Table_name's first row is a primary index.

edited Aug 16, 2017 at 9:31

answered Aug 14, 2017 at 12:34

Laposhasú Acsa

1,59017 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ben Jo Over a year ago

It actually worked at the beginning ( I printed the new added values) bit while it was running it ends with an error : SSL SYSCALL error: EOF detected with python and psycopg

Ben Jo Over a year ago

I suppose it can't be due to memory problem because they're not more than 10000 new values to add with the UPDATE querry !

Laposhasú Acsa Over a year ago

Did your read this thread? stackoverflow.com/questions/24130305/…

Ben Jo Over a year ago

Yep I did .. and I think I still have to add free more space. Does adding values like this take more space than adding them directly from a csv file? Because I dropped the whole table that normally contains 5 rows and replaced it with one with only 2 rows ... and with the first I don't get any memory problems

Gordon Linoff · Accepted Answer · 2017-08-14 12:32:21Z

0

Are you looking for update?

update table_name
    set column_name = ?;

Fill in the ? with the value you want (using a parameter is much preferred to munging the query string).

answered Aug 14, 2017 at 12:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Postgresql add values to a column with a for loop (on Python)

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related