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")
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
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")
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"
})
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)
You should get a result like the following:
The key of the document is: 08655F63EA0AE7791964F8F85F
>>>
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)
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)
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')
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"])
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'}}
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)
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')
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)