CODEWITHSUNDEEP
pythonsqlalchemy
jon077
jon077

Reputation: 10449

SQL Alchemy session.commit and flushing behavior

I am working with a large code base using SQL Alchemy. We recently changed to structlog for logging and the SQLAlchemy behavior has changed. In functions where data is inserted into MySQL, if the session.commit() call is before the log statement AND not in the finally clause, the object becomes None.

model_obj is empty when returned in the following code:

    model_obj = json.loads(model_serialized, object_hook=from_json)

try:
    session.add(model_obj)
    session.commit()
    config.logger.debug("added model_obj", model_obj=model_obj)
except IntegrityError as e:
    config.logger.debug("model_obj already exists", model_obj=model_obj, e=str(e))
    return createAndEncodeErrorElements(
        ErrorCodeDatabaseSQLAlchemyIntegrityError, None
    )
else:
    response = createAndEncodeSuccessElementsWithData({model_obj: model_obj})
    return response
finally:
    session.close()

model_obj is NOT empty when returned in the following code: model_obj = json.loads(model_serialized, object_hook=from_json)

try:
    session.add(model_obj)
    config.logger.debug("added model_obj", model_obj=model_obj)
except IntegrityError as e:
    config.logger.debug("model_obj already exists", model_obj=model_obj, e=str(e))
    return createAndEncodeErrorElements(
        ErrorCodeDatabaseSQLAlchemyIntegrityError, None
    )
else:
    response = createAndEncodeSuccessElementsWithData({model_obj: model_obj})
    return response
finally:
    session.commit()
    session.close()

Why does the session.commit() need to be in the finally clause? What is the underlying behavior of SQLAlchemy?

Upvotes: 0

Views: 577

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55943

SQLAlchemy expires all objects in a session when the session is committed. That is to say, all the column-value attributes of a model instance are removed from its __dict__

This can be prevented by passing expire_on_commit=False when creating the session; be aware that the data in expired instances may be stale.

Upvotes: 1

Related Questions