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()