Oracle REST Data Services for Python Developers—Part 2

Part 1 | Part 2 | of this of a two-part series that explains, by example, how Python developers can take advantage of Oracle REST Data Services when developing REST-based applications that interact with Oracle Database.

This article describes how to consume REST endpoints created with Oracle REST Data Services, which expose database resources to HTTP operations. It shows how Python users can make use of these REST endpoints defined along with create, read, update, and delete (CRUD) operations, accessing and manipulating the underlying database data. Here are the main topics covered in this article:

• Getting database data using Python's Requests HTTP library
• Parsing the JSON object returned by a REST call into a Python dictionary
• Drilling down into a REST GET Response object
• Making REST calls to manipulate database data
• Determining if a REST call is successful
• Examining the headers of a response
• Setting the headers of a request

This article assumes that you have already set up a working Oracle REST Data Services/Python environment and have created a RESTful service on an Oracle Database table. Detailed steps for these tasks were provided in the first article of this series.

Working with a REST GET Response in Python

In the first article of this series, you saw how easy it is to make REST GET requests from Python to an Oracle REST Data Services RESTful service. As you will see in the "Using the Python Requests Library's REST Calls to Manipulate Database Data" section later in this article, the other REST operations are not much more complicated to implement.

Before moving on to data manipulation operations, however, let's explore some more examples of getting data through an Oracle REST Data Services RESTful service. First, it would be interesting to look at a GET Response object that you get in your Python script after a Requests GET call is complete.

Note: Before proceeding to the examples in this article, make sure that Oracle REST Data Services is running in your system. Oracle REST Data Services is not restarted by default upon a reboot of the system. For details on how to run Oracle REST Data Services in standalone mode, check the "Running in Standalone Mode" section in the REST Data Services Installation, Configuration, and Development Guide. Or you can start it from Oracle SQL Developer by using the Run Wizard that can be launched by selecting the Tools > REST Data Services > Run menu item.

So, make a GET call to the RESTful service that was defined in the "Building a RESTful Service with Oracle REST Data Services" section of the first article in this series.

Note: To follow this article's examples, you'll need a running Python prompt. For details, refer to the "Installing Python on Oracle Linux" section of the first article in this series.

      	
>>> import requests
>>> resp = requests.get('http://yourdbhost:9090/ords/hr/departments/')
      	
  

Now that you have a Response object, you can look into it. For example, you might wonder what methods the object provides. The following code lists all the methods of the above Response object, excluding "dunder" (double underscore) methods:

      	
>>>[meth for meth in dir(resp) if callable(getattr(resp, meth)) and not meth.startswith("__")]
['close', 'iter_content', 'iter_lines', 'json', 'raise_for_status']
>>> 
      	
  

To start with, it would be interesting to look at the payload structure:

      	
>>> for item in resp.json():
...  print(item)
... 
items
hasMore
limit
offset
count
links
      	
  

You can drill down further into the payload to see its structure in detail:

      	
>>> type(resp.json()["items"])
<class 'list'>
>>> 

>>> type(resp.json()["items"][0])
<class 'dict'>
>>> 

>>> resp.json()["items"][0].keys()
dict_keys(['department_id', 'department_name', 'manager_id', 'location_id', 'links'])
>>>
      	
  

As you can see, to determine the structure of the hr.departments table that underlies the RESTful service used here, you do not even need to look into the database. This can be easily viewed on the Python side.

The following loop outputs the department IDs, iterating through all the department entities in the JSON document found in the response payload:

      	
>>> for item in resp.json()["items"]:
...   print(item["department_id"])
... 
10
20
30
...
      	
  

You can derive a certain entity from the document, specifying its ID as the filter criteria:

      	
>>> for item in resp.json()["items"]:
...   if item["department_id"] == 10:
...     print(item)
... 
{'department_id': 10, 'department_name': 'Administration', 
'manager_id': 200, 'location_id': 1700, 'links': [{'rel': 'self', 
'href': 'http://yourdbhost:9090/ords/hr/departments/10'}]}
>>> 
      	
  

Looking at the value of the href field in the above object (highlighted in bold), it's not too hard to guess that it represents the URL through which you can have REST access to this particular department entity individually. In other words, Oracle REST Data Services allows you to make GET requests for a single entity in the underlying record set. In the next section, you will see an example of how you can retrieve an entity representing a row in the underlying table by specifying its identifying key in the URL sent to Oracle REST Data Services.

Apart from the ability to retrieve the entire record set from the underlying data source (as in the previous example), Oracle REST Data Services lets you make GET requests for selected entities. For example, you can retrieve a certain entity by appending its identifying key to the request URL, as illustrated in the following example:

      	
>>> resp10 = requests.get('http://yourdbhost:9090/ords/hr/departments/10')
>>> type(resp10.json())
<class 'dict'>
>>> 

>>> resp10.json().keys()
dict_keys(['department_id', 'department_name', 'manager_id', 'location_id', 'links'])
>>>
      	
  

So, you can access a particular field:

      	
>>> print(resp10.json()["department_name"])
Administration
>>>
      	
  

And the entire object should look as follows:

      	
>>> print(resp10.json())
{'department_id': 10, 'department_name': 'Administration', 
'manager_id': 200, 'location_id': 1700, 'links': [{'rel': 'self', 
'href': 'http://yourdbhost:9090/ords/hr/departments/10'}, {'rel': 
'edit', 'href': 'http://yourdbhost:9090/ords/hr/departments/10'}, 
{'rel': 'describedby', 'href': 
'http://yourdbhost:9090/ords/hr/metadata-catalog/departments/item'}, 
{'rel': 'collection', 'href': 'http://yourdbhost:9090/ords/hr/departments/'}]}
      	
  

In the above example, you made a GET request, which Oracle REST Data Services translated into the following SQL query behind the scenes:

      	
SELECT * FROM hr.departments WHERE department_id = 10;

So, the GET request returned an object that represents a single row of the underlying database table.

However, you can specify other ways of making Oracle REST Data Services issue underlying SQL queries with the WHERE clause. The following example illustrates how you can use the filtering in queries feature of Oracle REST Data Services, which enables a per-request dynamic filter definition included in the query URL to retrieve only those rows from the underlying table that satisfy the filter condition.

In this particular example, the query URL includes a filter that restricts the department_id column of the departments underlying table to the values less than or equal ($lte) to 30:

      	
>>> resp10_30 = requests.get('http://yourdbhost:9090/ords/hr/departments/?q={"department_id":{"$lte":30}}')


>>> for item in resp10_30.json()["items"]:
...    print(item["department_id"],item["department_name"])
... 
10 Administration
20 Marketing
30 Purchasing
>>>
      	
  

As you can see, the URL in the above GET request includes the parameter q, which is set to a JSON object (FilterObject) that specifies the filtering criteria and (optionally) sorting to be applied to the underlying data source. In this particular example, the specified filtering condition is translated into the following WHERE clause (highlighted in bold) in the underlying SQL query:

      	
SELECT * FROM hr.departments WHERE department_id =< 30;      	
  

Of course, a FilterObject passed in the q parameter can be more complicated than that shown in the above example and it can involve more than one filter condition being applied. Filter conditions are defined in an array preceded by a logical combining clause (logical operator), such as $and, $or, or $nor.

In the following example, the FilterObject includes two filter conditions within a logical combining clause that uses operator $and. The request is supposed to return only those departments whose ID is less than or equal to 70 and whose name contains substring in.

      	
>>> resp = requests.get('http://yourdbhost:9090/ords/hr/departments/?q=
{"$and":[{"department_id":{"$lte":70}},{"department_name":{"$instr":"in"}}]}')
      	
  

When processing the above request, Oracle REST Data Services will issue the following query against the departments underlying table:

      	
SELECT * FROM hr.departments WHERE (department_id =< 70) AND (INSTR(department_name, 'in', 1, 1)>0);
      	
  

Returning to Python, first, however, let's check if the request was successful:

      	
>>>resp
<Response [200]>
      	
  

Then, you might want to make sure that the retrieved selection contains only those items that meet the specified filter criteria:

      	
>>> for item in resp.json()["items"]:
...    print(item["department_id"],item["department_name"])
... 
10 Administration
20 Marketing
30 Purchasing
50 Shipping
>>> 
      	
  

As you might guess, filtering in queries is especially useful when it comes to the traversal of large collections of data. In such cases, it is much more efficient to retrieve only the necessary items of a collection than to request the entire collection and then handle filtering on the client side.

Using the Python Requests Library's REST Calls to Manipulate Database Data

The examples so far in this article have shown how you can access REST-enabled database data through Oracle REST Data Services using Python's Requests HTTP library. The generalized steps involved are common to all those examples and are listed below:

1. In Python, you make a GET call with the requests.get method, specifying the service's URL with a filter query parameter if it is needed.

2. Oracle REST Data Services processes the request, transforming it into a corresponding SQL query issued against the underlying data source. Then, it sends a JSON-formatted response back to the caller.

3. In Python, the response is available in a Response object returned by the requests.get method. Using the Response.json method, you perform JSON decoding of the response payload, converting it into a dict.

However, when it comes to data manipulation operations that can be implemented with a POST or PUT request, the steps to be performed will be different. Below are the steps that describe a POST request (these same steps are applicable for PUT):

1. In Python, you create a dictionary that contains the payload to be sent with a POST request.

2. In Python, you make a POST request with the requests.post method, sending the dictionary created in the previous step as a data parameter.

3. Oracle REST Data Services processes the request, transforming it into a corresponding INSERT or UPDATE statement issued against the underlying data source. Then, it sends a response back to the caller, indicating the success or failure of the operation.

In the following example, you make a POST call, which should result in inserting a new row into the hr.departments underlying table:

      	
>>> resp = requests.post('http://yourdbhost:9090/ords/hr/departments/',
 data = {'department_id':280, 'department_name': 'Advertisement', 'manager_id':201, 'location_id':1700})

>>> resp
<Response [201]>
>>>
      	
  

You can learn more about the server's response details by viewing its headers:

      	
>>> resp.headers
{'Content-Type': 'application/json', 'Content-Location': 
'http://yourdbhost:9090/ords/hr/departments/280', 'ETag': 
'"h70cJQxW/AC/7qON+xvPmq5cfDAZOTz2teR8qrbPs7n+y0SDay8gomofE1KPzKg/jqW3t
vFmauXpIK9s3uA8xA=="', 'Location': 
'http://yourdbhost:9090/ords/hr/departments/280', 'Transfer-Encoding': 'chunked'}
      	
  

Sometimes, however, you may need to look at the headers that are sent with a request:

      	
>>> resp.request.headers
{'User-Agent': 'python-requests/2.18.4', 'Accept-Encoding': 'gzip, 
deflate', 'Accept': '*/*', 'Connection': 'keep-alive', 'Content-Length': '79', 
'Content-Type': 'application/x-www-form-urlencoded'}
>>>
      	
  

As you will see in the next example, you can change the default values of some of the request headers and/or add additional headers.

Note: In this example, you view the request headers after the response has been committed. Often, however, you might want to view a request's headers before sending the request. To address this problem, the Requests library allows you to create prepared requests.

Turning back to the Requests methods that implement HTTP operations (GET, POST, and so on), it is interesting to note that, apart from the methods specific to a particular HTTP operation (requests.get, requests.post, and so on), the Requests library provides a single method for all HTTP operations: requests.request. This method takes the operation to be performed as the first parameter. Then, after specifying the URL of the target service, you can define a number of optional parameters, including headers, auth, timeout, and allow_redirects, to name a few.

The following example illustrates the requests.request method in action. In this particular example, you make a PUT request to update the department entity created with the requests.post method in the preceding example.

Because the Content-Type entity header of the request in this example will be set to application/json, you first need to convert the dictionary that contains the payload to be sent into a JSON string:

      	
>>> import json
>>> obj = json.dumps({'department_id':280, 'department_name': 'Advertisement', 'manager_id':200, 'location_id':1700})
      	
  

Then, you can make the request.

      	
>>> resp = requests.request('PUT','http://yourdbhost:9090/ords/hr/departments/280'
, data = obj, headers = {'content-type': 'application/json'})
>>> resp
<Response [200]>
>>>
      	
  

As mentioned, the Content-Type entity header defines the format of a request payload. If this header is set to application/json, a request payload represents a JSON string:

      	
>>> resp.request.body
'{"department_id": 280, "department_name": "Advertisement", "manager_id": 200, "location_id": 1700}'
>>> 
      	
  

The only CRUD operation you haven't seen yet in this article is DELETE. So, let's delete the department entity you created and then updated in the above examples:

      	
>>> resp = requests.request('DELETE','http://yourdbhost:9090/ords/hr/departments/280');
>>> resp
<Response [200]>
>>>
      	
  

See Also

• Oracle REST Data Services OTN website
• Python Developer Center on OTN
• "Introduction to Python for PL/SQL Developers" tutorial series

About the Author

Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, databases, business intelligence (BI), machine learning, and virtualization. He is the author of a series of books on Oracle technology, including Oracle Business Intelligence: An introduction to Business Analysis and Reporting (Packt Publishing) and PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax (Packt Publishing).