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 [email protected].
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/70a2f74f-12ea-4177-b376-4537457b8716n%40googlegroups.com.
