Get count of inserted and updated records in sqlalchemy's upsert

Question

I have a working code of upserting several records by sqlalchemy:

from sqlalchemy.dialects.postgresql import insert

from sqlalchemy import BigInteger
from flask_sqlalchemy import SQLAlchemy as db 

class PetModel(db.Model):
  __tablename__ = "pets"

  id = db.Column(BigInteger, primary_key=True)
  data = db.Column(db.String(64), nullable=False, unique=True)

  def as_dict(self):
    return {
      "id": getattr(self, "id"),
      "data": getattr(self, "data"),
    }

pets = [PetModel(id=1, data="Dog"), PetModel(id=2, data="Cat")]

insert_statement = insert(PetModel).values([_.as_dict() for _ in pets])
upsert_statement = insert_statement.on_conflict_do_update(
  constraint="pet_pkey",
  set_={"data": insert_statement.excluded.data},
)
ans = db.session.execute(upsert_statement)

I have tried to return all rows by adding returning(PetModel.__table__) into insert_statement, but I can't separate the answer of the [_ for _ in ans] statement on updated and inserted. I don't want to add special field to database.

I know that ans.rowcount returns the sum of updated and inserted records.

How could I get separately the amount of updated and inserted records using sqlalchemy?

There's the `xmax` hack: https://stackoverflow.com/questions/34762732/how-to-find-out-if-an-upsert-was-an-update-with-postgresql-9-5-upsert — Ilja Everilä, Nov 29 '21 at 18:14
Thank you! It was the step to the right way. I'm using the way describing here: https://stackoverflow.com/a/62535477/9978223 — Dmitrii Sidenko, Nov 29 '21 at 19:22

score 1 · Answer 1 · answered Nov 29 '21 at 19:36

As Ilja Everilä said, one of the decisions is to use the xmax hack.

A column with this value we should add to the answer like describing here

from sqlalchemy.dialects.postgresql import insert
...
insert_statement = insert(PetModel).returning(
  sqlalchemy.column("xmax") == 0
).values([_.as_dict() for _ in pets])
upsert_statement = insert_statement.on_conflict_do_update(
  constraint="pet_pkey",
  set_={"data": insert_statement.excluded.data},
)
ans = db.session.execute(upsert_statement)
created = sum([_._row[0] for _ in ans])
updated = len(pets) - created

Get count of inserted and updated records in sqlalchemy's upsert

1 Answers1