Problem
I wanted to take the Objects,
In my Admin schema and copy them into a newly created Python Developer database user/schema. I have been meaning to do this for a while now since I’d to approach all future development work as a typical [developer] user might.
I previously created a “developer” user, but the recommendation was to create a user specifically for python development. This recommendation makes sense because I’d like to explore other developer frameworks — JavaScript/React, Derelicte, Golang, Hoobastank, Swift, Smashmouth.
DISCLAIMER: Some of the aforementioned languages/frameworks are entirely fictional.
So in creating this user and populating the schema, I could have just copied these objects over in seconds. I’m sure some already existing would have enabled me to do this. A less-elegant option would be to do a local upload. I’m confident I could export the tables of my Admin schema and then upload them as a developer in Database Actions. Doing so here:
But those options were too straightforward, effortless, and expeditious. I needed an approach that would make me question my life decisions. Something that would force me to rethink what it meant to be human.
Mostly, I wanted an excuse to tinker with ORDS some more. While I didn’t start with a well-defined use case, I was able to retcon (a portmanteau of the words retroactive and continuity) one.
Scenario
The scenario is that we have REST-enabled schema objects (in this case, various tables), and maybe we want to execute GET requests on that table. Perhaps we want all the data from that table (having been returned in JSON). And for whatever reason, we want to save this information in a JSON file. Plausible? Not likely. Possible? If I can do it, then yes, 100% possible.
Solution
NOTE: I've since disabled REST for these tables. So none of these URLs currently work. Also for security reasons you probably want to a require authentication to access these. The below screenshots and code snippets are for demonstration purposes. In this case, probably acceptable, but please (PLEASE) exercise caution if you are recreating at your workplace.
REST-enabling
When you REST-enable a table, you’re provided a URL (aka a REST Endpoint) for viewing. It will look something like this:
https://gf641ea24ecc468-dbmcdeebyface.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/scores/The response looks like this:
You are seeing the first 25 results from my “Scores” table. I’ve also created an alias name for that table as an extra level of protection. The table’s actual name is not “Scores,” but rather something else.
If you collapse the “items,” it’s easier to see the rest of the “name: value” pairs:
I can do my best to explain what we see here:
- ‘items: […]” are the individual entries in my table (the columns would be the items you see in the black bold). ORDS includes links for calling that single entry only
- ‘hasMore”: true,” essentially means that there are more pages with information to follow
- “limit: 25” refers to the fact that ORDS is only returning 25 results at a time. I didn’t set this; it was the default.
- “offset: 0” this is tricky. From what I can tell, the offset only becomes a consideration after the first page. You begin with no offset (this makes no sense, I know, but I will explain this shortly)
- “count: 25” – we have entries 1-25 on this page
- “links: […]” these are all pretty self-explanatory. Two links, in particular, are pretty interesting. They are:
- “describedby” and
- “next”
If I click the “describedby” link, you’ll see something that looks like this:
On this page, you can review valuable information without ever logging into Database Actions. I found this extremely helpful; from here, I could see the table name, Primary Key (if it exists), and the column names and types for the table.
Another link I wanted to highlight was “next.” If you look closely, you’ll see that the “next” URL includes an offset of zero:
However, if I click that URL (I’ve collapsed all the items on this page as well), I can see that “offset” has increased to 25, and the new “next” URL shows “offset=50”. This next part will do a better job of explaining what is happening.
Since we are on the second page of results, ORDS knows that we began with an offset of zero (the beginning of the table) and a limit of 25. Each “next” URL will increment by 25 (the offset) and return 25 results at a time. And it will keep producing “next” URLs until the “hasMore: false” condition is finally satisfied. We can modify a lot of this, but this is what ORDS does for you automatically/by default. Great news for me because at my current level of experience, the more steps that I can automate, the better!
Python
Prepare yourself for a bit of a leap. At this point, I was pretty confident I could copy/paste my way through some python code and pull down this data, and then save it in some format.
Finally, after about a week, I was able to duct-tape something together. Courtesy of Google, StackOverflow, so many YouTube video tutorials (so, so many), here is what I’ve arrived at:
import requests
import json
# requests is a python library that allows you to send GET,
# PUT, DELETE, HEAD, and OPTIONS requests. I'm just doing
# GET requests though. The json library comes with python,
# but I need it here to be able to work with the json that # is returned. I'll also need it to create the json file
# (at the end).
offset = 0
# I'm basically telling this little script that we should
# expect to start with an offset of zero. If you recall,
# this just means we are beginning on the first page.
# Hence, there is no offset.
url = 'https://gf641ea24ecc468-dbmcdeebyface.adb.us-ashburn-1.oraclecloudapps.com/ords/admin/places/?offset={}'
# I've added in this modifier {}, which, as you'll see in a # bit, allows me to increment by 25 with each subsequent
# GET request.
hasMore = True
# Read this as "hasMore is equal to True". In other words,
# if there is more STUFF, then we should see "true" (go
# back and look at the screenshots if you don't know what
# this means.)
while hasMore:
# We are saying, "While 'hasMore is equal to true, then..."
response = requests.get(url.format(offset))
#...recognize that "response" is a GET request that
# consists of the URL (from above) plus whatever the offset # is (we'll indicate the offset below).
data = response.json()
#data is just whatever the results are from the above
# response.
filename = 'places.json'
# "filename" is the json file we are going to create to
# put all these results into.
if data.get('hasMore'):
offset += 25
with open(filename, 'a') as json_file:
json.dump(data, json_file)
else:
break
# I'm going to switch to narrating here, this one is just
# too verbose. In lay terms, we are saying: if the GET
# request "data" (which is essentially a composite variable # at this point) has "hasMore: true" on the page, then keep # doing this next thing.
# With the next "thing" being: take the results from that
# URL and save it into a file called "places.json". Once
# you've done that, keep doing it, but add 25 to the offset # for that URL for subsequent GET requests. And whenever
# you have results, append it (that's what that "a" is on
# line 44) to the file you created, and just keep going
# until "hasMore" doesn't satisfy that condition on line
# 20.
# If it doesn't satisfy that condition then that means
# "hasMore" is equal to false. In other words, there is
# nothing more to request, so stop (that is what the "else: # break" means). I’m going to include an image to see the nesting better.
And once I run this, I’m left with a JSON file (“places.json”) that has all my entries saved à la:
Now what?
At this point, I have a few options. I could:
- include code that would convert this JSON file to a . CSV file
- include code to indent the JSON file, making it more readable, or
- drag and drop it into my Autonomous Database (via Data Load in Database Actions) with some slight tweaking (I just tried, so I already know I need to do more massaging)
The End
So what have we learned here? Even somebody with very basic skills can interact with an Autonomous Database via ORDS.
If you have an internet connection and are eager enough, the sky is the limit. Putting things into perspective, I wouldn’t have been able to do this about a week and a half ago.
Truthfully, you’re not that far behind! I encourage you to give it a go.