How do I make SQLAlchemy set values for a foreign key by passing a related entity in the constructor?

Question

When using SQLAlchemy I would like the foreign key fields to be filled in on the Python object when I pass in a related object. For example, assume you have network devices with ports, and assume that the device has a composite primary key in the database.

If I already have a reference to a "Device" instance and want to create a new "Port" instance linked to that device without knowing if it already exists in the database I would use the merge operation in SA. However, only setting the device attribute on the port instance is insufficient. The fields of the composite foreign key will not be propagated to the port instance and SA will be unable to determine the existence of the row in the database and unconditionally issue an INSERT statement instead of an UPDATE.

The following code examples demonstrate the issue. They should be run as one .py file so we have the same in-memory SQLite instance! They have only been split for readability.

Model Definition

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Unicode, ForeignKeyConstraint, create_engine

from sqlalchemy.orm import sessionmaker, relation
from textwrap import dedent


Base = declarative_base()

class Device(Base):
    __tablename__ = 'device'

    hostname = Column(Unicode, primary_key=True)
    scope = Column(Unicode, primary_key=True)
    poll_ip = Column(Unicode, primary_key=True)
    notes = Column(Unicode)

    ports = relation('Port', backref='device')


class Port(Base):
    __tablename__ = 'port'
    __table_args__ = (
        ForeignKeyConstraint(
            ['hostname', 'scope', 'poll_ip'],
            ['device.hostname', 'device.scope', 'device.poll_ip'],
            onupdate='CASCADE', ondelete='CASCADE'
        ),
    )

    hostname = Column(Unicode, primary_key=True)
    scope = Column(Unicode, primary_key=True)
    poll_ip = Column(Unicode, primary_key=True)
    name = Column(Unicode, primary_key=True)


engine = create_engine('sqlite://', echo=True)
Base.metadata.bind = engine
Base.metadata.create_all()
Session = sessionmaker(bind=engine)

The model defines a Device class with a composite PK with three fields. The Port class references Device through a composite FK on those three columns. Device also has a relationship to Port which will use that FK.

Using the model

First, we add a new device and port. As we're using an in-memory SQLite DB, these will be the only two entries in the DB. And by inserting one device into the database we have something in the device table that we expect to be loaded on the subsequent merge in session "sess2"

sess1 = Session()
d1 = Device(hostname='d1', scope='s1', poll_ip='pi1')
p1 = Port(device=d1, name='port1')
sess1.add(d1)
sess1.commit()
sess1.close()

Working example

This block works, but it is not written in a way I would expect it to behave. More precisely, the instance "d1" is instantiated with "hostname", "scope" and "poll_ip", and that instance is passed to the "Port" instance "p2". I would expect that "p2" would "receive" those 3 values through the foreign key. But it doesn't. I am forced to manually assign the values to "p2" before calling "merge". If the values are not assigned, SA does not find the identity and tries to run an "INSERT" query for "p2" which will conflict with the already existing instance.

sess2 = Session()
d1 = Device(hostname='d1', scope='s1', poll_ip='pi1')
p2 = Port(device=d1, name='port1')
p2.hostname=d1.hostname
p2.poll_ip=d1.poll_ip
p2.scope = d1.scope
p2 = sess2.merge(p2)
sess2.commit()
sess2.close()

Broken example (but expecting it to work)

This block shows how I would expect it to work. I would expect that assigning a value to "device" when creating the Port instance should be enough.

sess3 = Session()
d1 = Device(hostname='d1', scope='s1', poll_ip='pi1')
p2 = Port(device=d1, name='port1')
p2 = sess3.merge(p2)
sess3.commit()
sess3.close()

How can I make this last block work?

Answer 1

The FK of the child object isn't updated until you issue a flush() either explicitly or through a commit(). I think the reason for this is that if the parent object of a relationship is also a new instance with an auto-increment PK, SQLAlchemy needs to get the PK from the database before it can update the FK on the child object (but I stand to be corrected!).

According to the docs, a merge():

examines the primary key of the instance. If it’s present, it attempts to locate that instance in the local identity map. If the load=True flag is left at its default, it also checks the database for this primary key if not located locally.

If the given instance has no primary key, or if no instance can be found with the primary key given, a new instance is created.

As you are merging before flushing, there is incomplete PK data on your p2 instance and so this line p2 = sess3.merge(p2) returns a new Port instance with the same attribute values as the p2 you previously created, that is tracked by the session. Then, sess3.commit() finally issues the flush where the FK data is populated onto p2 and then the integrity error is raised when it tries to write to the port table. Although, inserting a sess3.flush() will only raise the integrity error earlier, not avoid it.

Something like this would work:

def existing_or_new(sess, kls, **kwargs):
    inst = sess.query(kls).filter_by(**kwargs).one_or_none()
    if not inst:
        inst = kls(**kwargs)
    return inst

id_data = dict(hostname='d1', scope='s1', poll_ip='pi1')
sess3 = Session()
d1 = Device(**id_data)
p2 = existing_or_new(sess3, Port, name='port1', **id_data)
d1.ports.append(p2)
sess3.commit()
sess3.close()

This question has more thorough examples of existing_or_new style functions for SQLAlchemy.