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+unsubscr...@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/fb5de644-f719-48aa-b8c2-c4c18f74d129%40www.fastmail.com.
