The plan was to create an ORACLE REST endpoint and then POST a CSV file to that auto-REST enabled table (you can see how I did that here, in section two of my most recent article). But, instead of doing this manually, I wanted to automate this POST request using Apple’s Automator application…
Me…two paragraphs from now
Follow along with the video
The plan
I did it. I went so far down the rabbit hole, I almost didn’t make it back alive. I don’t know when this ridiculous idea popped into my head, but it’s been well over a year. Until now, I either hadn’t had the time or the confidence to really tackle it.
The plan was to create an ORACLE REST endpoint and then POST a CSV file to that auto-REST enabled table (you can see how I did that here, in section two of my most recent article). But, instead of doing this manually, I wanted to automate this POST request using Apple’s Automator application.
The use case I made up was one where a person would need to periodically feed data into a table. The data doesn’t change, nor does the target table. Here is an example of the table I’m using:
The basic structure of the Bank Transfers table
And the DDL, should there be any interest:
CREATE TABLE ADMIN.BANK_TRANSFERS
(TXN_ID NUMBER ,
SRC_ACCT_ID NUMBER ,
DST_ACCT_ID NUMBER ,
DESCRIPTION VARCHAR2 (4000) ,
AMOUNT NUMBER
)
TABLESPACE DATA
LOGGING
;
Once this table was created, I auto-REST enabled the table and retrieved the complete cURL Command for performing a Batch Loadrequest. Remember, we have three examples for cURL Commands now, I chose Bash since I’m on a Mac:
Retrieving the the Batch Load cURL Command
Once I grabbed the cURL Command, I would temporarily save it to a clipboard (e.g. VS Code, TextEdit, etc.). I’d then create a new folder on my desktop.
The newly created ords_curl_post folder
How I actually did it
I’d then search via Spotlight for the Automator application. Once there, I’d choose Folder Action.
Choosing Folder Action for this automation
HEY!! README: I'm going to breeze through this. And it may seem like I am well-aquainted with this application. I am not.I spent hours debugging, reading through old StackExchange forums, and Apple documentation so I could share this with you. There is a ton more work to do. But bottom line, this thing works, and its something that is FREE and accessible for a lot of people. You could have a TON of fun with this stuff, so keep reading!
There’s no easy way to get around this, but to get really good at this, you’ll just need to tinker. Luckily, most of these automation modules are very intuitive. And there is a ton of information online on how to piece them all together.
Automator 🤖
All of these modules are drag-and-drop, so it makes it easy to create an execution path for your Folder Action application. Eventually, I ended up with this (don’t worry, I’ll break it down some, a video is in the works for a more detailed overview):
Complete Folder Action automation for the ORDS Batch Load request
The modules
The modules I’m using are:
Get Specified Finder Items
Get Folder Contents
Run Shell Script (for a zsh shell, the default for this MacBook)
Set Value of Variable
Get Value of Variable
Display Notification
You can see at the very top, that I have to choose a target folder since this is a folder action. I chose the folder I created; ords_curl_post.
Get Specified Finder Items and Get Folder Contents
The first two modules are pretty straightforward. You get the specified finder items (from that specific folder). And then get the contents from that folder (whatever CSV file I drop in there). That will act as a trigger for running the shell script (where the filename/s serve as the input for the cURL Command).
PAUSE: I must confess, I had essentially ZERO experience in shell scripting prior to this, and I got it to work. Its probably not the prettiest, but damn if I'm not stoked that this thing actually does what it is supposed to do.
The only main considerations on this shell script are that you’ll want to stay with zsh and you’ll want to choose “as arguments” in the “Pass input” dropdown menu. Choosing “as arguments” allows you to take that file name and apply it to the For Loop in the shell script. I removed the echo "$f" because all it was doing was printing out the file name (which makes sense since it was the variable in this script).
Choosing “as arguments“
The Shell Script
That cURL Command I copied from earlier looks like this:
I made some modifications though. I made sure Content-Type was text/csv. And then I added some fancy options for additional information (more details on this here, go nuts) when I get a response from the database.
REMINDER: I didn't know how to do this until about 30 mins before I got it to work. I'm emphasizing this because I want to drive home the point that with time and some trial-and-error, you too can get something like this to work!
With my changes, the new cURL Command looks like this:
What a mess…That -w option stands for write-out. When I receive the response from the Batch Load request, I’ll want the following information:
Response Code (e.g. like a 200 or 400)
Total Upload Time
Upload Speed
Upload Size
All of that is completely optional. I just thought it would be neat to show it. Although, as you’ll see in a little bit, Apple notifications has some weird behavior at times so you don’t really get to see all of the output.
I then applied the cURL command to the shell script, (with some slight modifications to the For Loop), and it ended up looking like this:
New shells script with updated cURL command
Here is what the output looked like when I did a test run (with a sample CSV):
Success on the cURL command
Set Value of Variable
All of that output, referred to as “Results”, will then be set as a variable. That variable will be henceforth known as the responseOutput (Fun fact: that is called Camel casing…I learned that like 3-4 months ago). You’ll first need to create the variable, and once you run the folder action, it’ll apply the results to that variable. Like this:
Creating a new variableResults from cURL command applied to variable
Get Value of Variable and Display Notification
Those next two modules simply “GET” that value of the variable/results and then sends that value to the Display Notification module. This section is unremarkable, moving on.
And at this point, I was done. All I needed to do was save the script and then move on to the next step.
Folder Actions Setup
None of this will really work as intended until you perform one final step. I’ll right-click the target folder and select “Folder Actions Setup.” From there a dialog will appear; you’ll want to make sure both the folder and the script are checked.
Selecting Folder Actions SetupDouble checking that everything is enabled
Trying it out
Next, I emptied the folder. Then I dropped in a 5000-row CSV file and let Folder Actions do its thing. This entire process is quick! I’m loving the notification, but the “Show” button simply does not work (I think that is a macOS quirk though). However, when I go back to my Autonomous Database, I can 100% confirm that this ORDS Batch Load worked.
Successful Batch LoadDouble checking the Autonomous Database
Final thoughts
This was relatively easy to do. In total, it took me about 3-4 days of research and trial and error to get this working. There is a lot I do not know about shell scripting. But even with a rudimentary understanding, you too can get this to work.
Next, I’d like to create a dialog window for the notification (the output from the cURL Command). I believe you can do that in AppleScript; I just don’t know how yet.
If you are reading this and can think of anything, please leave a message! If you want to try it out for yourself, I’ve shared the entire workbook on my GitHub repo; which can be found here.
I’ll also be doing an extended video review of this, where I’ll recreate the entire automation from start to finish. Be on the lookout for that too!
Overview and connecting with the python-oracledb library
Part II
Connecting with Oracle REST APIs unauthenticated
Part III
Custom Oracle REST APIs with OAuth2.0 Authorization
Welcome back
I finally had a break in my PM duties to share a small afternoon project [I started a few weeks ago]. I challenged myself to a brief Python coding exercise. I wanted to develop some code that allowed me to connect to my Autonomous Database using either our python-oracledb driver (library) or with Oracle REST Data Services (ORDS).
I undertook this effort as I also wanted to make some comparisons and maybe draw some conclusions from these different approaches.
NOTE: If you don't feel like reading this drivel, you can jump straight to the repository where this code lives. It's all nicely commented and has everything you need to get it to work. You can check that out here.
The test files
Reviewing the code, I’ve created three Python test files. test1.py relies on the python-oracledb library to connect to an Oracle Autonomous database while test2.py and test3.py rely on ORDS (test3.py uses OAuth2.0, but more on that later).
test1.py using the python-oracledb librarytest2.py relies on an unsecured ORDS endpointtest3.py with ORDS, secured with OAuth2
Configuration
Configuration directory
I set up this configuration directory (config_dir) to abstract sensitive information from the test files. My ewallet.pem and tnsnames.ora files live in this config_dir. These are both required for Mutual TLS (mTLS) connection to an Oracle Autonomous database (you can find additional details on mTLS in the docs here).
ewallet.pem and tnsnames.ora files
Other files
OAuth2.0, Test URLs, and Wallet Credential files
Other files include oauth2creds.py, testurls.py, and walletcredentials.py. Depending on the test case, I’ll use some or all of these files (you’ll see that shortly).
NOTE: If not obvious to you, I wouldn't put any sensitive information into a public git repository.
Connecting with python-oracledb
One approach to connecting via your Oracle database is with the python-oracledb driver (library). An Oracle team created this library (people much more experienced and wiser than me), and it makes connecting with Python possible.
FYI: I’m connecting to my Autonomous Database. If you want to try this, refer to the documentation for using this library and the Autonomous database. You can find that here.
The Python code that I came up with to make this work:
#Connecting to an Oracle Autonomous Database using the Python-OracleDB driver.
import oracledb
# 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 walletcredentials import uname, pwd, cdir, wltloc, wltpwd, dsn
# Requires a config directory with ewallet.pem and tnsnames.ora files.
with oracledb.connect(user=uname, password=pwd, dsn=dsn, config_dir=cdir, wallet_location=wltloc, wallet_password=wltpwd) as connection:
with connection.cursor() as cursor:
# SQL statements should not contain a trailing semicolon (“;”) or forward slash (“/”).
sql = """select * from BUSCONFIND where location='ZAF'
order by value ASC """
for r in cursor.execute(sql):
print(r)
In Line 7, you can see how I import the wallet credentials from the walletcredentials.py file. Without that information, this code wouldn’t work. I also import the database username, password, and configuration directory (which includes the ewallet.pem and tnsnames.ora files).
From there, the code is pretty straightforward. However, some library-specific syntax is required (the complete details are in the docs, found here), but aside from that, nothing is too complicated. You’ll see the SQL statement in Lines 16-17; the proper SQL format looks like this:
SELECT * FROM busconfind WHERE location='zaf'
ORDER BY value ASC;
And here is an example of this SQL output in a SQL Worksheet (in Database Actions):
Reviewing the SQL in Database Actions
FYI: This is a Business Confidence Index data-set, in case you were curious (retrieved here).
That SQL allows me to filter on a Location and then return those results in ascending orderaccording to the Value column. When I do this using the python-oracledb driver, I should expect to see the same results.
NOTE: You've probably noticed that the SQL in the python file differs from that seen in the SQL Worksheet. That is because you need to escape the single quotes surrounding ZAF, as well as remove the trailing semi-colon in the SQL statement. Its all in the python-oracledb documentation, you just have to be aware of this.
Once I have all the necessary information in my walletcredentials.py file, I can import that into the test1.py file and execute the code. I chose to run this in an Interactive Window (I’m using VS Code), but you can also do this in your Terminal. In the images (from left to right), you’ll see the test1.py file, then a summary of the output from that SQL query (contained in the test1.py code), and finally, the detailed output (in a text editor).
Executing the Python code in an Interactive WindowSummary output from test1.pyDetailed output from test1.py
Wrap-up
For those that have an existing Free Tier tenancy, this could be a good option for you. Of course, you have to do some light administration. But if you have gone through the steps to create an Autonomous database in your cloud tenancy, you probably know where to look for the tnsnames.ora and other database wallet files.
I’m not a developer, but I think it would be nice to simplify the business logic found in this Python code. Maybe better to abstract it completely. For prototyping an application (perhaps one that isn’t micro services-oriented, this could work) or for data- and business analysts, this could do the trick for you. In fact, the data is returned to you in rows of tuples; so turning this into a CSV or reading it into a data analysis library (such as pandas) should be fairly easy!
Connecting via ORDS: sans OAuth2.0
Auto-REST and cURL
I’m still using the “devuser” (although this may be unnecessary, as any unsecured REST-enabled table would do). I’m using the same table as before; the only change I’ve made is to auto-REST enable the BUSCONFIND table for the test2.py code.
In the following images, I’m retrieving the cURL command for performing a GET request on this table.
NOTE: In a recent ORDS update, we made available different shell variations (this will depend on your OS); I've selected Bash.
From there, I take the URI (learn more on URIs) portion of the cURL command and place it into my browser. Since this table is auto-REST enabled, I’ll only receive 25 rows from this table.
NOTE: The ORDS default pagination is limit = 25.
Getting the cURL command from an already ORDS REST-enabled tableSelecting the GET request for BashGET response in JSONThe raw JSON, pretty printed
The code
And the code for this test2.py looks like this:
# Auto-REST enabled with ORDS; in an Oracle Autonomous Database with query parameters.
import requests
import pprint
# Importing the base URI from this python file.
from testurls import test2_url
# An unprotected endpoint that has been "switched on" with the ORDS Auto-REST enable feature.
# Query parameters can be added/passed to the Base URI for GET-ing more discrete information.
url = (test2_url + '?q={"location":"ZAF","value":{"$gt":100},"$orderby":{"value":"asc"}}}')
# For prototyping an application, in its earlier stages, this could really work. On your front end, you
# expect the user to make certain selections, and you'll still pass those as parameters.
# But here, you do this as a query string. In later stages, you may want to streamline your application
# code by placing all this into a PL/SQL or SQL statement. Thereby separating application
# logic and business logic. You'll see this approach in the test3.py file.
# This works, but you can see how it gets verbose, quick. Its a great jumping-off point.
responsefromadb = requests.get(url)
pprint.pprint(responsefromadb.json())
Lines 8 and 13 are the two areas to focus on in this example. In Line 8 imported my URL from the testurls.py file (again, abstracting it, so it’s not in the main body of the code).
The test2.py and testurls.py files
And then, in Line 13, I appended a query string to the end of that URL. ORDS expects the query parameters to be a JSON object with the following syntax:
[ORDS Endpoint]/?q={"JSON Key": "JSON Value"}
The new, complete query string below requests the same information as was requested in the test1.py example:
This string begins with that same BASE URI for the ORDS endpoint (the auto-REST enabled BUSCONFIND table) and then applies the query string prefix “?q=” followed by the following parameters:
Filter by the location "ZAF"
Limit the search of these locations to values (in the Value column) greater than ($gt) 100
Return these results in ascending order (asc) of the Value column
NOTE: You can manipulate the offsets and limits in the python-oracledb driver too. More info found here. And filtering in queries with ORDS can be found here.
And if I run the test2.py code in the VS Code Interactive Window, I’ll see the following summary output.
Summary output from the response in test2.py
Here is a more detailed view in the VS Code text editor:
Detailed output with helpful links
Wrap-up
A slightly different approach, right? The data is all there, similar to what you saw in the test1.py example. There are a few things to note, though:
The consumer of this ORDS REST API doesn’t need access to the database (i.e. you don’t need to be an admin or have a schema); you can perform GET requests on this URI.
The response body is in JSON (ubiquitous across the web and web applications)
Also, language and framework agnostic (the JSON can be consumed/used widely, and not just with Python)
You are provided a URI for each item (i.e. entry, row, etc.)
No need for SQL; just filter with the JSON query parameters
No business logic in the application code
Needless to say, no ORMs or database modeling is required for this approach
However…security is, ahem…nonexistent. That is a problem and flies in the face of what we recommend in our ORDS Best Practices.
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!
Since this is what I’m considering “advanced” (it’s not difficult, there are just many pieces) I’m going to keep this section brief. Long story short, I’ll take those query parameters from above and place them into what is referred to as a Resource Handler.
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.
Creating the Resource Module in the ORDS REST WorkshopCreating the Resource TemplateReviewing the available operations for the Resource TemplateThe newly created Resource GET HandlerPlacing the SQL directly into the Resource HandlerTesting out the code to simulate a GET request using "ZAF" as the locationReviewing the output of that SQL query, in a table format
Chances are you may be the administrator of your Always Free 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:
After creating a client you can use the provided URL for requesting a new Bearer Token
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)
Lines 11 and 16 deserve some attention here. The URL for Line 11 comes from the testurls.py file; seen in the previous example. And the contents from Line 16 come from the oauth2creds.py file. Here are the files, side-by-side:
The test3.py, testurls.py, and oauth2creds.py files
As you can see in the testurls.py file, I’m relying on the test3_url for this example. And the OAuth2.0 information you see comes directly from the OAuth Client I created in Database Actions:
In this image, you can see the Client ID and Client Secret
If I put that all together, I can execute the code in test3.py and “pretty print” the response in my Interactive Window. 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:
With that out of the way, I can run this code and review the output.
Running the test3.py code in the Interactive WindowReviewing the summary output – a JSON arrayReviewing the detailed view of the “items“Scrolling to the bottom of the GET response body to see the available links for additional items
From top-to-bottom, left-to-right 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 array 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
You can probably tell, but this is like an expansion of the previous example. 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 the second example). 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
We’re at the end of this 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. There’s no way around this, but a video needs to accompany this post.
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…
Podman is a daemonless container engine for developing, managing, and running OCI Containers on your Linux System. Containers can either be run as root or in rootless mode. Simply put: alias docker=podman.
I’ve spent the past couple of weeks setting up Podman to work on my MacBook.
My current setup
I really wanted to take advantage of our Oracle Container Registry. While, we have several containers, the one I’m most interested in is the [Ace] freely available Oracle Enterprise Database version 21.3.0.0.
FYI: It can be found in the Database containers category.
I wanted to learn more about containers while also connecting locally (i.e., from my MacBook) via SQLcl to said container. In that scenario, as far as my computer thinks, the container is a production database running elsewhere in the world. Oh, and I’m using Podman instead of Docker to do all this.
be able to connect to it with various Oracle database tools
I began this exercise with SQLcl since it was used in one of my recent posts. But as a follow-on to this article, I’d like to install ORDS on my local computer and then connect again but with ORDS joining the party. But that’s for another time.
But before connecting to this container, you’ll need a lot of prerequisites. As far as “ingredients” go, you’ll need the following:
Homebrew installed and updated. (If you need to do this, review my recent article for instructions.)
Podman installed and updated
Apple X-Code Command Line Tools updated (this is tricky, so check my notes below)
SQLcl (you can review the installation steps here)
A sample CSV file (The subject doesn’t matter; I grabbed one from Kaggle – “IMDb’s Top 100 Movies“)
The setup before the setup
Since this was such a huge PITA, I’m going to walk through all the steps I took to make Podman work on my MacBook. I’ve done this about ten times so far to make sure I’m clearly explaining every step I took.
I first opened up a new Terminal session using Spotlight (Left Command + Spacebar). Once in Spotlight, I searched for “terminal” and then hit enter. A new Terminal window will appear.
Opening SpotlightSearching for the Terminal applicationA new Terminal window
From there, I reviewed Homebrew using the brew list command. If you’re following along, you’ll see a list similar to mine, depending on what you have installed.
The next part is easy. I installed Podman with the following command: brew install podman. Homebrew will run through an auto-update, and eventually, Podman will begin installing.
Upon the first installation, and depending on the macOS you are on, you may see a couple of errors appear. I can tell you they will cause issues within Podman later on down the line. So (and without having to take you back down the rabbit hole with me), you’ll need to uninstall Podman with the brew uninstall podman command.
The errors can be seen in this image:
Errors with Podman installing
There are a few ways one can remedy this. First, you should uninstall Podman, close your Terminal window, and open up a new Terminal window. I found (via this GitHub issue) that this is a known bug. Some have suggested running the brew doctor command to review a list of possible problems (this will reveal any potential problems Homebrew has discovered). This seems like a good practice, regardless, and I wasn’t aware of this feature until now!
And while writing this article, I did just that and found two errors I’ll need to fix. I’m still trying to figure out what either means, but the one about the executable is troubling.
But back to the Podman issue. To resolve the xcrun errors, I stumbled upon a solution buried deep in the recesses of the internet. Long story short, I needed to manually install Apple’s X-Code Command Line tools. But if you try and the installation fails, you have to take an extra step.
BTW, it did NOT take 78 hours to download and install
The x-tra step
If the xcode-select --install command fails, you have to remove the Command Tools from your machine altogether. I did this with the following command:
sudo rm -rf /Library/Developer/CommandTools
If you want to bore yourself with this issue, here are some resources I found:
I’ve seen sudo in the past; I wonder if I ever bothered to look up its meaning. Taken directly from the docs:
Sudo (su “do”) allows a system administrator to give certain users (or groups of users) the ability to run some (or all) commands as the superuser or another user, while logging all commands and arguments. Sudo operates on a per-command basis, it is not a replacement for the shell.
Back to our regularly scheduled program…you’ll probably need to enter your system’s password (the thing you use to log on to your computer when it first starts up and/or wakes). And after that, restart your Terminal (I don’t believe I did this, but it’s probably a good idea to restart the Terminal).
Once that new Terminal window fired up, I used the following command to install the latest X-Code Command Line tools:
sudo xcode-select --install
Reminder, it will not take 78 hours to install this. I just followed the prompts (license terms, the usual stuff, etc.).
NOTE: I suspect we have to do this because for some reason, X-Code Command Line tools are not updated upon every macOS version update. So, who knows when the last time these tools have been updated. This is just a hunch, but in reality, I've no idea what I'm talking about.
Are we ready yet? Well, almost. Again, if you’re following along, navigate to our Oracle Container Registry site to retrieve the database container for this “recipe.” The path I took was Landing page > Database > Enterprise.
The main landing pageYou want “enterprise”Pay attention to the sign-in
YOU NEED TO SIGN IN for this to work!!! Oh, suuuuure… it’ll seem like it’s working when you’re in Podman, and you’ve tried ten times…but then it just keeps failing, and failing, and failing! So be sure to sign in (or create an account if you haven’t already).
Once signed in and chosen your preferred language, you’ll see this:
I’m ready to head back to the Terminal
PAUSE: reviewing the limitations of this exercise
Alright, so there are a few limitations I should address, and in no particular order:
Checkpointing containers in Podman
Volumes in Podman
Creating the database versus signing on to the database
Checkpointing currently doesn’t work in Podman (at least for Macs on macOS Ventura). This is documented as well. Here’s a GitHub issue I found. I don’t seem to be the only one with the issue. I spent about a day on this trying to get it to work. I couldn’t figure it out; maybe if you’re reading this, you know the secret. Please share if you know!
Secondly, I couldn’t figure out how to mount a volume to a container. I know this is fundamental to containers, but I encountered error after error for days. And for the purposes of this exercise, it isn’t a big deal. Now, if I were on an actual development team, that would be a different story. But I’m too dumb for development, that is why I’m a product manager 🤣!
Finally, working with containers requires a paradigm shift. Shortly you’ll see that I’m setting up a container and “starting” the database therein. Later, I’ll separately log on to that database, using SQLcl,after the database is up and running. They are two different steps.
Looking at this screen you would think, “I’m just going to jump right in and execute the first command I see on this page.” Wrong!
Initial docker run command
Actually, you do NOT want to do that. You must scroll down to the “Connecting from outside of the container” section. Because I’m going to be connecting to this container from the outside.
Referring to the Custom Configurations section
I know this documentation mentions SQL*Plus, but this all applies to SQLcl also. And if you refer to my previous SQLcl post, you can review the logon syntax for logging on. The critical point is that I need to start the container with the -p (or Port) option included. Are you still with me? Let’s take a trip to the “Custom Configurations” section.
docker run -d --name <container_name> \
-p <host_port>:1521 -p <host_port>:5500 \
-e ORACLE_SID=<your_SID> \
-e ORACLE_PDB=<your_PDBname> \
-e ORACLE_PWD=<your_database_password> \
-e INIT_SGA_SIZE=<your_database_SGA_memory_MB> \
-e INIT_PGA_SIZE=<your_database_PGA_memory_MB> \
-e ORACLE_EDITION=<your_database_edition> \
-e ORACLE_CHARACTERSET=<your_character_set> \
-e ENABLE_ARCHIVELOG=true \
-v [<host_mount_point>:]/opt/oracle/oradata \
container-registry.oracle.com/database/enterprise:21.3.0.0
Parameters:
--name
The name of the container (default: auto generated
-p
The port mapping of the host port to the container port.
Two ports are exposed: 1521 (Oracle Listener), 5500 (OEM Express)
-e ORACLE_SID
The Oracle Database SID that should be used (default:ORCLCDB)
-e ORACLE_PDB
The Oracle Database PDB name that should be used (default: ORCLPDB1)
-e ORACLE_PWD
The Oracle Database SYS, SYSTEM and
PDBADMIN password (default: auto generated)
-e INIT_SGA_SIZE
The total memory in MB that should be used for all
SGA components (optional)
-e INIT_PGA_SIZE
The target aggregate PGA memory in MB that should be used
for all server processes attached to the instance (optional)
-e ORACLE_EDITION
The Oracle Database Edition (enterprise/standard, default: enterprise)
-e ORACLE_CHARACTERSET
The character set to use when creating the database (default: AL32UTF8)
-e ENABLE_ARCHIVELOG
To enable archive log mode when creating the database (default: false).
Supported 19.3 onwards.
-v /opt/oracle/oradata
The data volume to use for the database. Has to be writable by the
Unix "oracle" (uid: 54321) user inside the container If omitted the
database will not be persisted over container recreation.
-v /opt/oracle/scripts/startup | /docker-entrypoint-initdb.d/startup
Optional: A volume with custom scripts to be run after database startup.
For further details see the "Running scripts after setup and on
startup" section below.
-v /opt/oracle/scripts/setup | /docker-entrypoint-initdb.d/setup
Optional: A volume with custom scripts to be run after database setup.
For further details see the "Running scripts after setup and on startup"
section below
I believe the colons you see throughout the original code block (with certain exceptions) are there for the definitions (you wouldn’t actually include these in your commands). If you are coming from database development, I suspect some may think, “ahh, bind parameter.” I do not think that is the case here.
You might be asking, in this code block, what the hell am I supposed to be looking at? Well, the container has a “listener”, listening on port 1521. So if I want to connect to the container, I’ll need to “map” to it. I’m not sure if that is what it is called exactly (not a networking guy, don’t claim to be). But the next question is, what is my <host port> (How it is referred to in the code block above)?
Everything matters, and nothing matters
Executing a ping command in my Terminal, to see what my computer’s address is great, but it tells me nothing about the port.
Use ping localhost to see your IP address
So I took to the internet to try to figure out the appropriate port…Honestly, I’ve tried searching but I can’t find anything definitive. In the Podman documentation, I see a lot of reference to port 8080; as in localhost:8080.
Care to review it? Here are some search results using “8080” as the search parameter.
Buried in the docs, there is a brief mention of the port and it can be found in the –publish or -p parameter. The way I understand ports on your local machine is that if you omit the local host information, you shouldn’t have any problems. It will just default to…something. So..it doesn’t matter, nothing matters. It’s all an illusion.
I also reviewed the cURL documentation. I found something in the --connect-to option:
Can you leave it empty?
Aaaand, more port nonsense (if you are having trouble sleeping at night, try reading this):
Okay, with all this out of the way, I can finally start to make some progress (almost there, promise).
Remember, you have to start the Podman Linux virtual machine before you do anything (this is in the instruction steps, so review that first (steps for macOS). This is where the container “lives.” Once the virtual machine is up and running.
Podman virtual machine is ready
I then grabbed the Oracle container. But, since I’m using Podman I needed to modify the run command, like this:
podman run -d -p :1521 --name myoracledb container-registry.oracle.com/database/enterprise:21.3.0.0
REMINDER: Make sure you are logged into the Oracle Container Registry site before you attempt all this!
Assuming you’re still following along, you’ll see something like this in your Terminal:
New container ID
I used the podman ps command to check the status of the container. You should see something like this:
“Starting” and “Healthy” statuses
For several minutes, you’ll continue to see the container status as “starting”. You can do like me and just periodically enter the podman ps command, or you can go do something meaningful with your time. Check back in 10 mins or so. The choice is yours. Either way, you’ll eventually see the status change from “starting” to “healthy”.
The container is healthy and ready
“Healthy” means I now have an Oracle Enterprise database (version 21.3.0.0) running in my Linux virtual machine as a container. I still need to log in with SQLcl, though.
Hold up, I can’t just log into SQLcl. I still have some more setup to do. I need to reset the randomly generated password to one of my choosing. Our instructions tell you to first issue the docker logs + [your database name] command to view your database logs. And from there you should be able to locate it. I couldn’t maybe you can. Let me know if you were able to.
Using the logs command
Since I’m doing this in Podman, that command is slightly modified:
podman logs myoracledb
The printout of that command will appear like this (yours will be very similar). Although I wasn’t able to locate the password, there are still some important pieces of information that you’ll want to review and note.
podman logs [your database name] printout
In this print out you’ll see things like the local host and port information, and the “Global Database Name” and “System Identifier (SID)” can be seen as well. You’ll see where the log files are located (your temporary password can be retrieved from here) and the database version you are running. Finally, you’ll see the message “DATABASE IS READY TO USE!”
Use the included shell script for changing your password
We are this close to logging onto the database. Even though I couldn’t find the temporary password, it doesn’t matter. You have to change your password anyways. If you refer back to the instructions on the Oracle Container Registry page, there is a section entitled “Changing the Default Password for SYS User” and it reads as such (emphasis added):
On the first startup of the container, a random password will be generated for the database if not provided. The user [must] change the password after the database is created and the corresponding container is healthy.
Using the docker exec command, change the password for those accounts by invoking the setPassword.sh script that is found in the container. Note that the container must be running. For example:
Easy enough, and since my container is “healthy” at this point, I can execute this script. But since I’m using Podman, the command will look like this:
And the output of that command will look like this:
Automated password change with the provided Shell script
I guess it worked. As you can see, my new password is password1234 (pleeeeease, do NOT share that with anybody). And at this point, I’m ridiculously close to logging onto this completely containerized Oracle enterprise database. All I need to do now is log on using the same steps as before (in my previous post).
Referring back to the Oracle Container Registry docs, I see the following:
The different login options
NOTE: Remember I'm logging into this container from the outside.
The connect options are cut-off in that image, so let me copy/paste them here. Also, assume where it states “sqlplus” I’ll be connecting with SQLcl. The options are as follows:
Turns out you can just use the port command to discover the container’s port (I’m guessing this is the route the container uses to communicate with my MacBook – it’s all quite muddled at this point).
Here is the command I executed:
podman port myoracledb
And here is what was returned:
Exposing the ports for this network
If you are starting your journey from the MacBook, its address would be 0.0.0.0 with a port of 43073. Data/info flows in and out of that port. And 1521 is a reference to the [bleep blurp 🤖] TCP port at which the Transparent Network Substrate (TNS) Listener is located.
Actually, if you look at the previous output (from the podman logs myoracledb command) you’ll see how all the addresses and ports connect (including the TNS Listener).
TNS Listener information
It's in the logs, how could you not know this!?
Honestly, this is all ludicrous. You shouldn’t know all this, nobody should! It’s too much to retain, but that’s okay, I’m glad you’re still here…toughing it out with me. Once you get past this first big hurdle, I imagine working with containers is very fun.
Here is where I actually logged on (or is it logged into?) to this database with SQLcl. Here’s the command I used:
sql sys/password1234@//localhost:43073/ORCLCDB as sysdba
Which, if you recall is modeled on the original command found in the Oracle Container Registry docs; it looks like this (it’s also a few paragraphs back):
$ sqlplus sys/<your_password>@//localhost:<exposed_port>/<your_SID> as sysdba
NOTE: Exposed port is that where the TNS Listener is located, and the SID is the “System Identifier” – I showed that in the database logs earlier.
And again, I don’t think it matters if you include the localhost port. Here is what the output looked like in my Terminal:
Alright, so finally, I’m in! Next, I tested a SQLcl function to see if it worked as expected. I chose the LOAD function. And just as a refresher on the LOAD function I referred to the SQLcl help for in-context assistance. That help looks like this:
SQLcl Help and LOAD information
Specifically, I am going to test out the “CREATE TABLE” and “LOAD TABLE” function. So I scrolled down to the examples for reference.
Example showing how to create and load a table at the same time
At this point, the commands are pretty straightforward. I decided to use the LOAD NEW command, as seen above.
The beginning of the LOAD...NEW commandAlmost forgot the “NEW“Ready to execute
PRO TIP: You can simply drag the file and drop it into Terminal to get the complete file path.
DON’T forget to include the “NEW” at the end of the command. I forgot it the first time and my load failed. If doesn’t break anything, just a silly mistake.
I hit enter, and if you look at that image with the “Ready to execute” caption, everything worked as expected, here it is a zoomed-in (please excuse the gray shading):
Alright, so I have a brand new table. And if you recall, this was a data set that included the IMDb top 100 highest-rated movies of all time.
IMDb dataset by way of Kaggle
Well in the next few images, I wanted to test some SQL on the new table. I first searched by genre, with the following SQL:
SELECT DISTINCT genre FROM t100movies;
Selecting by the movie genre
Which returns all the distinct matches. Easy enough right? Then (because I like Adventure and Fantasy) I selected only those films that match those criteria, with this SQL statement:
SELECT * FROM t100movies WHERE genre = 'Adventure, Fantasy';
Single quotes and don’t forget your semi-colon 🫡
And once I correctly entered the SQL query, I found a single movie in the top 100 that meets those criteria. I’m actually surprised this one made the top 100 list.
Okay, but there’s just one more thing. The data persists inside the container even after I’ve stopped it. This isn’t necessarily the focus of this article, but I just wanted to demonstrate that even after shutting everything down, the table still exists.
This is true even after completely stopping my Podman container and shutting down the Podman Linux virtual machine.
The process was as follows:
Exited out of the SQLcl application
Stopped the myoracledb container process
Checked to make sure the process was actually stopped
Stopped and then restarted the Podman Linux virtual machine
Restarted the myoracledb container
Executed the same SQL query as before
Exited from the SQLcl application a final time
And if you take a look at all these images (they are numbered in order) you can see all the steps I took to during this little test. Pretty cool, eh!?
1. Exiting out of SQLcl2. Stopping the Podman container3. Stopping the Podman virtual machine4. Restarting the Podman virtual machine5. Restarting the myoracledb container6. Using the same SQL from before7. A final exit from SQLcl
A couple of notes here:
When I restarted the container, it only took about a minute for it to move from a “starting” to a “healthy” status. So I think the first time you start this container it takes a while. Subsequent start-ups, a minute or less.
When you start back up the container, you don’t have to map anything, I believe all those settings are still intact. Either that or I just spent a whole bunch of time networking when I didn’t need to.
And that does bring us to a close. If you’ve made it this far, you understand how you can at least get this container started and log on using SQLcl. Remember you can use Brew to install SQLcl and Podman. And of course, you’ll need to get the container I used (from our Container Registry; you can find it here in the Database category).
Remember, I didn’t do anything with checkpoints (or checkpointing containers) or with volumes. At the time of this article, I wasn’t 100% confident in my approach, so I wanted to exclude it. However, as I understand it, volumes (and their use) are the preferred approach to persisting data for later use (in containers). Just keep that in mind.
Finally, I’m not going to sugarcoat it. This was a grind – it was very tedious and frustrating, so hopefully, you can learn from my mistakes. I’m not claiming to be a developer or an expert in CI/CD. But I can totally see the appeal of having portable containers like this. The barrier to understanding stuff like this is incredibly high, so good luck to you. But hey, if you screw up, don’t worry about it. You can always uninstall and reinstall and try again.
Be sure to leave a comment if you see something amiss or if you have a better approach to something I’ve shown here. And as always…
What follows is a response I sent via Slack to one of our newest UX Designers. She comes to us by way of another sister business unit within Oracle. She was looking for some resources on where to learn/get better acquainted with SQL (Which for a UX and/or UI designer, I think is a really impressive!).
As I was putting this list together, the thought occurred to me, “hey, I think this stuff could be helpful to others outside of Oracle too!” So here we are. What follows (with minor edits) is a list of resources that I’ve found over the past year that have helped me to better understand SQL. Maybe you’ll discover another previously unknown resource.
Resources
In no particular order (seriously, these came to me at random):
What is it? Verbatim, here is the “elevator pitch” from our site:
Learn SQL in this FREE 12-part boot camp. It will help you get started with Oracle Database and SQL. The course is a series of videos to teach you database concepts, interactive SQL tutorials, and quizzes to reinforce the ideas. Complete the course to get your free certificate.
Hey, pretty cool you end up with a free certificate too!
NOTE: You'll need to create an Oracle account first. You can sign-up here.
O’Reilly
O’Reilly Welcome page
Within Oracle, we have access to O’Reilly. You may, too, check internally. This is the second employer I’ve seen where this is available. It’s chock full of digital learning content – videos, tutorials, books, and guides. You can even create “Playlists” for similar topics, here are mine:
My O’Reilly playlists
Live SQL
Oracle Live SQL
Oracle has a browser-based app, Live SQL, where you can learn all sorts of SQL. I don’t learn like this, but others might (I need skill acquisition to be more practical). If you learn through rote, then this is the site for you!
SQL Worksheet in the Database Actions Launchpad
SQL Worksheet via Database Actions
Sign up for one of our OCI Free Tier accounts and create an Autonomous Database (ADB). After that you can get a feel for how Database Actions (aka SQL Developer Web) works and how to interact with your database.
From there, if you want to look at SQL specifically, I would focus on the SQL Worksheet. Once there, you can practice simple SQL queries.
Reader: I don't know your level, so you may already be more familiar with this than me. But it's free, so why not?
LiveLabs
SQL learning in LiveLabs
This is a straightforward and approachable entry point. Simply typing “sql” reveals tons of relevant workshops. LiveLabs home.
Oracle SQL Language Guide
This is the official guide for the current Oracle version 21 database. It would be a good thing to bookmark. But there is so much stuff; you’d want to skip sitting down and reading through it in one sitting.
PL/SQL
This is a PL/SQL language guide. I can only explain PL/SQL as “SQL Advanced.” It’s not better; it is a way to give you more control over when, how, and where to use SQL (my interpretation). Wikipedia does a better job of explaining. You won’t be using this initially. I’m just starting to get into it after a year. But the sooner you can use it, the better!
W3 Schools
Great for many languages (as well as CSS/HTML). It is a memory HOG, though! I don’t know what is happening (probably the ads), but at least on Firefox, your computer’s fans will be working double-time. So get in, get out; otherwise, your computer will slow to a crawl. Link to SQL topic.
Errors/troubleshooting
StackOverflow
Using the error code (or parts of it) as a keyword in StackOverflow works quite well. Like this:
Using a random error code as an example
You can even create Watched Tags to keep up on topics that you are most interested in.
Did you know you can use Homebrew to install Oracle’s SQLcl on Mac? I just realized this about a week ago (always the bridesmaid, never the bride…amirite??).
Homebrew
First you’ll need to install Homebrew (I’m sure there are other ways to install SQLcl, but installing through Homebrew was a breeze).
You can install Homebrew on your Mac by first opening up a new terminal window and typing/entering:
That Shell script should walk you through the setup.
DISCLAIMER: I didn't go that route, but if you follow the directions on the Homebrew site I assume it should work.
If you want a more hands-on approach, visit this site for a complete walk through of setting up your new Mac for application development. You may not need to do everything on that site, but read up on the Homebrew & Cask sections.
Installing SQLcl
I’ve since learned that you are really installing the SQLcl app via Cask (which is included in Homebrew). Cask allows the installation of “large binary files” (see the site from the paragraph above for more details). A list of the current Cask applications available.
We’re giving Pi a run for its money with that semantic versioning…
Once you are all updated with Homebrew, you can then open up a new terminal and enter the following:
brew install sqlcl
As it installs, you’ll see a lot of activity in the terminal window. Once complete, you’ll see something that looks like this (I’ve already installed/reinstalled it tons of times, so there may be some slight difference):
Don’t forget to review the Caveats section!
Caveats
The main things to review are in the “Caveats” section. First, you’ll need Java 11+ or higher for this to work (i.e., connect to an Oracle database). I didn’t realize this, but we give you a command to update to the latest Java version. I wish I had known that, as I spent way too much time figuring out the best way to update.
Upgrading Java through Homebrew
Second, you’ll need to add a new line to your “PATH environment variable”.
New line to be added to your PATH Environment Variable
I understand this, as specific applications will only work if you’ve predefined the locations of their dependencies. You can indicate where your operating system looks for these dependencies by updating the PATH Environment Variable (a separate file; more on this in a second). We have another excellent resource here (it explains PATH and CLASSPATH well).
Locating PATH on Mac
On a Mac, there are a couple of ways you can find PATH.
PRO TIP: PATH export definitions are located in a .zprofile file.
The easiest way (for me) to find this file is by typing/entering in a terminal window:
open .zprofile
LEARN ZSH: Want to learn all there is about zsh , .zshenv, .zprofile, .zshrc or .zlogin? Bookmark this manual for future use.
From there, your .zprofile file will appear in a new window. Mine looks like this:
A look at my .zprofile file.
If you recall from the “Caveats” section, you may need to add a line to your PATH. I’ve already done that; I added a comment for reference (optional, but make sure the comment is preceded with a “#”).
.zprofile file with the new line added.
Remember to save (with CMD + S)! After which, you can close out the window.
Also, it’s a good idea to close any active terminals and open a new one (this way your terminal picks up any changes you’ve made).
You can also perform a check to see what is installed via Homebrew with the following command:
brew list
You’ll see something akin to this (depending on what you have installed):
Use brew list to see current Homebrew installs.
Dive into SQLcl
Okay, now we are ready to explore SQLcl!
DISCLAIMER: I'm not connecting to my database yet (I will be in my next post as I'm just working out the kinks on my Podman setup…containers, baby!).
I’ll keep this next section simple. Begin with a new terminal and type/enter:
sql -h
or
sql -help
You’ll see the following printout:
Help printout.
If you look closely, you’ll see information for Usage 1 and Usage 2.
README: When in doubt, refer to the help!
Usage 1
Usage 1 – great for reviewing in-context help documentation as well as version information.
Usage 1 focus.
Regarding help, I’ve tried the following (they all work):
sql -h
sql -help
sql -Help
sql -H
sql -HELP
HINT: Type/enter exit into the command line to exit the SQLcl help screen.
Using the exit command.
Usage 2
Usage 2 focus.
In Usage 2, you’ll find information for two login options:
Login with a “Connect Identifier”
No logon
The Connect Identifier can be either:
“Net Service Name”
“Easy Connect”
Wut r theez?
I found some information relating to the “Net Service Name” method of connection; you can refer to that here. Be forewarned – there seems to be some configuration required to use the Net Service Name method (I’ve not tested this yet).
Conversely, the Easy Connect Method looks well…easier. I found a good resource here. This was the method I used when experimenting with containers and Podman (blog coming soon!).
Now, if you are like me and want to explore SQLcl (without connecting to an Oracle database), you can log in using the /NOLOG option. Make sure you exit out of the SQLcl help screen first.
Once you’re out, type/enter the following command:
sql /NOLOG
NOTE: Make sure you have a space between the "l" in sql and the "/" of /NOLOG.
Once you hit enter, you should see a screen like this:
Logging in with the /NOLOG option.
Unimpressive, right? Well, allow me to whet your appetite some. From here, you have two more options. Those are:
h
help
Entering h will reveal a history of the most recent shell commands you’ve executed.
Shell command history.
Type/enter help and you’ll; reveal a list of the available SQLcl commands and options. It looks like this:
So. Many. Options.
Pretty cool, eh?
You can take this one step further by typing/entering a topic of interest. Here are a couple random topics I explored (ALIAS and MODELER):
Reviewing the Alias topicReviewing the Modeler topic
Final thoughts
While I have yet to take full advantage of what SQLcl offers, I see the potential time savings for application developers who want to stay in a text editor while coding (without switching to another GUI application).
I’ll include the SQLcl documentation so you have it for reference. But be forewarned we’re updating this document; some instructions may be changed.
And check back in a week or two once I get Podman fully working with one of our Database Containers. I’ll test SQLcl, ORDS, and an Oracle Enterprise database 21.3.x (if you’re curious about our available containers, you can find them here).
Want to learn even more about SQLcl? Check out these helpful resources:
While querying a table (based on this dataset) with SQL, you realize one of your columns uses 3-character ISO Country Codes. However, some of these 3-character codes aren’t countries but geographical regions or groups of countries, in addition to the actual country codes. How can you filter out rows so you are left with the countries only?
Answer
Use the Python Pandas library to scrape ISO country codes and convert the values to one single string. Then use that string as values for a subsequent SQL query (possibly something like this):
SELECT * FROM [your_table]
WHERE country_code IN ([values from the generated list-as-string separated by commas and encased by single / double quotes]);
Code
# Libraries used in this code
from bs4 import BeautifulSoup
import requests
import csv
import pandas as pd
# I found these ISO country codes on the below URL. Pandas makes it easy to read HTML and manipulate it. Very cool!
iso_codes = pd.read_html("https://www.iban.com/country-codes")
# I create a data frame, starting at an index of 0.
df = iso_codes[0]
# But really, all I care about is the 3-digit country code. So I'll make that the df (dataframe) and strip out the index
df = df['Alpha-3 code'].to_string(index=False)
# From here, I'll save this little guy as a text file.
with open("./countries.txt", "w") as f:
f.write(df)
# I'll set up a list. *** This was my approach, but if you find a better way, feel free to comment or adjust. ***
my_list = []
# Then I'll open that text file and read it in.
file = open("./countries.txt", "r")
countries = file.read()
# I need to remove the "new line" identifiers, so I'm doing that here.
my_list = countries.split('\n')
# Once I do that, I can create two new strings. I do this with f-Strings. Great article on using them here: https://realpython.com/python-f-strings/
# I have two options here: one where the codes are contained by single quotes, the other with double quotes. Oracle Autonomous Database likes single quotes, but your DB may differ.
countries_string_single_quotes = ','.join(f"'{x}'" for x in my_list)
countries_string_double_quotes = ','.join(f'"{x}"' for x in my_list)
# From here, I take those strings and save them in a text file. You don't have to do this; you can print and copy/paste the string. But this might be an excellent addition if you want to refer to these later without running all the code.
with open("./countries_as_list_single_quotes.txt", "a") as f:
f.write(countries_string_single_quotes)
with open("./countries_as_list_double_quotes.txt", "a") as f:
f.write(countries_string_double_quotes)
GitHub repo details
You can find the code from this post in my GitHub repository. The repository consists of the following:
The Python code I created for solving this problem
A countries.txt file, which is produced midway through the code (temporary placeholder for later processing)
‘Single quotes’ .txt file – the 3-character ISO Country Codes are formatted as a string. The values are enclosed by single quotes; commas throughout
“Double quotes” .txt file – the 3-character ISO Country Codes are formatted as a string. The values are enclosed by double quotes; commas throughout
I spent most of the morning figuring out how I would go about this, and after some trial and error, I devised a plan. I decided to take the list of ISO Country Codes (which I found here) and use them as values for filtering in a SQL statement (later on in Oracle SQL Developer Web).
After some research, I figured out the proper SQL syntax for a successful query.
SELECT * FROM [your_table]
WHERE country_code IN ([values from the generated list-as-string separated by commas and encased by single / double quotes]);
From there, I knew I needed to work backward on those ISO Country Codes. Meaning I needed to take something that looked like this:
The country code column I’m interested in.Reviewing the HTML for this table, I’m interested in the elements.
And turn it into something more workable. It turns out that grabbing this was pretty straightforward. I’m using Pandas primarily for this exercise, but first, I need to import some libraries:
# Libraries used in this code
from bs4 import BeautifulSoup
import requests
import csv
import pandas as pd
Next, I’ll use Pandas’ read_html function (this feels like cheating, but it’s incredible) to read in the table.
# I found these ISO country codes on the below URL. Pandas makes it easy to read HTML and manipulate it. Very cool!
iso_codes = pd.read_html("https://www.iban.com/country-codes")
# I create a data frame, starting at an index of 0.
df = iso_codes[0]
This is wild, but this is what the printout looks like:
The Pandas read_html() the function is powerful.
If you squint, you can see an “Alpha-2 code” and an “Alpha-3 code” column in the image. From here, I need to isolate the 3-code column. So I reshaped the data frame by making it a single column; dropping the index (this is optional, you could keep the index if you needed it; perhaps you wanted to create a separate table in your database).
# But really, all I care about is the 3-digit country code. So I'll make that the df (dataframe) and strip out the index
df = df['Alpha-3 code'].to_string(index=False)
I’ll save this data frame as a .txt file.
# From here, I'll save this little guy as a text file.
with open("./countries.txt", "w") as f:
f.write(df)
This is only temporary (FYI: this is the only way I could figure out how to do this). It’ll look like this:
The temporary .txt file of 3-character ISO Country Codes.
Next, I take that temporary text file and read it in. I’m going to add it to a list, so I’ll first create the empty list (aptly named “my_list“). I also need to remove the newline characters from the list; otherwise, if I don’t, then when I create my string of values (that comes in the final step), the string will look like this:
The “countries” string with “\n” characters.
I remove the newline characters with this piece of code:
# I need to remove the "new line" identifiers, so I'm doing that here.
my_list = countries.split('\n')
The almost string of values will look like this:
New line characters have now been removed.
I use F-Strings to create the following two strings; countries_strings_single_quotes and countries_strings_double_quotes, respectively. Need to learn about F-Strings (or, more formally, Literal String Interpolation)? No problemo! Check out these three resources:
The code for the F-Strings is below. I loop through my_list and separate the x (the things I’m iterating over) with commas (that’s the join).
# Once I do that, I can create two new strings. I do this with f-Strings. Great article on using them here: https://realpython.com/python-f-strings/
# I have two options here: one where the codes are contained by single quotes, the other with double
# quotes. Oracle Autonomous Database likes single quotes, but your DB may differ.
countries_string_single_quotes = ','.join(f"'{x}'" for x in my_list)
countries_string_double_quotes = ','.join(f'"{x}"' for x in my_list)
The new single quote string.The new double quote string.
And now that I have these two objects (are they called objects??). I’ll save them each as a text file. One file has the 3-character codes surrounded by single quotes, the other with double quotes. The code:
# From here, I take those strings and save them in a text file. You don't have to do this; you can print
# and copy/paste the string. But this might be a nice addition if you want to refer to these later
# without running all the code.
with open("./countries_as_list_single_quotes.txt", "a") as f:
f.write(countries_string_single_quotes)
with open("./countries_as_list_double_quotes.txt", "a") as f:
f.write(countries_string_double_quotes)
The text files look like this now:
The country codes are now presented in one long string. Pretty cool, eh?
SQL time
We have arrived! Let me show you what I can do now!
I took the CSV data from the World Bank and loaded it into my Autonomous Database. Our returning intern Layla put together a video of how to do this; you can check it out here:
Once my table was created, I did a SELECT [columns] FROM. Here you can see my “beginning state”.
At first glance this looks fine.But once you scroll down, you can see all the non-countries and regions.
There are 266 entries; some are countries, and others are not. And if you recall, the original question asked how somebody could filter out the non-countries. Onto that next!
This is the best part. I can take the string I made and use that in a SQL query such as this:
SELECT * from ADMIN.REDDIT_TABLE
WHERE COUNTRY_CODE IN('AFG','ALA','ALB','DZA','ASM','AND','AGO','AIA','ATA',
'ATG','ARG','ARM','ABW','AUS','AUT','AZE','BHS','BHR','BGD','BRB','BLR','BEL',
'BLZ','BEN','BMU','BTN','BOL','BES','BIH','BWA','BVT','BRA','IOT','BRN','BGR',
'BFA','BDI','CPV','KHM','CMR','CAN','CYM','CAF','TCD','CHL','CHN','CXR','CCK',
'COL','COM','COD','COG','COK','CRI','CIV','HRV','CUB','CUW','CYP','CZE','DNK',
'DJI','DMA','DOM','ECU','EGY','SLV','GNQ','ERI','EST','SWZ','ETH','FLK','FRO',
'FJI','FIN','FRA','GUF','PYF','ATF','GAB','GMB','GEO','DEU','GHA','GIB','GRC',
'GRL','GRD','GLP','GUM','GTM','GGY','GIN','GNB','GUY','HTI','HMD','VAT','HND',
'HKG','HUN','ISL','IND','IDN','IRN','IRQ','IRL','IMN','ISR','ITA','JAM','JPN',
'JEY','JOR','KAZ','KEN','KIR','PRK','KOR','KWT','KGZ','LAO','LVA','LBN','LSO',
'LBR','LBY','LIE','LTU','LUX','MAC','MKD','MDG','MWI','MYS','MDV','MLI','MLT',
'MHL','MTQ','MRT','MUS','MYT','MEX','FSM','MDA','MCO','MNG','MNE','MSR','MAR',
'MOZ','MMR','NAM','NRU','NPL','NLD','NCL','NZL','NIC','NER','NGA','NIU','NFK',
'MNP','NOR','OMN','PAK','PLW','PSE','PAN','PNG','PRY','PER','PHL','PCN','POL',
'PRT','PRI','QAT','REU','ROU','RUS','RWA','BLM','SHN','KNA','LCA','MAF','SPM',
'VCT','WSM','SMR','STP','SAU','SEN','SRB','SYC','SLE','SGP','SXM','SVK','SVN',
'SLB','SOM','ZAF','SGS','SSD','ESP','LKA','SDN','SUR','SJM','SWE','CHE','SYR',
'TWN','TJK','TZA','THA','TLS','TGO','TKL','TON','TTO','TUN','TUR','TKM','TCA',
'TUV','UGA','UKR','ARE','GBR','UMI','USA','URY','UZB','VUT','VEN','VNM','VGB',
'VIR','WLF','ESH','YEM','ZMB','ZWE')
ORDER BY COUNTRY_CODE ASC;
Once I execute that SQL statement, I’m left with the countries from that list. I opened up the results in another window so you can see a sample.
SQL query in action – with the new values-as-a-string.Results of the SQL query in another window.
The end
So yeah, that’s it! I don’t know if this was the best way to go about this, but it was fun. I’m curious (if you’ve made it this far), what do you think? How would you go about it? Let me know.
That’s right; I’m back again for yet another installment of this ongoing series dedicated to working with Medium.com story stats. I first introduced this topic in a previous post. Maybe you saw it. If not, you can find it here.
Recap
My end goal was to gather all story stats from my Medium account and place them into my Autonomous Database. I wanted to practice my SQL and see if I could derive insights from the data. Unfortunately, gathering said data is complicated.
Pulling the data down was a breeze once I figured out where to look for these story statistics. I had to decipher what I was looking at in the Medium REST API (I suppose that was somewhat tricky). My search was mostly an exercise in patience (there was a lot of trial and error).
I uploaded a quick video in the previous post. But I’ll embed it here so you can see the process for how I found the specific JSON payload.
Obtaining the raw JSON
Once I found that URL, I saved this JSON as a .json file. The images below show remnants of a JavaScript function captured with the rest of the JSON. I’m no JavaScript expert, so I can’t tell what this function does. But before I load this into my Autonomous Database (I’m using an OCI Free Tier account, you can check it out here if you are curious), it needs to go.
JSON response errorMuch nicer JSON presentation
README
I am pointing out a few things that may seem convoluted and unnecessary here. Please take the time to read this section so you can better understand my madness.
FIRST: Yes, you can manually remove the [presumably] JavaScript saved along with the primary JSON payload (see above paragraphs). I'm showing how to do this in Python as a practical exercise. But I'm also leaving open the opportunity for future automation (as it pertains to cleaning data).
SECOND: When it comes to the Pandas data frame steps, of course, you could do all this in Excel, Numbers, or Sheets! Again, the idea here is to show you how I can clean and process this in Python. Sometimes doing things like this in Excel, Numbers, and Sheets is impossible (thinking about enterprise security here).
THIRD: Admittedly, the date-time conversion is hilarious and convoluted. Of course, I could do this in a spreadsheet application. That's not the point. I was showing the function practically and setting myself up for potential future automation.
FOURTH: I'll be the first to admit that the JSON > TXT > JSON > CSV file conversion is comical. So if you have any suggestions, leave a comment here or on my GitHub repository (I'll link below), and I'll attribute you!
The code
Explaining the code in context, with embedded comments, will be most illuminating.
I’ve named everything in the code as literally as possible. In production, this feels like it might be impractical; however, there is no question about what the hell the code is doing! Being more literal is ideal for debugging and code maintenance.
Here is the entire code block (so CTRL+C/CTRL+V to your heart’s content 😘). I’ll still break this down into discrete sections and review them.
import csv
import json
import pandas as pd
import datetime
from pathlib import Path
# You'll first need to sign in to your account, then you can access this URL without issues:
# https://medium.com/@chrishoina/stats/total/1548525600000/1668776608433
# NOTES:
# Replace the "@chrishoina" with your username
# The two numbers you see are Unix Epochs; you can modify those as # needed; in my case, I
# wanted to see the following:
# * 1548525600000 - At the time of this post, this seems to be
# whenever your first post was published or when
# you first created a Medium account. In this case, for me, this
# was Sat, Jan/26/2019, 6:00:00PM - GMT
# * 1665670606216 - You shouldn't need to change this since it will # just default to the current date.
# For the conversion, I an Epoch Converter tool I found online: https://www.epochconverter.com/
# Step 1 - Convert this to a,(.txt) file
p = Path("/Users/choina/Documents/socialstats/1668776608433.json")
p.rename(p.with_suffix('.txt'))
# Step 2 - "read" in that text file, and remove those pesky
# characters/artifacts from position 0 through position 15.
# I'm only retaining the JSON payload from position 16 onward.
with open("/Users/choina/Documents/socialstats/1668776608433.txt", "r") as f:
stats_in_text_file_format = f.read()
# This [16:] essentially means grabbing everything in this range. Since
# there is nothing after the colon; it will just default to the end (which is
# what I want in this case).
cleansed_stats_from_txt_file = stats_in_text_file_format[16:]
print(cleansed_stats_from_txt_file)
# This took me a day to figure out, but this text file needs to be encoded
# properly, so I can save it as a JSON file (which is about to happen). I
# always need to remember this, but I know that the json.dumps = dump
# string, which json.dump = dump object. There is a difference, I'm not
# the expert, but the docs were helpful.
json.dumps(cleansed_stats_from_txt_file)
# Step 3 - Here, I create a new file, then indicate we will "w"rite to it. I take the
# progress from Step 2 and apply it here.
with open('medium_stats_ready_for_pandas.json', 'w') as f:
f.write(cleansed_stats_from_txt_file)
# Step 4 - Onto Pandas! We've already imported the pandas library as "pd."
# We first create a data frame and name the columns. I kept the names
# very similar to avoid confusion. I feared that timestampMs might be a
# reserved word in Oracle DB or too close, so I renamed it.
df = pd.DataFrame(columns=['USERID', 'FLAGGEDSPAM', 'STATSDATE', 'UPVOTES', 'READS', 'VIEWS', 'CLAPS', 'SUBSCRIBERS'])
with open("/Users/choina/Documents/socialstats/medium_stats_ready_for_pandas.json", "r") as f:
data = json.load(f)
data = data['payload']['value']
print(data)
for i in range(0, len(data)):
df.loc[i] = [data[i]['userId'], data[i]['flaggedSpam'], data[i]['timestampMs'], data[i]['upvotes'], data[i]['reads'], data[i]['views'], data[i]['claps'], data[i]['updateNotificationSubscribers']]
df['STATSDATE'] = pd.to_datetime(df['STATSDATE'], unit="ms")
print(df.columns)
# Step 5 - use the Pandas' df.to_csv function and save the data frame as
# a CSV file
with open("medium_stats_ready_for_database_update.csv", "w") as f:
df.to_csv(f, index=False, header=True)
I used several Python libraries I use for this script:
p = Path("/Users/choina/Documents/socialstats/1668776608433.json")
p.rename(p.with_suffix('.txt')
Pathlib allows you to assign the file’s path to “p”. From there, I changed the .json file extension to a .txt extension.
Note: Again, I'm sure there is a better way to do this, so if you're reading, leave a comment here or on my GitHub repository so I can attribute it to you 🙃.
The before and after of what this step looks like this:
JSONbeforeTXTafter
With that out of the way, I needed to remove that JavaScript “prefix” in the file. I do this in Step 2 (I got so fancy that I probably reached diminishing returns). My approach works, and I can repurpose this for other applications too!
Step 2:
# Step 2 - "read" in that text file, and remove those pesky
# characters/artifacts from position 0 through position 15. Or in other
# words, you'll retain everything from position 16 onward because that's
# where the actual JSON payload is.
with open("/Users/choina/Documents/socialstats/1668776608433.txt", "r") as f:
stats_in_text_file_format = f.read()
# This [16:] essentially means grabbing everything in this range. Since
# there is nothing after the colon; it will just default to the end (which is
# what I want in this case).
cleansed_stats_from_txt_file = stats_in_text_file_format[16:]
print(cleansed_stats_from_txt_file)
# This took me a day to figure out, but this text file needs to be
# appropriately encoded to save as a JSON file (which is about to
# happen). I always forget the difference between "dump" and "dumps";
# json.dumps = dump string, whereas json.dump = dump object. There is
# a difference, I'm not the expert, but the docs were helpful (you should
# read them).
json.dumps(cleansed_stats_from_txt_file)
I needed to remove these remnants from the Medium JSON response
While this initially came through as a JSON payload, those first 0-15 characters had to go.
FULL DISCLAIMER: I couldn't figure out how to get rid of this while it was still a JSON file hence why I converted this to a text file (this was the only way I could figure it out).
I captured position 16 to infinity (or the end of the file, whichever occurs first), then I re-encoded the file as JSON (I interpreted this as “something the target machine can read and understand as JSON“).
OPEN SEASON: CompSci folks, please roast me in the comments if I'm wrong.
Step 3
# Step 3 - I create a new file, then I'll "w"rite to it. I took the result from Step 2 and applied it here.
with open('medium_stats_ready_for_pandas.json', 'w') as f:
f.write(cleansed_stats_from_txt_file)
I’m still at the data-wrangling portion of this journey, but I’m getting close to the end. I’ll create a new JSON file, take the parts of the (freshly encoded) text file I need, and then save them as that new JSON file.
Step 4
# Step 4 - Onto Pandas! We've already imported the pandas library as "pd"
# I first create a data frame and name the columns. I kept the names
# similar to avoid confusion. I feared that timestampMs might be a
# reserved word in Oracle DB or too close, so I renamed it.
df = pd.DataFrame(columns=['USERID', 'FLAGGEDSPAM', 'STATSDATE', 'UPVOTES', 'READS', 'VIEWS', 'CLAPS', 'SUBSCRIBERS'])
with open("/Users/choina/Documents/socialstats/medium_stats_ready_for_pandas.json", "r") as f:
data = json.load(f)
data = data['payload']['value']
print(data)
for i in range(0, len(data)):
df.loc[i] = [data[i]['userId'], data[i]['flaggedSpam'], data[i]['timestampMs'], data[i]['upvotes'],
data[i]['reads'], data[i]['views'], data[i]['claps'], data[i]['updateNotificationSubscribers']]
df['STATSDATE'] = pd.to_datetime(df['STATSDATE'], unit="ms")
print(df.columns)
I won’t teach Pandas (and honestly, you do NOT want me to be the one to teach you Pandas), but I’ll do my best to explain my process. I first created the structure of my data frame (“df” in this case). And then, I named all the column headers (these can be anything, but I kept them very close to the ones found in the original JSON payload).
I then opened the newly-saved JSON file and extracted what I needed.
NOTE: I got stuck here for about a day and a half, so let me explain this part.
The data['payload']['value'] refers to the key and value in this particular {key: value} pair. This approach allowed me to grab all the values of “value“. This image explains what I started with (on the left) and what I ended up with (on the right).
The before and after JSON payload
You’ll notice a {"success": true} key: value pair. With this method, I removed that pair and shed others at the end of the JSON payload.
Removing a great deal of trash
I can’t take credit for organically coming up with this next part; Kidson on YouTube is my savior. I’d watch this video to understand what is happening in this piece of code entirely:
for i in range(0, len(data)):
df.loc[i] = [data[i]['userId'], data[i]['flaggedSpam'], data[i]['timestampMs'], data[i]['upvotes'],
data[i]['reads'], data[i]['views'], data[i]['claps'], data[i]['updateNotificationSubscribers']]
In short, you take the values from the columns in the JSON file (above) and then put them into the column locations named in this piece of code:
For instance, the "userId" values in the JSON file will all go into the 'USERID' column in the Pandas data frame. And the same thing will happen for the other values and associated (Pandas data frame) columns.
Finally, I changed the date (which, if you recall, is still in this Epoch format) with the Datetime library to a more friendly, readable date. Using this code:
with open("medium_stats_ready_for_database_update.csv", "w") as f:
df.to_csv(f, index=False, header=True)
I’m at the home stretch now. I take everything I’ve done in Pandas and save it as a CSV file. I wanted to keep the headers but ditch any indexing. The clean CSV file will look like this:
Cleaned, tidy CSV ready for Data Load via SQL Developer Web
Step 6
Lastly, I logged into SQL Developer Web and clicked the new Data Load button (introduced in Oracle REST Data Services version 22.3) to upload the CSV file into a new table. The Autonomous Database automatically infers column names and data types. I slightly modified the "statsdate" column (honestly, I could have left it alone, but it was easy enough to change).
Before and After
And that’s it! Once uploaded, I can compare what I did previously to what I have achieved most recently. And both ways are correct. For instance, depending on your requirements, you can retain the JSON payload as a CLOB (as seen in the first image) or a more traditional table format (as seen in the second image).
Medium stats as a CLOBMedium stats in a typical table format
Wrap up
If you’ve made it this far, congrats! You should now have two ways to store Medium stats data in a table (that lives in the Oracle Autonomous Database) either as:
a CLOB
an OG table
And if you’d like to review the code, you can find it here.
I feel so silly for posting this because you’ll quickly realize that I will have to leave things unfinished for now. But I was so excited that I got something to work, that I had to share!
If you’ve been following along, you know you can always find me here. But I do try my best to cross-post on other channels as well:
But given that everything I do supports the development community, audience statistics are always crucial to me. Because of this, I’ll periodically review my stats on this site and the others to get a feel for the most popular topics.
I even did a RegEx post a while back that was pretty popular too. Thankfully it wasn’t that popular, as it pained me to work through Regular Expressions.
I can quickly review site statistics on this blog, but other places, like Medium, are more challenging to decipher. Of course, you can download your Audience stats, but sadly not your Story stats 😐.
Audience stats download, but no Story stats download.
Undeterred, I wanted to see if it was somehow possible to acquire my Story stats. And it is possible, in a way…
Show and tell
If after you log into your Medium account, navigate to your stats page, open up the developer tools in your browser and navigate to your “Console.” From there, reload the page and simply observe all the traffic.
You’ll see a bunch of requests:
GET
POST
OPTION (honestly, I’ve no idea what this is, but I also haven’t looked into it yet)
My thought was that the stats content was produced through (or by) one of these API requests. So yes, I (one at a time) expanded every request and reviewed the Response Body of each request. I did that until I found something useful. And after a few minutes, there it was:
The magic GET request.
I confirmed I had struck gold by taking this URL, placing it in a new browser window, and hitting Enter. And after selecting “Raw Data,” I saw this:
Double-checking the raw JSON.
Indeed, we see my Story stats. But the final two paths in the URL made no sense to me.
The paths looked similar; I had no choice but to activate Turing Mode™.
I could see these numbers were similar, so I lined them up in my text editor and saw that they shared the same 166 prefixes. I don’t know much about machine-readable code, but since what was appearing on my screen was the last 30 days, I thought this might be some sort of date. But I’d never seen anything like this, so I wasn’t 100% sure.
Unix Time Stamps
After about 20 mins of searching and almost giving up, I found something in our Oracle docs (a MySQL reference guide of all places) that referenced Unix Time Stamps. Eureka!
About Unix time stamps in the Oracle MySQL docs.
Success, I’d found it. So I searched for a “Unix time stamp calculator” and plugged in the numbers. My hunch was correct; it was indeed the last thirty days!
Verifying the Unix Time Stamp.
So now I’m wondering if I change that leading date in the GET request will it allow me to grab all my story statistics from January 2022 till now? Oh, hell yeah, it will!
All my Story stats from Jan 2022 to the present.
End of the line
Right, so here is where I have to leave it open-ended. I had a finite amount of time to work on this today, but what I’d like to do is see if I can authenticate with Basic Authentication into my Medium account. And at least get a 200 Response Code. Oh wait, I already did that!?
Getting that sweet, sweet 200 Response Code.
And now the Python code!
import requests
import json
from requests.auth import HTTPBasicAuth
url = "https://medium.com/m/signin"
# I found this to work even if I typically sign on through
# the Google Single-sign-on. I just used the same email/password
# I do when I login directly to google (Gmail).
user = "[Your login/email]"
password = "[Your password]"
r = requests.get(url, auth=HTTPBasicAuth(user, password))
print(r)
# I found this URL in the console but then removed everything after
# the query string (the "?"), and used that for the requests URL
# "/m/signin?operation=login&redirect=https%3A%2F%2Fmedium.com%2F&source=--------------------------lo_home_nav-----------"
You’re probably wondering how I found the correct URL for the Medium login page. Easy, I trolled the Console until I found the correct URL. This one was a little tricky, but I got it to work after some adjusting. I initially found this:
And since I thought everything after that “?” was an optional querystring, I just removed it and added the relevant parts to Medium’s base URL to get this:
If I want to keep it as is, I know I can load the JSON with a cURL command and an ORDS Batch Load API with ease. I dropped this into my Autonomous Database (Data Load) to see what it would look like:
My CLOB.
We do something very similar in the Oracle LiveLabs workshop (I just wrote about it here). You can access the workshop here!
I’ll have a follow-up to this. But for now, this is the direction I am headed. If you are reading this, and want to see more content like this, let me know! Leave a comment, retweet, like, whatever. So that I know I’m not developing carpal tunnel for no reason 🤣.
Recently Jeff and I were invited by the Oracle Developers and Developer Relations teams to do a walkthrough of a LiveLabs workshop, “How to Build Powerful and Secure REST APIs for Your Oracle Autonomous Database.”
We spent about 90 minutes moving through selected labs in the workshop. Luckily they recorded it for us; you can watch it in all its glory here.
If that video piques your interest, I encourage you to complete the workshop since it provides an excellent overview of Oracle REST Data Services APIs — specifically when working in Database Actions (in the Oracle Autonomous Database).
About the workshop
Labs 1, 2, and 7 are common across many workshops. These were our focus.
The workshop consists of seven labs, but labs 3-6 were the main focus.
Two approaches to REST-enabling your Oracle database objects.
We also wanted to highlight the two ways a user could create Oracle REST APIs in Database Actions (formerly SQL Developer Web). You can jump right in with auto-REST enabling or get creative by building your Resource Modules > Templates > Handlers.
Workshop highlights
I won’t walk through the labs in detail here, but what I will do is highlight areas that:
Were cool/worth revisiting, or
Have (or continue to) helped speed up my productivity in Database Actions (and through association with the Autonomous Database)
The videos are queued up to the related topic.
Lab 3
Lab 3 walks you through connecting to an Autonomous Database with Database Actions. From there, you create a table from a CSV file. And finally, you’ll auto-REST enable the table with simple mouse clicks.
Data Loading
I’ve found no less than three GUI-based ways to load data in Database Actions.
Auto-REST enabling
We are using mouse clicks for auto-REST enabling database objects in the Oracle Autonomous Database.
Show Code toggle
The new “Show Code” toggle switch in Database Actions.
This feature isn’t limited to the SQL Worksheet; it's found across Database Actions!
cURL command options for your environment
cURL commands now provide Power Shell, Command Prompt, and Bash examples.
Lab 4
Lab 4 walks you through using a Batch Load API for loading two million+ rows into the table you previously created (in Lab 3). We also make a SQL procedure and later use PL/SQL to simulate a REST API call to the table.
We briefly discussed the Cloud Shell and Code Editor (both in Oracle Cloud Infrastructure). Click the links to learn more, they are free and included in your OCI tenancy 😀.
A crash course on query parameters
Jeff has a helpful article here (one I reference A LOT).
You can review our docs here (we mention it in several areas).
Graduating from auto-REST
A short discussion on when and why you may want to move away from auto-REST-enabled Oracle APIs to more customized Oracle REST APIs.
Lab 5
In Lab 5, you use Database Actions and the REST console to build a REST API using a parameterized PL/SQL procedure and SQL statement. We do this manually in the previous lab but then REST-enable it here (this is a continuation and refinement of the last lab).
This continues to confound me, so if you are in the same boat as me and you want me to do some more dedicated posts on this, let me know!
Lab 6
The goal of this lab was to educate you on Roles, Privileges, and OAuth 2.0 Client Authentication. Unfortunately, we ran out of time and had to speed through this final section. However, I did show off some of the OpenAPI functions within Database Actions.
OpenAPI Specifications
Specifically, we reviewed how you can view your Resource Modules in the OpenAPI view (displayed as a Swagger UI implementation). And view/execute handlers to observe their responses.
We also mentioned how you can export a Resource Module in either PL/SQL code or the OpenAPI JSON code.
I suspect you should be all set to complete this workshop (located here). But why stop the fun there? We have some other LiveLabs workshops that might interest you, too. You should check them out!
The last workshop on the list is our newest one! So if you do attempt it, feel free to create an issue for enhancements (or if anything is unclear and needs updating) on my GitHub repository 😊!
It was bugging me that I couldn’t perform a simple Python POST request to an ORDS REST-enabled table.
This one actually…
I don’t mean to convey that this isn’t possible. Up until very recently, I wasn’t able to do this. Luckily I had a few hours free, so I took to the docs to do some reading. And wouldn’t you know it, like most things in tech, reading the documentation was a practical and valuable use of my time.
Side note
Let me pause here for a minute or two. I should add this disclaimer that none of what I share here uses OAuth 2.0 authentication. Of course, I wish it did, but I’m just not there yet (technical proficiency). In the future, I’d like to update this with security in mind1:
The other thing I’ll mention is that I’m going to include the SQL for creating this table along with all the Python code in my GitHub repo (I’ll also add any code updates to my repo! will also add any updates I make to this code to the repo!).
Also, did you know that saving DDL with Database Actions is just a mouse click away?
Right-click an object in Database Actions > save DDL to a SQL Worksheet or File.DDL for creating this example table.
New User speed run
This section is a bit of an aside, but I also created a new Python Developer user in one of my Autonomous Databases. It’s straightforward to do as the admin. Here is a “speed run”:
POST haste
After creating my new user, I created a “Python_Post” table. Super imaginative, right? And I kept things simple, naming the four columns (wait for it): "col1", "col2", "col3", and "col4".
Damn…he actually did that.
Cheat codes
I auto-REST enabled my table and reviewed a Bash cURL command so I could remind myself of the expected data (aka payload).
A Bash cURL command for POST requests; can be used as later reference in your editor.
I’ve noticed that if I don’t specify a ‘rowid’ the Autonomous Database automatically does this. SQL-newbies (like me) might appreciate this since we still don’t know all the best practices for database design (or DDL, for that matter)!
My process might differ from yours, but I’ve used the cURL commands in Database Actions as a cheat. I’ve been copying/pasting this cURL command into my working Python file, so I don’t have to switch screens. Additionally, it helps to remind me what the {‘key’:‘value’} pairs are (even though I just created all this, I STILL can’t remember what I did).
In this case, I’m referencing a POST request, but you could do this for the other HTTPS methods too:
GET ALL
GET
POST
BATCH LOAD
PUT
DELETE
Moving on…
I could omit the ‘rowid’ when making these POST requests. I don’t know if this is typical when working with databases, but this seems to work consistently (at least with the testing I did) with Python and the Requests library.
If you weren’t taken aback by my imaginative table name and column names, then get ready because I’m about to blow your mind with this next bit. I created a payload for each of these POST request variations:
And I’m just going to be as literal as possible to avoid confusion…is it lame? Yes. Does it work? Undecided, you tell me.
In case you didn’t catch it, they were: payload1, payload2, and payload3.
On feedback…
I also included some feedback for myself. And I feel like this is a good practice because if you successfully POST something, how would you know? Conversely, the same would be true if you unsuccessfully POSTed something. If I were smart, I’d design a REST API in Database Actions that automatically includes some feedback. But I am not.
If you want to read about this Implicit Parameter and others, click here.
Luckily, the Python Requests library includes various feedback methods. I’ve included the following in my Python file:
raise_for_status() – this will display the error message (if applicable); displays "None" if the request was successful
headers – returns the server’s response headers as a Python dictionary
Different payloads
My Python code is simple enough. It looks more than it is because I include three POST request variations. I’ve also tested payloads, and it seems like we can send a "payload" as:
Oracle REST Data Services has a ton of documentation on filtering with query parameters… I’m still learning about it, but they are powerful. Check them out here.
You can even send payloads consisting of nested items/objects, too (e.g., an array or list as one of your values). I’m sure this violates normalization rules, but it’s possible. In my case, I didn’t do this; I just stuck to my four columns.
Executing the code
After I executed the code, I received the following feedback for these POST requests:
If you squint, you can see three separate responses. I’ve only boxed one, but you should be able to point out the other two.
You should see:
a 201 status code, which indicates “the request has been fulfilled and has resulted in one or more new resources being created2.
None – which if there were a 400 error, that would show me the error message
While in VS Code, the POSTrequests appeared successful. But since I had access, I logged into Database Actions and manually inspected my table. Success!
Reviewing the new updates in Database Actions.
And that’s it…zero to POST request in my Oracle Autonomous Database + ORDS in no time!
Was this helpful?
I have this code in my GitHub repository, so feel free to fork, download, or add comments. But I’ll include here too:
And if you are still here — was this helpful? Want to see more or something else that is Python + ORDS-related? Let me know here in the comments, on Twitter, or by email!
