Accessing CKAN's Data API using python - Canadian Watershed Information Network

The CKAN DataStore offers an API for reading, searching and filtering data without the need to download the entire file first. The DataStore is an ad hoc database which means that it is a collection of tables with unknown relationships. This allows you to search in one DataStore resource (a table in the database) as well as queries across DataStore resources.

Creating an API endpoint to search the DataStore

The datastore_search action

The datastore_search action allows you to search for data in a resource. By default 100 rows are returned - see the limit parameter for more information here.

Using datastore_search in an API URL endpoint

Example

URL= 'https://lwbin-dev.ad.umanitoba.ca/data/api/3/action/datastore_search?resource_id=c5c16064-e2b3-4618-9b27-0dbf5c1388c2', where:

https://lwbin-dev.ad.umanitoba.ca/data is the root url
api/3/action/datastore_search calls the API action 'datastore_search'
?resource_id=c5c16064-e2b3-4618-9b27-0dbf5c1388c2 is the query, and includes the resource id for that particular resource.

Finding the resource ID

Click on any resource on CanWIN's CKAN site. For example, the 'Greenedge Nutrient data 2016' data resource can be found here. Scroll down the page to 'Additional Information' and look for the field label 'Resource id'. This will be the ID for this specific resource.

Making requests to the API endpoint via python

Here are two ways to search and retrieve data from the DataStore using the API endpoint discussed above, and a python request module.

1. Requests

Installation

pip install requests

Python example using the requests module

import pandas as pd
import requests


#API endpoint
url = 'https://lwbin-dev.ad.umanitoba.ca/data/api/3/action/datastore_search?resource_id=c5c16064-e2b3-4618-9b27-0dbf5c1388c2'

#To make a ‘GET’ request, we’ll use the requests.get() function, which requires one argument — the URL we want to make the request to.
response = requests.get(url)

#The get() function returns a response object. We can use the response.status_code attribute to receive the status code for our request.
#Code - 200: Everything went okay, and the result has been returned (if any).
print(response.status_code)

#JSON (JavaScript Object Notation) is the language of APIs. JSON is a way to encode data structures that ensures that they are easily readable by machines. 
#JSON is the primary format in which data is passed back and forth to APIs, and most API servers will send their responses in JSON format.

#Use the response.json() method to see the data we received back from the API as a dictionary
response_data=response.json()
#print(response_data)

#To get the data records. 'data' is now a list, containing each line of data as a dictonary.
data=response_data['result']['records']

#To see a list of all the column headers
col_headers=data[0].keys()

#If we want to filter the data, for example, we only want Cast=3.
filtered_data =  list(filter(lambda row: row['Cast']=='3', data))

#Output data as a dataframe for easy manipulation
df1= pd.DataFrame(data)
df2= pd.DataFrame(filtered_data)

# Save to csv file
df1.to_csv('output.csv', index=False)


#To get more metadata
# dataDump = json.dumps(response_data, sort_keys=True, indent=4)
# print(dataDump)

2. urllib.request

Python example using the urllib.request module

Adding more functionality directly in the datasetore_search action call

To add more parameters to the query, use an & before each parameter pair (key=value) after the resource id. For example, to get the first two rows of data only add &limit=2 after the resource id. See all parameters here.

Filtering the data

Format: &filters={"key":"value"}

import pandas as pd
import requests

# filters (dictionary)

# Filtering only the data records where Cast=3 and sample_date=2016-06-09T00:00:00

url = 'https://lwbin-dev.ad.umanitoba.ca/data/api/3/action/datastore_search?resource_id=c5c16064-e2b3-4618-9b27-0dbf5c1388c2&filters={"Cast":"3","sample_date":"2016-06-09T00:00:00" }'

response = requests.get(url)
response_data=response.json()
data=response_data['result']['records']

df1= pd.DataFrame(data)
df1

Limiting the data returned

Format: &limit=3

import pandas as pd
import requests

# limit (integer)

# limit=2 , only the first two rows
url = 'https://lwbin-dev.ad.umanitoba.ca/data/api/3/action/datastore_search?resource_id=c5c16064-e2b3-4618-9b27-0dbf5c1388c2&limit=2'

response = requests.get(url)
response_data=response.json()
data=response_data['result']['records']

df1= pd.DataFrame(data)
df1