2019年7月31日 星期三

[ Python 常見問題 ] SQLAlchemy: What's the difference between flush() and commit()?

Source From Here 
Question 
What the difference is between flush() and commit() in SQLAlchemy? 

I'm particularly interested in their impact on memory usage. I'm loading some data into a database from a series of files (around 5 million rows in total) and my session is occasionally falling over - it's a large database and a machine with not much memory. 

I'm wondering if I'm using too many commit() and not enough flush() calls - but without really understanding what the difference is, it's hard to tell! 

How-To 
Session object is basically an ongoing transaction of changes to a database (update, insert, delete). These operations aren't persisted to the database until they are committed (if your program aborts for some reason in mid-session transaction, any uncommitted changes within are lost). The session object registers transaction operations with session.add(), but doesn't yet communicate them to the database until session.flush() is called. 

session.flush() communicates a series of operations to the database (insert, update, delete). The database maintains them as pending operations in a transaction. The changes aren't persisted permanently to disk, or visible to other transactions until the database receives a COMMIT for the current transaction (which is what session.commit() does). 

commit() commits (persists) those changes to the database. flush() is always called as part of a call to commit() (1). 

When you use a Session object to query the database, the query will return results both from the database and from the flushed parts of the uncommitted transaction it holds. By default, Session objects autoflush their operations, but this can be disabled. Hopefully this example will make this clearer: 
  1. #---  
  2. s = Session()  
  3.   
  4. s.add(Foo('A')) # The Foo('A') object has been added to the session.  
  5.                 # It has not been committed to the database yet,  
  6.                 #   but is returned as part of a query.  
  7. print 1, s.query(Foo).all()  
  8. s.commit()  
  9.   
  10. #---  
  11. s2 = Session()  
  12. s2.autoflush = False  
  13.   
  14. s2.add(Foo('B'))  
  15. print 2, s2.query(Foo).all() # The Foo('B') object is *not* returned  
  16.                              #   as part of this query because it hasn't  
  17.                              #   been flushed yet.  
  18. s2.flush()                   # Now, Foo('B') is in the same state as  
  19.                              #   Foo('A') was above.  
  20. print 3, s2.query(Foo).all()   
  21. s2.rollback()                # Foo('B') has not been committed, and rolling  
  22.                              #   back the session's transaction removes it  
  23.                              #   from the session.  
  24. print 4, s2.query(Foo).all()  
  25.   
  26. #---  
  27. Output:  
  28. 1 ['A')>]  
  29. 2 ['A')>]  
  30. 3 ['A')>, 'B')>]  
  31. 4 ['A')>]  


Supplement 
FAQ - SQLAlchemy insert or update example

沒有留言:

張貼留言

[Linux 常見問題] Linux and Unix Test Disk I/O Performance With dd Command

Source From   Here Question How can I use   dd   command on a Linux to test I/O performance of my hard disk drive? How do I check the   per...