query to remove entry with wrong institution

jaap....@gmail.com

unread,

May 31, 2024, 8:16:55 AMMay 31

to OpenREM

I'm importing new RDSR's every night from our PACS. As we import a lot of studies from other institutions which come with a dose report, I end up with a lot of wrong studies in my database. Using the filter IMPORT when running the query helps a bit, but I still have a lot of wrong data.

I'm looking for a sql query to find and remove those wrong studies, based upon the institution. I have opened the database for external connections and I'm able to run sql queries using python. The question remains, what query is behind that red button (delete study and table entry). And where in the db is the institution given?

Any suggestions?

Ed McDonagh

unread,

Jun 5, 2024, 6:14:10 PMJun 5

to jaap....@gmail.com, OpenREM

Hi Jaap

We have a similar problem, but my approach is to find them in the display name review page and set them all to be called 'imported', so at least they tidy themselves up. It does require a fair amount of work to do this though.

The delete studies and table entry button executes the code in views_admin.py: https://bitbucket.org/openrem/openrem/src/da0dfc2c53f0817de2e8cd48382e6d98266514bd/openrem/remapp/views_admin.py#lines-587

Rather than use SQL directly, it uses the Django python SQL interface to select all the studies against a specific equip_name_pk and modality, then if everything matches it does a simple delete of the studies then of the equipment object. You won't need to use the modality check step, so it could end up being just

GeneralStudyModuleAttr.objects.filter(generalequipmentmoduleattr__unique_equipment_name__pk=equip_name_pk).delete()

UniqueEquipmentNames.objects.get(pk=equip_name_pk).delete()

Let me know how you get on.

Ed

--
You received this message because you are subscribed to the Google Groups "OpenREM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openrem+u...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/openrem/05407df5-1635-4a87-8b7b-ae27882df082n%40googlegroups.com.

jaap....@gmail.com

unread,

Jun 7, 2024, 8:05:24 AMJun 7

to OpenREM

I got this

_____________________________________________________________________

import psycopg2

def get_connection():
return psycopg2.connect(
host="ip of openrem",
database="openrem_prod",
user="openrem_user",
password="openrem_pass"
)

def get_wrong_institutions(cursor):
sql = """
SELECT general_study_module_attributes_id, unique_equipment_name_id
FROM remapp_generalequipmentmoduleattr
WHERE (institution_name NOT LIKE '%name1%' AND institution_name NOT LIKE '%name2%')
OR institution_name IS NULL
OR institution_name = '';
"""
cursor.execute(sql)
rows = cursor.fetchall()
study_ids = [row[0] for row in rows]
equipment_ids = [row[1] for row in rows]
return study_ids, equipment_ids

def delete_study(cursor, id):
sql = "DELETE FROM remapp_generalstudymoduleattr WHERE id = %s;"
cursor.execute(sql, (id,))

def delete_equipment(cursor, id):
sql = "DELETE FROM remapp_uniqueequipmentnames WHERE id = %s;"
cursor.execute(sql, (id,))

def main():
try:
with get_connection() as conn:
with conn.cursor() as cur:
study_ids, equipment_ids = get_wrong_institutions(cur)

for id in study_ids:
delete_study(cur, id)

for id in equipment_ids:
if id is not None:
delete_equipment(cur, id)

conn.commit()
except Exception as e:
print(f"An error occurred: {e}")

main()

___________________________________________________________________________________________________________

But i got the following error:

An error occurred: update or delete on table "remapp_generalstudymoduleattr" violates foreign key constraint "D61978a0f009ca7878ec98b861ebe601" on table "remapp_ctradiationdose"
DETAIL: Key (id)=(694946) is still referenced from table "remapp_ctradiationdose".