On Thu, Sep 13, 2018 at 10:45 AM <[email protected]> wrote: > > Hello, > > I’d like to get some clarification on the following two code examples. First, > bulk delete an object: > > >>> u = dbsession.query(User).first() > >>> u.id > '0078ccdf7db046179c59bff01199c25e' > >>> dbsession.query(User).filter(User.id == > >>> "0078ccdf7db046179c59bff01199c25e").delete() > >>> dbsession.deleted > IdentitySet([]) > > Note how the `deleted` set of the Session object is empty; the objects, > however, aren’t yet deleted but will when the Session commits. The other way > of deleting would be to delete the objects from the session: > > >>> dbsession.delete(u) > >>> dbsession.deleted > IdentitySet([<User ORM object at 0x7fce2a0d6710 with id > 0078ccdf7db046179c59bff01199c25e>, …]) > > There is a warning in the documentation of “bulk delete” which says that > “[it] is a “bulk” operation, which bypasses ORM unit-of-work automation in > favor of greater performance”. I think this refers to the above observation > regarding the Session’s `deleted` set, but I don’t know what “unit-of-work > automation” refers to. > > Can somebody please shed some light on the above observation? > > Also, is there a way to detect objects in a Session that are queued to be > bulk-deleted? (This would a continuation of a previous discussion “Confusion > over session.dirty, query, and flush”.) >
Query.delete() doesn't "queue objects for deletion". It deletes them immediately from the database (within the context of the current transaction). The default behaviour of Query.delete() is to immediately execute a "DELETE FROM <table> WHERE <criteria>" statement in the database. The criteria can be as complicated as you like, including calls to SQL functions and so on. SQLAlchemy has no way of knowing which objects that are currently loaded in the session would match those criteria, and by default it doesn't even try. This means that the state of SQLAlchemy's session no longer corresponds with the state of the database. When you edit objects in the session, and then call session.flush(), SA will emit an UPDATE statement. If the corresponding row has already been deleted via a bulk delete, the UPDATE statement will fail. (The unit-of-work is the underlying algorithm that the session uses to keep track of changes and then execute them against the database when you call session.flush()) The Query.delete() method takes an optional "synchronize_session" parameter, which can either be False (the default), "fetch" or "evaluate". If you pass "fetch", then SA will first issue a "SELECT <primarykey> FROM <table> WHERE <criteria>" to find out which rows are about to be deleted. It will remove those from the session, so that the session once again matches the database. If you pass "evaluate", SA will try to evaluate the criteria in Python, to see which objects in the session are about to be deleted. This is impossible to do in the general case, but it may work for simple criteria. It will raise an error if the criteria aren't supported by the evaluator, but note the warning in the docs that "the expression evaluator currently doesn’t account for differing string collations between the database and Python". Hope that helps, Simon -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
