SQLAlchemy: Updates on persistent object are auto flushed when autoflush=False

user_2790078 0
Viewed 47

When applying updates to a persistent object, it seems that each update is automatically pushed to the transaction buffer even when the autoflush property is set as false.

Consider the following example. There are two entities - Employee and Department, and they have a many-to-many relationship between them. The ORM definition is as follows:

from sqlalchemy.orm import registry, relationship

mapper_registry = registry()

mapper_registry.map_imperatively(
   models.Employee,
   employee_table,
   properties={
       "employee_id": employee_table.c.EmployeeId,
       "first_name": employee_table.c.FirstName,
       "last_name": employee_table.c.LastName,
       "departments": relationship(
           models.Department, secondary=joinEmployeeDepartment
       ),
   },
)

The initial database state looks like this:

Employee:

EmployeeId FirstName FirstName
1 John Doe

Department:

DepartmentId DepartmentName
1 Sales

joinEmployeeDepartment:

RelationshipId EmployeeId DepartmentId
1 1 1

Then the following code is executed:

from sqlalchemy.orm.session import Session, sessionmaker

Session = sessionmaker(bind=engine, autocommit=False, autoflush=False, expire_on_commit=False)

session = Session()
session.begin()
try:
   employee: Employee = session.query(Employee).where(Employee.first_name == 'John').one_or_none()
   employee.departments = [Department(department_name='support')]  # 1
   employee.departments = [Department(department_name='IT')]       # 2
   session.commit()
finally:
   session.close()

Since autoflush is turned off, I would expect that when the persistent employee object is updated in-memory in #1, the changes would not be flushed to transaction buffer. Since #2 overwrites the changes made in #1, only the latter would be flushed and committed when session.commit() is called. However, I observed that is not the case. The change in #1 is also added to the transaction buffer. The resulting DB state is as follows:

Department:

DepartmentId DepartmentName
1 Sales
2 support
3 IT

joinEmployeeDepartment:

RelationshipId EmployeeId DepartmentId
2 1 3

My question is that is the autoflush setting ignored when updates are made to a persistent object?

sqlalchemy
1 Answers

What's happening is that as each new Department instance is created, it's added to session.new and enters the pending state. A pending object

[isn't] actually flushed to the database yet, but it will be when the next flush occurs.

So when the session is flushed commit, it finds two pending Department instances and sends them to the database.

The "delete" of the first Department instance is not tracked in session.deleted because that instance is not in a persistent state. Thus at flush time no DELETE is sent for that instance. However SQLAlchemy's attribute tracking tracks it as having been deleted from the relationship, so SQLAlchemy fixes the many-to-many mappings to reflect the final desired state.

We can see this by printing session.new and the relationship's history, and enabling logging on the engine:

with Session() as s:
    employee: Employee = s.query(Employee).where(Employee.first_name == 'John').one_or_none()
    hist = sa.inspect(employee).attrs.departments.history
    employee.departments = [Department(department_name='support')]
    print(s.new)
    print(sa.inspect(employee).attrs.departments.history)
    employee.departments = [Department(department_name='IT')]
    print(s.new)
    print(sa.inspect(employee).attrs.departments.history)
    s.commit()

Output:

2021-12-08 11:42:40,134 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-12-08 11:42:40,139 INFO sqlalchemy.engine.Engine SELECT employees.id AS employees_id, employees.first_name AS employees_first_name, employees.last_name AS employees_last_name 
FROM employees 
WHERE employees.first_name = ?
2021-12-08 11:42:40,139 INFO sqlalchemy.engine.Engine [generated in 0.00028s] ('John',)
2021-12-08 11:42:40,143 INFO sqlalchemy.engine.Engine SELECT departments.id AS departments_id, departments.department_name AS departments_department_name 
FROM departments, association 
WHERE ? = association.employee_id AND departments.id = association.department_id
2021-12-08 11:42:40,143 INFO sqlalchemy.engine.Engine [generated in 0.00024s] (1,)
IdentitySet([<__main__.Department object at 0x7fdbf4998eb0>])
History(added=[<__main__.Department object at 0x7fdbf4998eb0>], unchanged=[], deleted=[<__main__.Department object at 0x7fdbf4882860>])
IdentitySet([<__main__.Department object at 0x7fdbf4998eb0>, <__main__.Department object at 0x7fdbf4881420>])
History(added=[<__main__.Department object at 0x7fdbf4881420>], unchanged=[], deleted=[<__main__.Department object at 0x7fdbf4882860>])
2021-12-08 11:42:40,145 INFO sqlalchemy.engine.Engine INSERT INTO departments (department_name) VALUES (?)
2021-12-08 11:42:40,145 INFO sqlalchemy.engine.Engine [cached since 0.01444s ago] ('support',)
2021-12-08 11:42:40,145 INFO sqlalchemy.engine.Engine INSERT INTO departments (department_name) VALUES (?)
2021-12-08 11:42:40,145 INFO sqlalchemy.engine.Engine [cached since 0.01477s ago] ('IT',)
2021-12-08 11:42:40,147 INFO sqlalchemy.engine.Engine DELETE FROM association WHERE association.employee_id = ? AND association.department_id = ?
2021-12-08 11:42:40,147 INFO sqlalchemy.engine.Engine [generated in 0.00023s] (1, 1)
2021-12-08 11:42:40,148 INFO sqlalchemy.engine.Engine INSERT INTO association (employee_id, department_id) VALUES (?, ?)
2021-12-08 11:42:40,148 INFO sqlalchemy.engine.Engine [cached since 0.01509s ago] (1, 3)
2021-12-08 11:42:40,148 INFO sqlalchemy.engine.Engine COMMIT

To prevent the insertion of the first Department it must be removed from session.new by expunging it from the session:

    employee.departments = [Department(department_name='support')]
    s.expunge(employee.departments[0])
    employee.departments = [Department(department_name='IT')]

See the docs for sessions and object states for more detail.

user_53209060
Related