Local database management clarification

Anvil Q&A
5

Here’s some code I’ve used to export and import multiple data tables, each with more than 100 columns, which included every type except media objects and links. It properly handles None values (which can be troublesome, with default CSV dumps generated by Anvil), empty values, text, number, boolean, date, datetime, and simple objects (you should be able to add media objects by encoding to base64, for example). Notice that the import routine wipes the datatable with table.delete_all_rows(), so be careful using it:

@anvil.server.callable
def export_adult_table_to_json():
    table = app_tables.adult_eval_form_data
    rows = table.search()
    data = []
    for row in rows:
        row_data = dict(row)
        for key, value in row_data.items():
            if isinstance(value, datetime.date):
                row_data[key] = value.strftime('%Y-%m-%d')  # Convert date to date format
            elif isinstance(value, datetime.datetime):
                row_data[key] = value.strftime('%Y-%m-%d %H:%M:%S')  # Convert datetime to string
        data.append(row_data)
    json_data = json.dumps(data)
    json_data_bytes = json_data.encode('utf-8')
    media_object = anvil.BlobMedia("application/json", json_data_bytes, name="adult.json")
    return media_object

@anvil.server.callable
def import_adult_table_from_json(file):
    json_data = file.get_bytes().decode('utf-8')
    data = json.loads(json_data)
    table = app_tables.adult_eval_form_data
    table.delete_all_rows() 
    for row_data in data:
        for key, value in row_data.items():
            if value is not None and key in ['Patient_DOB', 'Date']:
                try:
                    parsed_value = datetime.datetime.strptime(value, '%Y-%m-%d').date()
                except ValueError:
                    parsed_value = None
                row_data[key] = parsed_value
            if value is not None and key in ['Timestamp']:
                try:
                    parsed_value = datetime.datetime.strptime(value, '%Y-%m-%d %H:%M:%S')
                except ValueError:
                    parsed_value = None 
                row_data[key] = parsed_value
        table.add_row(**row_data)
    return "Data imported successfully"

You can adjust the app_tables.<tablename> to import/export as many tables as needed.

On the front end, there’s a button and a fileloader widget:

  def button_export_adults_click(self, **event_args):
    media_object = anvil.server.call('export_adult_table_to_json')
    anvil.media.download(media_object)

  def file_loader_import_adults_change(self, file, **event_args):
    if confirm('ONLY RUN IF ON ANVIL-APP-SERVER'):   # just a note to ensure I don't copy data sensitive data back to the development server
      if file is not None:
        response = anvil.server.call('import_adult_table_from_json', file)
        alert(response)

To deal with linked columns, the Sqlite solution which ianbuywise suggested, parses the anvil.yaml config file to reconstruct schema.

2 Likes