Sqlalchemy: Using @compiles with dialect specific subclasses

[wtfuzz]

I'm attempting to cleanly append ON CONFLICT DO NOTHING to all INSERT statements with an ORM/declarative setup.

Should it be possible to do the following?

@compiles(Insert, 'postgresql')
def prefix_inserts(insert, compiler, **kw):
  return compiler.visit_insert(insert.on_conflict_do_nothing(), **kw)

Currently the provided insert argument is of class sqlalchemy.sql.dml.Insert rather than the postgresql Insert subclass with the on_conflict_do_nothing() method.

[zzzeek]

@compiles is probably not appropriate for this as you aren't defining any new kind of SQL string or construct. you want before_execute.

However, you will get pretty broken behavior from the ORM in particular if you are causing INSERT statements to behave in an unexpected way like this.

[wtfuzz]

Thanks for pointing out before_execute.

What's the advantage over @compiles? The documentation https://docs.sqlalchemy.org/en/latest/core/compiler.html has a section titled 'Changing the default compilation of existing constructs'

I was able to get this working using @compiles by adding implicit_returning=False during engine creation, and simply doing:

@compiles(Insert, 'postgresql')
def prefix_inserts(insert, compiler, **kw):
  stmt = compiler.visit_insert(insert, **kw)
  stmt += " ON CONFLICT DO NOTHING"
  return stmt

So far there have been no ill-effects and I can do batch inserts with the ORM models while ignoring already present rows with decent performance.

[zzzeek]

The advantage of before_execute() is you can totally swap out the Insert() construct for a new one, like, you'd copy it to a new Postgresql insert() construct with whatever you need and return that. However, you could do the same thing in the @compiles recipe also. The @compiles hook is more about changing just the string representation of the construct but not any of its in-python behavior. but that's what you're looking to do anyway.

bigger picture I would assume your program is of very limited scope because adding "ON CONFLICT DO NOTHING" to all INSERT statements dramatically changes their in-Python behavior. The Core and ORM assume INSERT ...RETURNING returns a row, for example, I would not have predicted that what you're doing above is not actually causing problems. but if you're happy I'm happy so carry on!

[zzzeek]

feel free to continue discussion