Search code examples
pythondatabasepostgresqlpsycopg

Postgresql table update

I updated a database table using postgresql from python My code was 

import psycopg2
connection=psycopg2.connect("dbname=homedb user=ria")
cursor=connection.cursor()
l_dict= {'licence_id':1}
cursor.execute("SELECT * FROM im_entry.usr_table")
rows=cursor.fetchall()

for row in rows:
   i=i+1
   p = findmax(row)
   #print p
   idn="id"
   idn=idn+str(i)


   cursor.execute("UPDATE im_entry.pr_table SET (selected_entry) = ('"+p+"') WHERE  image_1d ='"+idn+"'")
   print 'DATABASE TO PRINT'
cursor.execute("SELECT * FROM im_entry.pr_table")
rows=cursor.fetchall()
for row in rows:
    print row

I got the updated table displayed

But when i display updated table by psql as homedb=# SELECT * FROM im_entry.pr_table; i got an empty table displayed..what is wrong?? please help me

Solution

  • You're probably not committing the transaction, i.e. you need a connection.commit() after all your updates.

    There are various different settings you can make to the isolation level, e.g. autocommit, so you don't need to issue commits yourself. See, for example, How do I do database transactions with psycopg2/python db api?