223

I have users table in my MySql database. This table has id, name and age fields.

How can I delete some record by id?

Now I use the following code:

user = User.query.get(id)
db.session.delete(user)
db.session.commit()

But I don't want to make any query before delete operation. Is there any way to do this? I know, I can use db.engine.execute("delete from users where id=..."), but I would like to use delete() method.

vvvvv
  • 25,404
  • 19
  • 49
  • 81
Sergey
  • 5,396
  • 3
  • 26
  • 38

4 Answers4

347

You can do this,

User.query.filter_by(id=123).delete()

or

User.query.filter(User.id == 123).delete()

Make sure to commit for delete() to take effect.

MarredCheese
  • 17,541
  • 8
  • 92
  • 91
adarsh
  • 6,738
  • 4
  • 30
  • 52
  • 34
    make sure you put ```db.session.commit()``` in the end. where the db: ```db = SQLAlchemy(app)``` – Ben Oct 04 '16 at 04:36
  • Can you use IN with this method? – ram4nd Jun 22 '17 at 11:37
  • 15
    Warning: Such `delete` will not cascade if you rely on cascading in python/ORM. You will need to [query the object first, and then delete](https://stackoverflow.com/questions/19243964/sqlalchemy-delete-doesnt-cascade). – nirvana-msu Feb 02 '18 at 00:24
  • 11
    what would happen if `User` doesn't exist? – senaps Apr 14 '19 at 06:48
  • how can we override/extend this delete() method? for a class User defined in our models.py in the flask app. – Hossein Kalbasi Mar 12 '20 at 18:00
  • 1
    The above are direct sql `DELETE` statements that do not involve loading an object in the orm before deleting it and @nirvana-msu is correct in that the db is not aware of cascade rules set at the orm-level relationship. If you can instead set them in the foreign key declarations (i.e. at the db-level relationship), the cascade will work just fine (e.g. `Column('parent_id', ForeignKey('parents.id', ondelete='cascade'))`). – Michael Ekoka Oct 16 '20 at 14:25
  • 1
    @senaps the delete() method returns the number of items deleted. So if User does not exist, it returns zero and throws no exception. I tested this behavior on Sqlalchemy v1.4.29 with a SQLite in-memory database. – chrisinmtown Jan 27 '22 at 14:08
95

Just want to share another option:

# mark two objects to be deleted
session.delete(obj1)
session.delete(obj2)

# commit (or flush)
session.commit()

http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#deleting

In this example, the following codes shall works fine:

obj = User.query.filter_by(id=123).one()
session.delete(obj)
session.commit()
vvvvv
  • 25,404
  • 19
  • 49
  • 81
Steve
  • 1,448
  • 11
  • 18
  • 1
    when you're running a log, this option is quite nice because it enables you to make the query and record some of what it is your user has considered insignificant. that way, you can show them the log when they ask you why that record is missing! – beep_check Nov 02 '21 at 02:57
27

In sqlalchemy 1.4 (2.0 style) you can do it like this:

from sqlalchemy import select, update, delete, values

sql1 = delete(User).where(User.id.in_([1, 2, 3]))
sql2 = delete(User).where(User.id == 1)

db.session.execute(sql1)
db.session.commit()

or

u = db.session.get(User, 1)
db.session.delete(u)
db.session.commit()

In my opinion using select, update, delete is more readable. Style comparison 1.0 vs 2.0 can be found here.

Abc Xyz
  • 1,184
  • 12
  • 13
  • 5
    This answer should be higher. – Erfan Aug 27 '21 at 14:44
  • I know, when i was learning sqlalchemy all over the stackoverflow there are old style examples, and was even hard to figure out there are two styles. New style (2.0) is more like writing sql. But using `and_`, `or_` is ugly in new style hehe – Abc Xyz Sep 08 '21 at 21:22
20

Another possible solution specially if you want batch delete

deleted_objects = User.__table__.delete().where(User.id.in_([1, 2, 3]))
session.execute(deleted_objects)
session.commit()
Logovskii Dmitrii
  • 2,629
  • 4
  • 27
  • 44
  • 1
    this is super nice because sometimes the user name is empty or has Unicode characters. deleting by ID is golden. spasibo :) – Vortex Apr 18 '21 at 17:24