126

How do I delete all rows in a single table using Flask-SQLAlchemy?

Looking for something like this: 

>>> users = models.User.query.all()
>>> models.db.session.delete(users)

# but it errs out: UnmappedInstanceError: Class '__builtin__.list' is not mapped
Ilja Everilä
  • 45,748
  • 6
  • 100
  • 105
SeanPlusPlus
  • 7,944
  • 17
  • 55
  • 80

4 Answers4

175

Try delete:

models.User.query.delete()

From the docs: Returns the number of rows deleted, excluding any cascades.

Ilja Everilä
  • 45,748
  • 6
  • 100
  • 105
DazWorrall
  • 12,652
  • 4
  • 42
  • 35
  • 2
    Hmm, this worked for me, but only after changing it to something like this: `models.User.query().delete()` – killthrush Mar 18 '16 at 00:54
  • 1
    Does not work if you use a query like this: `Model.query.filter(Model.some_id == some_id, Model.other_id.in_(other_ids).delete()` – swade Jan 26 '17 at 19:27
  • 10
    Don't forget to commit after deleting. – Kevin Nov 13 '17 at 08:41
  • 8
    If you are using `Flask-SQLAlchemy` then try `User.query.delete()` as `query` object as 'BaseQuery' object is not callable. Tested and verified. – Shirish Kadam Jul 13 '19 at 23:29
127

DazWorrall's answer is spot on. Here's a variation that might be useful if your code is structured differently than the OP's:

num_rows_deleted = db.session.query(Model).delete()

Also, don't forget that the deletion won't take effect until you commit, as in this snippet:

try:
    num_rows_deleted = db.session.query(Model).delete()
    db.session.commit()
except:
    db.session.rollback()
Steve Saporta
  • 4,062
  • 2
  • 27
  • 32
80

Flask-Sqlalchemy

Delete All Records

#for all records
db.session.query(Model).delete()
db.session.commit()

Deleted Single Row

here DB is the object Flask-SQLAlchemy class. It will delete all records from it and if you want to delete specific records then try filter clause in the query. ex.

#for specific value
db.session.query(Model).filter(Model.id==123).delete()
db.session.commit()

Delete Single Record by Object

record_obj = db.session.query(Model).filter(Model.id==123).first()
db.session.delete(record_obj)
db.session.commit()

https://flask-sqlalchemy.palletsprojects.com/en/2.x/queries/#deleting-records

Anand Tripathi
  • 11,955
  • 1
  • 39
  • 46
  • 1
    I am in a similar kind of a situation. Say there are 4 records in the table as of now, from id 1 to 4. When I do a db.session.query(Table_name).delete() db.session.commit() and then if I do a db.session.add() again to add new records, the next record gets an id of 5. Since my table is now empty, I want my new records to now get an id starting from 1 again. How can I do this ? – qre0ct Oct 19 '16 at 19:16
  • Please refer to this link http://stackoverflow.com/questions/23038422/reset-id-count-after-table-delete – Anand Tripathi Oct 25 '16 at 07:39
-1

writing raw sql commands sometimes is useful

    def delete_table_content(self, table_name: str):
          "deletes table contents"
          CONNECTION = db_url
          conn = psycopg2.connect(CONNECTION)
          conn.autocommit = True
          cursor = conn.cursor()
          cursor.execute("TRUNCATE TABLE {}".format(table_name))
          logger.warning("deleted table {} content".format(table_name))
Mohamed MosȜd
  • 110
  • 2
  • 8