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?
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
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.
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
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.
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() ]
“”"
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
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.