CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
kartikey
kartikey

Reputation: 370

How to check if a list of primary keys already exist in DB in a single query?

In db I have a table called register that has mail-id as primary key. I used to submit in bulk using session.add_all(). But sometimes some records already exist; in that case I want to separate already existing records and non- existing.

Upvotes: 0

Views: 1166

Answers (2)

Rach Sharp
Rach Sharp

Reputation: 2444

http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.merge

If all the objects you are adding to the database are complete (e.g. the new object contains at least all the information that existed for the record in the database) you can use Session.merge(). Effectively merge() will either create or update the existing row (by finding the primary key if it exists in the session/database and copying the state across from the object you merge). The crucial thing to take note of is that the attribute values of the object passed to merge will overwrite that which already existed in the database.

I think this is not so great in terms of performance, so if that is important, SQLAlchemy has some bulk operations. You would need to check existence for the set of primary keys that will be added/updated, and do one bulk insert for the objects which didn't exist and one bulk update for the ones that did. The documentation has some info on the bulk operations if it needs to be a high-performance approach.

http://docs.sqlalchemy.org/en/latest/orm/persistence_techniques.html#bulk-operations

Upvotes: 2

Jassin Rdrgo
Jassin Rdrgo

Reputation: 9

User SQL ALchemys inspector for this:

inspector = inspect(engine)
inspector.get_primary_keys(table, schema)

Inspector "reflects" all primary keys and you can check agaist the returned list.

Upvotes: -1

Related Questions