Get information from Session's add/commit

[dimitarOnGithub]

Hey guys,

Reaching out hoping to pick your brains and hear your thoughts on a blocker I'm struggling with.

I have a small app that's periodically performing a bunch of operations, on a MySQL/MariaDB database, and the result of some of the operations dictates the flow of the rest of the application.

I have two models that are related to each other:

class Kid:

  __tablename__ = "kids"

  id: Mapped[int] = mapped_column(INTEGER, primary_key=True, autoincrement=True)
  parent: Mapped[int] = mapped_column(ForeignKey("parents.id"))


class Parent:

  __tablename__ = "parents"

  id: Mapped[int] = mapped_column(INTEGER, primary_key=True, autoincrement=True)
  last_contact: Mapped[DATETIME] = mapped_column(DATETIME, default=datetime.utcnow, nullable=False)
  is_alive: Mapped[BOOLEAN] = mapped_column(BOOLEAN, nullable=False, default=True)
  kids: Mapped[List["Kid"]] = relationship()

The flow of application is as it goes:

1. Loop over the Parent entries;
2. If an entry's last_contact is older than X, update is_alive to False
3. Loop over the kids list and change the value of parent for each Kid to another value.

The update of values happens via the Session add/add_all methods with a final commit at the end of the loop, for example:

for parent in all_parents:
  parent.is_alive = False
session.add_all(all_parents)
session.commit()

however, neither of these return any information about the number of rows matched or updated.

I want to be able to know if, for example, changing the is_alive attribute and committing has actually resulted in a row being updated. Is that possible?

[zzzeek]

so the unit of work checks the number of rows matched to ensure it matches how many objects it worked on in the first place. so if you are confirming rows matched (as opposed to "definitely changed the value of is_alive"), the UOW does that for you since it is updating on primary key and will error out if any of those primary keys are not present. that is, len(all_parents) is the number of rows matched if the commit succeeds.

if you are looking for confirmation that is_alive was different first, then changed value, while MySQL does support an UPDATE mode that returns this for an UPDATE statement, that is, will give you number of rows changed from an update, SQLAlchemy turns that mode off on the connection since it's not a standard SQL thing; the database drivers only support one or the other mode, not both at the same time. There's two or three ways to get this information in any case however.

First one, to do this from a UOW perspective you want to SELECT..FOR UPDATE your rows first, take a look at what is_alive says to start with, then run the UPDATE.

all_parents = session.scalars(select(Parent).where(Parent.is_alive).with_for_update()).all()
for parent in all_parents:
    assert parent.is_alive
    parent.is_alive = False
session.commit()

that would do what you're looking for, confirm is_alive changed. with_for_update() will lock the rows that were SELECTed.

The other way is you can use an UPDATE statement directly as documented here. you can use this with the SELECT...FOR UPDATE approach above if you want to lock the rows up front. Or you could set criteria in the update itself, to only match rows where "is_alive" is True and set them to False; in that case the matched rowcount will be the same as the updated rowcount:

result = session.execute(update(Parent).where(Parent.is_alive).values(is_alive=False))
assert resuilt.rowcount == <expected number>

[dimitarOnGithub]

Hey Michael,

Thanks a lot for the speedy reply and all the information provided, this indeed solves my problem in a very elegant manner!

Have a great day!

PS: That's a really cute kitty