These new querystring arguments can be used to request expanded foreign keys
in both JSON and CSV formats.
?_labels=on turns on expansions for ALL foreign key columns
?_label=COLUMN1&_label=COLUMN2 can be used to pick specific columns to expand
e.g. `Street_Tree_List.json?_label=qSpecies&_label=PlantType&_shape=array`
[
{
"rowid": 1,
"TreeID": 141565,
"qLegalStatus": 1,
"qSpecies": {
"value": 1,
"label": "Myoporum laetum :: Myoporum"
},
"qAddress": "501X Baker St",
"SiteOrder": 1,
"qSiteInfo": 1,
"PlantType": {
"value": 1,
"label": "Tree"
},
"qCaretaker": 1,
"qCareAssistant": null,
"PlantDate": "07/21/1988 12:00:00 AM",
"DBH": 21,
"PlotSize": "Width 0ft",
"PermitNotes": "Permit Number 25401",
"XCoord": 6000609,
"YCoord": 2110829,
"Latitude": 37.7759676911831,
"Longitude": -122.441396661871,
"Location": "(37.7759676911831, -122.441396661871)"
},
{
"rowid": 2,
"TreeID": 232565,
"qLegalStatus": 2,
"qSpecies": {
"value": 2,
"label": "Metrosideros excelsa :: New Zealand Xmas Tree"
},
"qAddress": "940 Elizabeth St",
"SiteOrder": 1,
"qSiteInfo": 2,
"PlantType": {
"value": 1,
"label": "Tree"
},
"qCaretaker": 1,
"qCareAssistant": null,
"PlantDate": "03/20/2017 12:00:00 AM",
"DBH": 3,
"PlotSize": "Width 4ft",
"PermitNotes": "Permit Number 779625",
"XCoord": 6000396.48544,
"YCoord": 2101998.8644,
"Latitude": 37.7517102172731,
"Longitude": -122.441498017841,
"Location": "(37.7517102172731, -122.441498017841)"
}
]
The labels option also works for the HTML and CSV views.
HTML defaults to `?_labels=on`, so if you pass `?_labels=off` you can disable
foreign key expansion entirely - or you can use `?_label=COLUMN` to request
just specific columns.
If you expand labels on CSV you get additional columns in the output:
`/Street_Tree_List.csv?_label=qLegalStatus`
rowid,TreeID,qLegalStatus,qLegalStatus_label...
1,141565,1,Permitted Site...
2,232565,2,Undocumented...
I also refactored the existing foreign key expansion code.
Closes#233. Refs #266.
The test used to expect CSV to come back like this:
hello
world
""
With the final blank value encoded in quotes.
Judging by Travis failures, this behaviour changed between Python 3.6.3 and
3.6.5:
https://travis-ci.org/simonw/datasette/jobs/392586661
Tables and custom SQL query results can now be exported as CSV.
The easiest way to do this is to use the .csv extension, e.g.
/test_tables/facet_cities.csv
By default this is served as Content-Type: text/plain so you can see it in
your browser. If you want to download the file (using text/csv and with an
appropriate Content-Disposition: attachment header) you can do so like this:
/test_tables/facet_cities.csv?_dl=1
We link to the CSV and downloadable CSV URLs from the table and query pages.
The links use ?_size=max and so by default will return 1,000 rows.
Also fixes#303 - table names ending in .json or .csv are now detected and
URLs are generated that look like this instead:
/test_tables/table%2Fwith%2Fslashes.csv?_format=csv
The ?_format= option is available for everything else too, but we link to the
.csv / .json versions in most cases because they are aesthetically pleasing.
