python psycopg2 条件插入语句

Question

我需要编写一个 INSERT 语句，首先检查数据是否已经存在。当前代码在 python 中使用 psycopg2 连接到 postgresql 数据库:

    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"

这是行不通的(而且我还没有进行质量错误处理，所以我没有得到任何有用的信息)。所以，我进入 psql 并尝试了:

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

我得到了以下错误:

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

所以我相信我的错误是在 sql 而不是 python 中(尽管我可能是错的)因为这个有效:

    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"

对于表 1，我的 CREATE 是:

    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

非常感谢任何帮助和指导。

Answer 1

我用了plpgsql在我的项目中有这样的需求

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

你可以像下面这样使用它:

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

上面的查询没有参数化。因此，如果您从外部源获取输入，请警惕 SQL 注入(inject)。

注意:您可以使用cursor.mogrify() 来规避SQL 注入(inject)攻击。

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