Flask-SQLAlchemy how to delete all rows in a single table

Question

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

score 188 · Accepted Answer · edited Sep 17 '19 at 18:25

188

Try delete:

models.User.query.delete()

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

edited Sep 17 '19 at 18:25

Ilja Everilä

50,538
7
126
127

answered May 15 '13 at 19:55

DazWorrall

13,682
5
43
37

3

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
11

Don't forget to commit after deleting. – Kevin Nov 13 '17 at 08:41
11

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

score 142 · Answer 2 · answered Aug 21 '14 at 19:28

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()

Anand Tripathi · Answer 3 · 2020-02-16T04:32:10.583

88

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

edited Feb 16 '20 at 04:32

answered Jul 13 '16 at 07:06

Anand Tripathi

14,556
1
47
52

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

score 0 · Answer 4 · answered Aug 05 '21 at 11:16

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))

score 0 · Answer 5 · edited Oct 16 '22 at 14:58

When I have an application with several types of tables, I pass the ID and TYPE in the URL of the delete button like this:

<a href="{{ url_for('app.cleartable',type='items') }}" class="btn btn-danger">
    <i class="fas fa-trash fa-fw"></i>
    Clear table
</a>

now if you make the request to '/delete/<string:type>/<int:id>' the value of the text string will be taken to do the deletion and redirect to the following url in items

@app.route('/delete/<string:type>/<int:id>', methods=['POST', 'GET'])
def delete(type, id):
    if type == "item":
        to_delete = Item.query.get(id)

        try:
            db.session.delete(to_delete)
            db.session.commit()
            flash(f'{to_delete.name} successfully deleted!', 'success')
        except:
            db.session.rollback() 
            flash(f'{to_delete.name} failed to delete!', 'error')
        return redirect(url_for('items'))

Flask-SQLAlchemy how to delete all rows in a single table

5 Answers5

Flask-Sqlalchemy

Linked