Anvil List/Array reading

Hi. I have a Google Colab model and I am connecting it to an Anvil App.

I had an array ‘times’ in Google Colab and now I have a Database Table ‘times’ in Anvil. I do a search of this table and try to pass the data to a list ‘timeslist’ or to an array ‘timesarray’ in order to pass to the model:

timeslist = list(app_tables.times.search())

or

timesarray = []
for row in app_tables.times.search():
  timesarray.append(row)

In Google Colab, I used the data in my model like: sum(times[i][j] for i in range(n-1) for j in range(m-1))

Now with Anvil, I have an error when trying to read the data of a specific element of the list/array using times[0][0] for example. It shows a tuple of another element and the same element for any times[i][j].

How do I pass the information of the datatable to a list/array and then how do I read a specific element instead of using times[i][j]?

I’m having trouble understanding what you are trying to do. Here’s my guess: you have data stored in the Anvil Data Tables that you want to send to the Google Colab notebook, which will then run some code to process it and return some kind of output to Anvil for display client-side. Is that right, or can you explain what you have in mind in similar terms?

To be specific about my confusion, I don’t understand why you say you are trying to “pass the data to a list ‘timeslist’ or to an array ‘timesarray’…” What do you mean, “or”? My guess is that you are not sure so you tried both ways?

I also don’t understand the relationship between these ‘timeslist’ or ‘timesarray’ objects and the ‘times’ object you reference in the second half of the post.

What you say I am trying to do is correct…

I don´t know if there is a way to read the Anvil Database from Google Colab. The way I try to solve it is to pass the data of the database in a function via an array or a list. Am I wrong?

Regards.

Your list is a list of dictionaries (app table rows behave like dictionaries). You seem to be trying to index into it like it’s a two-d array. That’s not how you index a list of dictionaries. The first index is into the list, that can be an integer. The second index is into a dictionary, that must be a column name, e.g. timeslist[0]['column']

Without knowing what your data table looks like or what you’re actually trying to sum, it’s impossible to give any more concrete advice.

2 Likes

As far as how to get the database information in Google Colab, you can do any of those ways. To read the Data Tables directly in Colab, you either need to change a setting to allow it to be read client-side (which is less secure) or else ensure that Colab has server-code privileges via uplink.

Thanks. I really need a tow-d array from a Datatable. I can’t find a way to create it, since the app_tables.table.search() is a list of dictionaries.

I tried something like this without succes:

timesarray = []
timessearch = app_tables.times.search()
timeslist = list(timessearch.values())   
timesarray.append(timeslist)

Thanks…

I can connect from a Google Drive Database like this:

from google.colab import drive

drive.mount(‘/content/drive’)

import sqlite3

conn = sqlite3.connect(‘/content/drive/MyDrive/IBMUniversity/Python/oee.db’)

print(“Opened database successfully”);

cursor = conn.execute(“SELECT * FROM components;”)

for row in cursor:

print(row)

conn.commit()

How should I do this wiht Anvil?

What does your data table structure look like?

What do you want from the data table in your two-d list?

Why do you want a two-d list in the first place?

There’s a lot of information missing that means we can’t really offer concrete help.

1 Like

Hi… those are two tables:

image

I need arrays because I need to use the indexes like in:

for (j, i) in product(range(n), range(1, m)):
model += x[j][machines[j][i]] - x[j][machines[j][i-1]] >=
times[j][machines[j][i-1]]

How does the information in the data table map into the two-d list? If you insist on having a two-d list, you are going to have to create it by taking the information in the data table and putting it into a two-d list.

The whole process might be simpler if you were to put your numerical values into a list in a simple object column, that way you’d already have a list of numbers to work with when creating the two-d list.

Thank you for your help.

The arrays and the tables are the following:

image

image
image

I need to have the data in those arrays.

Thanks

You don’t seem to have any column in your times data table that indicates order. You’ll need something to indicate order if you want to guarantee that you can retrieve the rows in the correct order.

Your machines table already has a Product column, which seems to indicate order. Here’s a brute force algorithm for getting the data into the format you want (there are more elegant ways of doing this, but this is probably the most understandable).

machines = app_tables.machines.search(tables.order_by('Product'))
machines_list = []

for machine in machines:
    inner_list = []

    inner_list.append(machine['M1'])
    inner_list.append(machine['M2'])
    # etc for the other columns

    machine_list.append(inner_list)

This is starting to sound like a job for a SimpleObject column, which can store a list directly. It will still have its own column name (so the row is still a dict), but it should be easier to distill it into the desired list format.

2 Likes

Thank you… that was what I wanted.

Now I have a new error:

anvil.server.SerializationError: Cannot serialize return value from function. Cannot serialize <class ‘mip.entities.Var’> object at msg[‘response’]

when running the model and using this variable:

y = [[[model.add_var(var_type=BINARY, name=‘y({},{},{})’.format(j+1, k+1, i+1)) for i in range(m)] for k in range(n)] for j in range(n)]

return(y[j][k][i])

The model runs well in Google but shows this problem in Anvil. I thought Anvil called the Google Colab model and the model run in Google, Anvil just asking for a function and retrieving its result but seems that Anvil processes the model… Do I need to use an object in Anvil?

Thank you for the help

The error tells you what it’s having trouble with. Anytime it says “Cannot serialize”, that means you’re trying to send something non-standard across a network boundary (in this case returning from your Google Colab to the Anvil server). Anvil can serialize standard Python data structures, but doesn’t know how to serialize custom classes.

You’re trying to return something that’s of the type mip.entities.Var. I don’t know what’s in that class, but to return it across the network boundary you’ll need to convert it to a list, a dict, something standard that has the information you need from whatever that class is.

For future, when you have an unrelated question to your original topic, you should start a new topic. That way people searching the forum for answers to a problem don’t see unrelated solutions.

1 Like