Psycopg2 : Insert multiple values if not exists in the table-CodePudding

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.

CodePudding user response：

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})

CodePudding user response：

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)