Hi Mike, Thanks for the quick reply. Solution found.
I tried both the event.listens and event.listens_for route before, but I was trying to use explicit relationships instead of the propagate flag. Thanks for the hint on propagate=True. That lead me to see my problem (that I also hit unknowingly before), which I'll list here in case someone else hits it. Namely, I thought the class instance (target) was the FIRST argument sent by the event, but no... the first argument was Mapper. I didn't see any exception until I placed print statements inside the updateTimestamp function. Turns out, the class target is the THIRD argument. Adding this after my class definition did the trick: @event.listens_for(Node, 'before_update', propagate=True) def updateTimestamp(mapper, connect, target): target.time_updated = func.now() Thanks for the help, and for an amazing set of libraries! -Chris On Mon, Jul 31, 2017 at 4:05 PM, Mike Bayer <mike...@zzzcomputing.com> wrote: > On Mon, Jul 31, 2017 at 4:37 PM, Chris Satterthwaite <cnsat...@gmail.com> > wrote: > > Conceptually, what I’m trying to do is: > > > > 1) Enable queries for a higher level type (e.g. Node) to return all > > direct instances and sub-class instances (e.g. Node, [Servers, [UnixType, > > [Linux, AIX]]]) > > > > 2) Enable a set of attributes to be inherited by all sub-types > > > > 3) Enable an attribute update at any level to update attributes seen > by > > all levels (e.g. time_updated in my example) > > > > > > > > 1 and 2 above “just work” by setting up SqlAlchemy with join inheritance. > > But I’ve failed to implement #3. > > > > > > > > Versions: Python v3.6.1, SQLAlchemy v1.1.11, and Postgres v9.6. > > > > > > Class definitions follow... > > in these class definitions, there's two fields mentioned > "time_updated" and then later "distribution", which are not mapped in > the same way. "time_updated" is only on the base Node class; to make > that update no matter what, do a before_update() event for the Node > class with propagate=True and then set object.time_updated=func.now() > in the event. > > For the "distribution" column, you've got the same-named column in two > different tables. This is typically not advised as the information > being stored is redundant; however if these two columns are indeed the > same information you can map both columns to one attribute using the > technique shown at > http://docs.sqlalchemy.org/en/latest/orm/mapping_columns. > html#using-column-property-for-column-level-options, > and they would automatically update together. > > > > > > class Node(Base): > > > > __tablename__ = 'node' > > > > hostname = Column(String(256), primary_key=True) > > > > domain = Column(String(256), primary_key=True) > > > > object_id = Column(CHAR(32), unique=True, default=lambda > :uuid.uuid4().hex) > > > > object_type = Column(String(16)) > > > > time_created = Column(DateTime(timezone=True), > server_default=func.now()) > > > > time_updated = Column(DateTime(timezone=True), default=func.now(), > > onupdate=func.now()) > > > > description = Column(String(256)) > > > > __mapper_args__ = {'with_polymorphic': '*', > 'polymorphic_identity':'node', > > 'polymorphic_on':object_type} > > > > > > class Server(Node): > > > > __tablename__ = 'server' > > > > object_id = Column(None, ForeignKey(Node.object_id), primary_key=True) > > > > related_application = Column(String(512), nullable=True) > > > > __mapper_args__ = {'with_polymorphic': '*', 'polymorphic_identity':' > server', > > 'inherit_condition': object_id == Node.object_id} > > > > > > class UnixType(Server): > > > > __tablename__ = 'unix_type' > > > > object_id = Column(None, ForeignKey(Server.object_id), primary_key=True) > > > > __mapper_args__ = {'polymorphic_identity':'node_windows', > > 'with_polymorphic': '*', 'inherit_condition': object_id == > Server.object_id} > > > > > > class Linux(UnixType): > > > > __tablename__ = 'linux' > > > > object_id = Column(None, ForeignKey(UnixType.object_id), > primary_key=True) > > > > distribution = Column(String(256), nullable=True) > > > > __mapper_args__ = {'with_polymorphic': '*', > 'polymorphic_identity':'linux', > > 'inherit_condition': object_id == UnixType.object_id} > > > > > > class AIX(UnixType): > > > > __tablename__ = 'aix' > > > > object_id = Column(None, ForeignKey(UnixType.object_id), > primary_key=True) > > > > distribution = Column(String(256), nullable=True) > > > > __mapper_args__ = {'with_polymorphic': '*', 'polymorphic_identity':'aix', > > 'inherit_condition': object_id == UnixType.object_id} > > > > > > > > > > I’d like my app to query the base class (Node) for either the two primary > > keys (hostname and domain) or the unique key (object_id), and then be > able > > to directly update any attribute on all inherited class levels. Right > now > > I’m having to query for Node, then query the Linux class for the same ID > (to > > get access to all the attributes for my update), then update. And that > > works ok unless I want automated actions on an inherited attribute – like > > the last update time (time_update). It only updates when I specifically > > update the class with that attribute (the Node class in my example). > > > > > > > > Complete script from my last iteration attached, along with sample > output. > > > > > > > > In my four iterations of test cases (required to show updates at all > > levels), I show the following: > > > > 1) Create a base type instance at the start, that can be sub-typed to > a > > more qualified subtype later (e.g. create Node instance, later sub-type > into > > AIX instance). I’m doing this by copy/delete/recreate-as-sub-type which > > works ok now; I am assuming there is a better way. > > > > 2) Create a more qualified type at the start, and send updates for > just > > the base type (e.g. create AIX instance at the start, later update the > > “related_application” on the Server instance, or the “time_updated” on > the > > Node instance) > > > > > > > > Samples show that the time_update attribute only changes when the base > class > > changes. > > > > > > > > I probably could update a Linux or AIX object, then re-query for its base > > Node with the same ID, and then force a manual update on the timestamp. > > Hoping there is another way. > > > > > > > > Sorry for the verbosity; I tried to be comprehensive. Appreciate your > help. > > > > > > > > > > > > =============================================== > > > > Additional detail (probably unnecessary) – hence listing it last: > > > > I’ve spent about a week spinning my wheels, so I’m reaching out. I’ve > tried > > different variations from web posts, including: > > > > • relationships from parent-to-child and vice versa (to event off of in > > order to propagate time_updated; hit different problems and gave up with > > circular dependency on deletes) > > > > • with_polymorphic in the query and attributes (to load all attributes > types > > so I could set in just one level) > > > > • duplicated attribute with foreign keys in all classes (and specifying > > inherit_condition - trying to get foreign keys to point to the same > > time_updated attribute in the base class) > > > > • a mixin object to use declared_attr.cascading on forign keys > > > > > > > > Perhaps one of those is the right way to do this, but I can’t figure it > out. > > Some posts show solutions without join inheritance, and I can’t seem to > > retrofit those solutions due to the foreign key magic. Most inheritance > > posts show only 2-levels deep but the same solution doesn’t seem to work > > with my model starting at 4-levels deep. > > > > -- > > 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. > > 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/sqlalchemy/rGTrG5vTOnU/unsubscribe. > To unsubscribe from this group and all its topics, 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. > 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. For more options, visit https://groups.google.com/d/optout.