Select the most recently created row in a data table

[richard.a.cairns]

Hi everyone, this is my first post. I’m having a play with the free version of Anvil. I’m a beginner programmer, and a project I’ve had in mind for a while is to build a tracker for my attempts to lose weight.

I have a data table called weights with three columns:

• date (date type)
• weight (number type)
• standard weigh-in (Bool type) [note 1]

What I want to be able to do is to select the row from the table that has the most recent date. What would be the best way to approach that? If the results of app_tables.weights.search() is a “list of dictionaries”, is it as simple as:

results = app_tables.weights.search()
most_recent_row = results[-1]

Can’t be that easy, can it?

Note 1: For the curious, this will denote whether the weight was obtained from Fitbit scales via the API into my Fitbit account, or manually entered (if I weighed in on different scales while away from home for example).

[stefano.menci]

See this:

How do I find the last record inserted?

Using [-1] requires the whole table to be scanned and it could be slow with large tables. Ordering the query and getting the first item requires no scan and is faster, but you need one column with an incremental or a timestamp value, so you can do: last_row = app_tables.my_table.search(tables.order_by('timestamp', ascending=False))[0]

[richard.a.cairns]

It seems it isn’t that easy… I tried the code above and got:

IndexError: list index cannot be negative

[richard.a.cairns]

Thanks, @stefano.menci. The date column in the table is using Anvil’s date type, which according to the docs uses Python’s datetime.date. That being the case, as long as I only ever have one entry per day (which I will) then I assume I can use your code above without adding any extra columns.

Just out interest though, can data tables automatically increment a number column, and enforce unique values, to make a reliable primary key column?

[richard.a.cairns]

UPDATE: works like a charm. Thanks

[stefano.menci]

@richard.a.cairns

The date column in the table is using Anvil’s date type

You could make that column a date and time instead of a date column.

[p.colbert]

There is already an implicit, reliable, unique column: row_id. See https://anvil.works/docs/data-tables/data-tables-in-code#getting-one-row-from-a-table

If you want to record a more conventional row number, you can, but you’ll need to manage the count and the incrementing with your own code.

[richard.a.cairns]

Thanks, @p.colbert.

After spending some time playing with Anvil, I’m quite impressed. I’ve got a barebones website working much, MUCH faster than I would have if I was building it from scratch.