Thank you for taking the time to look at this. I have shied away from using events in the past, but now that you mention it I can see how that could work for this case.
I had to add slightly to my previous solution. In some cases, the relationship is nullable and this solution was silently giving a NULL when I expected a Foreign Key constraint failure. @parent_key.setter def parent_key(self, val): non_existent_id = -999 self.parent_id = coalesce( select([Parent.id_]).select_from(Parent.__table__).where(Parent.key == val).as_scalar(), non_existent_id ) (the .as_scalar() is needed to make coalesce() happy) On Thursday, 19 November 2020 at 14:36:12 UTC-5 Mike Bayer wrote: > > > On Thu, Nov 19, 2020, at 2:03 PM, Steven James wrote: > > In general I have set up foreign keys using the following pattern: > > p = session.query(Parent).filter(Parent.natural_key == key).one() > new_child = Child(parent=p) > > This makes a SELECT and an INSERT and is fine, but it gets a little > cumbersome when I want to create a new child object with many relationships > where I am not given the primary key. It basically boils down to requiring > custom constructor functions for each kind of object. > > What I would like is the following pattern: > > new_child = Child(parent_key=key) > > where parent_key can be a kind of hybrid_property() on the Child and it > would generate an `INSERT INTO child_table (parent_id) VALUES ((SELECT id > FROM parent_table WHERE key=?))` I've gotten it working that way actually, > but I wanted to check to make sure that this pattern does not already exist > in some easier construct. > > > I think that's a fine pattern to use, it all comes down to when you want > to be able to access the object. since you are setting up Child() with a > key before the Child has any connection to any session or transaction, that > rules out being able to know the "parent_id" right at that moment. You > could, if you wanted, use events to do that lookup at different times, such > as using the after_attach() event to look up the "parent" where you could > load the full Parent object and associate it, that way child.parent would > be ready before you ever invoked the INSERT. Or you could use events like > before_insert(), before_update() to set up that parent_id too. but setting > it to the SELECT expression is more succinct and pretty clever too. > > there's basically a lot of event hooks (too many at this point) that can > be used for this, but you've found probably the one way to do it without > using a hook, so should be good. > > > > Working example: > > from sqlalchemy import Column, Integer, String, ForeignKey, create_engine, > select > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.ext.hybrid import hybrid_property > from sqlalchemy.orm import Session, relationship > > Base = declarative_base() > > class Parent(Base): > __tablename__ = 'parent' > id_ = Column(Integer, primary_key=True) > key = Column(String(4)) > data = Column(String(64)) > > def __repr__(self) -> str: > return 'Parent(id={id_!r}, key={key!r})'.format(**self.__dict__) > > > class Child(Base): > __tablename__ = 'child' > id_ = Column(Integer, primary_key=True) > parent_id = Column(ForeignKey('parent.id_')) > data = Column(String(64)) > > parent = relationship(Parent, backref='children', lazy='joined') > > @hybrid_property > def parent_key(self): > return self.parent.key > > @parent_key.expression > def parent_key(self): > return Parent.key > > @parent_key.setter > def parent_key(self, val): > self.parent_id = > select([Parent.id_]).select_from(Parent.__table__).where(Parent.key == val) > > > if __name__ == '__main__': > e = create_engine('sqlite:///', echo=True) > Base.metadata.create_all(e) > s = Session(bind=e) > > p1 = Parent(key='p1', data='parent1') > p2 = Parent(key='p2', data='parent2') > > s.add_all([p1, p2]) > s.commit() > > # initialize child/parent using the "natural key" > ## INSERT INTO child (parent_id, data) VALUES ((SELECT parent.id_ > FROM parent WHERE parent."key" = ?), ?) > c = Child(data='i am the child', parent_key='p1') > s.add(c) > s.commit() > print(c.parent) > > # update relationship using the "natural key" > ## UPDATE child SET parent_id=(SELECT parent.id_ FROM parent WHERE > parent."key" = ?) WHERE child.id_ = ? > c.parent_key = 'p2' > s.commit() > print(c.parent) > > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/b1fe8102-289a-4b8a-a96f-8acba644f9c8n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/b1fe8102-289a-4b8a-a96f-8acba644f9c8n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/70a2f74f-12ea-4177-b376-4537457b8716n%40googlegroups.com.