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.