On Thu, Sep 20, 2018 at 11:56 PM <[email protected]> wrote: > > Hello, > > While looking for a good way to implement a bulk update to numerous rows of > the same table, I came across this performance example: > > session = Session(bind=engine) > for chunk in range(0, n, 1000): > customers = session.query(Customer).\ > filter(Customer.id.between(chunk, chunk + 1000)).all() > for customer in customers: > customer.description += "updated" > session.flush() > session.commit() > > I noticed that the flush() happens with every iteration over the chunks, > instead of the end, right along with commit(). Why is that? > > And is the above the recommended way of a bulk update, instead of e.g. > calling Table.update() as recommended in this SO answer?
I think the idea of processing the rows in chunks is to limit the memory usage of the Python process. Until you call session.flush(), all your modifications are held in memory. For small numbers of rows this isn't a problem, but for huge numbers, it might be. In practice, it may not actually make a difference for this example, because the default session configuration is to autoflush whenever you call session.query(). As to whether this is preferred over Table.update(), it really depends on your use case. Table.update() will usually be faster, because all the work will be done by the database itself. If the changes you are trying to make can be expressed in SQL (ie. they don't have complicated dependencies or require outside information), and you are happy to resynchronise your session afterwards if necessary, then Table.update() is fine. 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.
