psycopg2 (insert, update) writing problem

Question

I can send select queries with any problem but when I send update and insert queries it start to wait the thread and don't respond anymore. I couldn't be sure but it seems like a loop.

I know we must use "commit()" for applying changes but it doesn't work.

Here is my code:

import psycopg2
conn = psycopg2.connect("dbname='test' user='postgres' host='localhost' password='xx");
cursor = conn.cursor()
cursor.execute("UPDATE recording SET rank = 10 WHERE id = 10;")
conn.commit()
cursor.close ()

Pumice

Answer 1

The problem is psycopg2 doesn't have support for threading.

Pumice

Answer 2

import psycopg2

conn = psycopg2.connect(
       database="dbasename",user="username",
       password='your_password',host='web_address',
       port='your_port')
cursor = conn.cursor()
cursor.execute(
  "UPDATE table_name SET update_column_name=(%s)"
  " WHERE ref_column_id_value = (%s)", 
  ("column_name","value_you_want_to_update",));
conn.commit()
cursor.close()

You did not format your execute statement correctly.

Contumelious

We just need to remove the ; – Francie

Answer 3

It is most likely a lock in the database, with thread/processes trying to update the same record.

Dyslexia

I use threading in Python for updating rows (each query updates different row). is there any way to update records without locking? – Pumice

It depends on what you have to do. Using an autocommit transaction would ensure to have short transactions and the locked process would work again soon, but it really depends on the desired effect. – Dyslexia

Answer 4

I had a hard time getting @jdeyrup answer to work, though I think it was correct for at the time. Maybe something changed? Nonetheless I am putting what I got to work:

id is something distinguishable on the row that you want to update and that postgres can find with a where clause.

id = 0
curs.execute("UPDATE {} SET {} = {} WHERE id = {}".format('"name_of_table"', '"column_to_update"', "'new_value'", id))

I was able to loop through all my entries and replace values with the below:

curs.execute('Select * from "name_of_table"')
# e[0] is typically the primary key or "id" in my case.
for e in curs.fetchall():
    curs.execute("UPDATE {} SET {} = {} WHERE id = {}".format('"name_of_table"', '"column_name"', "'new_value'", e[0]))
conn.commit()

Rooney