Calculate Background Tasks and Write Synchronized to Table

Gerhard · January 17, 2023, 1:30pm

Hi
What I’m trying to do:
Calculating 4 functions within Background Tasks:

copy rows (TIME) from DB Data to DB copy (TIME)
copy rows (DBT) from DB Data, add the value +10 and write to DB copy (DBT)
copy rows (DBT + TIME) from DB Data, sum them and write to DB copy (SUM)
generate with itertools.count the row number to DB copy (ROW)

All the calculation e.g. variables need to be synchronized.

What I’ve tried and what’s not working:
The BT calls are fine but I need to get the results in the Table copy to be in line from startrow to stoprow and not shifted.

I need the BT on the server calls (not for this small example but for calculations with steps up to 20000 rows). As well I do need different (def) functions e.g. up to 30.

What is the best way to keep the variables synchronized e.g. one synchronized call for different functions?

Clone link:
[share a copy of your app]
https://anvil.works/build#clone:U42HNEYFYKQDCASM=LVDVRR5L7YXHL6UKNSLJDSRH

Thank you,
Gerhard

jshaffstall · January 17, 2023, 1:45pm

It sounds like you want all of your background tasks to operate on the same rows, filling in different columns. Right now each of your background tasks is using add_row, which will add a new row, not edit an existing row.

If it were me, all this would happen from a single background task so that the row that got added would have all the information it needed. If sometimes some of the calculations needed to happen and other times not, I’d come up with a mechanism to allow that in a single background task.

If you really want multiple background tasks, you just have to accept the fact that the rows have to be created before other tasks can edit them. So one task has to happen first to copy the rows, then the other tasks will edit those rows.

Maybe you can start the other background tasks at the end of the first?

Gerhard · January 17, 2023, 3:00pm

Hi Jay,

thank you and yes, all the background tasks have to operate on the same rows, filling in different columns

I updated the app, now all the calculations are in one single background task however, the values are filled perfectly in the rows after each other but not in the same rows. Is there a method to set the startrow for the add_row
app_tables.copy.add_row(DBT=DBT)[startrow]
https://anvil.works/build#clone:U42HNEYFYKQDCASM=LVDVRR5L7YXHL6UKNSLJDSRH

Thank you,
Gerhard

jshaffstall · January 17, 2023, 7:17pm

That line of code doesn’t make sense. If you’re adding a row, you get back one row not a list. What are you actually trying to accomplish with that line?

You don’t say which background task you’re actually using now, but I’ll assume it’s func_1. You have this code:

    all_rows = app_tables.copy.search()
    for row in all_rows:
      row['SUM'] = None

    for row in app_tables.data.search()[startrow:stoprow]:
      SUM = (row['TIME'] + row['DBT'])
      print(SUM)
      app_tables.copy.add_row(SUM = SUM)

    for row in itertools.islice(itertools.count(), startrow, stoprow, step):
      print(row)
      app_tables.copy.add_row(ROW = row)

    for row in app_tables.data.search()[startrow:stoprow]:
      DBT = (row['DBT']+10)
      app_tables.copy.add_row(DBT=DBT)

I don’t know why you’re doing identical searches, or why you’re doing an add_row instead of modifying the existing row you already have. You modify the existing row when you set the Sum column to None, so I’m not sure exactly what you’re trying to accomplish with the add_row calls.

I have to make a lot of assumptions, because the code I’m seeing doesn’t match up with what I thought you wanted to do. Either I’m wrong on what you wanted to do, or you’re confused about the coding. If I’m wrong, you’ll have to explain in detail what you’re trying to accomplish.

On the chance that you’re confused about the coding, I’ll show you the code to do what I thought you’d wanted to do (this is limited to just a couple of the calculations, you can extend it from there if this is what you’d intended) :

    all_rows = app_tables.copy.search()
    for row in all_rows:
      row['SUM'] = row['TIME'] + row['DBT']
      row['DBT'] = row['DBT']+10

You only use add_row if you actually want to add a brand new row. If you want to edit an existing row, don’t use add_row.

You have some other code that also doesn’t make sense. You do slicing on the results, e.g. app_tables.data.search()[startrow:stoprow] but you have not specified an order by clause. The order in which results are returned are only predictable if you tell the search how to order the results. Slicing on results only make sense when you also have them ordered.

I have no idea what you’re trying to do with the itertools loop. If you can explain that without code, we might be able to help you accomplish your goal.

Gerhard · January 17, 2023, 8:42pm

Hi Jay,

sorry for making it not clear.
All functions / background task have to operate and be solved on the same row number for each column, step by step.

One Loop: all the functions and variables are solved to each other in the same row step (0, 1, 2 … 10)
e.g. STEP = 1 , starting from Startrow until Stoprow.
In this task I can not solve the functions with an order after each other.
Server Code Variables: are coming from fix table values like DBT, other variables are calculated with thermodynamic functions , others are coming from client input fields, so differnt sources combined in one anvil table.
To make it more clear. Each hour I have to calculate the state point of a process (0 to 8760 h/a).
I do have more than 100 functions with variables like Q = m*(h1-h4) that I have to pass to the next function in a closed cycle.

The functions here are at the moment short previews/ examples, it may be that they makes no sense at all at the moment.
For testing-we may take any function that reads variables by processing it in one.

Add_Rows: ok, for me it was not clear that add rows does not adds the rows at the beginng of the table, Since I delete all rows before calling the background task, I was assuming that rows from each column start at the top of the table e.g. 0.

Pls. find attached a table picture, how the values shoud be calculated and filled in the table in one loop.
Does it makes sense to go over dict or Panda Table an then back to an anvil table?

Was I able to clear out what I am trying to accomplish?

BR
Gerhard

jshaffstall · January 17, 2023, 9:07pm

It’s not where add_rows adds the rows, it’s the order in which search returns the rows. That is undefined unless you tell search how to order the rows.

You have a ROW column that looks like it is the order in which you want to process the rows. Use that in your search, e.g. app_tables.copy.search(tables.order_by('ROW')) That way you know exactly the order in which search will return the results. Then it makes sense if you want to slice the results.

Use a for loop of the form I showed, where you update row columns in the loop. If you’re using an order by on the search, you can safely slice the search results to loop over a subset of rows.

If you for some reason need to loop over the same search results more than once, don’t do the search again, just use the results, e.g.:

results = app_tables.copy.search(tables.order_by('ROW'))[start:end]

for row in results:
    # do something with the row

for row in results:
    # do something else with the row

If the slices are different between the two loops, still use just the one search:

results = app_tables.copy.search(tables.order_by('ROW'))

for row in results[start:end]:
    # do something with the row

for row in results[start2:end2]:
    # do something else with the row

Gerhard · January 18, 2023, 9:41pm

Hi Jay,

appreciate your clear explanation.
I used the tables.order_by mainly to get e.g. max value from an column.
Good idea to order the columns
results = app_tables.copy.search(tables.order_by(‘ROW’))[start:end] is clear.

Not clear the structure or code, how to to get the rows in order and back in the same table?
This would be a good topic for a tutorial.

Thanks,
Gerhard

jshaffstall · January 18, 2023, 10:11pm

The rows are already in the table, no need to get them back into it. The code I showed updates the rows in place.

Gerhard · January 19, 2023, 5:16pm

Hi @jshaffstall

thank you.
I do have problems to get the rows updated in place.
I reduced the app to a minimum to order the values: the table DBT values (90, 80 …) shloud be in row line with TIME (1, 2 …)
Pls. find the app attached.
What I am doing wrong?

https://anvil.works/build#clone:SKBP3R6VC57JZ2HX=W2H5YGGIHNXOGSBY4PHH6ZYU

Thank you for your effort,
Gerhard

jshaffstall · January 19, 2023, 5:48pm

None of the code in that app adds rows to the data tables. So I can’t say why some of those values are off.

Gerhard · January 19, 2023, 6:08pm

The values are stored in the table.
I do not import them.
Over the Button (GUI) I like only to order and updates the rows in place.

TIME DBT
1 90
2 80

jshaffstall · January 19, 2023, 6:34pm

You aren’t going to get values out of a table unless they are stored in the table that way. If you want the Time 1 and DBT 90 values to be together when you pull them out of the table, they should be together in the table.

If the way the data is stored in the table is correct, then you have to specify the rules for how you are going to reorganize it. Right now Time 1 has a DBT None, and DBT 90 is in Time 5. What rule says that DBT 90 ends up in Time 1?

Gerhard · January 19, 2023, 10:24pm

Hi @jshaffstall,

now I got the dirction, thank you!

For testing the for loop delete all rows with none, this solution delete as well the TIME order row?
I do not no way, at the beginning when I tested it it only delete the DBT None Rows and the TIME order keept to start with 1, 2 … so I was thinking to have the solution. Later on not anymore?

@anvil.server.callable
def delete_column():
  all_rows = app_tables.database.search(tables.order_by('TIME'))
  for row in all_rows:
    if row['DBT'] == None:
      row.delete()

This soltution is not working, should reference at the time order?

@anvil.server.callable
def delete_column():
  all_rows = app_tables.database.search(tables.order_by('TIME'))
  for row in all_rows:
    if row['DBT'] == None:
        row['DBT'] == all_rows

Any idea?
BR,
Gerhard

jshaffstall · January 19, 2023, 11:03pm

Your first way should work to delete rows where DBT is None. When I copy that same code into your sample app, it does work.

The second way doesn’t make sense, because DBT is a number field, but you’re trying to put a search results into it.

Gerhard · January 21, 2023, 3:12pm

Hi @jshaffstall,

yes it works but the actual table

looks like this

I need to have it like this

Anvil table has no shift () solution right?

So, Column TIME starts alway with 1 and is fix. Only the Column DBT should be shifted up to the top.

jshaffstall · January 21, 2023, 3:34pm

If you need to change the TIME column, you have to write code for that. The previous code only removed rows where DBT was None.

I don’t know where the data is coming from, but it would be easier to add the data to the table in the right format rather than try to fix up bad data after the fact.

Yes, you could remove DBT = None rows, but it’s easier to not add them in the first place. Then you wouldn’t use up TIME entries on DBT None values, and your TIME entries would end up the way you want them to.

Gerhard · January 21, 2023, 4:23pm

Blockquote @jshaffstall
The previous code only removed rows where DBT was None.

Thank you for your time, appreciate it.
Yes, but if I delete None rows in Column DBT, the rows in Column TIME are as well delete.
I like only to shift/adjust/order the Column DBT, Column TIME should not be shifted at any time.

I can not work indipendend on columns in an Anvil Table, right?
So, working totally with pandas + shift() and writing at the end of the calculation to Table could be the better way.

p.colbert · January 22, 2023, 2:06pm

Rather than having to massage the table into shape after collecting the data, would it be too much trouble to collect all the data for a given table row before creating the row? That way, the row could be created (added) in its final form in a single step.

Gerhard · January 22, 2023, 8:15pm

Hi @p.colbert

yes, it would be possible to collect all the data for a given table row before creating the row.
My background task should operate on the same rows, filling in different columns (solving at each time step = row all the variables and writing the values to the table starting with TIME 1, variables are coming from other ANVIL look up tables/excel or server code calulations).

With the Anvil Table I did not managed to have them all at the top e.g. TIME 1.
The background task is calculating/solving me all the task at the same time, but not writing me in the table at the same time row.

Like
Column TIME 1, 2, 3 (written down over code in the common Background task)
Column DBT 1, 2, 3 (First eq. in the common Background task , values from Look-Up Table)
Column DPT 4, 5, 6 (Second eq. in the common Background task, DBT values from Look-Up Table + value 10)

For this reason @jshaffstall was suggesting to order them all at the top (I did not managed).

I was working bevore a lot with the eng. equation solver (Fortran based), there I dit not had any issues, because eq. were solved at the same time and written to the table at the same time.

How would you solve it or code that the values are written at the start-row, any smal example would be apprecciated.

BR,
Gerhard

p.colbert · January 23, 2023, 1:55pm

How would you … code that the values are written at the start-row

When you have all of a row’s values together at the same place and time in your code, call add_row with all of that row’s values.

app_tables.copy.add_row(all your column values here)

as in

app_tables.copy.add_row(column1_name = column1_value, column2_name = column2_value, …)

add_row is not limited to adding a single column value. All of the row’s values can be passed, separated by commas, in a single add_row call.