On Wed, Jun 12, 2019, at 10:10 AM, Javier Martínez wrote: > Hi everyone, > I am searching but I find no solution for my issue that is why I decided to > ask here. Thanks in advance for your attention. > > The issue is related to the update of a second level hierarchy object Id. I > am creating a hierarchy, following the recommendations in the tutorials but I > find that when I want to update the id, which is the link between all the > entities through the foreign key, only the first and the third level (last > level) of the hierarchy update the id value. I think this could be an issue > because the id is what will link the tables for further queries. I attach an > example of the code:
thanks for the excellent test case. There is a bug here which I have set up in https://github.com/sqlalchemy/sqlalchemy/issues/4723. however, for SQLite without PRAGMA FOREIGN KEYS as well as MySQL ISAM, your program will work as expected by adding passive_updates=False, which is needed for your test to run correctly in any case: class Employee(Base): __tablename__ = 'employees' employee_id = Column(Integer, primary_key=True) name = Column(String(50)) type = Column(String(30), nullable=False) __mapper_args__ = { 'polymorphic_identity': 'employee', 'polymorphic_on': type, 'with_polymorphic': '*', 'passive_updates': False } if you want your program to work on a database that enforces foreign keys you need to add onupdate="cascade" to your ForeignKey definitions, and additionally you need the fix for the above #4723 for it to work in all cases, although if you do explicitly update other non-primary key columns on manager, that will work around #4723 for now. > > from sqlalchemy import ( > Column, Integer, String, create_engine, > ForeignKey) > from sqlalchemy.orm import create_session > from sqlalchemy.ext.declarative import declarative_base > > e = create_engine("sqlite:///test.db", echo=True) > Base = declarative_base(bind=e) > > > *class* Employee(Base): > __tablename__ = 'employees' > > employee_id = Column(Integer, primary_key=True) > name = Column(String(50)) > type = Column(String(30), nullable=False) > > __mapper_args__ = { > 'polymorphic_identity': 'employee', > 'polymorphic_on': type, > 'with_polymorphic': '*' > } > > *def* __init__(self, name): > self.name = name > > > *class* Manager(Employee): > __tablename__ = 'managers' > __mapper_args__ = {'polymorphic_identity': 'manager'} > > employee_id = Column( > Integer, > ForeignKey('employees.employee_id'), > primary_key=True > ) > manager_data = Column(String(50)) > > *def* __init__(self, name, manager_data): > super(Manager, self).__init__(name) > self.manager_data = manager_data > > > *class* Owner(Manager): > __tablename__ = 'owners' > __mapper_args__ = {'polymorphic_identity': 'owner'} > > employee_id = Column( > Integer, > ForeignKey('employees.employee_id'), > ForeignKey('managers.employee_id'), > primary_key=True > ) > owner_secret = Column(String(50)) > > *def* __init__(self, name, manager_data, owner_secret): > super(Owner, self).__init__(name, manager_data) > self.owner_secret = owner_secret > > > Base.metadata.drop_all() > Base.metadata.create_all() > > s = create_session(bind=e, autoflush=True, autocommit=False) > o = Owner('nosklo', 'mgr001', 'ownerpwd') > s.add(o) > s.commit() > > el = s.query(Owner).one() > *# for el in session.query(Product, Event,Travel).all():* > print(el) > el.employee_id = 33 > *# el.manager_data='hello'* > s.commit() > > > The response when running this is : > 2019-06-12 16:07:36,232 INFO sqlalchemy.engine.base.Engine UPDATE employees > SET employee_id=? WHERE employees.employee_id = ? > 2019-06-12 16:07:36,233 INFO sqlalchemy.engine.base.Engine (33, 1) > 2019-06-12 16:07:36,233 INFO sqlalchemy.engine.base.Engine UPDATE owners SET > employee_id=? WHERE owners.employee_id = ? > 2019-06-12 16:07:36,233 INFO sqlalchemy.engine.base.Engine (33, 1) > 2019-06-12 16:07:36,234 INFO sqlalchemy.engine.base.Engine COMMIT > > So it is not updating my Manager table....and that is a big issue.... > Any idea about it? > > Thanks > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/c204902f-bab1-4b01-8937-0c2746758ca1%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/c204902f-bab1-4b01-8937-0c2746758ca1%40googlegroups.com?utm_medium=email&utm_source=footer>. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/aa783d0b-c63c-4897-9ee8-248ca3ff27ef%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.
