Part III: Python and the Oracle Autonomous Database - Three Ways to Connect
Connecting via ORDS: secured with OAuth2
Note: This is an abbreviated explanation, I'll be posting an expanded write-up on this example post haste!
For context, be sure to review Part II of this series. I'll try to keep Part III brief, but long story short, I’ll take those query parameters from Part II and place them into a Resource Handler (read on friend).
TIME-OUT: Auto-REST enabling a database object (the BUSCONFIND table in this case) is simple in Database Actions. Its a simple left-click > REST-enable. You saw that in the previous example. You are provided an endpoint and you can use the query parameters (i.e. the JSON {key: value} pairs) to access whatever you need from that object.
However, creating a custom ORDS REST endpoint is a little different. First you create a Resource Module, next a (or many) Resource Template/s, and then a (or many) Resource Handler/s. In that Resource Handler, you'll find the related business logic code for that particular HTTP operation (the menu includes: GET, POST, PUT, and DELETE).
The Resource Module
The process of creating a custom ORDS API might be difficult to visualize, so I’ll include the steps I took along with a sample query (in that Resource Handler) to help illustrate.
Chances are you may be the administrator of your Oracle Free Tier tenancy, so you have full control over this. Other times, you might be provided the REST endpoint. In that case, you may not ever have to worry about these steps. Either way, you can see how we’re simulating (as well as both abstracting and keeping the business logic in the database) the query with this final example (test3.py).
Security
The OAuth 2.0 authorization framework enables a third-party application to obtain limited access to an HTTP service, either on behalf of a resource owner by orchestrating an approval interaction between the resource owner and the HTTP service, or by allowing the third-party application to obtain access on its own behalf.
RFC 6749: The OAuth 2.0 Authorization Framework
I’ll keep this section brief, but I’m protecting this resource through the aid of an ORDS OAuth2.0 client. I’ve created one here:
And, as you’ll see shortly, I’ll rely on some Python libraries for requesting an Authorization Token to use with the related Client ID and Client Secret. If you want to nerd out on the OAuth2.0 framework, I challenge you to read this .
test3.py example
NOTE: Remember, I'm keeping this section intentionally brief. It deserves a slightly deeper dive, and class is almost over (so I'm running out of time).
The code for this example:
领英推荐
# Custom ORDS Module in an Oracle Autonomous Database.
import requests
from requests_oauthlib import OAuth2Session
from oauthlib.oauth2 import BackendApplicationClient
import pprint
import json
# Importing the base URI from this python file.
from testurls import test3_url
# A separate python file I created and later import here. It contains my credentials,
# so as not to show them in this script here.
from oauth2creds import token_url, client_id, client_secret
token_url = token_url
client_id = client_id
client_secret = client_secret
client = BackendApplicationClient(client_id=client_id)
oauth = OAuth2Session(client=client)
token = oauth.fetch_token(token_url, client_id=client_id, client_secret=client_secret)
bearer_token = token['access_token']
# Location can be anything from the table. Now, only the single variable needs to be passed. Business logic has been abstracted somewhat; as it now resides within
# ORDS. This could make your application more portable (to other languages and frameworks, since there are fewer idiosyncracies and dependencies):
location = "ZAF"
# print(location)
# ------------------------------------------------------------------------------ #
# In Database Actions, we:
# 1. Create an API Module
# 2. Then create a Resource Template
# 3. Finally, a GET Resource Handler that consists of the code from test1.py:
# select * from BUSCONFIND where location= :id
# order by value ASC
# ------------------------------------------------------------------------------ #
url = (test3_url + location)
# print(url)
responsefromadb = requests.get(url, headers={'Authorization': 'Bearer ' + bearer_token}).json()
# This step isn't necessary; it simply prints out the JSON response object in a more readable format.
pprint.pprint(responsefromadb)
Here you'll see I import the test3_url from the testurls.py file; as seen in the previous example. A few lines below that, you'll see where I import the OAuth Credentials from my oauth2creds.py file. And here are the files, side-by-side (for context):
As you can see in the testurls.py file (middle pane), I’m relying on the test3_url for this example. And the OAuth2.0 information you see (right side pane) comes directly from the OAuth Client I created in Database Actions:
If I put that all together, I can execute the code in test3.py and “pretty print” the response in my Interactive Window (via VS Code). But first I need to adjust the Resource Handler’s URI (the one I copied and pasted from the “REST Workshop”). It retains the “:id” bind parameter. But the way I have this Python code set up, I need to remove it. It ends up going from this:
test3_url = 'https://test3_url = 'https://abc123def456ghi789-darmok.adb.us-ashburn-1.oraclecloudapps.com/ords/devuser/myapi/locations/:id'
To this:
test3_url = 'https://abc123def456ghi789-darmok.adb.us-ashburn-1.oraclecloudapps.com/ords/devuser/myapi/locations/'
Otherwise, if I don’t remove that bind parameter, the target URL will end up looking like this (after the "ZAF" location is passed along):
'https://abc123def456ghi789-darmok.adb.us-ashburn-1.oraclecloudapps.com/ords/devuser/myapi/locations/:idZAF'
With that out of the way, I can run this code and review the output.
From top-to-bottom, you’ll see I first execute the code in the Interactive Window. From there I can review a summary of the response to my GET request. That pretty print library allows us to see the JSON object in a more readable format (one that has indentation and nesting); which you can see in the second image. The third image is a more detailed view of the first half of this response. And I include the final image to highlight the helpful URLs that are included in the response body.
Since I know my limit = 25, and the 'hasMore': True (seen in the output in that third image) exists, I know there are more items. You can adjust the limit and offset in subsequent requests, but I’ll save that for another day.
Wrap-up
This was an expansion of the previous example found in Part II . But instead of relying on the auto-REST enabling, you are in full control of the Resource Module. And while you don’t need to use OAuth2.0 it’s good practice to use it for database authentication. You can see how the response comes through a little differently, compared to the previous example, but still very similar.
In this example, I did all the work, but that might not be the case for you; much of it might be handled for you. The main thing I like about this example is that we rely on stable and popular Python libraries: requests, requests_oauthlib, and oautlib.
The fact that this is delivered as a JSON object is helpful as well (for the same reasons mentioned in Part II). And finally, I enjoy the fact that you only need to pass a single parameter from your (assumed) presentation layer to your application layer; an example might be a selection from an HTML form or drop-down menu item.
The end
This marks the end of my fun little exercise. As I mentioned before, I will expand on this third example. There are so many steps, and I think it would be helpful for people to see a more detailed walk-through.
And be on the lookout (BOLO) for a video playlist (Part I is live now ). There’s no way around this, but a video needs to accompany this series!
And finally, you can find all the code I review in this post in my new “blogs” repository on GitHub . I encourage you to clone, fork, spoon, ladle, knife, etc…
Follow me
Be sure to subscribe/follow for more!