inheritance hierarchy not updating second level hierarchy

[Javier Martínez]

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:

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

[Mike Bayer]

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.

--

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+...@googlegroups.com.

To post to this group, send email to sqlal...@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.

For more options, visit https://groups.google.com/d/optout.

[Javier Martínez]

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

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.