Save XLSX to Data Table

Anvil Q&A
1

What I’m trying to do:
I have a function that creates an excel file using openpyxl which appears to be working correctly. Now I’m trying to load the file into a data table.

What I’ve tried and what’s not working:
I am pretty new to python and very new to Anvil, but I have attempted to make sense of the documentation. I’ve tried many of the methods mentioned in the Files, Media and Binary Data section and have tried everything that I can find from searches in the forums. The function appears to work properly until I attempt to load it into the data table.

I am on a free trial right now and did see in the docs that writing to the file system is only available on the Personal Plan or higher. Maybe that is the issue? However, I also can’t figure out any of the other methods and am open to any working method. Likely I will upgrade but sure would like to see it work before I subscribe. Appreciate any help that the community can provide!

Code Sample:
I have redacted much of my function that builds the excel file but am happy to upload the entire thing if you think that would help. The problem portions of the code (I think) are as follows:

file_name = "Test.xlsx"
writer = pd.ExcelWriter(file_name, engine='openpyxl')
df.to_excel(writer, index=False, sheet_name='Worksheet')

writer.close()

app_tables.worksheets.add_row(created=datetime.now(), name=name, worksheet=file_name)
2

Welcome to the Forum!

And thank you for all the effort you’ve put into research. It pays off, big time.

It looks like you’re putting the name of the file into table worksheets, column worksheet. I suspect that you want more than just the name to be stored in the database, i.e., you want the file’s contents (the sequence of bytes that define what’s in the file) as well. Is that correct?

3

I think you are trying to store the entirely already created excel file in a database row?

I do this all the time, you are very close, you just need to turn your file into a media object to store in the data table like:

import anvil.media

excel_media_object = anvil.media.from_file(filename, name=filename)

app_tables.worksheets.add_row(
                                created=datetime.now(),
                                name=name, 
                                worksheet=excel_media_object
                                   )

Are you doing this on your own system using anvil-uplink? Before I moved to a paid plan I used the uplink for things like using openpyxl since it was not available using the free plan in server modules.

If you are using uplink, this should just work.

4

Just to add a note on this use of column worksheet:

The column will need to be of type MediaObject. If you haven’t defined it that way, then you’ll need to change it, i.e., delete or rename the existing worksheet column, and add a new one of the correct type.

5

@ianb and @p.colbert thank you so much for the helping me with this! This solution worked perfectly!

@p.colbert yes I am storing the file contents in the database.
@ianb I’m not using the uplink. It is not something that I have experimented with yet.

6

This function writes the excel file to the “file system” before it adds it to the database. What does that actually mean? Where is the file system? Is it just a temporary write while the function is running? If not do I need to delete the file since I won’t be using it? Are there any performance worries with this method or quotas that I should be aware of? I see the Personal plan has a limit of 10GB in the database, but I don’t see a limit on the “file system”.

7

Here is all I could find about it from the Docs:

Read and write files as normal

You can also use Python’s open to read and write files as normal. Your filesystem is your own; other users do not have access to it.

Files in your filesystem are temporary, and may be removed without warning. We advise you only to access files in the /tmp directory.

So if it is working, great, I did not know you can access openpyxl now in the free plan. I am unsure if you are able to change the directory where it will write in to /tmp but if this is working for you the way it is I wouldn’t worry about it.

Every time you run a function on a server module, it is ephemeral. You can only guarantee that the file system and variables in memory will exist as long as the server module is still executing code, and that’s it.

That’s the basic source of the reasoning for the 30 second timeout for server modules and background tasks on the free plan.

8

It might last for a few function calls, it might not. Anvil can clean it up (delete it) on its own schedule.

This means that a fixed filename might be a bad idea. What happens when two different users, from two different browsers, call this function at essentially the same time? They’ll step on each others’ data file!

It’s probably safest if you

  1. generate a unique, temporary file name per call.
  2. delete the file when you know you’re done with it.
1 Like
9

Sounds like a great reason to create a context manager:

from contextlib import contextmanager
from uuid import uuid4
from os import remove as remove_file

@contextmanager
def random_ephemeral_filename():
  file_name = str(uuid4())
  yield file_name
  try:
    remove_file(file_name)
  except FileNotFoundError as err:
    print(err)
    pass

Usage:


with random_ephemeral_filename as rep_fn:
  writer = pd.ExcelWriter(rep_fn, engine='openpyxl') # Just an example
  ...  # All the rest of your Excel / file use Code in this indented code block

#  When it exits the indented block the random file name of 'rep_fn' 
#  Will be deleted

Edit: Note from before, you can totally name a file in a media object differently than the system file:

excel_media_object = anvil.media.from_file( rep_fn , name=filename)
1 Like
10

Also see NamedTemporaryFile in

11

Thanks for the ideas!