Python - Database Update & Delete Records

In this application, we will learn how to update employee salaries in the employees table.

The salary increment and salary range are taken dynamically from the keyboard.

The program updates only those employees whose salary is less than the salary range entered by the user.

Example given in the tutorial:

Increment all employee salaries by 500
whose salary is less than 5000.
        

This application demonstrates:

• Using the SQL UPDATE statement
• Taking salary increment dynamically
• Taking salary range dynamically
• Updating multiple employee records
• Using execute()
• Using commit()
• Using rollback()
• Handling Database errors
• Closing Database resources

Keyboard Input
      │
      ├── Increment Salary
      │
      └── Salary Range
              │
              ▼
        UPDATE employees
              │
              ▼
       Increase Salaries
              │
              ▼
           commit()
        

UPDATE is a DML statement used to modify existing records in a Database table.

General Syntax

UPDATE table_name
SET column_name = new_value
WHERE condition;
        

For this application:

UPDATE employees
SET esal = esal + increment
WHERE esal < salary_range;
        

The existing salary is increased instead of replacing it with a completely unrelated fixed value.

UPDATE belongs to DML - Data Manipulation Language.

DML Statement	Purpose
INSERT	Add new records
UPDATE	Modify existing records
DELETE	Remove existing records

Because UPDATE modifies data, the program uses con.commit() to permanently save the changes.

The complete program performs the following operations:

Import cx_Oracle
      │
      ▼
Connect to Oracle
      │
      ▼
Create Cursor
      │
      ▼
Read Increment Salary
      │
      ▼
Read Salary Range
      │
      ▼
Create UPDATE SQL
      │
      ▼
Execute UPDATE Query
      │
      ▼
Display Success Message
      │
      ▼
commit()
      │
      ▼
Close Resources
        

The complete program with comments is shown below.

Records Updated Successfully

Step 1 - Import the Module

The program starts with:

import cx_Oracle

The cx_Oracle module is imported to communicate with Oracle Database.

Python Program
      │
      ▼
cx_Oracle
      │
      ▼
Oracle Database
        

Step 2 - Establish Database Connection

The connection is established using:

con = cx_Oracle.connect('scott/tiger@localhost')

The connection details used in the tutorial are:

Property	Value
Username	scott
Password	tiger
Database	localhost

The returned Connection object is stored in con.

Step 3 - Create Cursor Object

The Cursor object is created using:

cursor = con.cursor()

The Cursor is required to execute the UPDATE SQL statement.

Step 4 - Read Dynamic Input

The program reads two values dynamically from the keyboard.

increment = float(input("Enter Increment Salary:"))

salrange = float(input("Enter Salary Range:"))
        

Variable	Description
increment	Salary amount to be added
salrange	Maximum salary limit for updating employees

Suppose the user enters:

Enter Increment Salary:500
Enter Salary Range:5000
        

The variables contain:

Variable	Value
increment	500.0
salrange	5000.0

The meaning is:

Add 500 to salary

ONLY IF

current salary < 5000
        

The SQL statement is created as:

sql = "update employees set esal=esal+%f where esal<%f"

This statement updates salaries for employees whose current salary is less than the specified salary range.

The SQL contains two %f format specifiers.

Specifier	Represents
First %f	Salary increment
Second %f	Salary range

Understanding the UPDATE Query

update employees
set esal = esal + %f
where esal < %f
        

The query contains three important parts:

SQL Part	Meaning
update employees	Selects the employees table for updating
set esal=esal+%f	Adds the increment to the current salary
where esal<%f	Updates only employees below the salary range

Understanding the SET Clause

The SET clause is set esal = esal + %f. It does not simply replace the salary with the increment value.

Instead:

New Salary = Existing Salary + Increment
        

For example:

Existing Salary = 3000
Increment       = 500

New Salary      = 3000 + 500
                = 3500
        

Understanding the WHERE Clause

The WHERE clause is where esal < %f. It determines which employee records should be updated.

If salrange = 5000, then WHERE esal < 5000. Only employees with a salary below 5000 are selected for the update.

The WHERE clause restricts the UPDATE operation to matching records. Employees that satisfy esal < 5000 are updated, and employees that do not satisfy the condition are not updated.

Employee Salary
      │
      ▼
Is Salary < 5000?
   /          Yes         No
  │           │
  ▼           ▼
Update      No Update
Salary
        

SQL Statement Example

After substituting the values Increment Salary = 500 and Salary Range = 5000, the SQL statement becomes:

UPDATE employees
SET esal = esal + 500
WHERE esal < 5000;
        

Suppose the employees table contains:

+-----+--------+------+
| eno | ename  | esal |
+-----+--------+------+
| 100 | Durga  | 1000 |
| 200 | Sunny  | 2000 |
| 300 | Chinny | 3000 |
| 400 | Bunny  | 4000 |
| 500 | Sachin | 5000 |
| 600 | Dhoni  | 6000 |
+-----+--------+------+
        

Suppose the user enters Increment Salary = 500 and Salary Range = 5000. The condition is esal < 5000.

Which Employees are Updated?

Employee	Current Salary	Salary < 5000?	Updated?
Durga	1000	Yes	Yes
Sunny	2000	Yes	Yes
Chinny	3000	Yes	Yes
Bunny	4000	Yes	Yes
Sachin	5000	No	No
Dhoni	6000	No	No

Notice that a salary equal to 5000 is not updated because the condition uses < not <=.

Salary Calculation

The qualifying salaries are increased by 500.

1000 + 500 = 1500

2000 + 500 = 2500

3000 + 500 = 3500

4000 + 500 = 4500
        

The other salaries remain unchanged:

5000 → 5000
6000 → 6000
        

After Updating Salaries

+-----+--------+------+
| eno | ename  | esal |
+-----+--------+------+
| 100 | Durga  | 1500 |
| 200 | Sunny  | 2500 |
| 300 | Chinny | 3500 |
| 400 | Bunny  | 4500 |
| 500 | Sachin | 5000 |
| 600 | Dhoni  | 6000 |
+-----+--------+------+
        

Only employees satisfying the WHERE condition have their salary increased.

The UPDATE query is executed using:

cursor.execute(sql % (increment, salrange))

The values entered by the user are substituted into the SQL statement.

For example, increment = 500 and salrange = 5000 results in:

update employees
set esal=esal+500
where esal<5000
        

How execute() Works

increment
    │
    ├───────────┐
    │           │
salrange        │
    │           │
    └─────┬─────┘
          ▼
  Format SQL Query
          │
          ▼
cursor.execute()
          │
          ▼
   Oracle Database
          │
          ▼
   employees Table
          │
          ▼
Find Matching Records
          │
          ▼
  Update Salaries
        

Display Success Message

After the UPDATE query executes successfully, the program displays:

print("Records Updated Successfully")

Output:

Records Updated Successfully

The word Records is used because the UPDATE statement may modify multiple employee rows.

Commit the Changes

After the successful UPDATE operation, the program executes con.commit(). The commit() method permanently saves the updated salary values in the Database.

UPDATE Query
     │
     ▼
Salary Values Changed
     │
     ▼
Pending Transaction
     │
     ▼
con.commit()
     │
     ▼
Changes Permanently Saved
        

UPDATE is a DML operation, so the transaction should be committed to permanently save the updated values. The same transaction concept applies to INSERT, UPDATE, and DELETE.

Exception Handling

The program handles Oracle Database errors using:

except cx_Oracle.DatabaseError as e:

If a Database-related error occurs, execution moves to the except block. The exception object is stored in e.

UPDATE Operation
      │
      ▼
Database Error?
   ┌──┴──┐
   │     │
  No    Yes
   │     │
   ▼     ▼
commit  except
         │
         ▼
         e
        

Rollback

If an SQL error occurs, the program executes:

if con:
    con.rollback()
        

If the Connection object exists, rollback() cancels temporary uncommitted changes.

UPDATE
  │
  ▼
Temporary Changes
  │
  ▼
Error Occurs
  │
  ▼
rollback()
  │
  ▼
Cancel Changes
        

commit() vs rollback()

commit()	rollback()
Saves changes permanently	Cancels uncommitted changes
Used after successful UPDATE	Used when an error occurs
con.commit()	con.rollback()

Display Error Message

The program displays the SQL error using:

print("There is a problem with sql :", e)

The general output is:

There is a problem with sql : <Oracle Database Error>

The actual exception information is stored in e.

Finally Block

The finally block executes whether an exception occurs or not. Its purpose is to release Database resources.

finally:
    if cursor:
        cursor.close()

    if con:
        con.close()
        

Close Cursor and Connection

The Cursor is closed using if cursor: cursor.close(). This releases the Cursor resource.

The Connection is closed using if con: con.close(). This releases the connection with Oracle Database.

Methods Used

Method	Purpose
connect()	Creates a Database connection
cursor()	Creates a Cursor object
execute()	Executes the UPDATE SQL statement
commit()	Permanently saves updated records
rollback()	Cancels temporary changes if an error occurs
close()	Closes the Cursor and Connection

The original tutorial uses:

sql = "update employees set esal=esal+%f where esal<%f"

cursor.execute(sql % (increment, salrange))
        

This is useful for understanding the original example.

For real applications, parameterized queries using bind variables are preferred:

sql = """
update employees
set esal = esal + :increment
where esal < :salrange
"""
        

Then the values are passed separately to execute().

This avoids manually constructing the SQL statement from input values.

No output captured.

In this application, we will learn how to delete employee records from the employees table.

The cutoff salary is taken dynamically from the keyboard.

The program deletes all employees whose salary is greater than the cutoff salary entered by the user.

Example given in the tutorial:

Delete all employees whose salary > 5000.
        

This application demonstrates:

• Using the SQL DELETE statement
• Taking cutoff salary dynamically from the keyboard
• Using a WHERE condition
• Deleting multiple matching records
• Using execute()
• Using commit()
• Using rollback()
• Handling Oracle Database errors
• Closing Database resources

Keyboard Input
      │
      ▼
Cutoff Salary
      │
      ▼
DELETE FROM employees
WHERE esal > cutoff
      │
      ▼
Delete Matching Records
      │
      ▼
commit()
        

The SQL DELETE statement is used to remove existing records from a Database table.

General Syntax

DELETE FROM table_name
WHERE condition;
        

For this application:

DELETE FROM employees
WHERE esal > cutoff_salary;
        

The WHERE condition decides which employee records should be deleted.

DELETE belongs to DML - Data Manipulation Language.

Because DELETE modifies table data, the program uses con.commit() to permanently save the deletion.

The program performs the following major operations:

Import cx_Oracle
      │
      ▼
Create Connection
      │
      ▼
Create Cursor
      │
      ▼
Read CutOff Salary
      │
      ▼
Create DELETE SQL
      │
      ▼
Execute DELETE Query
      │
      ▼
Records Deleted Successfully
      │
      ▼
Commit Changes
      │
      ▼
Close Resources
        

The complete program with comments is shown below.

Records Deleted Successfully

Step 1 - Import the Module

The program starts with import cx_Oracle. The cx_Oracle module is imported to communicate with Oracle Database.

Step 2 - Establish Database Connection

A connection is established with Oracle Database using:

con = cx_Oracle.connect('scott/tiger@localhost')

The connection details used in the tutorial are:

Property	Value
Username	scott
Password	tiger
Database	localhost

The returned Connection object is stored in con.

Step 3 - Create Cursor Object

The Cursor object is created using cursor = con.cursor(). In this application, the Cursor executes the DELETE query.

Step 4 - Read Cutoff Salary

The cutoff salary is read dynamically from the keyboard:

cutoffsalary = float(input("Enter CutOff Salary:"))

Example:

Enter CutOff Salary:5000
        

The value returned by input() is converted into a floating-point number: cutoffsalary = 5000.0.

The variable cutoffsalary stores the maximum salary limit used for deciding which employee records should be deleted.

Employee Salary
      │
      ▼
Salary > Cutoff?
    /         Yes       No
   │         │
   ▼         ▼
Delete     Keep
Record     Record
        

Why float() is Used

Python's input() function returns keyboard input as a string. Therefore float(input("Enter CutOff Salary:")) converts the entered salary into a floating-point value.

Input:
5000

After float():
5000.0
        

The DELETE query is created using:

sql = "delete from employees where esal>%f"

This SQL statement deletes all employees whose salary is greater than the entered cutoff salary.

The %f placeholder represents cutoffsalary.

SQL Part	Meaning
delete from employees	Delete records from the employees table
where	Apply a condition
esal	Employee salary column
>	Greater-than operator
%f	Cutoff salary value

Understanding the WHERE Clause

The most important part of this DELETE statement is where esal > %f. This condition prevents every employee record from being deleted.

Suppose cutoffsalary = 5000. The condition becomes where esal > 5000. Only employees earning more than 5000 are deleted.

Why the WHERE Clause is Very Important with DELETE

A DELETE statement without a WHERE condition can delete all rows from a table. For example, DELETE FROM employees; targets every employee record.

But App8 uses:

DELETE FROM employees
WHERE esal > cutoffsalary;
        

Therefore, only matching employee records are targeted.

DELETE
  │
  ├── Without WHERE → All rows
  │
  └── With WHERE    → Matching rows only
        

SQL Statement Example

The original SQL template is delete from employees where esal>%f. After substituting 5000, the SQL statement becomes:

DELETE FROM employees
WHERE esal > 5000;
        

Suppose the employees table contains:

+-----+--------+------+
| eno | ename  | esal |
+-----+--------+------+
| 100 | Sachin | 3000 |
| 200 | Dhoni  | 4500 |
| 300 | Kohli  | 5000 |
| 400 | Rohit  | 5500 |
| 500 | Rahul  | 7000 |
+-----+--------+------+
        

Now the user enters Enter CutOff Salary:5000. The condition becomes esal > 5000.

Which Employees are Deleted?

Employee	Salary	Salary > 5000?	Result
Sachin	3000	No	Kept
Dhoni	4500	No	Kept
Kohli	5000	No	Kept
Rohit	5500	Yes	Deleted
Rahul	7000	Yes	Deleted

What Happens to Salary Equal to the Cutoff?

The condition used by the program is esal > cutoffsalary. Therefore, if Salary = 5000 and Cutoff Salary = 5000, then 5000 > 5000 is False.

So an employee earning exactly the cutoff salary is not deleted.

Salary < Cutoff  → Keep

Salary = Cutoff  → Keep

Salary > Cutoff  → Delete
        

After DELETE

After executing DELETE FROM employees WHERE esal > 5000;, the remaining records are:

+-----+--------+------+
| eno | ename  | esal |
+-----+--------+------+
| 100 | Sachin | 3000 |
| 200 | Dhoni  | 4500 |
| 300 | Kohli  | 5000 |
+-----+--------+------+
        

The employees with salaries 5500 and 7000 were deleted because both values are greater than 5000.

The DELETE statement is executed using:

cursor.execute(sql % (cutoffsalary))

The value entered by the user is substituted for %f. For example, cutoffsalary = 5000 produces:

delete from employees where esal>5000

The Cursor sends this SQL statement to Oracle Database.

How execute() Works

cutoffsalary
     │
     ▼
Replace %f
     │
     ▼
DELETE SQL Statement
     │
     ▼
cursor.execute()
     │
     ▼
Oracle Database
     │
     ▼
employees Table
     │
     ▼
Check esal > cutoff
     │
     ▼
Delete Matching Rows
        

Display Success Message

If the DELETE operation executes successfully, the program displays:

print("Records Deleted Successfully")

Output:

Records Deleted Successfully

The DELETE statement can affect multiple employees because every row satisfying the condition is deleted.

Commit the Changes

After deleting the matching records, the program executes con.commit(). The commit() method permanently saves the deletion in the Database.

DELETE Query
     │
     ▼
Rows Deleted
     │
     ▼
Pending Transaction
     │
     ▼
con.commit()
     │
     ▼
Deletion Saved Permanently
        

DELETE is a DML operation, so the transaction should be committed to permanently save the changes. The same transaction concept applies to INSERT, UPDATE, and DELETE.

Sample Program Execution

Suppose the user enters:

Enter CutOff Salary:5000
Records Deleted Successfully
        

Exception Handling

The program handles Oracle Database errors using:

except cx_Oracle.DatabaseError as e:

If an Oracle Database error occurs, execution moves to the except block. The exception object is stored in e.

DELETE Operation
      │
      ▼
Database Error?
   /           No           Yes
 │             │
 ▼             ▼
Continue     except
              │
              ▼
              e
        

Rollback

If an SQL error occurs, the program executes:

if con:
    con.rollback()
        

If the Connection object exists, rollback() cancels temporary uncommitted changes.

DELETE
   │
   ▼
Temporary Changes
   │
   ▼
Database Error
   │
   ▼
rollback()
   │
   ▼
Cancel Uncommitted Changes
        

commit() vs rollback()

commit()	rollback()
Saves changes permanently	Cancels uncommitted changes
Used after successful DELETE	Used if an error occurs
con.commit()	con.rollback()

Display Error Message

The program displays the SQL error using:

print("There is a problem with sql :", e)

The general form of the output is:

There is a problem with sql : <Oracle Database Error>

The actual Oracle Database error information is available through e.

Finally Block

The finally block executes whether an exception occurs or not. Its purpose is to release Database resources.

finally:
    if cursor:
        cursor.close()

    if con:
        con.close()
        

Close Cursor and Connection

The Cursor is closed using if cursor: cursor.close(). This releases the Cursor resource after the Database operation is completed.

The Database connection is closed using if con: con.close(). This releases the Oracle Database connection.

Methods Used

Method	Purpose
connect()	Creates a Database connection
cursor()	Creates a Cursor object
execute()	Executes the DELETE SQL statement
commit()	Permanently saves deleted records
rollback()	Cancels temporary changes if an error occurs
close()	Closes the Cursor and Connection

The original tutorial uses:

sql = "delete from employees where esal>%f"

cursor.execute(sql % (cutoffsalary))
        

This is useful for understanding how the example in the tutorial works.

For real applications, a bind variable can be used:

sql = """
delete from employees
where esal > :cutoffsalary
"""

cursor.execute(
    sql,
    cutoffsalary=cutoffsalary
)
        

The SQL statement and the dynamic value are passed separately.

No output captured.

The following table compares the two applications covered on this page.

Feature	App7	App8
Operation	UPDATE	DELETE
Purpose	Modify employee salaries	Remove employee records
Dynamic Input	Increment and salary range	Cutoff salary
Condition	esal < salrange	esal > cutoffsalary
Execution	execute()	execute()
Save Changes	commit()	commit()
Error Recovery	rollback()	rollback()

App7 Summary - Update Operation:

Topic	Description
Application	App7
SQL Operation	Update
Table	employees
Column Updated	esal
SQL Statement	update employees set esal=esal+%f where esal<%f
Dynamic Inputs	Increment Salary, Salary Range
Execution Method	execute()
Commit Required	Yes
Commit Method	con.commit()
Error Handling	rollback()
Exception	cx_Oracle.DatabaseError
Resource Cleanup	cursor.close() and con.close()

App8 Summary - Delete Operation:

Topic	Description
Application	App8
SQL Operation	Delete
Table	employees
Condition Column	esal
SQL Statement	delete from employees where esal>%f
Dynamic Input	Cutoff Salary
Variable	cutoffsalary
Condition	Employee salary greater than cutoff salary
Execution Method	execute()
Commit Required	Yes
Commit Method	con.commit()
Error Handling	rollback()
Exception	cx_Oracle.DatabaseError
Resource Cleanup	cursor.close() and con.close()