106

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?

Braiam
  • 1
  • 11
  • 47
  • 78
Dmitrijs Zubriks
  • 2,696
  • 6
  • 22
  • 33
  • "Last" could have done with a clear definition here. In a system with concurrent updates the most recently allocated id and the most recently committed id may not be the same, for example transaction A allocates id 42, transaction B allocates id 43, transaction B commits, then transaction A commits. Which is "last"? – snakecharmerb Aug 28 '23 at 14:47

4 Answers4

214

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()
miku
  • 181,842
  • 47
  • 306
  • 310
  • 7
    @dimazubrik I get a warning using by using it `SAWarning: Can't resolve label reference '-id'; converting to text() (this warning may be suppressed after 10 occurrences)` – Shift 'n Tab Nov 07 '18 at 14:44
  • 4
    ^ This has now become an error; I used `Model.query.order_by(-Model.field_name).first()` to retrieve the desired object – Pratik K. Oct 09 '19 at 20:40
  • 4
    Does this actually return all values of the query and then just pop the first one (ie. super inefficient) or is it smart enough to actually to something like `ORDER BY desc LIMIT 1` under the hood? – Adam Hughes Aug 24 '20 at 17:25
  • @AdamHughes you could specify this with a .limit() modifier such as session.query(ObjectRes).order_by(ObjectRes.id.desc()).limit(1) – Kyle Meador Dec 28 '22 at 03:43
9

Sometimes it is difficult to reformulate simple things:

SELECT * FROM ObjectRes WHERE id IN (SELECT MAX(id) FROM ObjectRes)

but this worked for me:

session.query(ObjectRes).filter(ObjectRes.id == session.query(func.max(ObjectRes.id)))
empiric
  • 7,825
  • 7
  • 37
  • 48
Robert
  • 133
  • 1
  • 3
4

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()
Alex Kosh
  • 2,206
  • 2
  • 19
  • 18
0

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()
Oliver
  • 1,576
  • 1
  • 17
  • 31