I need to get last record from db. I'm using sqlalchemy. At the moment, I'm doing like that:
obj = ObjectRes.query.all()
return str(obj[-1].id)
But it's too heavy query. How can I get last record better?
I need to get last record from db. I'm using sqlalchemy. At the moment, I'm doing like that:
obj = ObjectRes.query.all()
return str(obj[-1].id)
But it's too heavy query. How can I get last record better?
Take a look at Query.first()
. If you specify a sort on the right column, the first will be your last. An example could look like this:
obj = session.query(ObjectRes).order_by(ObjectRes.id.desc()).first()
Don't forget to disable existing ordering if needed
In my case I have dynamic ordered relationships:
class Match:
...
records = relationship("Record", backref="match", lazy="dynamic", order_by="Record.id")
And when I tried accepted answer I got first record, not the last, cause ORDER BY
were applied twice and spoiled the results.
According to documentation:
All existing ORDER BY settings can be suppressed by passing None
So the solution will be:
match = db_session.query(Match).first()
last_record = match.records.order_by(None).order_by(Record.id.desc()).first()
This answer modifies the others to allow for cases where you don't know what the primary key is called.
from sqlalchemy.inspection import inspect
# ...
def last_row(Table: type, *, session): # -> Table
primary_key = inspect(Table).primary_key[0].name # must be an arithmetic type
primary_key_row = getattr(Table, primary_key)
# get first, sorted by negative ID (primary key)
return session.query(Table).order_by(-primary_key_row).first()