I'm trying to mimic this query:

DELETE FROM files WHERE id IN (SELECT id FROM files WHERE patient_id=X);

But I fail to do it using this as this only deletes a single record:

obj = File.query.filter_by(patient_id=X).delete()
session.delete(obj)
sessionmit()

I'm trying to mimic this query:

DELETE FROM files WHERE id IN (SELECT id FROM files WHERE patient_id=X);

But I fail to do it using this as this only deletes a single record:

obj = File.query.filter_by(patient_id=X).delete()
session.delete(obj)
sessionmit()

• python
• flask
• sqlalchemy
• flask-sqlalchemy

• I don't know why you use delete() two times. – furas Commented Mar 2 at 12:28
• maybe first get filtered result and check how many items it gives – furas Commented Mar 2 at 12:29
• see: python - How to delete a record by id in Flask-SQLAlchemy - Stack Overflow. it shows batch deleting as deleted_objects = User.__table__.delete().where(User.id.in_([1, 2, 3])) and session.execute(deleted_objects) – furas Commented Mar 2 at 12:32

2 Answers 2

If you want to delete all files of a patient including the patient himself, it makes sense to define a relationship that contains the keyword cascade.

class Patient(db.Model):
    __tablename__ = 'patients'
    id : db.Mapped[int] = db.mapped_column(primary_key=True)
    name : db.Mapped[str] = db.mapped_column(db.String, nullable=False)
    files : db.Mapped[List['File']] = db.relationship(back_populates='patient', cascade='all, delete')

class File(db.Model):
    __tablename__ = 'files'
    id : db.Mapped[int] = db.mapped_column(primary_key=True)
    patient_id : db.Mapped[int]= db.mapped_column(db.Integer, db.ForeignKey('patients.id'), nullable=False)
    patient : db.Mapped['Patient'] = db.relationship(back_populates='files')

This way you can simply delete the patient as you are used to and all associated files will also be removed.

pat_id = 1
patient = db.session.get(Patient, pat_id)
db.session.delete(patient)
db.sessionmit()

If, however, you only want to delete all files of a patient, you can do so with the following call.

pat_id = 1
db.session.execute(db.delete(File).where(File.patient_id == pat_id))

All code examples I have written here do not use the legacy query interface, but the syntax introduced with SQLAlchemy 2.0.

If you want to completely mimic the query you mentioned you would need to use subquery.

from your_app import db
from your_app.models import File

subquery = db.session.query(File.id).filter(File.patient_id == X).subquery()
db.session.query(File).filter(File.id.in_(subquery)).delete(synchronize_session=False)
db.sessionmit()

It is important that query.delete() performs the delete operation and returns the number of rows deleted not an object which can be used later.

But if you need to get, for example, ids for deleted rows you can use the .returning() method.

deleted_ids = (
    db.session.query(File)
    .filter(File.id.in_(subquery))
    .returning(File.id)
    .delete(synchronize_session=False)
)
db.sessionmit()