CODEWITHSUNDEEP
pythonsqlsqlalchemyorm
Kuznetsov-M
Kuznetsov-M

Reputation: 435

How to delete last row using SQLAlchemy?

I need to exec next query:

DELETE FROM <table_name>
ORDER BY <column_name> DESC LIMIT 1

I trying:

query = session.query(MyTable)
query.order_by(MyTable.my_column.desc()).limit(1).delete()

error: Can't call Query.update() or Query.delete() when order_by() has been called

My current way (I would like to get rid of the loop):

query = session.query(MyTable)
for item in query.order_by(MyTable.my_column.desc()).limit(1).all():
    query.filter(MyTable.id == item.id).delete()

Upvotes: 0

Views: 1854

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123474

Since you're dealing with ORM objects you can just retrieve the "last" item and then delete it:

# ORM class for demo
class Team(Base):
    __tablename__ = "team"
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    rank = Column(Integer)

    def __repr__(self):
        return f"<Team(id={self.id}, name='{self.name}', rank={self.rank})>"


Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

# test data
with Session(engine) as session:
    session.add_all(
        [
            Team(name="Oilers", rank=3),
            Team(name="Flames", rank=1),
            Team(name="Canucks", rank=2),
        ]
    )
    session.commit()

# demo code
with Session(engine) as session:
    last_place_team = session.execute(
        select(Team).order_by(Team.rank.desc()).limit(1)
    ).scalar()
    print(last_place_team)  # <Team(id=1, name='Oilers', rank=3)>
    session.delete(last_place_team)
    session.commit()
    """SQL code:
    2021-04-25 14:08:16,251 INFO sqlalchemy.engine.Engine DELETE FROM team WHERE team.id = ?
    2021-04-25 14:08:16,252 INFO sqlalchemy.engine.Engine [generated in 0.00073s] (1,)
    """

Upvotes: 1

Abdlrahman Saber
Abdlrahman Saber

Reputation: 164

First get the last row

Using MySQL

SELECT * FROM Table ORDER BY ID DESC LIMIT 1

Using SQL

SELECT TOP 1 * FROM Table ORDER BY ID DESC

Then from the first query get the ID or this row and run this query

DELETE FROM table_name WHERE id=the_id_that_you_get_from_first_query

============ Update ===========

Let's start with the getting the last row:

obj = session.query(ObjectRes).order_by(ObjectRes.id.desc()).first()

To delete

Obj.query.filter(table.id == obj['id']).delete()

Upvotes: 1

Related Questions