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

Postgresql – Psycopg2 : Insert multiple values if not exists in the table

I need to insert multiple values into a table after checking if it doesn’t exist using psycopg2.
The query am using:

WITH data(name,proj_id) as (
    VALUES ('hello',123),('hey',123)
)
INSERT INTO keywords(name,proj_id)
SELECT d.name,d.proj_id FROM data d 
WHERE NOT EXISTS (SELECT 1 FROM keywords u2 WHERE
u2.name=d.name AND u2.proj_id=d.proj_id)

But how to format or add the values section from tuple to (‘hello’,123),(‘hey’,123) in query.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    insert_query = """WITH data(name, proj_id) as (
                  VALUES (%s,%s)
                  ) 
                INSERT INTO keywords(name, proj_id) 
                SELECT d.name,d.proj_id FROM data d 
                WHERE NOT EXISTS (
                  SELECT 1 FROM keywords u2 
                  WHERE u2.name = d.name AND u2.proj_id = d.proj_id)"""
tuple_values = (('hello',123),('hey',123))
            
psycopg2.extras.execute_batch(cursor,insert_query,tuple_values)

    Here I created a tuple which holds the data to be inserted. Then execute_batch() method of the psycopg2.extras module is used to execute the insert_query with the tuple_values as the parameter. The execute_batch() method can be used to efficiently execute a parameterized query multiple times with different sets of parameters, which makes it useful for bulk inserts.


  2. 0

    As suggested in the comment, assuming that your connection is already established as conn one of the ways would be:

    from typing import Iterator, Dict, Any

def insert_execute_values_iterator(connection, keywords: Iterator[Dict[str, Any]], page_size: int = 1000) -> None:
    with connection.cursor() as cursor:
        psycopg2.extras.execute_values(
           cursor,
           """  WITH data(name,proj_id) as (VALUES %s)
                INSERT INTO keywords(name,proj_id)
                SELECT d.name,d.proj_id FROM data d 
                WHERE NOT EXISTS (SELECT 1 FROM keywords u2 WHERE
                u2.name=d.name AND u2.proj_id=d.proj_id);""", 
           (( keyword['name'],
              keyword['proj_id'] ) for keyword in keywords),
           page_size=page_size)


insert_execute_values_iterator(conn,{'hello':123,'hey':123})
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search