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()
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,)
"""
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()
MyTablean ORM class?