3

A bit of background:

I am using pyramid framework with SQLAlchemy. My db session is handled by pyramid_tm and ZTE

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

I have a very complicated database design with lots of model classes, foreign keys, and complex relationships between my models.

So while doing some very complicated logic on my Models and deleting, updating , inserting, and moving around objects from relationships in different models I used to get IntegrityError randomly which would go away after restarting pserve.

This is very strange because autoflush is on and in theory session must be flushed as soon as I change anything on models.

So my solution to the random IntegrityError was to manually flush the session within my logic whenever things get very complicated.

Since I did the DBSession.flush() within my logic I haven't got the IntegrityError any more.

The question

Now I have 2 questions:

  1. How come autoflush does not prevent from integrity error? Is it that autoflush does not clean the models but DBSession.flush() cleans the models?

  2. Are there any side effects of calling DBSession.flush() within my code? I can't really think of any side effects (apart from little performance overhead of calling DB). I don't really like calling DBSession.flush() within my code as it is something that must really be handled by framework.

See also

When should I be calling flush() on SQLAlchemy?

Thank you.

Community
  • 1
  • 1
A squared
  • 131
  • 6
  • Can you share the full message of the `IntegrityError` that is throw? – adarsh Mar 30 '15 at 09:30
  • And another thing to note here is that `flush` will write the data to database, but unless you use `commit`, the changes won't be stored to the database, they will be discarded when the session expires. If you call `commit`, that flushed the changes and makes it persistent. – adarsh Mar 30 '15 at 09:33
  • Thanks adarsh. My question is if there are any side effects for calling flush within code manually rather than letting the framework handle it. – A squared Mar 30 '15 at 10:19
  • There is no side effects to that as such. But in my opinion you should only do that if your autoflush is disabled to prevent confusion. Effectively, I recommend not using autoflush and doing the flushing and committing by hand because that gives you better control over handling errors, how you rollback and optimizing writes to the database depending on how the query is. – adarsh Mar 30 '15 at 10:22
  • @adrash: No, "committing by hand" is not a recommended approach and is discouraged in both Pyramid and SQLAlchemy docs. """As a general rule, keep the lifecycle of the session separate and external from functions and objects that access and/or manipulate database data.""". – Sergey Mar 30 '15 at 11:47
  • @Sergey why? Could you point me to the docs where this is mentioned? – adarsh Mar 30 '15 at 14:35
  • 1
    @adrash: http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it – Sergey Mar 30 '15 at 21:16

1 Answers1

0

It is very hard to say why you used to get IntegrityError's without seeing any code, but in theory there are a few scenarios where autocommit may actually cause it by flushing the session prematurely. For example:

COURSE_ID = 10
student = Student(name="Bob")
student.course_id = COURSE_ID

course = Course(id=COURSE_ID, name="SQLAlchemy")

The above code will probably (haven't tested) fail with autocommit turned on and should succeed if you let SQLAlchemy to flush the changes.

I don't think there's any harm in flushing the session periodically if it helps, but again, it's hard to tell whether something can be done to avoid the manual flush without any code samples.

Sergey
  • 11,892
  • 2
  • 41
  • 52