CRUD operations in Oracle Database using Python

In my previous post, I explained how to connect to Oracle Database using Python. In this blog series, I will explain how to perform CRUD Operations using Python.


This is the first blog of the series, where I explain the basics of the cx_Oracle module. 

I'll use the emp_test table for all the examples in this series. Create the table using the below script.
CREATE TABLE EMP_TEST(EMPNO    NUMBER(4,0) PRIMARY KEY, 
                  ENAME    VARCHAR2(10), 
                  JOB      VARCHAR2(9), 
                  MGR      NUMBER(4,0), 
                  HIREDATE DATE, 
                  SAL      NUMBER(7,2), 
                  COMM     NUMBER(7,2), 
                  DEPTNO   NUMBER(2,0));
To perform any action with the database, basically, we need to perform three basic steps.:
  • First, we need to import the cx_Oracle module. cx_Oracle module provides the API for accessing the Oracle Database. There are different modules available to connect with different databases. 
    • import cx_Oracle
  • Second, we need to establish the database connection. cx_Oracle module provides connect method for the database connection. We need to pass username, password, and database connection string as parameters.
    • conn = cx_Oracle.connect('username','password','db_connect_string')
  • In the last, we need to create a cursor object. Create cursor object to execute queries and get results after execution.
    • cur = conn.cursor()
We need to perform these three steps each time no matter which database we are using. 
 
After establishing the connection with the database, we need to execute the various SQL statements. For that the cursor object provides different methods as follow:
  • Methods to execute SQL statement:
    • cursor.execute()     : Used to execute a single query
    • cursor.executeall() : Used to execute parameterised query
Similarly, the cursor object also provides three methods to fetch the result set of a SQL query from the database.
  • Methods to fetch the result:
    • cursor.fetchone()        : Used to fetch only one row
    • cursor.fetchall()          : Used fetch all rows
    • cursor.fetchmany(n)   : Used to fetch n number of rows
Once we completed these operations, we can either commit the connection or roll back the connection. We can do this using the below methods of the connection object.
  • Commit or Rollback:
    • connection.commit()
    • connection.rollback()
And after all these steps, we need to close the cursor and connection using the below methods

    • cursor.close()             : Used to close the cursor
    • connection.close()     : Used to close the connection
Now we understood the basic steps to connect with the Oracle database and the available methods to execute the statements and fetch the records from the database. In the next post, I'll explain 'C' of the CRUD operation, that is how to create records into the Oracle database.

Comments

Post a Comment

Popular posts from this blog

Sorting Order in Oracle APEX Classic Report

Image
In APEX, the sorting option for a classic report is divided into two aspects:  How records will be sorted when the report first renders? Whether the end-user can change the sort order interactively by clicking the column header. A classic report can be developed using either the static query or the dynamic query and the sorting behavior is different for both options. First, we will see the sorting behavior for the report based on the static query.  The below classic report is based on the static query and displays all the records of the EMP_DUP table. For this blog, I created EMP_DUP which is the same as the EMP table but contains duplicate records. The sorting aspects for this report are determined by the properties in each column's  sorting  section. The below image shows the  sorting  section for  JOB. The Default Sequence  property determines the initial sort order. Each column that participates in the initial sort order will have a unique sequence number that will determine
Read more

Multi-select List Item in Oracle APEX | Checkbox Group

Image
In my previous post , I explained how to use Shuttle Item. In this post, I will explain how to use another multi-select item Checkbox Group.  In Oracle APEX, Checkbox Group displays multiple values as checkboxes and enables end-user to select multiple values. Like the Shuttle item, the Checkbox Group also stores values of checked boxes in a colon-delimited string. We need to provide a list of values for values displayed as checkboxes.   How to create a Checkbox Group?  Create a VARCHAR2 column having a large size as it stores a colon-delimited string. Navigate to an APEX page and create a page item in a region that is based on the database table. Change its type to "Checkbox Group". Go to the "List of Values" section in the property section and select the list of values type. Here I selected "Static Values".  Click on the "Static Values" and a popup will open. Add Display Value and Return Value in this popup as shown below.  Enter the number of
Read more

OTP based Authentication in Oracle APEX using Twilio

Image
Recently, a new requirement came up to implement One Time Password (OTP) based authentication in Oracle APEX. In this, the user will enter his mobile number on the login page and will receive an OTP via SMS. The user will then enter the OTP on the login page and be able to authenticate himself in the APEX app.  When I started working on this requirement, first I identified all the steps that I need to implement for the solution.  Below are the steps that I followed: 1. Generate the OTP and temporarily store it in a database table. 2. Send the generated OTP to the user by integrating REST API to send the SMS 3. Validate the user entered OTP against the OTP that is stored in the table and authenticate the user. 4. Create a Custom Authentication Scheme. 5. Modify the login page. Let's understand each step in more detail. Step 1:  Generate the OTP and temporarily store it in a database table.  First, create a table to temporarily store the OTP for the validation. Here I created a si
Read more