Postgresql ON CONFLICT in sqlalchemy

I’ve read quite a few resources (ao. 1, 2) but I’m unable to get Postgresql’s ON CONFLICT IGNORE behaviour working in sqlalchemy.

I’ve used this accepted answer as a basis, but it gives

SAWarning: Can't validate argument 'append_string'; can't locate any SQLAlchemy dialect named 'append'

I’ve tried adding the postgresql dialect to the @compile clause, renaming my object, but it doesn’t work. I also tried to use the str(insert())+ " ON CONFILCT IGNORE" without results. (not surprising btw)

How can I get the On CONFLICT IGNORE to get added to my inserts? I like the proposed solution, as I can see myself not wanting the IGNORE behaviour on each INSERT

ps. using python 2.7 (don’t mind upgrading to 3.4/3.5), latest sqlalchemy (1.x)

Answer

You don’t need this, use exists condition to prevent inserting duplicates.

for example:

INSERT INTO table (unique_name) 
SELECT 'some_string'
WHERE NOT EXISTS(SELECT 1 FROM table WHERE unique_name = 'some_string')

you can also

INSERT INTO table (unique_name)
VALUES('some_string')
ON CONFLICT (unique_name) DO NOTHING

but if you need insert or update in single query, then this is example for you:

INSERT INTO distributors (did, dname)
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

This is an example from PostgreSQL Documentation.