How Do I Make Sqlalchemy Set Values For A Foreign Key By Passing A Related Entity In The Constructor?

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 po

Solution 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.