CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
SeanPlusPlus
SeanPlusPlus

Reputation: 9043

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

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

Upvotes: 151

Views: 205527

Answers (5)

joellpaim
joellpaim

Reputation: 48

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

Upvotes: 0

Mohamed MosȜd
Mohamed MosȜd

Reputation: 146

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

Upvotes: -1

Anand Tripathi
Anand Tripathi

Reputation: 16166

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

Upvotes: 94

DazWorrall
DazWorrall

Reputation: 14210

Try delete:

models.User.query.delete()

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

Upvotes: 198

Steve Saporta
Steve Saporta

Reputation: 4881

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

Upvotes: 150

Related Questions