PostgreSQL Question posted in
The official documentation can be found here.

Bulk update in PostgreSQL with psycopg2 doesn't work

def update_database(self, results):
    try:
        length = len(self.unique_ids)
        update_data = [(self.unique_ids[i], results[i]) for i in range(length)]
        query = """
                UPDATE public.post_data AS p
                SET content = t.content
                FROM (VALUES %s)
                AS t (unique_id, content)
                WHERE p.unique_id = t.unique_id;
                """

        template = '(%s, %s)'
        execute_values(self.cursor, query, update_data, template=template)
        self.conn.commit()
        logging.info("Successfully Updated")


    except Exception as error:
        logging.warning(f"Update Error -> {error}")

    finally:
        self.conn.close()
        self.cursor.close()

This is the code i execute for bulk update. When i run the query separately in query console it works just fine, doesn’t properly work this way though.

Tags:

2

Answers


  1. 0

    Change self.cursor to self.conn.cursor().

    Even better, use with to ensure the connection is closed after the transaction.
    like:

    with self.conn.cursor() as cursor:
    execute_values(cursor, query, update_data, template=template)
    self.conn.commit()
    logging.info("Successfully Updated")
    Login or Signup to reply.
  2. 0

    Thank you so much for your answer, unfortunately it didn’t work. On the other hand i have realized another interesting point here. When i try to run this:

    from psycopg2 import connect
from decouple import Config, RepositoryEnv

class Postgres:
    def __init__(self):
        connection_params = Postgres.get_connection_params()
        self.conn = connect(**connection_params)


    @staticmethod
    def get_connection_params():
        config = Config(RepositoryEnv("data/.database_env"))
        connection_params = {
            'dbname': config("dbname"),
            'user': config("user"),
            'password': config("password"),
            'host': config("host"),
            'port': config("port")
        }
        return connection_params


if __name__ == "__main__":
    postgres = Postgres()
    postgres.conn.cursor().execute("UPDATE public.post_data SET content='abc' WHERE unique_id='abc'")
    postgres.conn.commit()

    It doesn’t make any change in database at all. What can be causing it? Note: The table and columns indeed exist.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search