def _insert_or_update(self, insert_obj: DeclarativeMeta, identity_insert=False) -> None:
        A safe way for the sqlalchemy to insert if the record doesn't exist, or update if it does. Copied from
        trafficstat.crash_data_ingester
        :param insert_obj:
        :param identity_insert:
        :return:
        session = Session(bind=self.engine, future=True)
        if identity_insert:
            session.execute(text('SET IDENTITY_INSERT {} ON'.format(insert_obj.__tablename__)))
        session.add(insert_obj)
            session.commit()
            logger.debug('Successfully inserted object: {}', insert_obj)
        except IntegrityError as insert_err:
            session.rollback()
            if '(544)' in insert_err.args[0]:
                # This is a workaround for an issue with sqlalchemy not properly setting IDENTITY_INSERT on for SQL
                # Server before we insert values in the primary key. The error is:
                # (pyodbc.IntegrityError) ('23000', "[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
                # Cannot insert explicit value for identity column in table <table name> when IDENTITY_INSERT is set to
                # OFF. (544) (SQLExecDirectW)")
                self._insert_or_update(insert_obj, True)
            elif '(2627)' in insert_err.args[0] or 'UNIQUE constraint failed' in insert_err.args[0]:
                # Error 2627 is the Sql Server error for inserting when the primary key already exists. 'UNIQUE
                # constraint failed' is the same for Sqlite
                cls_type = type(insert_obj)
                qry = session.query(cls_type)
                primary_keys = [i.key for i in sqlalchemyinspect(cls_type).primary_key]
                for primary_key in primary_keys:
                    qry = qry.filter(cls_type.__dict__[primary_key] == insert_obj.__dict__[primary_key])
                update_vals = {k: v for k, v in insert_obj.__dict__.items()
                               if not k.startswith('_') and k not in primary_keys}
                if update_vals:
                    qry.update(update_vals)
                        session.commit()
                        logger.debug('Successfully inserted object: {}', insert_obj)
                    except IntegrityError as update_err:
                        logger.error('Unable to insert object: {}\nError: {}', insert_obj, update_err)
            else:
                raise AssertionError('Expected error 2627 or "UNIQUE constraint failed". Got {}'.format(insert_err)) \
                    from insert_err
        finally:
            if identity_insert:
                session.execute(text('SET IDENTITY_INSERT {} OFF'.format(insert_obj.__tablename__)))
            session.close()
I would open this as a PR, but I am not sure where this should go, and I am pretty sure its not up to the standards of this project.
  use case
  not really a feature or a bug; can be support for new DB features or user use cases not anticipated
  labels
      Jul 13, 2021
          
please see #5441.      this is an occasionally requested feature.  The implementation would require significant effort in order to work in a fully generalized way, and if someone were to work on this they'd be advised to check in with me very early on before going too far with any particular approach; it's probably a feature I'd have to implement at some point.
          
Heya @zzzeek, commenting here after reading through this discussion. Would you consider revisiting this anytime in the future, now that PostgreSQL also supports MERGE INTO syntax? Considering Oracle and MS SQL have also had it for some time now.
Thanks for all the work you put into SQLAlchemy 🙂
          
do you have a reason you need MERGE INTO on PostgreSQL ?    I am -1 on trying to support "the SQL Standard MERGE" because a. it's enormously complicated b. I've never seen anyone use it ever and c. it likely is not "standard" at all for Oracle / MSSQL , these DBs never do anything in a "standard" way.
I am +1 on supporting real world use cases.   that is, people need "upsert".   So I am in favor of a limited form of upsert() specific to Oracle and/or specific to MSSQL.    I am also in favor of a Core SQL construct "upsert" which generalizes the SQLite, PostgreSQL, and MySQL capabilities into a single, very limited interface.
So that's -1 for "merge", +1 for platform agnostic "upsert" which uses the existing ON CONFLICT routines for PG/SQLite ON DUPLICATE KEY for MySQL/MariaDB.
do you have a reason you need MERGE INTO on PostgreSQL
Thanks for the quick response @zzzeek. Specifically, PostgreSQL? No, not really. The bigger picture is about the couple of convenient methods being worked on over at the Litestar repository.
Having a generic "upsert" convenience method that works on most engines is the ideal plan. For SQLite, PostgreSQL, and MySQL, I'm 100% on board with what you suggest, and it'd be better than the current implementation in that repository. For Oracle and MS SQL, there isn't really a way to achieve this other than using "MERGE" (correct me if I'm wrong). Currently, an approach that involves "session.merge" or "session.add" is used, but the hope was that a native "merge" function would make things easier and more flexible (to overcome the "primary key" only restriction of session.merge).
The real question I should've asked was, "Would you consider adding MERGE for Oracle and MS SQL now that PostgreSQL also has it?" Emphasizing that three popular backends support this. That being said, I'm all in for a future core-level upsert function that'd one day make life easier for most cases and includes these DBs.
TL;DR - I agree with you on not needing SQL Merge and needing a platform-agnostic upsert that also works with Oracle and MS SQL.