Exclude "ID" Column from CSV Data Table Export

Anvil Q&A
1

‘’’
@anvil.server.callable
def DownloadCSVLoadout():
print("!!! Exporting Loadout CSV")

self.link_1.url = app_tables.loadout.search().to_csv().url
‘’’

I have a data table that Im building, and I have it exporting to a downloadable CSV. Everything is working great, except I dont want to export the default ID column from the database.

Any tips on how I can exclude only that default column when I build the CSV?

app_tables.loadout.search().to_csv().url

Would this be in search() ?

2

image
image820×489 12.1 KB

this is the ID column I would like exclude from the CSV export

3 
from io import StringIO
import csv
  
@anvil.server.callable
def table_to_csv(table_name):
  
  csvfile = StringIO(newline='')
  fieldnames = [ x['name'] for x in getattr(app_tables, table_name).list_columns() ]
  
  writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
  
  writer.writeheader()
  for row in getattr(app_tables, table_name).search():
    writer.writerow(dict(row))
    
  csvfile.seek(0)

  media_obj = anvil.BlobMedia('text/plain', csvfile.read().encode() , name=f"{table_name}.csv" )
  
  return media_obj

:exclamation: Edit: For security reasons, you should make sure the user should be able to have access to the table_name before you allow just any random user to just dump the entire contents of literally any named data table by changing the string passed to this function.

3 Likes
4

Put the media object as the url entry for an Anvil Link() component to make a downloadable link.
From the docs:

If a Link’s url property is set to a Media object, it will open or download that media in a new tab.

m = anvil.BlobMedia('text/plain', b'Hello, world!', name='hello.txt')
c = Link(text='Open text document', url=m)
1 Like
5

Since pandas is a common library for dealing with tabular data, I think it’s worth including that as an option:

# Get search iterator as a dataframe    
csv = app_tables.MY_TABLE.search(**search_args).to_csv()
df = pd.read_csv(io.BytesIO(csv.get_bytes()), dtype=str)

# you can do any pandas data manipulation here:
df = df[list_of_my_columns]

# dump to a bytes stream
f_buf = io.StringIO()
df.to_csv(f_buf, index=False)
         
# return to beginning of file stream and dump to an Anvil-friendly bytes stream
f_buf.seek(0)
b_buf = f_buf.read().encode()
    
# Package the Bytes object into an Anvil Media object, so we can return to client
csv_file = anvil.BlobMedia('text/plain', b_buf, name=FILE_NAME)
return csv_file
1 Like
6

Also, just for completeness with the original question and @danbolinson’s code, you might do a
df.drop('ID', axis=1) because the op wanted to remove just the ID column from the results of the app_tables … .to_csv() method, but dump everything else in the table.

3 Likes
7

Thanks for the help. Almost got it finished. I was able to generate the new CSV with the code below. However, it looks like it changed the layout of the columns and rearranged them somehow.

“”"
@anvil.server.callable
def table_to_csv():
table_name = “loadout”
csvfile = StringIO(newline=’’)
fieldnames = [ x[‘name’] for x in getattr(app_tables, table_name).list_columns() ]

writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

writer.writeheader()
for row in getattr(app_tables, table_name).search():
writer.writerow(dict(row))

csvfile.seek(0)

media_obj = anvil.BlobMedia(‘text/plain’, csvfile.read().encode() , name=f"{table_name}.csv" )

return media_obj"""

Client side:

“”"
class Generate_Routing(Generate_RoutingTemplate):
def init(self, **properties):
# Set Form properties and Data Bindings.
self.init_components(**properties)
self.link_2.url = anvil.server.call(‘table_to_csv’)
“”"

Generates correctly without the ID column. Just need to rearrange the columns in the order it was before

This is what I have now:

image
image512×514 8.83 KB

Need columns to be:

[Shippoint], [LOADINGMETHOD], [PO], [CASES], [WEIGHT], [QTY]

8

You can probably just list the column names in fieldnames to be
fieldnames = ["Column_name1", "Column_name2", "Column_name3", "etc", ]

…and it will probably keep the order you put it in, the same thing with @danbolinson’s code where he wrote list_of_my_columns

9

nice! Thats exactly what I was about to try.

10

BOOM!

image

That worked.

What if I wanted to put this in .xls format before downloading. Would you do that with PANDAS?

11

Getting closer on converting the csv to excel:

with anvil.media.TempFile(media_obj) as file_name:
print(file_name)
with open(file_name, ‘r’) as FILE:
for line in FILE:
print(line)

I can get the tempfile name of the media object and iterate through it. So should be able to open that directly with xlswriter or pandas and create the excel file I think unless there is a much easier way.

12 
from io import StringIO, BytesIO

import pandas as pd

the end of my code could be changed to:

  csvfile.seek(0)
  
  df = pd.read_csv(csvfile)
  
  excel_file = BytesIO(b'')
  
  df.to_excel(excel_file, index=False)
  excel_file.seek(0)
  
  media_obj = anvil.BlobMedia('text/plain', excel_file.read() , name=f"{table_name}.xlsx" )

  return media_obj

Seems to work, or follow @danbolinson s code and do it all in pandas with .to_excel()

1 Like