How to Read Records from Oracle Database Table using Python?

This post is part of a series of blog posts on basic CRUD operations in Oracle Database using python.

This is the third post of this series. In the second post, we have already covered how to create records in the Oracle database table. In this post, let's understand the 'R' of the CRUD operations, which is how to read records from a database table.

To read the records from the database, we use the Select query. After executing the Select statement, we have to fetch the records from the database. For this operation, we have three methods: 
  • cursor.fetchone()        : Used to fetch only one-row
  • cursor.fetchall()          : Used fetch all rows
  • cursor.fetchmany(n)   : Used to fetch n number of rows
Fetch Single Row

To fetch the records from the table, we first need to define the SELECT statement and then execute this statement using the execute() method. These are the same steps that we followed to insert the records. To fetch the single record from the table, we need to use the fetchone() method. This method fetches the first records from the result set of our query and returns them in the form of a tuple. 

		sql_stmt = """
                     SELECT * FROM EMP_TEST
                   """
        #execute the statement 
        cur.execute(sql_stmt)
        #fetch the row
        row = cur.fetchone()
As you can see in the above code snippet, I defined the Select statement to fetch the record from the EMP_TEST table. Then I executed that statement and fetched the single row using the fetchone() method. The row is returned in the form of a tuple and I stored it in a variable called row.

Here is the complete code block.
#fetchone.py
import cx_Oracle as cx
import db_config as db
try:
    #create connection
    conn = cx.connect(db.username, db.password,db.connect_string)
except Exception as err:
    print('Error occured while trying to create a connection',err)
else:
    try:
        #create cursor
        cur = conn.cursor()
        sql_stmt = """
                     SELECT * FROM EMP_TEST
                   """
        #execute the statement 
        cur.execute(sql_stmt)
        #fetch the row
        row = cur.fetchone()
    except Exception as err:
        print('Error while fetching the records',err)
    else:
         print(row)   
    finally:
        #close cursor
        cur.close();        
finally:
    #close connection
    conn.close()
Result:
Fetch All Rows

Using the fetchall() method, we can fetch all the rows from a table.  This method fetches all the records from the result set of our query and returns them in the form of a list of tuples.  
		sql_stmt = """
            SELECT * FROM EMP_TEST
        """
        #execute the statement 
        cur.execute(sql_stmt)

        #fetch all the rows
        row = cur.fetchall()
As you can see in the above code snippet, I defined the Select statement to fetch the record from the EMP_TEST table. Then I executed that statement and fetched all the rows using the fetchall() method. The rows are returned in the form of a list of tuples and I stored it in a variable called row.

Here is the complete code block.
#fetchall.py
import cx_Oracle as cx
import db_config as db
try:
    #create connection
    conn = cx.connect(db.username, db.password,db.connect_string)
except Exception as err:
    print('Error occured while trying to create a connection',err)
else:
    try:
        #create cursor
        cur = conn.cursor()
        sql_stmt = """
            SELECT * FROM EMP_TEST
        """
        #execute the statement 
        cur.execute(sql_stmt)

        #fetch all the rows
        row = cur.fetchall()

    except Exception as err:
        print('Error while fetching the records',err)
    else:
        print(row)  
         #work on data
        for index,record in enumerate(row):
            print('Index is ',index,' : ',record)
    finally:
        #close cursor
        cur.close();        
finally:
    #close connection
    conn.close()
Result:
Fetch n Rows

Using the fetchmany(n) method, we can fetch n number of rows from a table. Here n represents the number of rows we want to fetch from a table. This method fetches n records from the result set of our query and returns it in the form of a list of tuples.   
		sql_stmt = """
            SELECT * FROM EMP_TEST
        """
        #execute the statement 
        cur.execute(sql_stmt)

        #fetch all the rows
        row = cur.fetchmany(3)
As you can see in the above code snippet, I defined the Select statement to fetch the record from the EMP_TEST table. Then I executed that statement and fetched three rows using the fetchmany(3) method. The rows are returned in the form of a list of tuples and I stored it in a variable called row.

Here is the complete code block.
#fetchmany.py
import cx_Oracle as cx
import db_config as db
try:
    #create connection
    conn = cx.connect(db.username, db.password,db.connect_string)
except Exception as err:
    print('Error occured while trying to create a connection',err)
else:
    try:
        #create cursor
        cur = conn.cursor()
        sql_stmt = """
            SELECT * FROM EMP_TEST
        """
        #execute the statement 
        cur.execute(sql_stmt)

        #fetch the rows
        row = cur.fetchmany(3)

    except Exception as err:
        print('Error while fetching the records',err)
    else:
        print(row)  
         #work on data
        for index,record in enumerate(row):
            print('Index is ',index,' : ',record)
    finally:
        #close cursor
        cur.close();        
finally:
    #close connection
    conn.close()
Result:
We now have a full understanding of how to perform Create and Read operations. In the next posting, we'll see the 'U' and 'D' of the CRUD operation, which is how to update and delete records from the 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