SQLAlchemy has a merge function, which inserts or updates, but it only works on the primary key conflict, not on an arbitrary specified constraint key.
Hence, I tried using PostgreSQL's dialect, which provides function on_conflict_do_update. This works OK, but if you have a related parent-child joined table relationship, where child table's primary key is also a foreign key to the parent table's primary key, the things start to get complicated, and I couldn't find an effective way to do the upsert (without the foreign key constraint, you can get away with cte, though).
Preferably, I would like to do this in a single execution statement, since otherwise I can just filter out the rows which exist, run the update on each of them separately, and run add_all on the set of the remaining rows which don't exist yet.
I couldn't even find an efficient way of doing this in pure PostgreSQL, so solution in pure PostgreSQL is appreciated as well.