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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/b1fe8102-289a-4b8a-a96f-8acba644f9c8n%40googlegroups.com.
