def create_upsert_postgres(table, record): """Creates a statement for inserting the passed record to the passed table; if the record already exists, the existing record will be updated. This uses PostgreSQL `on_conflict_do_update` (hence upsert), and that why the returned statement is just valid for PostgreSQL tables. Refer to this `SqlAlchemy PostgreSQL documentation`_ for more information. The created statement is not executed by this function. Args: table (sqlalchemy.sql.schema.Table): database table metadata. record (dict): a data record, corresponding to one row, to be inserted. Returns: sqlalchemy.sql.dml.Insert: a statement for inserting the passed record to the specified table. .. _SqlAlchemy PostgreSQL documentation: https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#insert-on-conflict-upsert """ insert_stmt = postgres_insert(table).values(record) return insert_stmt.on_conflict_do_update( index_elements=[col for col in table.primary_key], set_=record )
def upsert(model, data, update_field, engine): """ upsert实现 依据session.bind.dialect得到当前数据库类型 然后生成对应的upsert语句,当前支持mysql,postgresql,sqlite三种数据库 on_duplicate_key_update for mysql on_conflict_do_nothing for postgresql insert or ignore for sqlite Parameters ---------- model : Base orm model data : list update_field : list engine _engine.Engine instance Returns ------- insert insert statement """ if engine.dialect.name == 'mysql': stmt = mysql_insert(model).values(data) d = {f: getattr(stmt.inserted, f) for f in update_field} return stmt.on_duplicate_key_update(**d) elif engine.dialect.name == 'postgresql': stmt = postgres_insert(model).values(data) return stmt.on_conflict_do_nothing(index_elements=[update_field[0]]) elif engine.dialect.name == 'sqlite': stmt = insert(model).values(data).prefix_with('OR IGNORE') return stmt else: raise Exception(f"can't support {engine.dialect.name} dialect")
def bulk_add_with_ignore(cls, data, postgres=False): """ This method would take list of data dictionaries and add it to session with INSERT IGNORE option. >>> NOTE: Make sure each item in the list have same schema (keys) otherwise it will raise error "INSERT value for column table.column is explicitly rendered as a boundparameter in the VALUES clause; a Python-side value or SQL expression is required" :param list data: List of data dictionaries """ if isinstance(data, list) and data: if postgres: cls.session.execute(postgres_insert(cls.__table__).values(data).on_conflict_do_nothing()) else: try: cls.session.execute(insert(cls.__table__, values=data, prefixes=['IGNORE'])) except Exception as e: cls.session.rollback() # logger.info('Exception: ', e) cls.bulk_commit() cls.buffer_add_ignore = list()