Re: [sqlalchemy] Setting Foreign Key based on natural key
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
>
Re: [sqlalchemy] Setting Foreign Key based on natural key
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
>
> .
--
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
[sqlalchemy] Setting Foreign Key based on natural key
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.
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.
