Thanks a lot Mike. I really appreciate your quick response. You were right, I am working with AWS Redshift which does not enforce Foreign Keys so 'passive_updates': False made the work for me. (despite
Pretty happy to have a contribution (even a collateral, you found the bug not me :) ) if the bug is helping to improve SQLalchemy (great project). Best On Wednesday, June 12, 2019 at 6:30:30 PM UTC+2, Mike Bayer wrote: > > > > 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. > <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 sqlal...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > 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/1cf88ba3-945d-492e-853e-7f70dafc2032%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
