How can I tell SQLAlchemy to use a different identity rule for Session.merge (instead of the PK)?
<p>I have a legacy DB which was blindly created with auto-increment IDs even though there's a perfectly valid natural key in the table.</p>

<p>This ends up with code littered with code along the lines:</p>

<pre><code>Fetch row with natural key 'x'
if exists:
    update row with NK 'x'
else:
    insert row with NK 'x'
</code></pre>

<p>Essentially an upsert.</p>

<p>This use-case (upsert) is covered by <a href="http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.merge" rel="nofollow">Session.merge()</a> from SQLAlchemy. But SA will only look at the primary key of the table to reconcile whether it has to do an insert or update. In the existing DB, the PK does however - contrary to what it <em>should</em> do - not represent the true identity of the row. So the same identity can appear with multiple auto-increment IDs. There are some other business rules in place to ensure uniqueness. But the ID <code>1</code> of today can be ID <code>3246</code> tomorrow!</p>

<p>There is currently no good way to modify the DB in a sensible manner as too many legacy applications are dependent on the structure as it is.</p>

<p>For the sake of a tangible example, assume we have network devices in the table, and take their hostname as natural key. The <em>current</em> DB would look something like this:</p>

<pre><code>CREATE TABLE device (
    id SERIAL PRIMARY KEY,
    hostname TEXT UNIQUE,
    some_other_column TEXT
)
</code></pre>

<p>The corresponding SA model:</p>

<pre><code>class Device(Base):
    id = Column(Integer, primary_key=True)
    hostname = Column(String(256))
    some_other_column = Column(String(20))
</code></pre>

<p>I would like to be able to do the following:</p>

<pre><code>mydevice = Device(hostname='hello-world', some_other_column='foo')
merged_device = session.merge(mydevice)
session.commit()
</code></pre>

<p>In this example, I would like SA to do an "insert or update". But with the current model, this would actually result in an error (due to the unique hostname constraint).</p>

<p>I <em>could</em> specify the <code>hostname</code> column as primary key in the SA model (and leave the PK in the DB as-is). But that looks a bit hacky. Is there not a more explicit and understandable way to tell SQLAlchemy that it should use "hostname" as identity? And if yes, how?</p>

<p>In situations like this, i find it best to <em>lie</em> to sqlalchemy.  Tell it that the natural key is primary.</p>

<pre><code>class Device(Base):
    hostname = Column(String(256), primary_key=True)
    some_other_column = Column(String(20))
</code></pre>

python sqlalchemy