SQLAlchemy look up and merge object before adding
<p>I have an object which has foreign keys to another object. Let's call the first object <code>A</code> and the second object <code>B</code>. <code>A</code> can be represented as <code>(integer id, integer b_id, integer some_data)</code> and <code>B</code> can be represented as <code>(integer id, integer datum_one, integer datum_two)</code></p>

<p><code>datum_one</code> and <code>datum_two</code> form a unique composite in <code>B</code> (I think this is the right term - I don't want more than on entry in B with the same combination of these fields). However, I just want to reference the id in the ForeignKey pointing from <code>A</code> to <code>B</code>. It seems tedious and redundant to do something like a composite foreign key <a href="http://docs.sqlalchemy.org/en/latest/core/constraints.html" rel="nofollow">like here</a>.</p>

<p>I want to have functionality such that when I add <code>A</code> to my database, it first searches for a matching <code>B</code> which has the same <code>datum_one</code> and <code>datum_two</code>. If this exists, it uses the matching entry in the database, and otherwise it adds a new row to the table representing <code>B</code>.</p>

<p>Is there a way to accomplish this? I suspect the solution have have something to do with the merge directive, but I'm not sure how to get this functionality exactly.</p>

<p>One potential solution I considered was actually using the UNIQUE directive, but it seems like SQLAlchemy doesn't play nice with unique - I would basically need to just write my own error handling, which is an option but I was hoping SQLAlchemy would have a more elegant solution to this.</p>