EPA List of COVID-19 Disinfectants
brew install datasette sqlite-utilspip3 install datasette-publish-vercelordatasette install datasette-publish-vercel
cd Projects/Advocacy/list-N/list-Nsqlite-utils insert list-N.db listN list-N.csv --csvorcurl "https://cfpub.epa.gov/wizards/disinfectants/includes/queries.cfc?method=getDisData&Keyword=&RegNum=&ActiveIng=All&ContactTime=&UseSite=&SurfType="(was:curl 'https://cfpub.epa.gov/giwiz/disinfectants/includes/queries.cfc?method=getDisData&Keyword=&RegNum=&ActiveIng=All&ContactTime=&UseSite=&SurfType=' | python transform.py | jq . | sqlite-utils insert disinfectants.db listN - --pk ID)
sqlite-utils enable-fts disinfectants.db listN 'Surface_type' 'Active_ingredient' 'Risk_level' 'Date_on_List_N' 'Company' 'Contact_time' 'Use_site' 'Product_name' 'Formulation_type' 'Follow_directions_for_this_virus' 'Why_on_List_N' 'EPA_reg_num' --create-triggers --tokenize=porter
sqlite-utils transform disinfectants.db listN \--column-order EPA_reg_num \--column-order Risk_level \--column-order Active_ingredient \--column-order Product_name \--column-order Company \--column-order Use_site \--column-order Surface_type \--column-order Contact_time \--column-order Formulation_type \--column-order Follow_directions_for_this_virus \--column-order Date_on_List_N \--column-order Why_on_List_N \--column-order ID
datasette disinfectants.db -m metadata.json --setting default_page_size 2000 --setting max_returned_rows 3000 --setting default_facet_size 35 --static static:static/ --template-dir templates/ --plugins-dir plugins -p 8001 -o
datasette disinfectants.db -m metadata.json \--setting default_page_size 3000 \--setting max_returned_rows 3000 \--setting default_facet_size 35 -o \--static static:static/ \--template-dir templates/ \--plugins-dir plugins
Visit: https://vercel.com/download to get CLI tool.
Run: vercel login to login to Vercel, then you can do this:
datasette publish vercel disinfectants.db --project "list-n" --title "Disinfectants Used for Addressing COVID" --source "List N Tool COVID-19 Disinfectants" --source_url "https://cfpub.epa.gov/giwiz/disinfectants/index.cfm" --install datasette-vega --static static:static/ --metadata metadata.json --setting default_page_size 3000 --setting max_returned_rows 3000 --setting default_facet_size 35 --template-dir templates --plugins-dir plugins
datasette publish vercel disinfectants.db \--project "list-n" \--title "Disinfectants Used for Addressing COVID" \--source "List N Tool COVID-19 Disinfectants" \--source_url "https://cfpub.epa.gov/giwiz/disinfectants/index.cfm" \--install datasette-vega \--setting default_page_size 3000 \--setting max_returned_rows 3000 \--setting default_facet_size 35 \--static static:static/ \--template-dir templates/--plugins-dir plugins/--metadata metadata.json \
sqlite-utils tables disinfectants.db --counts --columnssqlite-utils analyze-tables disinfectants.db listNsqlite-utils disable-fts disinfectants.db listNopen /Applications/DB\ Browser\ for\ SQLite.app disinfectants.db
Datasette Session Notes - https://docs.google.com/document/d/1f61st8AXtpXvjeHB3UlmUhSCG1Ddiwih9nr-nO8LTEY/edit
Margie’s original notes - https://docs.google.com/document/d/1RHv_Twe7gzUMcfAeHZ-RlVQU-ZwshpEalZ4NmF9-ISk/edit
Simon’s original notes - https://docs.google.com/document/d/1Ck4Gopt8ssumGUjH1TeqASvHpFAPBJpTpDE_bvf4bCI/edit
sqlite> .schema listNCREATE TABLE [listN] ([EMER_PATH] TEXT,[REGI_NUM] TEXT,[INST_VIRUS] TEXT,[COMPANY] TEXT,[USE_SITE] TEXT,[CONT_TIME] FLOAT,[ACTI_ING] TEXT,[USE_SURF] TEXT,[COMPANY_URL] TEXT,[DATE_ON_LIST_N] TEXT,[FORM_TYPE] TEXT,[ID] INTEGER PRIMARY KEY,[PROD_NAME] TEXT);
Now:
CREATE TABLE [listN] ([Surface_type] TEXT,[Active_ingredient] TEXT,[Risk_level] TEXT,[Date_on_List_N] TEXT,[Company] TEXT,[Contact_time] FLOAT,[Use_site] TEXT,[Product_name] TEXT,[Active_ingredient] TEXT,[Formulation_type] TEXT,[Follow_directions_for_this_virus] TEXT,[Why_on_List_N] TEXT,[ID] INTEGER PRIMARY KEY,[EPA_reg_num] TEXT);