python psycopg2 conditional insert statements

Question

I need to write an INSERT statement that first checks to see if the data already exists. The current code is inside python using psycopg2 to connect to a postgresql db:

    sql = """IF NOT EXISTS (SELECT * FROM table \  
         WHERE col_1 = (%s) AND col_2 = (%s) ) \
         INSERT INTO table (col1, col2)      \
         VALUES (%s, %s);"""
    data = ( col1_data, col2_data, col1_data, col2_data)
    try:
        CURSOR.execute(sql, data)
        DB.commit()
    except:
        print "Cursor failed INSERT INTO table.\n"

which does not work (and I haven't done quality error handling so I don't get any good information). So, I went into psql and tried just:

    IF NOT EXISTS (SELECT * FROM t WHERE c1=d1 AND c2=d2)
        INSERT INTO t (c1, c2) VALUES (d1,d2);

and I got the following error:

    ERROR:  syntax error at or near "IF" 
    LINE 1: IF NOT EXISTS (SELECT * FROM table WHERE c1 = d1...
            ^

So I BELIEVE my error is in the sql not the python (though I could be wrong) since this works:

    sql = """INSERT INTO t2 (col_0, col_1, col_2) \
             VALUES (%s, %s, %s);"""
    data = (d1, d2, time.time())
    try:
        CURSOR.execute(sql, data)
        DB.commit()
    except:
        print "Cursor failed to INSERT INTO t2.\n"

For table 1, my CREATE was:

    db=> CREATE TABLE table (
    col_0 SERIAL PRIMARY KEY,
    col_1 varchar(16),
    col_2 smallint
    );
    NOTICE:  CREATE TABLE will create implicit sequence "pm_table_ip_id_seq" for serial column "pm_table.ip_id"
    NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pm_table_pkey" for table "pm_table"
    CREATE TABLE

I am grateful for any help and guidance.

Answer 1

I used plpgsql for such a requirement in my project

insert_function = """
CREATE LANGUAGE plpgsql;
CREATE FUNCTION insert_if_unique (sql_insert TEXT)
    RETURNS VOID
    LANGUAGE plpgsql
AS $$
BEGIN
    EXECUTE sql_insert;
    RETURN;
    EXCEPTION WHEN unique_violation THEN
    RETURN;
        -- do nothing
END;
$$;
"""

cursor.execute(insert_function);

You can use something like below to use it:

cursor.execute("insert_if_unique("+sql+")"%data)

The above query is not parameterized. So please be wary of SQL injection if you are getting the input from an external source.

Note: You can use cursor.mogrify() to evade SQL injection attacks.

sql = cursor.mogrify(sql,data)
cursor.execute("insert_if_unique("+sql+")")