subject:"\[sqlalchemy\] inheritance hierarchy not updating second level hierarchy"

Re: [sqlalchemy] inheritance hierarchy not updating second level hierarchy

2019-06-13 Thread 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


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. 
>    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 tableand that is a big issue
> Any idea about it?
>
> Thanks
>
>
> --
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
>

Re: [sqlalchemy] inheritance hierarchy not updating second level hierarchy

2019-06-12 Thread 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.




> 
> 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 tableand 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
>  
>

[sqlalchemy] inheritance hierarchy not updating second level hierarchy

2019-06-12 Thread 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 tableand 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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] inheritance hierarchy not updating second level hierarchy

Re: [sqlalchemy] inheritance hierarchy not updating second level hierarchy

[sqlalchemy] inheritance hierarchy not updating second level hierarchy

3 matches

Site Navigation

Mail list logo

Footer information