How to Create Records into 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 second post of this series. In the first post, I have explained the steps that we need to follow to connect with the Oracle database using cx_Oracle and also explained the basic methods that we will use to perform database operations.
Now we know the basic steps that we need to follow, let's understand the 'C' of the CRUD operations, which is how to create records into a table.

First, we need to create the connection as mentioned earlier. 
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 while creating the connection',err)
else:
    try:
        #create cursor
        cur = conn.cursor()
I imported two modules, cx_Oracle, and db_config. I created the db_config module to store username, password, and connect string. 
#db_config.py
username = 'username'
password = 'password#2105'
connect_string = 'connect_string'
After that, we need to define that insert statement to insert the record into the emp table and execute that statement using the execute method. 
		sql_stmt = """
                    INSERT INTO EMP_TEST(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
                    VALUES (7839, 'KING', 'PRESIDENT',NULL,TO_DATE('17-NOV-81'),5000,
                           NULL,10)  
                  """
        #execute statement
        cur.execute(sql_stmt)
    except Exception as err:
        print('Error while inserting the data',err)
    else:
        print('Insert Completed')
        #commit the transaction
        conn.commit()
    finally:
        #close cursor
        cur.close()     
finally:
    #close connection
    conn.close()
I defined the insert statement and executed it by passing it as a parameter to the execute method in line 19. Once that is completed I closed the cursor and connection in lines no 28 and 31 respectively.

Here is the complete code block.
#insert.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 while creating the connection',err)
else:
    try:
        #create cursor
        cur = conn.cursor()
        sql_stmt = """
                    INSERT INTO EMP_TEST(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
                    VALUES (7839, 'KING', 'PRESIDENT',NULL,TO_DATE('17-NOV-81'),5000,
                           NULL,10)
        	      """
        #execute statement
        cur.execute(sql_stmt)
    except Exception as err:
        print('Error while inserting the data',err)
    else:
        print('Insert Completed')
        #commit the transaction
        conn.commit()
    finally:
        #close cursor
        cur.close()     
finally:
    #close connection
    conn.close()
Result:
Insert using Bind Variables

In the previous example, we directly provided values into the insert statement. Now let's understand how to insert records using bind variables.

As you can see that, instead of directly providing the values into the VALUES clause, I used bind variables (:1,:2,:3...:8). These variables are mapped by position with the columns that we defined in the INSERT INTO clause.

We have defined the insert statement using the bind variables and now we need to pass the value of these variables. For that, we need to create a list and then we need to pass that list to the execute method as a parameter. I created two lists rec_list1 and rec_list2 two insert two records and passed these lists to the execute method along with the Insert statement.
 		sql_stmt = """
               INSERT INTO EMP_TEST(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
               VALUES (:1,:2,:3,:4,:5,:6,:7,:8) 
        	   """
        #list 
        rec_list1 = [7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,None,30]
        rec_list2 = [7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,None,10]
        
        #execute statement
        cur.execute(sql_stmt,rec_list1)
        #execute statement
        cur.execute(sql_stmt,rec_list2)
The complete code block:
#insert_with_bind_var.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 while creating the connection',err)
else:
    try:
        #create cursor
        cur = conn.cursor()
        sql_stmt = """
                    INSERT INTO EMP_TEST(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
                    VALUES (:1,:2,:3,:4,:5,:6,:7,:8) 
        	      """
        #list 
        rec_list1 = [7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,None,30]
        rec_list2 = [7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,None,10]
        
        #execute statement
        cur.execute(sql_stmt,rec_list1)
        #execute statement
        cur.execute(sql_stmt,rec_list2)

    except Exception as err:
        print('Error while inserting the data',err)
    else:
        print('Insert Completed')
        #commit the transaction

        conn.commit()
    finally:
        #close cursor
        cur.close()     
finally:
    #close connection
    conn.close()

Insert Multiple rows using executemany() method

In the previous example, we have inserted two records by invoking execute method two times. This is not an efficient technique. So now let's understand how to insert multiple rows efficiently.

As you can see in the below code snippet, the insert statement is the same as we used in the previous example. But now to insert the multiple rows, we require a list of tuples. Till now we used the execute() method to insert the single record. But here we need to use the executemany() method to insert multiple records.
 		sql_stmt = """
                    INSERT INTO EMP_TEST(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
                    VALUES (:1,:2,:3,:4,:5,:6,:7,:8) 
                  """ 
        #data: list of tuples for multiple rows
        data = [(7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,315,30),
                (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30),
                (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,15,30),
                (7900,'JAMES','CLERK',7698,'03-DEC-81',950,None,30),
                (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,None,10)]
        #execute statement
        cur.executemany(sql_stmt,data)
The complete code block:
#insert_multirow.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 while creating the connection',err)
else:
    try:
        #create cursor
        cur = conn.cursor()
        sql_stmt = """
                    INSERT INTO EMP_TEST(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
                    VALUES (:1,:2,:3,:4,:5,:6,:7,:8) 
                  """
        #data: list of tuples for multiple rows
        data = [(7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,315,30),
                (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30),
                (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,15,30),
                (7900,'JAMES','CLERK',7698,'03-DEC-81',950,None,30),
                (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,None,10)]
        #execute statement
        cur.executemany(sql_stmt,data)
    except Exception as err:
        print('Error while inserting data',err)
    else:
        print('Insert completed')
        #commit transaction
        conn.commit();        
    finally:
        #close cursor
        cur.close()        
finally:
    #close connection
    conn.close()
That's it for now. In the next posting, we'll see the 'R' of the CRUD operations, which is how to read/fetch 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