How to add new data only if it is nor already stored using the ORM interface?

Question

I've been learning how to use sqlalchemy (I'm still very much a beginner). I am using the ORM (as opposed to the SQLAlchemy Expression Language) and have set up a number of scripts to put timestamped sensor data as it becomes available into the database using the simple

>>> session.add(query)
>>> session.commit()

approach.

On occasions, one of the scripts can get some 'new' data that isn't in fact new at all; the exact same data has already been processes and added to the database.

If if ignore this and simply add whatever data I get, I get an

 (IntegrityError) duplicate key value violates unique constraint

I've initially worked around this by simply catching the exception and rolling back the transaction. However, this is causing many many such IntegrityErrors and clogging the DB error logs. Clearly this is a poor solution, instead I should be either updating the duplicate data, or first checking what data is already present and only adding the new stuff. There are an infinte number of ways to do this, but I'm sure there is an inbuilt simple and efficient approach (since this must be far from a unique issue).

What is the best way of solving this issue?

Answer 1

Can you have duplicate data? If it is timstamped data it seems that it is more like a log? I'd first evaluate to make sure that your unique constraints are on the proper columns.

There are quite a few ways of handling this, as you mentioned.

Your way of handling it is fine. Your current way has a benefit as it only requires 1 round trip to the db, trying to insert a record, catching error if it does not exist.

You can easily check to see if the record is present first, this will usually require an additionaly roundtrip to the db. (1 to check if record exists, 1 to insert) This additional round trip might not be an issue for you, and would allow you to control logging the duplicate in whatever way you see fit.

You might also look at configuring your log-level

a related question:

Does SQLAlchemy have an equivalent of Django's get_or_create?