In this 3 part series we will explore using JSON and SODA with Oracle. We will start with a local Database and move to the Autonomous JSON Database. The main goal here is to demonstrate how easy it is to use the Oracle Database to store JSON collections and documents. In this first part we’ll see what the steps are to use the Oracle Database with Python. We will then take a look at creating and storing JSON data in a local Oracle Database.
Note: All code samples used in this post can be downloaded from Github: https://github.com/seantstacey/SODA
If you’ve not done so already you will need to install the cx_Oracle module. cx_Oracle is an open source Python extension module that enables you to connect to an Oracle database from Python. This is extremely simple to do as demonstrated below.
You may have noticed I am using Python3 so I am able to use the latest version of cx_Oracle. If you are using an older version of Python you will need to use an older version of cx_Oracle. Also, should you encounter any problems with the installation, or would like to know more about the cx_Oracle module, the documentation can be accessed here:
Documentation for performing this installation can be found here- https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html
STEP 1. Using cx_Oracle with Python
We are now ready to run a quick test by connecting to an Oracle database from Python. To keep things simple in this example I will connect using the database user- scott/tiger to connect to a local pluggable database called- PDB1. Don’t worry, we’ll explore more on connection models including keeping your password under wraps later. At the end of the script, the last thing we need to do, is close the database connection.
But right now, to get started, I just want to demonstrate how easy it is to get up and running with python and cx_Oracle. Take note I am not running any JSON operations yet. We’ll get to that soon.
Here’s a short script I have written for the demo – I have called it step-one.py :
…and here’s the results when we run the script:
STEP 2. A Simple Approach to Obscuring the Password
Okay so at this stage we are able to use the cx_Oracle module to connect to a local pluggable database but we are breaking some fundamental rules when it comes to managing password and user accounts. A better approach is to separate the connection information from our program and this can be done using a separate script to store the database connection information. This way we can use a single place to manage our connectivity for multiple scripts and obscure the connection information from our Python code.
The connection information can be stored in a configuration file which we can then import into our Python script –
I have called the file myConnectionInfo.py and it can now be imported into our script which is called: step-two.py. You will notice that I have used the full connect string as it would typically appear in a tnsnames.ora file. But this could just as easily have been entered with only the net service name itself used in conjunction with a TNS_ADMIN environment variable to reference the network settings. If you want to see a sample of that method, take a look at Part 3 of this series where we connect to an Autonomous JSON Database.
There are other settings you can also use in this file for example: connection pooling, transparent application failover, etc. This is documented here: https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html
Now that we know how to connect our python script to a local database, we’re now ready to get started with using JSON.
STEP 3. Getting started with JSON
In our first example we will see how easy it is to create an Oracle table with a JSON document from python and then to query the table containing the JSON data.
I have created the following python program called step-three.py
After opening a connection to the database specified in MyConnectInfo, we create a table called TEST_JSON with 2 columns: ID and JSON_DATA. The JSON document is being stored as an Oracle CLOB in this example and we are using a check constraint to ensure the data inserted meets the JSON format. The check constraint is completely optional.
We then insert a single row containing a JSON document into the table using SQL. Once the row is inserted and committed, we query the row using a SQL select statement. Our final query in the program queries the table using the JSON_Value function to a single element from document- the firstName.
Here’s the output we see when we run step-three.py
Notice that in our first query, the contents of the JSON document are un-readable. But we are able to view the contents using the json_value function.
We can also query the table we just created in SQLPlus to see how things look from the view point of a database user.
In the example above we’re using the json_serialize function on the document to output the contents of the json document. We opted to return the results using pretty format to display the output in a more readable format.
SQLDeveloper does a better job of handling the output of the json_document, however the pretty formatting operation does not work today. Here’s a clip of how the newly created object appears in SQLDeveloper-
That ends our first part in this series. So far I think you will agree, we have only just dipped our toes in the water when it comes to using JSON with Oracle and Python.
In the next part of this series we will shift gears and move onto working with SODA…
The sample code used in this blog entry is available for download from Github: https://github.com/seantstacey/SODA
