mirror of
https://github.com/simonw/datasette.git
synced 2025-12-10 16:51:24 +01:00
40287b1ba0These 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.
61 lines
2 KiB
Python
61 lines
2 KiB
Python
from .fixtures import app_client # noqa
|
|
|
|
EXPECTED_TABLE_CSV = '''id,content
|
|
1,hello
|
|
2,world
|
|
3,
|
|
'''.replace('\n', '\r\n')
|
|
|
|
EXPECTED_CUSTOM_CSV = '''content
|
|
hello
|
|
world
|
|
'''.replace('\n', '\r\n')
|
|
|
|
EXPECTED_TABLE_WITH_LABELS_CSV = '''
|
|
pk,planet_int,state,city_id,city_id_label,neighborhood
|
|
1,1,CA,1,San Francisco,Mission
|
|
2,1,CA,1,San Francisco,Dogpatch
|
|
3,1,CA,1,San Francisco,SOMA
|
|
4,1,CA,1,San Francisco,Tenderloin
|
|
5,1,CA,1,San Francisco,Bernal Heights
|
|
6,1,CA,1,San Francisco,Hayes Valley
|
|
7,1,CA,2,Los Angeles,Hollywood
|
|
8,1,CA,2,Los Angeles,Downtown
|
|
9,1,CA,2,Los Angeles,Los Feliz
|
|
10,1,CA,2,Los Angeles,Koreatown
|
|
11,1,MI,3,Detroit,Downtown
|
|
12,1,MI,3,Detroit,Greektown
|
|
13,1,MI,3,Detroit,Corktown
|
|
14,1,MI,3,Detroit,Mexicantown
|
|
15,2,MC,4,Memnonia,Arcadia Planitia
|
|
'''.lstrip().replace('\n', '\r\n')
|
|
|
|
def test_table_csv(app_client):
|
|
response = app_client.get('/test_tables/simple_primary_key.csv')
|
|
assert response.status == 200
|
|
assert 'text/plain; charset=utf-8' == response.headers['Content-Type']
|
|
assert EXPECTED_TABLE_CSV == response.text
|
|
|
|
|
|
def test_table_csv_with_labels(app_client):
|
|
response = app_client.get('/test_tables/facetable.csv?_labels=1')
|
|
assert response.status == 200
|
|
assert 'text/plain; charset=utf-8' == response.headers['Content-Type']
|
|
assert EXPECTED_TABLE_WITH_LABELS_CSV == response.text
|
|
|
|
|
|
def test_custom_sql_csv(app_client):
|
|
response = app_client.get(
|
|
'/test_tables.csv?sql=select+content+from+simple_primary_key+limit+2'
|
|
)
|
|
assert response.status == 200
|
|
assert 'text/plain; charset=utf-8' == response.headers['Content-Type']
|
|
assert EXPECTED_CUSTOM_CSV == response.text
|
|
|
|
|
|
def test_table_csv_download(app_client):
|
|
response = app_client.get('/test_tables/simple_primary_key.csv?_dl=1')
|
|
assert response.status == 200
|
|
assert 'text/csv; charset=utf-8' == response.headers['Content-Type']
|
|
expected_disposition = 'attachment; filename="simple_primary_key.csv"'
|
|
assert expected_disposition == response.headers['Content-Disposition']
|