984
edits
Changes
Jump to navigation
Jump to search
Line 237:
Line 237: − + Line 255:
Line 255: + + + + + + + + + + + + + + + + + + + +
Add a whole example
</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.
* 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.).