Python Developer Track for Oracle JSON and Duality Views - Part 2: First steps with Oracle and Python

In the previous blog, we had installed and configured the Oracle Database 23c Developer free and Python 3.11. In this blog, we are going to start learning how to use Python with JSON documents at the Oracle Database 23c.

During this workshop, we mainly are going to use SODA API. Simple Oracle Document Access (SODA) is a set of NoSQL-style APIs that let you create and store collections of documents (in particular JSON) in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the documents are stored in the database. SODA makes it extremely easy to work with JSON and speed up our development.

In this part, we are going to connect to the Oracle Database 23c, create a few collections to store data, delete some of them and also list the collections available.

First, we need to import the Oracle Database driver for Python: oracledb

import oracledb

Now we are going to learn a new concept for the oracledb driver. There are two ways of using it: thin driver or thick driver. Depending on which features you want to use of the Oracle Database, you should use one or the other. If you are using the thin driver, there is nothing else to configure. In our case, for using the SODA APIs, we need the thick driver.

In order to use the thick driver, we need to call the following function which will load the Oracle Client libraries. With the Oracle 23c Developer Free, the client is automatically installed and we had configured the environment variables in the first part. With this configuration already done, we can now call the function:

oracledb.init_oracle_client()

If you want to know which features are available for thin and thick driver, you can find them at the following link:https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_a.html#featuresummary

The next step is to define and establish the connection. We are going to use the user we created at the first blog and we are going to connect to the PDB created automatically during the installation called FREEPDB1:

connection = oracledb.connect(user="myapp",password="PassworD123##",dsn="localhost:1521/FREEPDB1")
soda = connection.getSodaDatabase()

Now let's create your two first collections. Once created, we are going to call getCollectionNames() to see all the available collections:

collection1 =soda.createCollection("my_first_collection")
collection2= soda.createCollection("my_second_collection")
list=soda.getCollectionNames()
print(list)

The output should be the following:

['my_first_collection', 'my_second_collection']
>>>

Now let's drop the second collection and list all the collections again:

collection2.drop()
list=soda.getCollectionNames()
print(list)

Now you should be able to see the first collection only. In this part of the workshop, we have learned what is SODA, the possible configurations for the Oracle driver for Python and how to connect. In the next part we are going to start running CRUD operations based on an example: creating a hotel booking system. The next part is the following: https://dev.to/javierdelatorre/python-developer-track-for-oracle-json-and-duality-views-part-3-crud-operations-2oji