Python - Database Insert Records

DML stands for:

Data Manipulation Language
        

DML statements are used to manipulate the data stored inside Database tables.

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

For DML operations, changes remain part of the current transaction until they are committed.

DML Operation
     │
     ▼
INSERT / UPDATE / DELETE
     │
     ▼
Temporary Transaction Changes
     │
     ├───────────────┐
     │               │
     ▼               ▼
 commit()        rollback()
     │               │
     ▼               ▼
Save Changes     Cancel Changes
        

Therefore, while performing DML operations such as Insert, Update, and Delete, we have to use commit() so that the changes are permanently reflected in the Database.

In this application, we will insert one record into the employees table.

This program demonstrates:

• Inserting a single row into a table
• Using the INSERT SQL statement
• Using the commit() method
• Using rollback() when an error occurs
• Using try, except, and finally blocks

The program inserts the following employee record:

Employee Number	Employee Name	Salary	Address
100	Durga	1000	Hyd

The SQL statement used is:

INSERT INTO employees
VALUES(100,'Durga',1000,'Hyd');
        

Python Program
      │
      ▼
Connect to Oracle
      │
      ▼
Create Cursor
      │
      ▼
Execute INSERT
      │
      ▼
commit()
      │
      ▼
Record Saved
        

The complete program inserts the record, commits the transaction, and prints a success message.

Record Inserted Successfully

The program follows these main steps:

Import cx_Oracle
      │
      ▼
Create Connection
      │
      ▼
Create Cursor
      │
      ▼
Execute INSERT Query
      │
      ▼
commit()
      │
      ▼
Record Saved
      │
      ▼
Print Success Message
      │
      ▼
Close Resources
        

The program starts with import cx_Oracle so that we can communicate with Oracle Database.

The connection is established using:

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

Detail	Value
Username	scott
Password	tiger
Database	localhost

The Cursor object is created using cursor = con.cursor(). The Cursor is required to execute SQL statements, and in this program it executes the INSERT statement:

cursor.execute(
    "insert into employees values(100,'Durga',1000,'Hyd')"
)
        

The general SQL syntax is:

INSERT INTO table_name
VALUES(value1, value2, value3, ...);
        

Part	Meaning
INSERT INTO	SQL command used to insert data
employees	Target table
VALUES	Specifies the values for the new record

The inserted values are:

Column	Value
eno	100
ename	Durga
esal	1000
eaddr	Hyd

employees
+-----+-------+------+-------+
| eno | ename | esal | eaddr |
+-----+-------+------+-------+
| 100 | Durga | 1000 | Hyd   |
+-----+-------+------+-------+
        

After executing the INSERT statement, the program calls:

con.commit()
        

commit() is a transaction-management method called using the Connection object. Its purpose is to make the current transaction changes permanent.

While performing DML operations such as insert, update and delete, commit() has to be used so that the results are reflected in the Database.

cursor.execute(INSERT...)
        │
        ▼
Record Inserted in Transaction
        │
        ▼
con.commit()
        │
        ▼
Record Permanently Saved
        

If we execute a DML statement but do not commit the transaction, the change has not been permanently committed by that transaction.

cursor.execute(INSERT...)
        │
        ▼
Transaction Changed
        │
        ▼
No commit()
        │
        ▼
Change Not Permanently Committed
        

After committing, the program executes print("Record Inserted Successfully"), indicating that the INSERT operation and commit() completed before the success message was reached.

INSERT Query
    │
    ▼
Record Added
    │
    ▼
commit()
    │
    ▼
Record Saved
    │
    ▼
print()
    │
    ▼
Record Inserted Successfully
        

The program handles Oracle Database errors using:

except cx_Oracle.DatabaseError as e:
        

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

Inside the exception handler, if the Connection object is available, the program calls con.rollback(). If an SQL error occurs before the transaction is committed, rollback() cancels the pending transaction changes.

commit()	rollback()
Saves transaction changes	Cancels uncommitted transaction changes
Makes successful DML changes permanent	Used when pending changes should be undone
con.commit()	con.rollback()

After rollback, the program executes print("There is a problem with sql", e), which displays the error information stored in e.

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

finally:
    if cursor:
        cursor.close()
    if con:
        con.close()
        

If the Cursor object exists it is closed with cursor.close(), and if the Connection object exists it is closed with con.close(). The resources are opened as Connection then Cursor, and closed in the reverse order:

cursor.close()
con.close()
        

The complete error flow is:

                  START
                    │
                    ▼
            Execute INSERT
                    │
                    ▼
             SQL Error Occurs
                    │
                    ▼
 cx_Oracle.DatabaseError as e
                    │
                    ▼
                 if con
                    │
                    ▼
             con.rollback()
                    │
                    ▼
      Cancel Pending Changes
                    │
                    ▼
            Print SQL Error
                    │
                    ▼
                 finally
                    │
                    ▼
            cursor.close()
                    │
                    ▼
              con.close()
                    │
                    ▼
                   END
        

Statement	Purpose
import cx_Oracle	Imports the Oracle Database module
try:	Starts protected Database operations
cx_Oracle.connect(...)	Connects to Oracle Database
con.cursor()	Creates the Cursor object
cursor.execute(...)	Executes the INSERT statement
con.commit()	Permanently saves the inserted record
print(...)	Displays the success message
except cx_Oracle.DatabaseError as e:	Handles Oracle Database errors
con.rollback()	Cancels pending changes if an error occurs
finally:	Runs resource-cleanup code
cursor.close()	Closes the Cursor
con.close()	Closes the Connection

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

In the previous application, we inserted one record into the employees table. In this application, we will insert multiple records by using the executemany() method.

executemany() is a Cursor method used to execute the same parameterized SQL statement for multiple sets of values. Instead of calling execute() separately for every employee, we can provide all employee records together.

cursor.executemany(sql, records)
        

Argument	Purpose
sql	Parameterized SQL statement
records	Collection containing multiple sets of values

SQL Statement
     +
Multiple Records
     │
     ▼
executemany()
     │
     ▼
Multiple Rows Inserted
        

This application demonstrates: inserting multiple employee records, creating a parameterized SQL statement, using named bind variables, storing records in a list of tuples, using executemany(), using commit() and rollback(), and closing Database resources.

The program inserts the following three employee records:

Employee No	Employee Name	Salary	Address
200	Sunny	2000	Mumbai
300	Chinny	3000	Hyd
400	Bunny	4000	Hyd

The complete application inserts all three records with a single executemany() call, then commits the transaction.

Records Inserted Successfully

The program creates the following SQL statement:

sql = "insert into employees values(:eno,:ename,:esal,:eaddr)"
        

This is a parameterized SQL statement. Instead of directly placing actual employee values in the SQL statement, it contains placeholders:

:eno
:ename
:esal
:eaddr
        

The placeholders correspond to the employee information:

Placeholder	Represents
:eno	Employee Number
:ename	Employee Name
:esal	Employee Salary
:eaddr	Employee Address

The placeholders present in the SQL statement are called bind variables. The actual values are supplied separately through the records collection.

A parameterized SQL statement allows the same SQL structure to be reused for multiple records. The SQL structure remains the same; only the employee values change:

(200, 'Sunny', 2000, 'Mumbai')

(300, 'Chinny', 3000, 'Hyd')

(400, 'Bunny', 4000, 'Hyd')
        

This makes the statement suitable for executemany().

The employee records are stored in a list of tuples:

records = [
    (200, 'Sunny', 2000, 'Mumbai'),
    (300, 'Chinny', 3000, 'Hyd'),
    (400, 'Bunny', 4000, 'Hyd')
]
        

The outer structure is a Python list, and each employee record is represented using a tuple. The complete structure is a list of tuples:

records
   │
   ├── (200, 'Sunny', 2000, 'Mumbai')
   │
   ├── (300, 'Chinny', 3000, 'Hyd')
   │
   └── (400, 'Bunny', 4000, 'Hyd')
        

eno	ename	esal	eaddr
200	Sunny	2000	Mumbai
300	Chinny	3000	Hyd
400	Bunny	4000	Hyd

The SQL statement contains four placeholders and each tuple also contains four values, so the mapping is the same for every employee record:

(200, 'Sunny', 2000, 'Mumbai')
  │       │       │       │
  ▼       ▼       ▼       ▼
 eno    ename    esal    eaddr
        

The program inserts all records using:

cursor.executemany(sql, records)
        

The executemany() method executes the same SQL statement for every tuple present in the records list. Instead of calling execute() multiple times, a single executemany() call processes the complete collection.

sql
 │
 │  insert into employees
 │  values(:eno,:ename,:esal,:eaddr)
 │
 ├──────────────────────────────┐
 │                              │
 ▼                              ▼
records                     executemany()
 │                              │
 ├── Record 1 ──────────────────┤
 ├── Record 2 ──────────────────┤
 └── Record 3 ──────────────────┤
                                │
                                ▼
                         Oracle Database
                                │
                                ▼
                        employees Table
        

Conceptually, executemany() applies the INSERT statement to every record:

INSERT INTO employees
VALUES(200,'Sunny',2000,'Mumbai');

INSERT INTO employees
VALUES(300,'Chinny',3000,'Hyd');

INSERT INTO employees
VALUES(400,'Bunny',4000,'Hyd');
        

In the actual Python program, we do not manually write these three INSERT statements. The parameterized SQL and executemany() handle the multiple value sets.

After these records are inserted and committed, the employee data involved in App4 and App5 can be represented as:

+-----+--------+------+---------+
| eno | ename  | esal | eaddr   |
+-----+--------+------+---------+
| 100 | Durga  | 1000 | Hyd     |
| 200 | Sunny  | 2000 | Mumbai  |
| 300 | Chinny | 3000 | Hyd     |
| 400 | Bunny  | 4000 | Hyd     |
+-----+--------+------+---------+
        

The 100 record was inserted by App4, while App5 inserts the remaining three records.

After inserting the records, the program executes con.commit(), which permanently saves all inserted records in the Database.

executemany()
     │
     ▼
Multiple Rows Inserted
     │
     ▼
Transaction Changes
     │
     ▼
con.commit()
     │
     ▼
Changes Saved
        

INSERT is a DML operation, so commit() is required to save the transaction changes permanently.

If an Oracle Database error occurs during insertion, the program handles it with except cx_Oracle.DatabaseError as e and executes con.rollback() to cancel the uncommitted transaction changes.

Multiple employee records are processed as part of the transaction. If a Database error occurs before commit(), rollback cancels the pending transaction changes for all of them.

Record 1
Record 2
Record 3
   │
   ▼
Pending Transaction
   │
   ├── Success → commit()
   │
   └── Error   → rollback()
        

After the rollback check, the program executes print("There is a problem with sql", e) to display the SQL error along with the exception information.

The key difference between the two cursor methods is:

execute()	executemany()
Executes one SQL statement for one record	Executes the same parameterized SQL statement for multiple records
Used in App4 for inserting a single row	Used in App5 for inserting multiple rows
Called separately when processing individual records	Called once for the collection of records

In App5, employee records were written directly inside the program as fixed data:

records = [
    (200, 'Sunny', 2000, 'Mumbai'),
    (300, 'Chinny', 3000, 'Hyd'),
    (400, 'Bunny', 4000, 'Hyd')
]
        

In App6, the employee information is entered at runtime from the keyboard. The program repeatedly asks the user to enter Employee Number, Employee Name, Employee Salary, and Employee Address, and after every insertion it asks whether the user wants to insert another record.

App5	App6
Records are predefined	Records are entered by the user
Static data	Dynamic data
Uses a list of tuples	Uses keyboard input
Uses executemany()	Uses execute() repeatedly

Start Program
     │
     ▼
Connect to Oracle
     │
     ▼
Create Cursor
     │
     ▼
Read Employee Details
     │
     ▼
Execute INSERT
     │
     ▼
Ask "Do you want to insert one more record?"
     │
   ┌─┴─┐
   │   │
  Yes  No
   │   │
   │   ▼
   │ commit()
   │   │
   │   ▼
   │  End
   │
   └──► Read Next Employee
        

The main purpose of App6 is to insert an unknown number of employee records. Because we do not know beforehand how many employees the user wants to enter, the program uses while True and asks after every insertion: Do you want to insert one more record [Yes|No]:. If the answer is No, the loop breaks and con.commit() saves the transaction.

Enter Employee Number:500
Enter Employee Name:Sachin
Enter Employee Salary:5000
Enter Employee Address:Mumbai
Record Inserted Successfully
Do you want to insert one more record [Yes|No]:Yes

Enter Employee Number:600
Enter Employee Name:Dhoni
Enter Employee Salary:6000
Enter Employee Address:Ranchi
Record Inserted Successfully
Do you want to insert one more record [Yes|No]:Yes

Enter Employee Number:700
Enter Employee Name:Kohli
Enter Employee Salary:7000
Enter Employee Address:Delhi
Record Inserted Successfully
Do you want to insert one more record [Yes|No]:No

The program uses while True because the number of employee records is not fixed. One user may want to insert 2 employees while another may want to insert 10. The loop continues until the user chooses to stop.

Each employee field is read from the keyboard:

Statement	Purpose
eno = int(input("Enter Employee Number:"))	Reads the employee number as a string and converts it with int()
ename = input("Enter Employee Name:")	Reads the employee name as a string
esal = float(input("Enter Employee Salary:"))	Reads the salary and converts it with float()
eaddr = input("Enter Employee Address:")	Reads the employee address as a string

Suppose the user enters:

Enter Employee Number:500
Enter Employee Name:Sachin
Enter Employee Salary:5000
Enter Employee Address:Mumbai
        

The Python variables contain:

Variable	Value	Type
eno	500	int
ename	Sachin	str
esal	5000.0	float
eaddr	Mumbai	str

The INSERT query is created with format specifiers:

sql = "insert into employees values(%d,'%s',%f,'%s')"
        

Specifier	Used For
%d	Employee Number
%s	Employee Name
%f	Employee Salary
%s	Employee Address

Notice that string values are placed inside single quotes, '%s', because SQL string values require quotes.

The expression sql % (eno, ename, esal, eaddr) substitutes the current employee values into the SQL string, and cursor.execute() executes it. For the sample input, the generated SQL becomes conceptually:

insert into employees
values(500,'Sachin',5000.000000,'Mumbai')
        

After every successful INSERT, the program prints Record Inserted Successfully and asks: Do you want to insert one more record [Yes|No]:. The user's answer is stored in option.

if option == "No":
    break
        

If the user enters Yes, the condition is False, break is not executed, and the while True loop starts its next iteration. If the user enters No, the condition is True, break terminates the loop.

Because commit() is placed after the loop, the program can execute multiple INSERT operations first, and once the user finishes entering records the transaction is committed:

Insert Record 1
      │
Insert Record 2
      │
Insert Record 3
      │
User Enters No
      │
      ▼
Exit Loop
      │
      ▼
commit()
      │
      ▼
Save Transaction
        

Since commit() is executed only after the loop finishes, the inserted records belong to the current transaction until that commit occurs. If a Database error occurs before commit, the exception handler calls con.rollback(), which rolls back the pending changes in that transaction.

Record 1 ─┐
Record 2 ─┼──► Pending Transaction
Record 3 ─┘          │
                     ▼
                   Error
                     │
                     ▼
                 rollback()
        

The finally block then closes the Cursor with cursor.close() and the Connection with con.close(), whether the program completed successfully or a Database exception occurred.

The program above follows the tutorial's original % string-formatting example. For real applications, values should normally be passed using bind variables instead of constructing SQL by directly inserting user input into the SQL string. This keeps the SQL code and the user-provided values separate.

Approach	Recommendation
sql % (...)	Shown to explain the original tutorial example
Bind variables	Preferred for real Database applications

No output captured.

Feature	App5	App6
Input	Fixed records	Keyboard input
Records	Known beforehand	Entered dynamically
Main Method	executemany()	execute()
Loop	Not required for input	while True
Stopping Condition	List finishes	User enters No
Transaction	commit()	commit()
Error Handling	rollback()	rollback()

The important methods and functions used across all three applications are:

Method / Function	Purpose
cx_Oracle.connect()	Establishes the Oracle Database connection
con.cursor()	Creates the Cursor object
cursor.execute()	Executes one INSERT query
cursor.executemany()	Executes one parameterized query for many records
input()	Reads data from the keyboard
int()	Converts employee number to integer
float()	Converts salary to floating-point value
con.commit()	Permanently saves inserted records
con.rollback()	Cancels pending transaction changes
cursor.close() / con.close()	Closes the Cursor and Connection