oracle 触发器 upsert,如何在PostgreSQL中UPSERT（MERGE，INSERT ...在DUPLICATE UPDATE上）？...

由于这个问题已经结束，我在这里发布你是如何使用SQLAlchemy进行的。 通过递归，它会重试批量插入或更新以对抗竞争条件和验证错误。

首先是进口

import itertools as it

from functools import partial

from operator import itemgetter

from sqlalchemy.exc import IntegrityError

from app import session

from models import Posts

现在有几个辅助函数

def chunk(content, chunksize=None):

"""Groups data into chunks each with (at most) `chunksize` items.

https://stackoverflow.com/a/22919323/408556

"""

if chunksize:

i = iter(content)

generator = (list(it.islice(i, chunksize)) for _ in it.count())

else:

generator = iter([content])

return it.takewhile(bool, generator)

def gen_resources(records):

"""Yields a dictionary if the record's id already exists, a row object

otherwise.

"""

ids = {item[0] for item in session.query(Posts.id)}

for record in records:

is_row = hasattr(record, 'to_dict')

if is_row and record.id in ids:

# It's a row but the id already exists, so we need to convert it

# to a dict that updates the existing record. Since it is duplicate,

# also yield True

yield record.to_dict(), True

elif is_row:

# It's a row and the id doesn't exist, so no conversion needed.

# Since it's not a duplicate, also yield False

yield record, False

elif record['id'] in ids:

# It's a dict and the id already exists, so no conversion needed.

# Since it is duplicate, also yield True

yield record, True

else:

# It's a dict and the id doesn't exist, so we need to convert it.

# Since it's not a duplicate, also yield False

yield Posts(**record), False

最后是upsert函数

def upsert(data, chunksize=None):

for records in chunk(data, chunksize):

resources = gen_resources(records)

sorted_resources = sorted(resources, key=itemgetter(1))

for dupe, group in it.groupby(sorted_resources, itemgetter(1)):

items = [g[0] for g in group]

if dupe:

_upsert = partial(session.bulk_update_mappings, Posts)

else:

_upsert = session.add_all

try:

_upsert(items)

session.commit()

except IntegrityError:

# A record was added or deleted after we checked, so retry

#

# modify accordingly by adding additional exceptions, e.g.,

# except (IntegrityError, ValidationError, ValueError)

db.session.rollback()

upsert(items)

except Exception as e:

# Some other error occurred so reduce chunksize to isolate the

# offending row(s)

db.session.rollback()

num_items = len(items)

if num_items > 1:

upsert(items, num_items // 2)

else:

print('Error adding record {}'.format(items[0]))

这是你如何使用它

>>> data = [

... {'id': 1, 'text': 'updated post1'},

... {'id': 5, 'text': 'updated post5'},

... {'id': 1000, 'text': 'new post1000'}]

...

>>> upsert(data)

它超过bulk_save_objects的优势在于它可以处理插入时的关系，错误检查等(与批量操作不同)。