Storing inputs in db

Hi,
I’m developing this calculation tool with twenty or so inputs and a result page to display results.
I’m passing input page as an object to result page so that user can go back and edit without having to fill in the whole form again. This works great.

Now I’m trying to build a database for the users to store projects. Of course I could go the route of passing all the inputs to a table and fill the form in some way from database, but then again if I can store the object in a variable, can’t I just store this variable in the database as well and add component from database.

I tried storing it as a simple object but could not get it to work.
Any ideas?

Kind Regards
Per

A simple object column is the right way to go, but without seeing what you’ve already tried and hearing how it isn’t working, it’s hard to say what might be going wrong.

I will note that simple object columns only hold basic Python data structures, e.g. lists, dictionaries, ints, strings, etc. The blog post about simple object columns covers this: Simple Object Storage

If you’re trying to put something else into simple object storage, it won’t work.

2 Likes

Great thanks,
Now I know I’m on the right track, I can probably solve it by reformatting what I did then. By the way, different question, I would like to keep this on a separate database with my external server, what do you recommend I try. A few thousand users keeping track of their projects, do all the SQL variants have this column type. I’m mostly developing calculations for backend and I’m using simple sqlite3 read only for the calculation data on the server that works great for this project. I guess it’s not recommended to use lite for the users in this case.
Kind regards
Per

Off the top of my head PostgreSQL and MySQL (MariaDB) have had JSON columns since 2012 and 2015 respectively, the simple object column in the Anvil DataTables is essentially just using a PostgreSQL JSON column.

If you are only using the information for storage, and not for searching through the JSON column to find specific keys (the DB’s in question are optimized to do just that), then nothing is really stopping you from just storing the JSON data as a string in a TEXT type column, (or VARCHAR or even a BLOB if you want to store in in bytes, or compress the data if you are planning on having hundreds of thousands of users but need no ability to search through the data)

If SQLite and uplink isn’t going to handle that many simultaneous users, then the next easiest solution I can think of is to install docker on your computer and run a containerized version of one of the databases mentioned above, having a python script interact with it using uplink.

In the long term you might even look into moving the uplink and script into the container after you are done with development.

1 Like

Hey, many thanks for the explanation. No they don’t need to search the variable column just the name of the projects connected to their username so that should work. Just started with docker containers since business probably would need to set up servers at different locations for some of their reps down the road. This also gave me a road map to put the db for customer and project in a separate container, Articles and such in a separate container, then separate anvil apps for reps for new set of containers. Kudos to you for that. More to learn and test out, love that.
Kind Regards
Per

Wow, that sounds like a level of complexity required for managing millions of users!

I have hundreds of apps and tables, and zero containers, just the Anvil account on a dedicated server, allmanaged by Anvil.

If you really need that level of complexity, good luck and kudos for setting it up.

2 Likes

I don’t like to make things over complicated, front end is simplistic, backend is where it happens and has been my focus up till now, Anvil is such a great tool, but have left to test putting the app at my dev server as well. This app will not get too much traffic, it’s a sales support tool for engineers in a special field, but business is worldwide and some markets like China have strict regulations on hosting outside the country. So I need to prepare for multiple deployments and expanded functionality. They did not have to think about location before, since this app will replace an old 32 bit windows program they use today. This will be my first “public” deployment. I certainly need all the luck I can get, so thank you.

Ok, I was a little bit too optimistic. The save of variable above to simple object seems to work fine with testing but I’ve been struggling with getting this button 1 on the repeating panel to pass the Simple Object. Think I have been all over the place trying to get this to work.
Well now it opens the Input form but the passed panel2 does not make any difference.
I have been trying to save and return it as string, blob. But cant get that to work.

class ItemTemplate4(ItemTemplate4Template):
  def __init__(self, **properties):
    # Set Form properties and Data Bindings.
    self.init_components(**properties)

    # Any code you write here will run before the form opens.

  def button_1_click(self, **event_args):   
    panel2 = self.rich_text_1.data
    open_form('Inputs',**panel2)

Doing something wrong here

To get any help, you’ll need to be more explicit about what you want to happen, and what is and isn’t happening. Obviously it isn’t doing what you want, but from your description we have no idea what it is doing. Is it doing nothing? Are you seeing an error? Is it displaying the right form with the wrong content?

You also haven’t shown nearly enough code for context. How does the rich text’s data property get populated? What’s in it (e.g. do a print of it to see what’s there). What code is in the Inputs form?

I have succeded to pass input page column as a variable to resultpage. So when I click recalculate button on resultpage it returns me to input page with previous settings. That works fine. Now I want to store the same variable in db, together with project name unit name username etc. Preferably in a repeating panel with a button to open the Input page with settings stored in db.
Something like that

Here is code I use to pass variable

  def save_unit_click(self, **event_args):
    if self.inputpanel != None:
      projectname = self.text_box_projectname.text or self.text_box_projectname.placeholder
      unitname = self.text_box_unitname.text or self.text_box_unitname.placeholder
      panel = self.inputpanel
      anvil.server.call('save_unit',projectname, unitname,panel)
      open_form('Inputs')

Print out shows a couple of pages of json.
The input page opens as a new page with placeholder info no input, no messages.

This works on the result page, its the same variable.

  def recalculate_click(self, **event_args):
    self.grid_panel_3.clear()
    self.grid_panel_3.add_component(self.inputpanel)

You have two lines of code where you open the Inputs form:

Those two lines show very different ways of constructing the input form. If your printout shows a couple pages of JSON, then I seriously doubt you want to use the second form.

When you use **panel2 it expands the dictionary into keyword arguments to the Inputs form. You never showed any code for Inputs, so I can’t say what you’re expecting to pass into the __init__ function, but when you use code like open_form('Inputs',**panel2), anything after the form name is going to be passed into the form’s __init__ function.

Let’s say your panel2 dictionary looked like this:

{
    'foo': 'some value',
    'bar': 'some other value'
}

Then your __init__ would need to look like this:

def __init__(self, foo, bar, **properties):

I’m assuming that you’re trying to pass the dictionary into the form so you can prefill it (again, I haven’t seen any code for Inputs, which would have answered a lot of these questions). If that’s the case, you do not want to expand the dictionary. e.g. just use open_form('Inputs', panel2)

Then your __init__ would be something like:

def __init__(self, data, **properties):

Then you could use the data dictionary to prefill the form. But, you also open the Inputs form without passing in that parameter, so you’d need to allow it to be done either way:

def __init__(self, data=None, **properties):

And then code the prefilling based on data not being None.

Thank you for understanding my terrible description!
This was my take on your response.
However it’s complaining about panel not being a component. Maybe because it is now a saved object?
What did I do wrong?

class Inputs(InputsTemplate):
  def __init__(self, panel=None, **properties):
    self.inputpanel = panel
    
    # Set Form properties and Data Bindings.
    self.init_components(**properties)
    if self.inputpanel !=None:
      self.openform()
      
  def openform(self):
    panel = self.inputpanel
    self.column_panel_inputs.clear()
    self.column_panel_inputs.add_component(panel)

panel is not a component, it’s a dictionary. You have to use it to populate components that are either already on the form, or that you create dynamically.

I’ll use a simple example, because I still have no idea what your Inputs form looks like. If I had a form with a couple of labels, and I had my dictionary from above (with foo and bar keys), I could do something like this:

 def __init__(self, data=None, **properties):
     self.init_components(**properties)

     if data:
         self.label_1.text = data['foo']
         self.label_2.text = data['bar']

If you’re instead wanting to repopulate a rich text component, you could probably put the dictionary directly into it’s data property.

Thank you very much for making it clear.
If I understand this right then, if I store a component in a variable it will remain a component, otherwise it will become a dict.
This was very helpful.
Kind regards
Per

Nothing just becomes a dict. The value you were passing in was a dict because that’s what the rich text component’s data property gives you. From way back when you showed this code:

panel2 = self.rich_text_1.data
1 Like

Ok, got it. So is there an effective way to store and retrieve a component variable object in a db? as I do with the same object in memory.
That was my aim.

You pull data out of the components and into a dictionary, and store that dictionary in a simple object column. e.g.:

    data = {}

    data['foo'] = self.label_1.text
    data['bar'] = self.label_2.text

Anvil’s column type name “SimpleObject” comes from Python’s treatment of all things as objects, including data values. However, because the underlying storage is in JSON format, the only Python objects that can be stored there are None, True, False, strings, numbers, and lists/dicts thereof. More specific objects, such as Forms, don’t fit.

For more detail, please see Column types.

Made an effort out of curiosity with pickle.dump and pickle.load to save and retrieve it. Anvils media blob would not accept it. Then tried my own dev server with sqlite3. It saved and retrieved the blob nicely, but Anvil did not seem to support this type as a variable coming from the outside. So I guess this route is a dead end. However I learned so much from this thread, thanks.