How to Use JSON, SODA, Python and Oracle Autonomous JSON Database – Part 3

This is the third and final installment in our 3 part series. In the first post we saw how we can connect a Python application to an Oracle database and then how to store and access JSON data using Python.

In the second post we introduced you to SODA. We looked at how working with Collections and Documents in their native format can be done without using SQL all with very little effort.

In this post we will use an Oracle Autonomous JSON Database as our document store. If you are not familiar with the Autonomous JSON Database and all of its benefits here’s a short video by Juan Loaiza of Oracle to get you up to speed…

Video by Juan Loaiza Oracle SVP Development

1. Provision an Autonomous JSON Data store

The first step for us start with is to provision an Autonomous JSON Datastore. You can skip this step if you have already have an Autonomous JSON Datastore that you can use. Assuming you’re new to this offering, let’s demonstrate the process…

Keep in mind Oracle offers an Always-Free account. So this can be done without spending a dime: https://www.oracle.com/cloud/free/

We have now provisioned our Autonomous JSON store, let’s go ahead and access it.

In the previous posts for this series we used a database user called “SCOTT”. To keeps things straightforward, let’s continue to use Scott as our database user. The password rules for the Autonomous JSON Data store are stricter than for our local database, so our password will need to address these rules.

We will create the user as follows:

create user SCOTT identified by <My Password> ; 
grant DWROLE, UNLIMITED TABLESPACE to SCOTT ; 

We will grant access to the SQLDeveloper Web console to user SCOTT too:

 BEGIN 
   ORDS.enable_schema( p_enabled => TRUE, 
                       p_schema => 'SCOTT', 
                       p_url_mapping_type => 'BASE_PATH', 
                       p_url_mapping_pattern => 'scott', 
                       p_auto_rest_auth => FALSE ) ; 
COMMIT; 
END; 
/

The ORDS.ENABLE_SCHEMA step above is only required if you wish to use the SQLDeveloperWeb console with SCOTT. If you are planning to connect to the JSON Datastore with SQL Developer desktop client or any other Oracle tool then this step is not necessary.

One of the neat features about using the Oracle cloud is that all data is encrypted, whether in motion or at rest. This is no different with the Autonomous JSON Data storeb, data is encrypted using TLS 1.2 encryption and a wallet is required for authentication. Don’t worry, this is all taken care of for you out-of-the-box and there is very little effort required to get started.

Oracle provides a wallet containing your connection certificate as well as additional connection information to connect to your data store, so all you need to do is download the wallet and point your application to use the credentials. The following viewlet demonstrates how to download your wallet:

If we were planning to use SQLDeveloper to connect to our Autonomous JSON data store we would simply need to point to the downloaded zip file and let SQLDeveloper take care of retrieving the files it needs for its connections from inside the file. It can do this without the need to unzip the file.

In our case we will be connecting a Python program to the Autonomous JSON database. So we do need to unzip the file into a directory to access it’s contents.

This is a one time configuration operation that sets you up for future use, so it is a good idea to place the unzipped files in a directory location that can be referenced by your application. If you are already using an Oracle client with a sqlnet directory created, you should place this in it’s own separate location so that you do not overwrite any of the existing files like your tnsnames.ora or sqlnet.ora files.

In our example we are creating a new directory called “Wallet_AJDBSEANS” and will place the contents of the zip file there in one operation.

3. Set up the connection to use the Wallet credentials

We are now ready to move onto the next step, beginning with modifying the sqlnet.ora file located in the unzipped directory.

The first line in the sqlnet.ora file contains:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))

Simply replace the DIRECTORY entry with the directory location that we unzipped our wallet into. In our case it is:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/oracle/Python_Demo/Wallet_AJDBSEANS")))

For the next file we will not be modifying it, but instead just taking a look at it: tnsnames.ora

We need to find out what the service name to connect to the Autonomous JSON store is. You will see that there are several service-names that provide different resource consumption levels that we can choose from. For our setup we are going with the “AJDBSEANS_TP” service which provides us with a profile suitable for “Transaction Processing” workloads, hence the “_TP” suffix.

The final step we need to perform to use the wallet is to set a TNS_ADMIN environment variable to point to our wallet directory. This can be easily set for the user running the program as follows:

export TNS_ADMIN=/home/oracle/Python_Demo/Wallet_AJDBSEANS

But if we want to remove that external dependency, we can add this directly to our python program using os.environ. In our case, for added portability of our program we will set this in our connection file: myConnectInfo.py

While we’re at it we’ll also go ahead and update the password and the DSN to connect directly to our Autonomous JSON Datastore.

With those updates made we can go ahead and rerun our python application as before. You will see the database version displayed when we run this is different. The Autonomous JSON database is running version 19.5.0.0.0

We can also log into SQLDeveloper Web Console from our Autonomous JSON Database and query the collection there:

4. But wait there’s more… Introducing JSON_DATAGUIDE

Before we call it quits on this post, let’s take a quick look at yet one more of the many powerful tools provided for working with JSON in the Oracle Database.

Please note this function does not require SODA, you can use JSON_DATAGUIDE with regular JSON without using SODA.

This time we will use the JSON_DATAGUIDE function. This function provides you with the ability to describe the data stored within the JSON document. This is extremely useful when you are dealing with a schemaless design that the database user may have no idea of what the application is parsing, as is the case here, when using SODA.

In the example below, we are using JSON_DATAGUIDE to create a view, that we’ve called “auto_view”, of the SODA collection. Once we have a view defined we can then go ahead and run regular SQL operations on the document contents within the collection.

declare dguide clob ; 

begin 
   select json_dataguide(json_document, DBMS_JSON.FORMAT_HIERARCHICAL) 
   into   dguide 
   from   sodacollection ; 
 
   dbms_json.create_view('auto_view', 'sodacollection', 'json_document', dguide ) ; 
 
end ; 
/

We can describe the view too:

DESC auto_view

…and now we know what the key-values are, we can run a regular SQL Query against the view…

select  "name", "infraclass", "family", "venomous" 
from     auto_view 
order by 1,2 
/

Here’s what it looks like when we run this in SQLDeveloper Web:

As with the previous posts in this series, code samples from this post can be downloaded from Github: https://github.com/seantstacey/SODA