Python & REST APIs: Testing my Oracle Autonomous Database

Let’s not kid ourselves, I’m not a Python programmer. I picked it up yesterday, literally.

My background:

• B.S. in Computer Science (a long time ago)
• decent amount of shell scripting
• Perl/DB/CGI web stuff for about 5 years
• SQL & PL/SQL for a long time, but not an expert

I’m also the product manager for Oracle REST Database Services. So for the last several years I’ve been going deeper into HTTP, JSON, and REST APIs.

So as you read my code below, rest assured that you will think oh that’s not good, and I could have done that in 25 lines. It’s not my goal today to blow you away with my Python skills.

No, what I want to do is show you how to use an Oracle Database REST API that is protected with a privilege and accessed via an OAuth2 workflow (Client Credentials).

It’s hard to show you how to do this without also talking about how to setup the database, the REST APIs, the OAuth2 clients, etc, but I will do a bit of that, and then jump straight into the Python.

Setup / The ‘Rig’

On the Oracle Cloud I have an Always Free Autonomous (Transaction Processing) Database.

See Layla’s step-by-step for getting started on a new account/database.

This cloud service includes a mid-tier/web server that delivers BOTH a web UI for the database (SQL Developer Web!), AND an engine for REST APIs.

One of the included REST APIs is ‘REST Enabled SQL‘ – I can POST any valid Oracle SQL or script, and the API will run it, and return the results. It’s quite handy.

While this API can be authenticated with your Oracle Database user credentials, I don’t want to use Basic Auth or share my database username/passwords. And while it’s ‘easier,’ it’s also slower, as much as 300% slower than using OAuth2.

So if I am going to use OAuth2 workflows, I am going to need an OAuth2 Client to access my database REST APIs provided by ORDS.

How To Resources (Pure ORDS/Database Setup)

• Get an Always Free Oracle Database with SQL Developer Web
• Detailed How-to Create an OAuth2 Client and use REST Enable SQL
• Comparing OAuth2 vs Database Auth for GET and POST APIs

Our REST API

Let’s pretend that someone has some data or resources they’re willing to share with you. You know it’s a database, but really all you’ve been given is some REST APIs and a set of credentials.

 API Endpoint: https://abcdefghijklmnop-tjsatp.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/_/sql
       Method: POST
 Content-Type: application/sql
    Client ID: somestring.. 
Client Secret: someotherstring..

The request is ‘SQL’ on the request body. The response will be a JSON doc, that describes the results as well as contains the results.

It looks like this in a GUI REST Client, I use Insomnia.

Our Python Program

Libraries Used:

• json
• requests
• sys
• logging
• colorama
• oauthlib
• requests_oauthlib

import json
import requests
import sys
import logging
from colorama import Fore
from colorama import Style
from oauthlib.oauth2 import BackendApplicationClient
from requests_oauthlib import OAuth2Session
 
##
## get a bearer token (OAuth2, Client Credentials)
##
 
oauth_server_url = "https://abcdefhjijkl-tjsatp.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/oauth/token"
client_id = 'sometext..'
client_secret = 'sometmoretext..'
 
client = BackendApplicationClient(client_id=client_id)
oauth = OAuth2Session(client=client)
token = oauth.fetch_token(token_url=oauth_server_url, client_id=client_id, client_secret=client_secret)
#print(token)
bearer_token = token['access_token']
#print(bearer_token)
##print('token is', token)
 
##
## call the API with our token
##
 
my_query = 'select systimestamp from dual'
 
response = requests.post('https://abcdefghijkl-tjsatp.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/_/sql',
                           headers={'Authorization': 'Bearer ' + bearer_token, 'Content-Type' : 'application/sql'},
                           data = my_query)
 
print (Fore.BLUE + 'Status Code:' , response.status_code)
pretty_json = json.dumps(response.json(), indent=4)
#print(pretty_json)
print (Fore.GREEN + '    Elapsed:', response.elapsed.total_seconds(), 'seconds')
jsonResponse = response.json()
ords_time_zulu = jsonResponse['items'][0]['resultSet']['items'][0]['systimestamp']
print(Fore.YELLOW + '  ZULU TIME:' , ords_time_zulu)
print(Fore.WHITE + '')

Let’s break things up a bit

Getting the Bearer Token

One of the benefits of Python’s popularity is how many tutorials, how-to’s, StackOverflow questions, etc. that practically cover every single use case you could image.

I was able to basically pick this up verbatim from the Requests-OAuthlib documentation.

oauth_server_url = "https://abcdefghij-tjsatp.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/oauth/token"
client_id = 'sometext..'
client_secret = 'somemoretext..'
 
client = BackendApplicationClient(client_id=client_id)
oauth = OAuth2Session(client=client)
token = oauth.fetch_token(token_url=oauth_server_url, client_id=client_id, client_secret=client_secret)

What actually comes back for our token?

A JSON document that looks like this –

{'access_token': 'rzLPKVPBMBhViNwPGnpuww', 'token_type': 'bearer', 'expires_in': 3600, 'expires_at': 1662133909.2579508}

Now, all I needed to send my POST request was JUST the value from the ‘access_token’ attribute.

This is one more reason to love JSON, if I want to pull that off, I can just refer to it as

bearer_token = token['access_token']

Ok, so now I have the bearer token, I can make the request.

I need to setup my actual query, and then I can do a requests.post.

my_query = 'select systimestamp from dual'
 
response = requests.post('https://abcdefghijk-tjsatp.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/_/sql',
                           headers={'Authorization': 'Bearer ' + bearer_token, 'Content-Type' : 'application/sql'},
                           data = my_query)

I knew the format/content expected on the request on REST SQL, as my REST Client shows me what it does under the covers, so coding that was pretty easy, once I figured out how to append strings 🙂

And then everything after that was just playing with the response.

I wanted to know the response status code.

Easy, just print(response.status_code).

I wanted to know how long it took to run.

Easy, just print(response.elapsed.total_seconds(), ‘seconds’).

And, I wanted to know what time it was for my database, that is, what was the result of running SELECT SYSTETIMESTAMP FROM DUAL;

Parsing the JSON

Having worked with JSON for a few years made this pretty easy as well. I know objects and arrays when I see them. And I know that array indexes start with [0].

So I literally just put my RAW JSON response on the call up next to my code editor and worked it out….

Note my query changed slightly from when I took this screenshot and finished.

I was quickly reminded that the ‘print’ command is your friend, just like DBMS_OUTPUT.PUT_LINE is when you’re trying to troubleshoot stuff. It’s how I figured out why ORDS didn’t like my bearer token for example.

What it looks like, in action –

The actual response, so it’s easier to read, and then we’ll talk about the data itself.

Status Code: 200
    Elapsed: 0.172489 seconds
  ZULU TIME: 2022-09-02T15:07:38.034Z

ORDS takes any DATE and converts it to a TIMESTAMP (with TimeZone) when representing that data in a JSON response. I’m not working with a date here, but with SYSTIMESTAMP, a function that ‘returns the system date, including fractional seconds and time zone, of the system on which the database resides‘.

For those timestamps without timeszones, ORDS also converts those as well. Temporal data will ALWAYS have a timezone, and by default, it’s represented in UTC, Zulu, or Greenwich time. So if it’s 10AM Eastern, Zulu would be 15 hundred hours (+5).

Now, I wanted to wrap this program up by using the built-in date and time functions in python to auto convert my time to Eastern, but I got distracted and decided to call it quits here. The important thing to know is you know it’s Zulu, and you as an app developer can then figure out how to represent that point in time for your intended audience.

Disclaimers

Did I say I’m not a Python programmer? I literally started on this yesterday. Here’s what it looked like using Basic (Database Credentials) Auth on my local rig –

Also, my code assumes NO PROBLEMS. If my system is up/available, I’ll probably get nasty Python errors. I’m OK with that. But with a few more IF..THENs, I could generate much nicer messages.

If this were a real app, I could still do this, but also have my token re-used until it expires, and then when it does expire, simply refresh the token. This code/logic is shared on the the Requests-OAuthlib documentation that I referenced earlier.

What did I get out of this exercise?

Specifically my little program tells me four things:

• status of Cloud availability
• database/user availability
• the time, from my database’s perspective
• the distance from me to my database (response time)

Professionally, this gives me an opportunity to demonstrate the power of REST APIs for your database (using Oracle Database and ORDS).

It also helps me help Chris with his new ORDS+Python Lab he’s been working on this Summer for Cloud World. I wanted to know if the bonus directions we gave for using OAuth2 would work, and um yeah, they will!

And finally, this gave me an opportunity to pick up a new toy and have a go. I can easily see why Python is so popular. Just mind those indents!