We need spreadsheet blobs

[timlinux]

Make a spredsheet using pl/python and the xls writer library

Store it as a blob in the flood event table

In the web UI have a download button to grab the sheet (@carina1000 to help)

[pierrealixt]

CREATE TABLE dev_reports (
    output bytea
);

-- DROP FUNCTION save_excel_as_blob();

CREATE OR REPLACE FUNCTION save_excel_as_blob ()
  RETURNS bytea
AS $$
  import sys
  sys.path.insert(0, '/usr/local/lib/python3.7/dist-packages')
  import xlsxwriter
  import io
  ouput = io.BytesIO()
  workbook = xlsxwriter.Workbook('hello.xlsx')
  worksheet = workbook.add_worksheet()
  worksheet.write('A1', 'Hello world')
  workbook.close()
  plan = plpy.prepare("INSERT INTO dev_reports (output) VALUES ($1)", ["bytea"])
  plpy.execute(plan, [ouput])

  return ouput

$$ LANGUAGE plpythonu;

select * from save_excel_as_blob();
select * from dev_reports;

[timlinux]

Frikking awesome @pierrealixt !

[timlinux]

And by the way if you wanted to be super sneaky you could make a function that fetches the needed packages and puts them on the hard drive, then add that path to your writer function. That way we could have arbitrary functions. Something like (in pseudocode):

CREATE OR REPLACE FUNCTION satisfy_dependency ()
  RETURNS bool
AS $$
   # logic to fetch and unpack python package here or return true if we already have it
   return True # or false if fetch fails
$$ LANGUAGE plpythonu;

select * from satisfy_dependency('xlswriter');
select * from save_excel_as_blob();
select * from dev_reports;

[timlinux]

Further clarification based on discussion with @pierrealixt and @timlinux

[pierrealixt]

https://github.com/pierrealixt/SmartExcel

[anitanh]

hi @pierrealixt please regenerate the spreadsheet using the new data. Thank you.

[pierrealixt]

yes @ann26

CREATE OR REPLACE FUNCTION fbf_generate_excel_all_flood_events ()
  RETURNS varchar
 AS $$   
   res = plpy.execute("SELECT id from flood_event")

   for flood_event in res:
     plan = plpy.prepare("SELECT * from fbf_generate_excel_report_for_flood(($1))", ["integer"])
     plpy.execute(plan, [flood_event['id']])
   return "OK"
$$ LANGUAGE plpython3u;

select * from fbf_generate_excel_all_flood_events();

[NyakudyaA]

I converted the function to a trigger function that listens for inserts.