I have a before_flush event listener which checks if the employee's manager changed. In such case a record is automatically created in EmpManHist table. manager is self-reference to the Employee table. Here's my table definition:
class Employee(Base):
__tablename__ = 'employees'
emp_id = Column(String, primary_key=True, unique=True)
name = Column(String, nullable=False)
manager_id = Column(String, ForeignKey('employees.emp_id'))
direct_reports = relationship('Employee', backref=backref('manager', remote_side=[emp_id]))
When I assign new manager to an employee by modifying the ForeignKey (manager_id) directly it is correctly picked up by the event listener. But when I do an instance assignment it doesn't:
# this code does not pick up the manager_id change in the 'before_flush' event listener
emp2.manager = emp3
dal.session.add(emp2)
dal.session.commit()
# this does
emp2.manager_id = '1'
dal.session.add(emp2)
dal.session.commit()
Why is that? I'm using python 3.6.3 and sqlalchemy 1.1.13
Below is the full working example:
from sqlalchemy import create_engine, Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy import event
from sqlalchemy.orm.attributes import get_history
import datetime
Base = declarative_base()
class DataAccessLayer(object):
def __init__(self):
self.conn_string = conn_string
self.engine = None
self.session = None
self.Session = None
self.echo = True
def connect(self):
self.engine = create_engine(self.conn_string, echo=self.echo)
Base.metadata.create_all(self.engine)
self.Session = sessionmaker(bind=self.engine)
class Employee(Base):
__tablename__ = 'employees'
emp_id = Column(String, primary_key=True, unique=True)
name = Column(String, nullable=False)
manager_id = Column(String, ForeignKey('employees.emp_id'))
direct_reports = relationship('Employee', backref=backref('manager', remote_side=[emp_id]))
class EmpManHist(Base):
__tablename__ = 'emp_man_history'
id = Column(Integer, primary_key=True)
emp_id = Column(String, ForeignKey('employees.emp_id'))
man_id_from = Column(String, ForeignKey('employees.emp_id'))
man_id_to = Column(String, ForeignKey('employees.emp_id'))
when = Column(DateTime, default=datetime.datetime.now)
manager_from = relationship('Employee', foreign_keys=[man_id_from])
manager_to = relationship('Employee', foreign_keys=[man_id_to])
conn_string = 'sqlite:///:memory:'
dal = DataAccessLayer()
dal.echo = True
dal.connect()
dal.session = dal.Session()
@event.listens_for(dal.session, 'before_flush')
def _emp_history_update(session, flush_context, instances):
print("BEFORE FLUSH")
for instance in session.dirty:
if not isinstance(instance, Employee):
continue
man_hist = get_history(instance, 'manager_id')
if man_hist.added:
if man_hist.deleted:
man_deleted = str(man_hist.deleted[0])
else:
man_deleted = None
emp_man_hist = EmpManHist(emp_id=instance.emp_id, man_id_from=man_deleted,
man_id_to=str(man_hist.added[0]))
session.add(emp_man_hist)
emp1 = Employee(emp_id='1', name="AAA")
emp2 = Employee(emp_id='2', name="BBB", manager_id='1')
emp3 = Employee(emp_id='3', name="CCC", manager_id='1')
dal.session.add(emp3)
dal.session.flush()
dal.session.add(emp1)
dal.session.add(emp2)
dal.session.commit()
# this code does not pick up the manager_id change in the 'before_flush' event listener
emp2.manager = emp3
dal.session.add(emp2)
dal.session.add(emp3)
dal.session.commit()
# this does
emp2.manager_id = '1'
dal.session.add(emp2)
dal.session.commit()
from sqlalchemy - get_history doesn't pick up the change of the foreign key when instance is assigned, only when modifying foreign key directly
No comments:
Post a Comment