CODEWITHSUNDEEP
pythonpython-3.xpostgresqlpsycopg2
Sujay DSa
Sujay DSa

Reputation: 1192

retain only specific rows psycopg2

I have a table containing jobs like this

id   owner          collaborator    privilege 
90  "919886297050"  "919886212378"  "read"
90  "919886297050"  "919886297052"  "read"
88  "919886297050"  "919886212378"  "read"
88  "919886297050"  "919886297052"  "read"

primary key is a composite of id, owner and collaborator I want to pass in details of only the collaborators i want to retain. For example, if my collaborator = "919886212378" it means I want to delete the row for "919886297052" and keep the row for "919886212378"

Is there a way to do this in one query / execution instead of fetching the details separately and then performing the delete after filtering the missing values?

EDIT: My use case might have new collaborators added and old ones deleted. However, my input will just have a set of chosen collaborators so I will need to cross check with the old list, retain existing, add new and delete missing collaborators.

Upvotes: 0

Views: 35

Answers (2)

Sujay DSa
Sujay DSa

Reputation: 1192

Comparing the old and new collaborator lists in python kind of did the trick for me

    original_list = ["C1","C2","C3","C4"] // result from query
    updated_list= ["C1","C6","C7"] // list obtained from request


    # compute the differences   
    to_be_added  = set(updated_list).difference(set(original_list) )
    to_be_deleted  = set(original_list).difference(set(updated_list) )

Then I use an insert and delete statement within a transaction using the above two lists to make an update.

Upvotes: 0

hootnot
hootnot

Reputation: 1014

DELETE FROM table WHERE collaborator NOT IN ("919886212378", "id ..")

does the delete for the specific case you mentioned. But I don't know how you get these id's. You give too little information regarding your exact case. If you can get these id's by a query, you could make it a subquery like:

DELETE FROM table WHERE collaborator NOT IN (SELECT ... FROM ...)

Upvotes: 1

Related Questions