CODEWITHSUNDEEP
pythonpython-3.xpostgresqlsqlalchemyflask-sqlalchemy
Dmitrii Sidenko
Dmitrii Sidenko

Reputation: 658

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

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?

Upvotes: 0

Views: 1108

Answers (1)

Dmitrii Sidenko
Dmitrii Sidenko

Reputation: 658

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

Upvotes: 1

Related Questions