DEV Community

Cover image for Python Developer Track for Oracle JSON and Duality Views - Part 3: CRUD operations
Javier
Javier

Posted on • Updated on

Python Developer Track for Oracle JSON and Duality Views - Part 3: CRUD operations

In the 2 we learned what is SODA, how to connect to an Oracle Database and how to create some collections. In this tutorial we are going to learn the different CRUD operations we can perform. In the part 4 we will have a deeper look at the queries.

As mentioned in the last part, we are going to build a simple hotel booking application as an example of all the exercises we are going to see.

We are going to use the flexibility of JSON to store data about our customers, bookings and payments. Let's start!

As we already saw, the first thing we are going to do is to connect to the Oracle Database using the oracledb driver:

import oracledb
#connect with thick-client
oracledb.init_oracle_client()
connection = oracledb.connect(user="myapp",
                              password="PassworD123##",
                              dsn="localhost:1521/FREEPDB1")
Enter fullscreen mode Exit fullscreen mode

As we are going to perform some inserts and deletes, I want to be sure that no data is lost. We are going to configure the autocommit, so all data will be committed after each transaction automatically:

connection.autocommit = True
Enter fullscreen mode Exit fullscreen mode

As we also know, let's create a new collection to store all the information regarding our hotel, let's call it "hotel_reservations". After that, we are going to open it to start doing some operations:

soda = connection.getSodaDatabase()
collection = soda.createCollection("hotel_reservations")
collection = soda.openCollection("hotel_reservations")
Enter fullscreen mode Exit fullscreen mode

Let's insert our first reservation. We are going to use the method "insertOne" for this purpose:

collection.insertOne(
{
    "reservation_id": "1",
    "hotel_id": "123",
    "room_id": "105",
    "checkin_date": "2023-06-03",
    "checkout_date": "2023-06-07",
    "num_adults": 2,
    "num_children": 0,
    "guest_name": {
      "first_name": "Maria",
      "last_name": "Rodriguez"
    },
    "guest_contact_info": {
      "email": "mrodriguez@example.com",
      "phone": "777-4231",
      "address": {
        "city": "Paris",
        "country": "France"
      }
    },
    "total_cost": 650.00,
    "payment_status": "paid"
  })

Enter fullscreen mode Exit fullscreen mode

Congratulations! You have inserted our first JSON document using SODA API against the Oracle Database. As you have seen, no need for SQL knowledge or structure created in advance!

For each document we store, the Oracle Database generates a key. Let's insert a new document and retrieve the key for this document. We are going to use the method "insertOneAndGet" for this purpose:

content = {
    "reservation_id": "2",
    "hotel_id": "123",
    "room_id": "315",
    "checkin_date": "2023-06-15",
    "checkout_date": "2023-06-17",
    "num_adults": 1,
    "num_children": 0,
    "guest_name": {
      "first_name": "Ethan",
      "last_name": "Lee"
    },
    "guest_contact_info": {
      "email": "ethan.lee@example.com",
      "phone": "123-8106",
      "address": {
        "city": "Madrid",
        "country": "Spain"
      }
    },
    "total_cost": 350.00,
    "payment_status": "paid"
  }

document = collection.insertOneAndGet(content)
key = document.key
print('\n The key of the document is: ', key)
Enter fullscreen mode Exit fullscreen mode

You should get a result like the following:

The key of the document is:  08655F63EA0AE7791964F8F85F
>>>
Enter fullscreen mode Exit fullscreen mode

Now that we have the key, let's use it to get the document. As we know that this key will be unique, we are going to use "getOne". In case you want to retrieve more documents, you should use "getDocuments()":

document = collection.find().key(key).getOne() 
content = document.getContent()                
print('\n The document is:')
print(content)
Enter fullscreen mode Exit fullscreen mode

You will see that we are getting the same JSON document. We need to insert more JSON documents to have a more representative workload. Instead of inserting one by one, we are going to use "insertManyandGet()". We could use also "insertMany". The only difference is the first one returns a list of SODA Document objects:

all_documents=[
    {
    "reservation_id": "3",
    "hotel_id": "123",
    "room_id": "207",
    "checkin_date": "2023-06-25",
    "checkout_date": "2023-06-30",
    "num_adults": 2,
    "num_children": 0,
    "guest_name": {
      "first_name": "Olivia",
      "last_name": "Johnson"
    },
    "guest_contact_info": {
      "email": "olivia.johnson@example.com",
      "phone": "987-1890",
      "address": {
        "city": "Barcelona",
        "country": "Spain"
      }
    },
    "total_cost": 932.00,
    "payment_status": "pending"
  }
,

{
    "reservation_id": "4",
    "hotel_id": "123",
    "room_id": "222",
    "checkin_date": "2023-06-07",
    "checkout_date": "2023-06-17",
    "num_adults": 2,
    "num_children": 0,
    "guest_name": {
      "first_name": "Liam",
      "last_name": "Patel"
    },
    "guest_contact_info": {
      "email": "liam.patel@example.com",
      "phone": "123-8106",
      "address": {
        "city": "Malaga",
        "country": "Spain"
      }
    },
    "total_cost": 350.00,
    "payment_status": "paid"
  }

,

{
    "reservation_id": "5",
    "hotel_id": "123",
    "room_id": "101",
    "checkin_date": "2023-06-01",
    "checkout_date": "2023-06-05",
    "num_adults": 2,
    "num_children": 1,
    "guest_name": {
      "first_name": "John",
      "last_name": "Smith"
    },
    "guest_contact_info": {
      "email": "john.smith@example.com",
      "phone": "555-1234",
      "address": {
        "city": "Lyon",
        "country": "France"
      }
    },
    "additional_requests": [
      {
        "type": "extra_bed",
        "quantity": 1
      },
      {
        "type": "late_checkout",
        "details": "Please arrange for a 2pm checkout"
      }
    ],
    "total_cost": 800.00,
    "payment_status": "paid"
  }
  ,
    {
    "reservation_id": "6",
    "hotel_id": "123",
    "room_id": "305",
    "checkin_date": "2023-06-04",
    "checkout_date": "2023-06-20",
    "num_adults": 2,
    "num_children": 0,
    "guest_name": {
      "first_name": "Marcus",
      "last_name": "Wong"
    },
    "guest_contact_info": {
      "email": "marcus.wong@example.com",
      "phone": "123-1234",
      "address": {
        "city": "Nice",
        "country": "France"
      }
    },
    "total_cost": 1350.00,
    "payment_status": "cancelled"
  }

]
result_docs = collection.insertManyAndGet(all_documents)
Enter fullscreen mode Exit fullscreen mode

Now we have a lot of documents but, how many? Let's run our first simple query by running a "count()":

total = collection.find().count()
print('\n My hotel has', total, 'reservations')
Enter fullscreen mode Exit fullscreen mode

We have 6 reservations! As our hotel is getting more reservations, we need to know if there is any customer who didn't pay! Let's check the payment status. For that, we are going to run a query with a filter. We want to get the customers who are on pending status:

documents = collection.find().filter({'payment_status': "pending"}).getDocuments()
print('\n Customers who didn\'t pay:')
for d in documents:
    content = d.getContent()
    print(content["guest_contact_info"])
Enter fullscreen mode Exit fullscreen mode

We are going to print only the contact info from the JSON Document. In this case we have 1 customer with status pending:

{'email': 'olivia.johnson@example.com', 'phone': '987-1890', 'address': {'city': 'Barcelona', 'country': 'Spain'}}
Enter fullscreen mode Exit fullscreen mode

It seems there was a mistake! Customer solved the issue and we have to change the status from pending to paid. To do that, we are going to get the key of the document with status pending and replace the document using "replaceOne()":

new_content = {
    "reservation_id": "3",
    "hotel_id": "123",
    "room_id": "207",
    "checkin_date": "2023-06-25",
    "checkout_date": "2023-06-30",
    "num_adults": 2,
    "num_children": 0,
    "guest_name": {
      "first_name": "Olivia",
      "last_name": "Johnson"
    },
    "guest_contact_info": {
      "email": "olivia.johnson@example.com",
      "phone": "987-1890",
      "address": {
        "city": "Barcelona",
        "country": "Spain"
      }
    },
    "total_cost": 932.00,
    "payment_status": "paid"

  } 

to_modify_doc = collection.find().filter({'payment_status': "pending"}).getOne()
key=to_modify_doc.key
collection.find().key(key).replaceOne(new_content)
Enter fullscreen mode Exit fullscreen mode

Finally, we have seen that a customer cancelled. Let's delete that reservation:

deleted = collection.find().filter({'payment_status': "cancelled"}).remove()
print('\n Deleted', deleted, 'documents')
Enter fullscreen mode Exit fullscreen mode

We have done some CRUD operations, easy right? In the next part we are going to run some more queries! You can find the next part here: https://dev.to/javierdelatorre/python-developer-track-for-oracle-json-and-duality-views-part-4-queries-by-example-57ho

Top comments (0)