Postgresql - Insert into where not exists using sqlalchemy's INSERT from SELECT

Question

As pointed out here its possible to do the following with postgresql 9.1+

INSERT INTO example_table
    (id, name)
SELECT 1, 'John'
WHERE
    NOT EXISTS (
        SELECT id FROM example_table WHERE id = 1
    );

I've been having a play around with version 0.9 of sqlalchemy where they have introduced INSERT from SELECT method which should theoretically handle the above.

Is it possible and if so how? (as i want to take advantage of the result.inserted_primary_key that is not returned when using raw sql)

How can I use bindparams for the 'from_select' part as the only way I can seem to use it is when using table columns in the select.

e.g.

insrt = example_table.insert().
    from_select(['id', 'name'],
    example_table.select().
    where(~exists(select([example_table.c.id],
    example_table.c.id == 1))))

result = session.execute(insrt)

if result.is_insert:
    print 'do something with result.inserted_primary_key'

It's only safe to do the command you gave when you already know the primary key and want to create it if it doesn't exist. If you're doing it with a non-unique key then it's subject to race conditions that can cause multiple inserts to run successfully at the same time. Even as it is, the command won't necessarily succeed, it might fail with a unique violation error if it races with another concurrent insert. To learn more about this read some of the many other discussions of upsert on PostgreSQL here on stack overflow. — Craig Ringer, Aug 29 '13 at 02:34
many, many, many discussions. The key im working with is unique and there shouldnt (hopefully) be any issues with concurrent inserts. The problem I am having here is the syntax of sqlalchemy and getting it to use my own params (through bindparams or any other way) in the select instead of using table columns — 7wonders, Aug 29 '13 at 02:42

score 24 · Accepted Answer · answered Sep 04 '13 at 03:58

from sqlalchemy import *

"""
INSERT INTO example_table
    (id, name)
SELECT 1, 'John'
WHERE
    NOT EXISTS (
        SELECT id FROM example_table WHERE id = 1
    );
"""

m = MetaData()

example_table = Table("example_table", m,
                        Column('id', Integer),
                        Column('name', String)
                    )

sel = select([literal("1"), literal("John")]).where(
           ~exists([example_table.c.id]).where(example_table.c.id == 1)
      )

ins = example_table.insert().from_select(["id", "name"], sel)
print(ins)

output:

INSERT INTO example_table (id, name) SELECT :param_1 AS anon_1, :param_2 AS anon_2 
WHERE NOT (EXISTS (SELECT example_table.id 
FROM example_table 
WHERE example_table.id = :id_1))

Postgresql - Insert into where not exists using sqlalchemy's INSERT from SELECT

1 Answers1