CODEWITHSUNDEEP
pythonsqlsqlitesqlalchemy
Alex Alifimoff
Alex Alifimoff

Reputation: 1849

SQLAlchemy look up and merge object before adding

I have an object which has foreign keys to another object. Let's call the first object A and the second object B. A can be represented as (integer id, integer b_id, integer some_data) and B can be represented as (integer id, integer datum_one, integer datum_two)

datum_one and datum_two form a unique composite in B (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 A to B. It seems tedious and redundant to do something like a composite foreign key like here.

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

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.

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.

Upvotes: 1

Views: 1986

Answers (1)

Sam Bourne
Sam Bourne

Reputation: 650

I think you should just handle this yourself before inserting your object to the database. From the docs... http://docs.sqlalchemy.org/en/latest/core/events.html

from sqlalchemy import event

# standard decorator style
@event.listens_for(SomeSchemaClassOrObject, 'before_create')
def receive_before_create(target, connection, **kw):
    "listen for the 'before_create' event"

    # ... (event handling logic) ...

Upvotes: 1

Related Questions