Changes

Jump to navigation Jump to search
Add a whole example
Line 237: Line 237:  
</pre>
 
</pre>
   −
=== CSV export via SQL (alpha) ===
+
=== CSV export via SQL (beta) ===
 
We are testing a new kind of tool to provide the data: every day an SQL database is fed by the regular daily CSV export, and published online thanks to Datasette tool.
 
We are testing a new kind of tool to provide the data: every day an SQL database is fed by the regular daily CSV export, and published online thanks to Datasette tool.
   Line 255: Line 255:  
* It is possible to restrict the number of fields displayed and exported.
 
* It is possible to restrict the number of fields displayed and exported.
 
* It is possible to order results by any field.
 
* It is possible to order results by any field.
 +
 +
==== Example ====
 +
'''1 -- Build your query (or ask someone to build it for you)'''
 +
 +
Eg. all German products that have been scanned at least one time.
 +
-- Products from Germany that have been scanned at least one time
 +
select code, product_name from [all]
 +
where countries_en like "%germany%" and unique_scans_n is not null
 +
order by unique_scans_n desc
 +
-- the limit here displays 20 results; remove it or comment it with "--" when you build your CSV export
 +
limit 20
 +
<nowiki>https://mirabelle.openfoodfacts.org/products?sql=--+Products+from+Germany+that+have+been+scanned+at+least+one+time%0D%0Aselect+code%2C+product_name+from+%5Ball%5D%0D%0Awhere+countries_en+like+%22%25germany%25%22+and+unique_scans_n+is+not+null%0D%0Aorder+by+unique_scans_n+desc%0D%0A--+the+limit+here+displays+20+results%3B+remove+it+or+comment+it+with+%22--%22+when+you+build+your+CSV+export%0D%0Alimit+20</nowiki>
 +
 +
'''2 -- Copy "CSV" link on the result page.'''
 +
 +
'''3 -- If necessary, edit the link to remove the "limit+20" limit to get all the products.'''
 +
 +
Eg. (don't click this link if you don't want to get 90,000+ products) <nowiki>https://mirabelle.openfoodfacts.org/products.csv?sql=--+Products+from+Germany+that+have+been+scanned+at+least+one+time%0D%0Aselect+code%2C+product_name+from+%5Ball%5D%0D%0Awhere+countries_en+like+%22%25germany%25%22+and+unique_scans_n+is+not+null%0D%0Aorder+by+unique_scans_n+desc%0D%0A--+the+limit+here+displays+20+results%3B+remove+it+or+comment+it+with+%22--%22+when+you+build+your+CSV+export%0D%0A&_size=max</nowiki>
 +
 +
Now you can use this link to download the CSV with your favourite tool (wget, curl, web browser, etc.).

Navigation menu