How to Update and Delete 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.

1. CRUD operations in Oracle Database using Python
2. How to Create Records into Oracle Database Table using Python?
3. How to Read Records from Oracle Database Table using Python?
4. How to Update and Delete Records from Oracle Database Table using Python?

This is the fourth and final post of this series. In this post, let's understand the 'U' and 'D' of the CRUD operations, which is how to Update and Delete records from a database table.

Update Records

We already know how to insert and fetch the records from the table and now updating the records is easy for us. Let's I want to update the salary and commission of an employee Martin into our EMP_TEST table.

		#Update statement
        sql_stmt = """
                    UPDATE EMP_TEST
                    SET SAL  =:1,
                        COMM = :2
                    WHERE EMPNO = :3                       
        	      """
        #execute statement
        cur.execute(sql_stmt,[2000,500,7654]) 

As you can see in the above code snippet, I first defined the update statement using the bind variables, and then, as we did while inserting the record, I executed this statement by passing the statement and list as parameters in the execute method. Once I executed this statement, the salary and commission of employee Marting is updated to 2000 and 500 respectively.

Here is the complete code block.

#update.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()
        #display old record
        sql_stmt1 = """
                    SELECT * FROM EMP_TEST WHERE EMPNO = :1                       
        	      """
        #execute statement
        cur.execute(sql_stmt1,[7654])
        
        #Fetch current records
        row = cur.fetchone()
        
        print('Old record')
        print(row)

        #Update statement
        sql_stmt = """
                    UPDATE EMP_TEST
                    SET SAL =:1,
                        COMM = :2
                    WHERE EMPNO = :3                       
        	      """
        #execute statement
        cur.execute(sql_stmt,[2000,500,7654])
    except Exception as err:
        print('Error while updating the data',err)
    else:
        #print updated record
        cur.execute(sql_stmt1,[7654])
        #Fetch current records
        row = cur.fetchone()
        print('New record')
        print(row)
        #print no of rows updated
        print('Number of rows updated: ' + str(cur.rowcount))
        #commit the transaction
        conn.commit()
    finally:
        #close cursor
        cur.close()     
finally:
    #close connection
    conn.close()

Result:

Delete Records

By the time you have already predicted how to delete the records, so no need to provide any explanation, and let's directly jump to the complete code block.

#delete.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 = """
                    DELETE FROM EMP_TEST WHERE EMPNO = :1
        	      """
        #execute statement
        cur.execute(sql_stmt,[7654])
    except Exception as err:
        print('Error while deleting the data',err)
    else:
        print('Delete Completed')
        print('Number of rows deleted: ' + str(cur.rowcount))
        #commit the transaction
        conn.commit()
    finally:
        #close cursor
        cur.close()     
finally:
    #close connection
    conn.close()

Result:

In this blog series, we have seen how to perform basic CRUD operations with the Oracle database using Python. You now have a basic understanding of how to use the cx_Oracle module to perform various database operations.