Before learning Python Database Programming, first we should understand where data is stored in an application and why databases are required.

As part of an application, we may need to store different types of information.

For example:

• Customer Information
• Billing Information
• Calls Information
• Other Application Data

To store this information, we require Storage Areas.

Application
    │
    ▼
Generates Data
    │
    ▼
Needs Storage
    │
    ▼
Storage Areas

A Storage Area is a location where application data can be stored.

According to the document, Storage Areas are divided into two types:

1. Temporary Storage Areas
2. Permanent Storage Areas

                 Storage Areas
                      │
          ┌───────────┴───────────┐
          │                       │
          ▼                       ▼
Temporary Storage          Permanent Storage
     Areas                       Areas

The major difference between them is how long the stored data remains available.

Temporary Storage Areas are memory locations where data is stored only for a short period of time.

In Python, examples include:

• List
• Tuple
• Dictionary

These Python objects hold data while the program is running.

Python Program Starts
        │
        ▼
Create Python Objects
        │
        ├── List
        ├── Tuple
        └── Dictionary
        │
        ▼
Data Available
While Program Runs

Consider some simple Python objects.

List

students = ["Durga", "Ravi", "Sunny"]

Tuple

employee = (100, "Durga", 1000)

Dictionary

customer = {
    "id": 101,
    "name": "Durga"
}

All these objects can hold information during program execution.

But they are considered temporary storage for this discussion because the data exists in program memory and is not automatically persisted after the program finishes.

Once the Python program completes its execution:

• The objects are destroyed automatically.
• The stored data is lost.

Program Running
      │
      ▼
List / Tuple / Dictionary
      │
      ▼
Data Available
      │
      ▼
Program Completes
      │
      ▼
Objects Destroyed
      │
      ▼
Stored Data Lost

Therefore, Temporary Storage Areas are not suitable for storing data permanently.

Characteristic	Description
Storage Duration	Short-term
Data Availability	Normally available while the program is running
Examples	List, Tuple, Dictionary
After Program Ends	Objects are destroyed and non-persisted data is lost
Permanent Storage	No

Permanent Storage Areas are also called Persistent Storage Areas.

These storage areas are used when application data must be stored permanently.

Examples given in the document are:

• File Systems
• Databases
• Data Warehouses
• Big Data Technologies

             Permanent Storage Areas
                       │
        ┌──────────────┼──────────────┐
        │              │              │
        ▼              ▼              ▼
   File Systems    Databases    Data Warehouses
                                      │
                                      ▼
                            Big Data Technologies

Unlike temporary storage, persistent storage is intended to keep data available even after the application stops.

Temporary Storage	Permanent Storage
Stores data temporarily	Stores data persistently
Data is associated with program execution	Data remains available after program execution
Examples: List, Tuple, Dictionary	Examples: File System, Database, Data Warehouse, Big Data Technologies
Not suitable for permanent application records	Suitable for persistent application records

A File System is one type of Permanent Storage Area.

According to the document:

A File System is provided by the local operating system.

It is suitable for storing a small amount of information.

Application
    │
    ▼
Operating System
    │
    ▼
File System
    │
    ▼
Files
    │
    ▼
Persistent Data

Although File Systems provide permanent storage, they have several limitations for larger and more complex data-management requirements.

The document explains four major limitations of File Systems:

1. Cannot Store Huge Amount of Information
2. No Query Language Support
3. No Security
4. Duplicate Data Problem

               File System
                    │
      ┌─────────────┼─────────────┐
      │             │             │
      ▼             ▼             ▼
Limited for      No Query      No Database-
Huge Data        Language      Level Security
                    │
                    ▼
             Duplicate Data
                    │
                    ▼
           Data Inconsistency

According to the document, a File System is not suitable for storing a very large amount of data.

For small applications, files may be sufficient.

But when the amount of application information becomes very large, managing everything using ordinary files becomes difficult.

Small Amount of Data
        │
        ▼
   File System
        │
        ▼
    Suitable


Very Large Amount of Data
        │
        ▼
   File System
        │
        ▼
  Not Suitable

According to the document, File Systems do not provide query-language support like a database.

Because of this, performing complex operations on stored data becomes difficult.

For example, in a database we can use SQL statements such as:

SELECT * FROM employees;

or:

SELECT * FROM employees
WHERE esal > 5000;

A normal File System does not provide SQL-style query processing automatically.

Database
   │
   ▼
Query Language
   │
   ▼
Easy Data Operations


File System
   │
   ▼
No Built-in SQL Query Language
   │
   ▼
Operations Become More Complex

The document identifies lack of database-style security as another limitation of File Systems.

A database normally provides controlled access through authentication and authorization mechanisms.

For example:

• Username
• Password

File-based storage by itself does not provide the same database-level access-control mechanism.

File System
    │
    ▼
No Built-in Database
Security Mechanism

Another problem discussed in the document is duplicate data.

In simple File Systems, there may be no database constraint mechanism to prevent the same logical record from being stored multiple times.

For example:

100, Durga, 1000, Hyd
100, Durga, 1000, Hyd
100, Durga, 1000, Hyd

Repeated information can create a Data Inconsistency Problem.

Duplicate Data
      │
      ▼
Same Information
Stored Multiple Times
      │
      ▼
Different Copies May
Become Different
      │
      ▼
Data Inconsistency

To overcome the limitations of File Systems, we can use Databases.

Databases provide better facilities for storing, organizing, searching, securing, and managing application data.

File System Problems
        │
        ├── Limited for huge data
        ├── No query-language support
        ├── Limited database-level security
        └── Duplicate-data problems
        │
        ▼
Need Better Data Management
        │
        ▼
     Database

A Database is a Permanent Storage Area used to store and manage application data in an organized manner.

The document explains several advantages of using Databases over File Systems.

Advantages of Databases

1. Can Store Huge Amount of Information
2. Query Language Support
3. Data Security
4. No Data Duplication

According to the document, a Database can store a very large amount of information compared with ordinary File System storage used for application data.

Large Application Data
        │
        ▼
     Database
        │
        ▼
Large Amount of
Structured Information

This makes databases suitable for applications that manage many records.

Every Database provides some form of Query Language support.

For relational databases, SQL is commonly used.

SQL stands for:

Structured Query Language

Using SQL, we can perform operations such as:

• Create
• Insert
• Select
• Update
• Delete

For example:

SELECT * FROM employees;

Because query-language support is available, database operations become easier.

Databases provide security mechanisms for controlling access to stored information.

The document explains that to access data present inside a Database, valid credentials such as:

• Username
• Password

are required.

User
 │
 ▼
Username + Password
 │
 ▼
Database Authentication
 │
 ├── Valid
 │      │
 │      ▼
 │   Access
 │
 └── Invalid
        │
        ▼
      Denied

Therefore, databases provide mechanisms for securing stored information.

Inside a relational Database, data is commonly organized in the form of Tables.

While designing database tables, Database Administrators can use techniques and constraints such as:

• Normalization Techniques
• Unique Key Constraints
• Primary Key Constraints

These mechanisms help control duplicate data.

Database Table Design
        │
        ├── Normalization
        ├── Unique Key
        └── Primary Key
        │
        ▼
Reduce / Prevent
Unwanted Duplication
        │
        ▼
Better Data Consistency

For example, consider an employee table:

eno	ename	esal	eaddr
100	Durga	1000	Hyd
200	Ravi	2000	Mumbai

If eno is declared as a Primary Key, duplicate employee numbers are not allowed.

eno = 100
    │
    ▼
Already Exists
    │
    ▼
Duplicate Primary-Key Value
Not Allowed

File System	Database
Suitable for comparatively small/simple storage requirements	Suitable for managing large structured datasets
No built-in SQL-style query language	Provides query-language support
No database-level security by itself	Provides authentication/security mechanisms
Duplicate-data control is difficult	Constraints and normalization help control duplication
Complex data operations may require custom programming	Queries make many data operations easier

Although Databases solve many File System problems, the document also discusses limitations of traditional databases.

The two limitations highlighted are:

1. Cannot Hold Extremely Huge Data
2. Supports Mainly Structured Data

The document states that a traditional Database is not intended for extremely huge amounts of information such as very large Terabyte-scale datasets.

Normal Structured Data
       │
       ▼
Traditional Database
       │
       ▼
Suitable


Extremely Huge Data
       │
       ▼
Traditional Database
       │
       ▼
May Require More
Advanced Technologies

This is where technologies designed for large-scale distributed data processing can become useful.

The document mainly describes traditional relational Databases as supporting:

• Structured Data
• Tabular Data
• Relational Data

Examples include data arranged in rows and columns.

eno	ename	esal
100	Durga	1000
200	Ravi	2000

This is an example of structured/tabular data.

The document distinguishes structured data from other forms of data.

Data Type	Examples
Structured Data	Tables, rows, columns, relational records
Semi-Structured Data	XML Files
Unstructured Data	Video Files, Audio Files, Images

                    Data
                     │
        ┌────────────┼────────────┐
        │            │            │
        ▼            ▼            ▼
   Structured   Semi-Structured  Unstructured
        │            │            │
        ▼            ▼            ▼
     Tables          XML       Video / Audio
                               / Images

To overcome the limitations discussed for traditional Databases, the document introduces more advanced storage technologies.

• Big Data Technologies
• Data Warehouses

Traditional Database
       │
       ▼
Limitations
       │
       ├── Extremely huge data requirements
       └── Different data forms / analytical requirements
       │
       ▼
Advanced Storage Technologies
       │
       ├── Big Data Technologies
       └── Data Warehouses

Storage Area	Main Purpose in This Tutorial
List / Tuple / Dictionary	Temporary in-program storage
File System	Permanent storage for comparatively small/simple data requirements
Database	Persistent management of large structured data
Data Warehouse	Advanced persistent storage, especially for analytical data
Big Data Technologies	Technologies for very large and varied datasets

Sometimes, as part of programming requirements, we need to connect a Python program with a Database.

After establishing the connection, the Python application can perform different database operations.

Examples include:

• Creating Tables
• Inserting Data
• Updating Data
• Deleting Data
• Selecting Data

The process of communicating with a Database from a Python program is called Python Database Programming.

Python Program
      │
      ▼
Database Connection
      │
      ▼
Database
      │
      ├── Create
      ├── Insert
      ├── Update
      ├── Delete
      └── Select

Operation	Purpose
Create	Create database objects such as tables
Insert	Add new records
Update	Modify existing records
Delete	Remove records
Select	Retrieve records

These operations form the foundation of database programming.

We use SQL to communicate with relational Databases.

SQL stands for:

Structured Query Language

Python is used to send these SQL commands to the Database through an appropriate database driver/module.

So, in simple terms:

• SQL describes the Database operation.
• Python sends or executes those SQL commands through the database interface.

Python Program
      │
      ▼
SQL Command
      │
      ▼
Database Driver
      │
      ▼
Database
      │
      ▼
Operation Performed

For example, suppose Python sends the following SQL command:

SELECT * FROM employees;

The Database executes the query and returns the employee records.

Python
  │
  ▼
"SELECT * FROM employees"
  │
  ▼
Database
  │
  ▼
Execute SELECT
  │
  ▼
Employee Records
  │
  ▼
Python Program

Technology	Responsibility
Python	Application logic and sending/executing SQL through a database module
SQL	Describes operations to perform on relational database data
Database	Stores and manages persistent data and executes supported queries

Python
  │
  │ Sends SQL
  ▼
Database
  │
  │ Executes Operation
  ▼
Result
  │
  ▼
Python

The document lists several databases that can be accessed from Python.

• Oracle
• MySQL
• SQL Server
• GadFly
• SQLite

                   Python
                      │
      ┌───────────────┼───────────────┐
      │               │               │
      ▼               ▼               ▼
    Oracle           MySQL         SQL Server
      │                               │
      └──────────┐           ┌────────┘
                 ▼           ▼
               GadFly      SQLite

Different databases can require different Python drivers or modules for communication.

Python database communication requires an appropriate database-specific module or driver.

Examples provided in the document include:

Database	Module
Oracle Database	cx_Oracle
Microsoft SQL Server	pymssql

For example:

Python Program
      │
      ▼
cx_Oracle
      │
      ▼
Oracle Database

Similarly:

Python Program
      │
      ▼
pymssql
      │
      ▼
Microsoft SQL Server

Python and a database system require a software interface for communication.

A database-specific module or driver provides this interface.

Python Program
      │
      ▼
Database Module / Driver
      │
      ▼
Database

Later parts of this tutorial explain this concept in detail while working with Oracle and MySQL.

                        Application Data
                              │
                              ▼
                         Storage Areas
                              │
                 ┌────────────┴────────────┐
                 │                         │
                 ▼                         ▼
         Temporary Storage          Permanent Storage
                 │                         │
       ┌─────────┼─────────┐        ┌──────┼──────────────┐
       │         │         │        │      │              │
       ▼         ▼         ▼        ▼      ▼              ▼
      List      Tuple   Dictionary Files Database   Data Warehouse
                                            │
                                            ▼
                                      Structured Data
                                            │
                                            ▼
                                  Python Database Programming
                                            │
                                            ▼
                                      Python Program
                                            │
                                            ▼
                                           SQL
                                            │
                                            ▼
                                   Database Module/Driver
                                            │
                                            ▼
                                         Database
                                            │
                          ┌─────────────────┼─────────────────┐
                          │                 │                 │
                          ▼                 ▼                 ▼
                        Insert            Update            Delete
                          │                                   │
                          └──────────┐             ┌──────────┘
                                     ▼             ▼
                                       Select / Create

Program Starts
      │
      ▼
Python Application
      │
      ▼
Connect to Database
      │
      ▼
Send SQL Command
      │
      ▼
Database Executes Command
      │
      ▼
Database Operation
      │
      ├── Create Table
      ├── Insert Data
      ├── Update Data
      ├── Delete Data
      └── Select Data
      │
      ▼
Return Result if Required
      │
      ▼
Python Program
      │
      ▼
Program Ends

The exact connection and execution steps will be covered in the next part.

Storage Area	Permanent?	Typical Data	Main Point
List	No	Python objects	Temporary program data
Tuple	No	Python objects	Temporary program data
Dictionary	No	Key-value program data	Temporary program data
File System	Yes	Files	Persistent storage
Database	Yes	Structured/tabular data	Query-based data management
Data Warehouse	Yes	Large analytical datasets	Advanced storage and analytics
Big Data Technologies	Yes	Very large/varied datasets	Large-scale data processing and storage

File System Limitation	Database Advantage
Not suitable for very large application data	Can manage large amounts of structured information
No built-in SQL-style query support	Provides query-language support
No database-level security mechanism by itself	Provides authentication and access controls
Duplicate-data control is difficult	Keys, constraints and normalization help control duplication

Term	Meaning
Storage Area	A location used for storing application data
Temporary Storage	Storage used while a program is executing
Persistent Storage	Storage intended to retain data permanently
File System	Operating-system-based file storage
Database	System for organized persistent data storage and management
Structured Data	Data organized according to a defined structure such as rows and columns
Semi-Structured Data	Data with some structure, such as XML
Unstructured Data	Data such as video, audio and images
SQL	Structured Query Language
Python Database Programming	Using Python to communicate with databases and perform database operations

Storage Areas
     │
     ├── Temporary
     │      │
     │      ├── List
     │      ├── Tuple
     │      └── Dictionary
     │
     └── Permanent
            │
            ├── File System
            ├── Database
            ├── Data Warehouse
            └── Big Data Technologies


File System
     │
     ├── Suitable for small/simple storage
     ├── No built-in SQL query language
     ├── Limited database-level security
     └── Duplicate-data problems


Database
     │
     ├── Large structured data
     ├── Query-language support
     ├── Security
     └── Duplicate-data control


Python Database Programming
     │
     ▼
Python
     │
     ▼
SQL
     │
     ▼
Database
     │
     ├── Create
     ├── Insert
     ├── Update
     ├── Delete
     └── Select

• Applications require Storage Areas to store information such as customer, billing and call information.
• Storage Areas are broadly divided into Temporary Storage Areas and Permanent Storage Areas.
• List, Tuple and Dictionary are examples of temporary in-program storage.
• Temporary data that is not persisted is lost after the program terminates.
• Permanent Storage Areas are also called Persistent Storage Areas.
• File Systems, Databases, Data Warehouses and Big Data Technologies are examples of Permanent Storage Areas.
• A File System is provided by the local operating system and is suitable for comparatively small/simple storage requirements.
• The document identifies limitations of File Systems such as difficulty handling huge data, lack of query-language support, lack of database-style security, and duplicate-data problems.
• Databases overcome many File System limitations.
• Databases provide query-language support and security mechanisms.
• Normalization, Unique Keys and Primary Keys can help prevent unwanted duplicate data.
• Traditional relational databases primarily work with structured, tabular and relational data.
• The document gives XML as an example of Semi-Structured Data.
• Video, Audio and Images are examples of Unstructured Data.
• Big Data Technologies and Data Warehouses are introduced for more advanced storage requirements.
• Python Database Programming means connecting a Python program with a Database and performing database operations.
• Common operations include Creating Tables, Inserting, Updating, Deleting and Selecting data.
• SQL stands for Structured Query Language.
• Python sends/executes SQL commands through an appropriate database interface.
• The document lists Oracle, MySQL, SQL Server, GadFly and SQLite among databases accessible from Python.
• Examples of database-specific modules include cx_Oracle for Oracle and pymssql for Microsoft SQL Server.

1. Do not confuse temporary Python objects with persistent storage. A List, Tuple or Dictionary does not automatically survive after program termination.
2. Permanent Storage Areas are also known as Persistent Storage Areas.
3. A File System is useful for persistent file storage, but database systems provide specialized facilities for structured data management.
4. Query-language support is one of the major advantages of relational databases.
5. Database constraints such as Primary Keys and Unique Keys help control duplicate values.
6. Normalization is a database-design technique used to organize data and reduce undesirable redundancy.
7. Authentication through a username and password is one part of database security; real database systems can provide additional authorization mechanisms.
8. The document's database limitations describe traditional relational-database use cases. Modern database systems can also handle very large datasets and various non-tabular data types, depending on the technology.
9. Python itself does not replace SQL. Python application code uses database drivers/modules to send SQL operations to relational databases.
10. Different database systems can require different Python drivers.
11. This part provides the foundation required before learning the standard Python Database Programming steps.

Application
    │
    ▼
Needs to Store Data
    │
    ▼
Storage Areas
    │
    ├──────── Temporary
    │            │
    │            ├── List
    │            ├── Tuple
    │            └── Dictionary
    │
    └──────── Permanent
                 │
                 ├── File System
                 ├── Database
                 ├── Data Warehouse
                 └── Big Data Technologies
                         │
                         ▼
                  Database Selected
                         │
                         ▼
                  Python Program
                         │
                         ▼
                        SQL
                         │
                         ▼
                     Database
                         │
              ┌──────────┼──────────┐
              │          │          │
              ▼          ▼          ▼
            Insert     Update     Delete
              │                     │
              └─────────┬───────────┘
                        ▼
                       Select

Part 1 Completed

Next: Part 2 — Standard Steps for Python Database Programming

Whenever we want to communicate with a Database from a Python program, we should follow a standard sequence of steps.

These steps form the basic procedure of Python Database Programming.

According to the document, there are 7 standard steps.

Python Program
     │
     ▼
Import Database Module
     │
     ▼
Establish Connection
     │
     ▼
Create Cursor
     │
     ▼
Execute SQL Queries
     │
     ▼
Commit / Rollback
     │
     ▼
Fetch Results
     │
     ▼
Close Resources

The seven standard steps are:

1. Import the database-specific module
2. Establish the connection
3. Create a Cursor object
4. Execute SQL queries
5. Commit or Rollback changes
6. Fetch the results
7. Close the resources

Step	Operation	Main Method / Statement
1	Import database-specific module	import cx_Oracle
2	Establish connection	connect()
3	Create Cursor object	cursor()
4	Execute SQL queries	execute(), executescript(), executemany()
5	Save or cancel changes	commit(), rollback()
6	Fetch SELECT results	fetchone(), fetchall(), fetchmany(n)
7	Close resources	close()

The first step is to import the module required for the Database we want to use.

For example, the document uses the following module for Oracle Database:

import cx_Oracle

The database-specific module acts as a bridge between the Python program and the Database.

Python Program
      │
      ▼
Database-Specific Module
      │
      ▼
Database

For Oracle in the document:

Python
   │
   ▼
cx_Oracle
   │
   ▼
Oracle Database

Python needs an appropriate module or driver to communicate with a particular Database.

The module provides the functions and classes required to:

• Connect to the Database
• Create Cursor objects
• Execute SQL statements
• Fetch results
• Manage transactions
• Close database resources

For the Oracle examples in this chapter:

import cx_Oracle

is used.

After importing the database-specific module, the next step is to establish a connection between the Python program and the Database.

A Connection object can be created by using the:

connect()

function.

Syntax

con = cx_Oracle.connect(database_information)

Example

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

Here:

• con is the Connection object.
• connect() establishes the connection with the Oracle Database.

Consider:

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

The connection information used in the example is:

Part	Value
Username	scott
Password	tiger
Database / Host information	localhost

scott / tiger @ localhost
  │      │         │
  │      │         └── Database connection information
  │      │
  │      └── Password
  │
  └── Username

The Connection object returned by connect() is stored in:

con

The object returned by:

cx_Oracle.connect(...)

is called a Connection object.

In the document, it is stored in:

con

The Connection object is important because it is used for operations such as:

• Creating a Cursor object
• Committing transactions
• Rolling back transactions
• Closing the Database connection

              con
               │
       ┌───────┼───────┐
       │       │       │
       ▼       ▼       ▼
    cursor() commit() rollback()
       │
       └──────────┐
                  ▼
               close()

To execute SQL queries and hold their results, we require a special object called a Cursor object.

A Cursor object can be created by using the:

cursor()

method of the Connection object.

Syntax

cursor = con.cursor()

Here:

• con represents the Connection object.
• cursor() creates a Cursor object.
• cursor stores the returned Cursor object.

A Cursor object is used to execute SQL statements and work with query results.

Connection Object
      │
      ▼
con.cursor()
      │
      ▼
Cursor Object
      │
      ▼
Execute SQL

For example:

cursor = con.cursor()

After creating the Cursor object, we can use it to execute SQL queries.

cursor.execute(...)

Connection Object	Cursor Object
Represents the connection with the Database	Used for executing SQL statements
Created using connect()	Created using cursor()
Example variable: con	Example variable: cursor
Used for commit() and rollback()	Used for execute() and fetching results
Closed using con.close()	Closed using cursor.close()

After creating the Cursor object, we can execute SQL queries.

SQL queries are executed by using methods of the Cursor object.

The document lists the following three important methods:

1. execute()
2. executescript()
3. executemany()

Cursor Object
     │
     ├── execute()
     │
     ├── executescript()
     │
     └── executemany()

The execute() method is used to execute a single SQL query.

Syntax

cursor.execute(sqlquery)

Example

cursor.execute("select * from employees")

This statement executes:

select * from employees

against the connected Database.

Cursor
  │
  ▼
execute()
  │
  ▼
Single SQL Query
  │
  ▼
Database

Consider:

cursor.execute("select * from employees")

Here:

• cursor is the Cursor object.
• execute() executes one SQL statement.
• select * from employees retrieves employee records.

The result of a SELECT query can then be obtained using fetch methods such as:

fetchone()
fetchall()
fetchmany(n)

The document describes executescript() as a method used to execute a string containing multiple SQL queries separated by a semicolon:

;

Syntax

cursor.executescript(sqlqueries)

Conceptually:

SQL Query 1;
SQL Query 2;
SQL Query 3;

can be provided as a SQL script when the particular database interface supports this method.

Cursor
   │
   ▼
executescript()
   │
   ▼
Multiple SQL Statements
Separated by ;

The executemany() method is used to execute a parameterized query for multiple sets of values.

Syntax shown in the document

cursor.executemany()

Later in this chapter, we will use this method for inserting multiple employee records.

Conceptually:

One Parameterized SQL Query
            │
            ▼
     Multiple Records
            │
            ▼
      executemany()
            │
            ▼
        Database

Method	Purpose
execute()	Executes a single SQL query
executescript()	Executes a script/string containing multiple SQL queries separated by semicolons, when supported
executemany()	Executes a parameterized SQL operation for multiple sets of values

The next step is transaction management.

This step is especially required for DML — Data Manipulation Language operations such as:

• INSERT
• UPDATE
• DELETE

DML Operations
      │
      ├── INSERT
      ├── UPDATE
      └── DELETE
      │
      ▼
Commit or Rollback

Two important methods are:

commit()
rollback()

The commit() method saves the changes permanently to the Database.

Syntax

con.commit()

Purpose:

• Saves the changes made during the transaction.
• Makes the transaction changes permanent.

INSERT / UPDATE / DELETE
          │
          ▼
   Temporary Changes
          │
          ▼
      con.commit()
          │
          ▼
   Changes Saved

The rollback() method rolls back the temporary changes in the current transaction.

Syntax

con.rollback()

Purpose:

• Cancels transaction changes that have not been committed.
• Returns the transaction to the appropriate previous state according to the database transaction rules.

INSERT / UPDATE / DELETE
          │
          ▼
   Temporary Changes
          │
          ▼
     Error / Problem
          │
          ▼
    con.rollback()
          │
          ▼
Uncommitted Changes
    Rolled Back

commit()	rollback()
Saves transaction changes	Cancels uncommitted transaction changes
Makes changes permanent	Rolls changes back
Used after successful DML operations	Commonly used when a transaction must be cancelled, such as after an error
con.commit()	con.rollback()

          DML Query
             │
             ▼
     INSERT / UPDATE / DELETE
             │
             ▼
       Execute Query
             │
             ▼
       Was it successful?
          /        \
        Yes         No
         │           │
         ▼           ▼
     commit()    rollback()
         │           │
         ▼           ▼
    Save Changes   Cancel
                  Uncommitted
                   Changes

Fetching results is required for SELECT queries.

After executing a SELECT statement, the Cursor object can be used to retrieve the returned rows.

The document lists three important fetch methods:

1. fetchone()
2. fetchall()
3. fetchmany(n)

SELECT Query
     │
     ▼
Cursor Result
     │
     ├── fetchone()
     ├── fetchall()
     └── fetchmany(n)

The fetchone() method fetches only one row from the result set.

Example

data = cursor.fetchone()
print(data)

Here:

• cursor.fetchone() fetches one row.
• The fetched row is stored in data.
• print(data) displays the row.

SELECT Result
     │
     ▼
fetchone()
     │
     ▼
One Row
     │
     ▼
data

The fetchall() method fetches all remaining rows from the query result.

The document states that it returns a list of rows.

Example

data = cursor.fetchall()

for row in data:
    print(row)

The fetched rows are stored in:

data

A for loop is then used to process every row.

SELECT Query
     │
     ▼
Multiple Rows Returned
     │
     ▼
cursor.fetchall()
     │
     ▼
Collection of Rows
     │
     ▼
data
     │
     ▼
for row in data
     │
     ▼
Print Each Row

The fetchmany(n) method fetches up to the specified number of rows from the result set.

Syntax

cursor.fetchmany(n)

For example:

cursor.fetchmany(3)

requests up to three rows from the current result-set position.

Result Set
    │
    ▼
fetchmany(3)
    │
    ▼
Up to 3 Rows

Method	Purpose
fetchone()	Fetches one row
fetchall()	Fetches all remaining rows
fetchmany(n)	Fetches up to n rows

SELECT Result
     │
     ├── Need one row?
     │       └── fetchone()
     │
     ├── Need all rows?
     │       └── fetchall()
     │
     └── Need a limited batch?
             └── fetchmany(n)

DML Queries	SELECT Queries
INSERT	Retrieve data
UPDATE	Use fetch methods to obtain rows
DELETE	fetchone()
Normally require transaction handling	fetchall()
commit() / rollback()	fetchmany(n)

After completing all Database operations, it is highly recommended to close the resources.

The document says that resources should be closed in the reverse order of their opening.

We created:

1. Connection
2. Cursor

Therefore, while closing:

1. Cursor
2. Connection

Close Cursor

cursor.close()

Close Connection

con.close()

The Cursor depends on the Connection.

Connection
    │
    ▼
Cursor

Therefore, cleanup is performed in reverse:

Cursor
    │
    ▼
Close Cursor
    │
    ▼
Close Connection

The code is:

cursor.close()
con.close()

Opening Order	Closing Order
1. Connection	1. Cursor
2. Cursor	2. Connection

OPEN

Connection
    │
    ▼
Cursor


CLOSE

Cursor
    │
    ▼
Connection

According to the document, the following methods are important for Python Database Programming:

connect()

cursor()

execute()

executescript()

executemany()

commit()

rollback()

fetchone()

fetchall()

fetchmany(n)

fetch

close()

Method	Purpose
connect()	Establishes a Database connection
cursor()	Creates a Cursor object
execute()	Executes a single SQL query
executescript()	Executes multiple SQL statements as a script when supported
executemany()	Executes a parameterized operation for multiple sets of values
commit()	Saves transaction changes
rollback()	Rolls back uncommitted transaction changes
fetchone()	Fetches one row
fetchall()	Fetches all remaining rows
fetchmany(n)	Fetches up to n rows
fetch	Listed in the source document among important database-programming methods
close()	Closes database resources

The document states that these important database-programming methods remain generally common while working with different databases.

connect()
cursor()
execute()
executescript()
executemany()
commit()
rollback()
fetchone()
fetchall()
fetchmany(n)
close()

However, the database-specific module, connection details, parameter styles, and exact API support can differ between database drivers.

For example:

Oracle
   │
   ▼
cx_Oracle


MySQL
   │
   ▼
MySQL-specific Connector

The overall database-programming workflow remains similar.

                  Program Starts
                        │
                        ▼
              Import Database Module
                        │
                        ▼
               Establish Connection
                        │
                        ▼
                Create Cursor
                        │
                        ▼
              Execute SQL Queries
                        │
              ┌─────────┴─────────┐
              │                   │
              ▼                   ▼
          DML Query           SELECT Query
              │                   │
      INSERT / UPDATE / DELETE    │
              │                   │
              ▼                   ▼
       Commit / Rollback      Fetch Results
                                  │
                      ┌───────────┼───────────┐
                      │           │           │
                      ▼           ▼           ▼
                  fetchone()  fetchall()  fetchmany(n)
                      │           │           │
                      └───────────┼───────────┘
                                  │
                                  ▼
                           Close Cursor
                                  │
                                  ▼
                         Close Connection
                                  │
                                  ▼
                             Program Ends

Step 1
Import Database-Specific Module
              │
              ▼
       import cx_Oracle
              │
              ▼
Step 2
Establish Connection
              │
              ▼
con = cx_Oracle.connect(...)
              │
              ▼
Step 3
Create Cursor Object
              │
              ▼
cursor = con.cursor()
              │
              ▼
Step 4
Execute SQL Query
              │
              ▼
cursor.execute(...)
              │
              ▼
Step 5
Commit / Rollback
              │
              ▼
con.commit()
     OR
con.rollback()
              │
              ▼
Step 6
Fetch SELECT Results
              │
      ┌───────┼───────┐
      │       │       │
      ▼       ▼       ▼
 fetchone  fetchall  fetchmany
              │
              ▼
Step 7
Close Resources
              │
              ▼
cursor.close()
              │
              ▼
con.close()

The previous program combines the main steps required for a simple SELECT operation.

1. Import Module

import cx_Oracle

2. Establish Connection

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

3. Create Cursor

cursor = con.cursor()

4. Execute SELECT

cursor.execute("select * from employees")

5. Fetch All Rows

data = cursor.fetchall()

6. Display Rows

for row in data:
    print(row)

7. Close Resources

cursor.close()
con.close()

No commit() is shown here because this conceptual example only performs a SELECT operation.

Import cx_Oracle
      │
      ▼
Connect to Oracle
      │
      ▼
Create Cursor
      │
      ▼
Execute INSERT
      │
      ▼
con.commit()
      │
      ▼
Changes Saved
      │
      ▼
Close Cursor
      │
      ▼
Close Connection

This demonstrates the difference between a SELECT operation and a DML operation.

For INSERT, UPDATE and DELETE operations, transaction management using commit() or rollback() becomes important.

Step	Description
Step 1	Import the database-specific module
Step 2	Establish a connection using connect()
Step 3	Create a Cursor object using cursor()
Step 4	Execute SQL queries using execute(), executescript(), or executemany()
Step 5	Save or cancel transaction changes using commit() or rollback()
Step 6	Fetch SELECT-query results using fetchone(), fetchall(), or fetchmany(n)
Step 7	Close the Cursor and Connection using close()

1. IMPORT
   │
   └── import cx_Oracle
          │
          ▼
2. CONNECT
   │
   └── con = cx_Oracle.connect(...)
          │
          ▼
3. CURSOR
   │
   └── cursor = con.cursor()
          │
          ▼
4. EXECUTE
   │
   ├── execute()
   ├── executescript()
   └── executemany()
          │
          ▼
5. TRANSACTION
   │
   ├── commit()
   └── rollback()
          │
          ▼
6. FETCH
   │
   ├── fetchone()
   ├── fetchall()
   └── fetchmany(n)
          │
          ▼
7. CLOSE
   │
   ├── cursor.close()
   └── con.close()

• Python Database Programming follows a standard sequence of steps.
• The document identifies seven standard steps.
• The first step is importing the database-specific module.
• For the Oracle examples, the document uses cx_Oracle.
• connect() establishes a connection between Python and the Database.
• The returned object is called a Connection object.
• cursor() creates a Cursor object.
• The Cursor object is used to execute SQL statements and work with query results.
• execute() executes a single SQL query.
• The document describes executescript() for executing a string containing multiple SQL queries separated by semicolons.
• executemany() is used with a parameterized operation and multiple sets of values.
• INSERT, UPDATE and DELETE are DML operations.
• commit() saves transaction changes permanently.
• rollback() rolls back uncommitted transaction changes.
• Fetch methods are used for SELECT-query results.
• fetchone() fetches one row.
• fetchall() fetches all remaining rows.
• fetchmany(n) fetches up to the requested number of rows.
• Database resources should be closed after completing operations.
• The Cursor should be closed before the Connection when following the reverse order of resource creation.
• cursor.close() closes the Cursor.
• con.close() closes the Connection.

1. The seven steps form the basic workflow for Python Database Programming.
2. The database-specific module can change depending on the Database.
3. The document uses cx_Oracle for its Oracle examples.
4. A Connection object represents the active database connection.
5. A Cursor object is required for executing SQL statements.
6. execute() is used for a single SQL statement.
7. executemany() is useful when the same parameterized operation must be performed for multiple records.
8. commit() and rollback() are transaction-management methods.
9. Use fetch methods when processing rows returned by SELECT queries.
10. fetchone(), fetchall() and fetchmany(n) serve different result-fetching requirements.
11. Always release Database resources after use.
12. Resources should be closed in reverse order: Cursor first, then Connection.
13. The source document lists fetch among important methods, but standard Python DB-API cursor interfaces normally expose specific methods such as fetchone(), fetchmany(), and fetchall().
14. Although the overall workflow is common, exact methods supported by a driver can differ. For example, executescript() is not a universal method of every Python database driver.
15. Connection syntax and SQL parameter styles can also vary between database drivers.

                 Python Database Programming
                            │
                            ▼
                  1. Import Module
                            │
                            ▼
                  2. Connect Database
                            │
                            ▼
                   3. Create Cursor
                            │
                            ▼
                    4. Execute SQL
                            │
                  ┌─────────┴─────────┐
                  │                   │
                  ▼                   ▼
                DML                 SELECT
                  │                   │
                  ▼                   ▼
        5. Commit / Rollback     6. Fetch Data
                  │                   │
                  └─────────┬─────────┘
                            ▼
                   7. Close Cursor
                            │
                            ▼
                 Close Connection
                            │
                            ▼
                           END

Part 2 Completed

Next: Part 3 — Working with Oracle Database: Driver, cx_Oracle, Installation & Testing

Before a Python program can communicate with an Oracle Database, we require a special software component called a Driver or Connector.

This part explains:

• Working with Oracle Database
• Why a Driver is required
• Driver / Connector
• cx_Oracle
• Features of cx_Oracle
• Installing cx_Oracle
• Installation output
• Testing the installation
• help("modules")
• Important Points

Python Program
      │
      ▼
Driver / Connector
      │
      ▼
Oracle Database

From a Python program, if we want to communicate with any Database, some translator is required.

The translator is responsible for converting:

• Python calls into Database-specific calls
• Database-specific calls into Python calls

This translator is called a:

Driver / Connector

Therefore, a Driver acts as the communication layer between the Python application and the Database.

A Python program and an Oracle Database use different interfaces for communication.

Therefore, an intermediate component is required to translate the communication between them.

This component is the Database Driver.

Python Program
      │
      │ Python Calls
      ▼
Driver / Connector
      │
      │ Oracle-Specific Calls
      ▼
Oracle Database

When Oracle sends information back:

Oracle Database
      │
      │ Oracle Response
      ▼
Driver / Connector
      │
      │ Python-Compatible Response
      ▼
Python Program

A Driver or Connector is a translator between a Python program and a Database.

Its main responsibilities are:

1. Receive calls from the Python program.
2. Convert Python calls into Database-specific calls.
3. Send those calls to the Database.
4. Receive responses from the Database.
5. Convert Database-specific responses into a form usable by Python.

             Driver / Connector
                    │
        ┌───────────┴───────────┐
        │                       │
        ▼                       ▼
Python → Database        Database → Python
Translation              Translation

The communication architecture can be represented as:

+-----------------------+
|    Python Program     |
+-----------------------+
           │
           │ Python Calls
           ▼
+-----------------------+
|   Driver / Connector  |
|      cx_Oracle        |
+-----------------------+
           │
           │ Oracle-Specific Calls
           ▼
+-----------------------+
|    Oracle Database    |
+-----------------------+

The Driver works as a bridge between Python and Oracle Database.

The communication is not only from Python to Oracle. It works in both directions.

Python Program
      │
      │ Request
      ▼
cx_Oracle
      │
      │ Oracle Request
      ▼
Oracle Database
      │
      │ Result / Response
      ▼
cx_Oracle
      │
      │ Python-Compatible Result
      ▼
Python Program

Therefore, the Driver handles both requests and responses.

According to the document, the Driver required for Oracle Database is:

cx_Oracle

cx_Oracle is a Python extension module that enables access to Oracle Database.

Python
   │
   ▼
cx_Oracle
   │
   ▼
Oracle Database

In the examples that follow in this chapter, cx_Oracle is used for Python-to-Oracle communication.

cx_Oracle is a Python extension module that enables Python programs to access Oracle Database.

It provides the functionality required to:

• Establish a connection with Oracle Database
• Create Cursor objects
• Execute SQL statements
• Fetch records
• Manage transactions
• Close database resources

For example:

import cx_Oracle

imports the Oracle Database module used throughout the source tutorial.

According to the document, the important features of cx_Oracle are:

• It is a Python extension module.
• It enables access to Oracle Database.
• It supports both Python 2 and Python 3.
• It can work with Oracle Database versions 9, 10, 11 and 12.

Feature	Description
Module	cx_Oracle
Type	Python Extension Module
Purpose	Access Oracle Database from Python
Python Versions in Source	Python 2 and Python 3
Oracle Versions in Source	9, 10, 11 and 12

The source document specifically mentions that cx_Oracle can work with the following Oracle Database versions:

Oracle 9
Oracle 10
Oracle 11
Oracle 12

Graphically:

               cx_Oracle
                   │
       ┌───────────┼───────────┐
       │           │           │
       ▼           ▼           ▼
   Oracle 9    Oracle 10   Oracle 11
                               │
                               ▼
                           Oracle 12

Note: These versions reflect the source document's original environment and should be understood as historical tutorial information.

Before using cx_Oracle, it must be installed in the Python environment.

The document instructs us to install it from the Normal Command Prompt.

It should not be typed inside the Python Console.

Command Prompt

D:\python_classes>pip install cx_Oracle

The important installation command is:

pip install cx_Oracle

Run:

pip install cx_Oracle

from the operating system's Command Prompt.

Correct

D:\python_classes>pip install cx_Oracle

Not the Python Console

>>> pip install cx_Oracle

pip install ... is a command-line installation command, not normal Python source code.

Open Command Prompt
        │
        ▼
Run Installation Command
        │
        ▼
pip install cx_Oracle
        │
        ▼
pip Finds Package
        │
        ▼
Package Downloaded
        │
        ▼
Package Installed
        │
        ▼
Installation Successful

The document shows installation output similar to the following:

Collecting cx_Oracle
Downloading cx_Oracle-6.0.2-cp36-cp36m-win32.whl (100kB)
100% |-----------| 102kB 256kB/s
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-6.0.2

The important success message is:

Successfully installed cx-Oracle-6.0.2

This indicates that the package was installed successfully in the environment used by the source tutorial.

Let us understand the major parts of the displayed installation output.

1. Collecting Package

Collecting cx_Oracle

pip starts locating the required package.

2. Downloading Package

Downloading cx_Oracle-6.0.2-cp36-cp36m-win32.whl

The required package file is downloaded.

3. Installing Package

Installing collected packages: cx-Oracle

The downloaded package is installed.

4. Successful Installation

Successfully installed cx-Oracle-6.0.2

The installation has completed successfully.

The source installation output contains:

cx_Oracle-6.0.2-cp36-cp36m-win32.whl

This is the package file downloaded in the environment used by the original tutorial.

Part	Meaning in the Source Environment
cx_Oracle	Package name
6.0.2	Package version
cp36	CPython 3.6 tag
win32	32-bit Windows platform tag
.whl	Python wheel package

You do not need to manually type this filename when using the normal pip install cx_Oracle command.

After installation, we should verify whether the module is available in Python.

The source document uses:

help("modules")

for verification.

Open the Python Console and execute:

>>> help("modules")

Python displays the available modules.

We should check whether:

cx_Oracle

appears in that list.

Install cx_Oracle
      │
      ▼
Open Python Console
      │
      ▼
Run
help("modules")
      │
      ▼
Python Displays Modules
      │
      ▼
Search for cx_Oracle
      │
      ▼
Is cx_Oracle Present?
     /       \
   Yes        No
    │          │
    ▼          ▼
Installation  Check
Verified      Installation

The output of:

help("modules")

contains a large list of modules.

The source document shows cx_Oracle among the available modules.

A partial representation of the output shown in the document is:

_multiprocessing
_opcode
_operator
_osx_support
_overlapped
_pickle
_pydecimal
_pyio
_random
_sha1
_sha256
_sha3
_sha512
_signal
_sitebuiltins
_socket
_sqlite3
_sre
_ssl
_stat
...
csv
ctypes
cx_Oracle
data
datetime
dbm
decimal
difflib
dis
distutils
doctest
dummy_threading
durgamath
easy_install
...
parser
pathlib
pdb
pick
pickle
pickletools
pip
pipes
pkg_resources
pkgutil
platform
plistlib
polymorph
...
urllib
uu
uuid

The important entry for this tutorial is:

cx_Oracle

If cx_Oracle appears in the output of:

help("modules")

then the module is available to that Python environment.

help("modules")
      │
      ▼
List of Installed / Available Modules
      │
      ▼
cx_Oracle Found
      │
      ▼
Module Installation Verified

Two different environments are used during installation and testing.

Environment	Command	Purpose
Command Prompt	pip install cx_Oracle	Install the package
Python Console	help("modules")	Check available Python modules

Command Prompt
      │
      ▼
pip install cx_Oracle
      │
      ▼
Install Package
      │
      ▼
Python Console
      │
      ▼
help("modules")
      │
      ▼
Verify Package

For practical testing, we can also try importing the module:

import cx_Oracle

If the module is installed correctly and available to the current Python environment, the import should complete without a module-not-found error.

For example:

>>> import cx_Oracle
>>>

If Python immediately returns to the prompt without an import error, the module was found.

This is an additional practical verification method. The source document itself specifically demonstrates help("modules").

If Python cannot find the module, an error similar to the following may occur:

ModuleNotFoundError: No module named 'cx_Oracle'

This usually means that the package is not available in the Python environment currently running the program.

The basic relationship is:

Python Environment A
       │
       ├── cx_Oracle installed
       │
       └── import cx_Oracle works


Python Environment B
       │
       ├── cx_Oracle not installed
       │
       └── import cx_Oracle fails

Therefore, installation and program execution should use the intended Python environment.

After installation, cx_Oracle can participate in the database communication process.

Step 1
Python Program
      │
      ▼
Import cx_Oracle
      │
      ▼
Step 2
Use cx_Oracle API
      │
      ▼
Step 3
Driver Communicates
with Oracle
      │
      ▼
Step 4
Oracle Executes
Database Operation
      │
      ▼
Step 5
Result Returned
through Driver
      │
      ▼
Python Program

The actual connection program will be covered in the next part.

Layer	Role
Python Program	Contains application logic and database calls
cx_Oracle	Provides the Python interface used to communicate with Oracle
Oracle Database	Stores data and executes supported database operations

Application Logic
      │
      ▼
Python
      │
      ▼
cx_Oracle
      │
      ▼
Oracle Database

In this chapter, cx_Oracle is described as the Oracle Driver/Connector and as a Python extension module.

For learning purposes, remember the following flow:

Python Program
      │
      ▼
Oracle Database Module / Driver
      │
      ▼
cx_Oracle
      │
      ▼
Oracle Database

The main purpose is to provide communication between Python and Oracle.

                    START
                      │
                      ▼
          Need Oracle Communication
                      │
                      ▼
              Driver Required
                      │
                      ▼
                 cx_Oracle
                      │
                      ▼
           Open Command Prompt
                      │
                      ▼
          pip install cx_Oracle
                      │
                      ▼
             Package Installed
                      │
                      ▼
            Open Python Console
                      │
                      ▼
              help("modules")
                      │
                      ▼
            Search for cx_Oracle
                      │
              ┌───────┴───────┐
              │               │
              ▼               ▼
            Found          Not Found
              │               │
              ▼               ▼
          Installation     Recheck
           Verified       Environment /
                          Installation
              │
              ▼
        Ready for Oracle
       Database Programming
              │
              ▼
             END

Stage	Environment	Command	Result
Installation	Command Prompt	pip install cx_Oracle	Installs the package
Verification in Source	Python Console	help("modules")	Shows available modules
Simple Import Check	Python Console / Program	import cx_Oracle	Checks whether the module can be imported

Working with Oracle Database
           │
           ▼
      Driver Required
           │
           ▼
       cx_Oracle
           │
           ├── Python Extension Module
           │
           ├── Oracle Access
           │
           ├── Python 2 & 3
           │
           └── Oracle 9, 10, 11, 12
           │
           ▼
        Installation
           │
           ▼
pip install cx_Oracle
           │
           ▼
         Testing
           │
           ▼
     help("modules")
           │
           ▼
   Find "cx_Oracle"
           │
           ▼
Installation Verified

Topic	Description
Driver / Connector	Translator between Python and Oracle Database
Oracle Driver in Source	cx_Oracle
Purpose	Converts communication between Python and Oracle Database
Module Type	Python extension module
Python Versions Mentioned	Python 2 and Python 3
Oracle Versions Mentioned	9, 10, 11 and 12
Installation Command	pip install cx_Oracle
Where to Install	Normal Command Prompt
Installation Verification	help("modules")
Success Check	cx_Oracle should appear in the module list

• A Driver is required for communication between a Python program and Oracle Database.
• The Driver acts as a translator between Python calls and Database-specific calls.
• The Oracle Driver used in the source tutorial is cx_Oracle.
• cx_Oracle is a Python extension module for accessing Oracle Database.
• The source document states that cx_Oracle supports Python 2 and Python 3.
• The source document mentions Oracle Database versions 9, 10, 11 and 12.
• The installation command shown in the document is pip install cx_Oracle.
• The installation command should be executed from the Command Prompt, not typed as normal code in the Python Console.
• The source verifies installation using help("modules").
• If cx_Oracle appears in the module list, the source considers installation successful.

The chapter uses cx_Oracle because that was the Oracle Python driver used when the original material was prepared.

For modern Python projects, Oracle's current Python driver is generally known as python-oracledb, imported as:

import oracledb

Therefore, keep this distinction in mind:

Tutorial / Legacy Examples	Modern Oracle Python Development
cx_Oracle	python-oracledb
import cx_Oracle	import oracledb

We will continue using cx_Oracle in this tutorial wherever required to preserve the programs and examples from the source document.

               Python Program
                     │
                     ▼
          Needs Oracle Database
               Communication
                     │
                     ▼
             Driver Required
                     │
                     ▼
                cx_Oracle
                     │
                     ▼
             Install Driver
                     │
                     ▼
          pip install cx_Oracle
                     │
                     ▼
             Test Installation
                     │
                     ▼
              help("modules")
                     │
                     ▼
          cx_Oracle Available
                     │
                     ▼
             Ready to Connect
                     │
                     ▼
              Oracle Database

Part 3 Completed

Next: Part 4 — App1: Program to Connect with Oracle Database and Print Its Version

After installing the cx_Oracle driver successfully, the first Oracle Database application is to connect a Python program with Oracle Database and print the Oracle Database version.

This is a simple program, but it demonstrates the most basic database operation: establishing a connection.

This application demonstrates how to:

• Import the Oracle Database module
• Establish a connection with Oracle Database
• Store the Connection object
• Access the Oracle Database version
• Print the Database version
• Close the Database connection

Python Program
      │
      ▼
Import cx_Oracle
      │
      ▼
Connect to Oracle
      │
      ▼
Get Database Version
      │
      ▼
Print Version
      │
      ▼
Close Connection

The complete program given in the document is:

The document shows the program being executed as:

D:\python_classes>py db1.py
11.2.0.2.0

Therefore, the Oracle Database version in the source example is:

11.2.0.2.0

This value depends on the Oracle Database server to which the Python program is connected.

The program contains only four main statements:

import cx_Oracle

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

print(con.version)

con.close()

Let us understand every statement step by step.

The first statement is:

import cx_Oracle

This statement imports the cx_Oracle module into the Python program.

The cx_Oracle module enables communication between:

Python Program
      │
      ▼
cx_Oracle
      │
      ▼
Oracle Database

Without importing the required database module, the program cannot use functions such as:

cx_Oracle.connect()

Part	Meaning
import	Python keyword used to import a module
cx_Oracle	Oracle Database driver/module used in the source tutorial

import cx_Oracle
       │
       ▼
Oracle Database functionality
becomes available to Python

The second statement is:

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

This statement establishes a connection between the Python program and the Oracle Database.

The connect() function is provided by the cx_Oracle module.

cx_Oracle.connect(...)
        │
        ▼
Connect to Oracle
        │
        ▼
Return Connection Object
        │
        ▼
Store in con

The connect() function is used to create a connection with Oracle Database.

General Syntax

cx_Oracle.connect(database_information)

Program

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

The returned Connection object is assigned to:

con

The source program uses the following connection information:

scott/tiger@localhost

It represents:

Connection Detail	Value
Username	scott
Password	tiger
Database / Connection Location	localhost

scott / tiger @ localhost
  │      │         │
  │      │         └── Database connection location
  │      │
  │      └── Password
  │
  └── Username

The program uses:

localhost

localhost refers to the local computer in this tutorial setup.

Therefore, the example assumes that the required Oracle Database service can be reached through the local machine configuration.

Python Program
      │
      ▼
localhost
      │
      ▼
Oracle Database

When the following statement executes:

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

connect() returns a Connection object.

This object is stored in:

con

The Connection object represents the connection between Python and Oracle Database.

                con
                 │
                 ▼
         Connection Object
                 │
                 ▼
          Oracle Database

The variable:

con

contains a reference to the Oracle Connection object.

Through this object, we can perform operations such as:

• Access connection information
• Create a Cursor object
• Commit transactions
• Rollback transactions
• Close the connection

In this particular program, we use it for:

con.version
con.close()

The third statement is:

print(con.version)

The version attribute of the Connection object returns the version of the connected Oracle Database.

The value is then passed to:

print()

so that it is displayed on the screen.

Consider:

con.version

Here:

Part	Meaning
con	Oracle Connection object
.	Member-access operator
version	Attribute containing the connected Oracle Database version

con.version
 │     │
 │     └── Database version
 │
 └── Connection object

con
 │
 ▼
Oracle Connection Object
 │
 ▼
Access version Attribute
 │
 ▼
11.2.0.2.0
 │
 ▼
print()
 │
 ▼
11.2.0.2.0

According to the document, the output is:

11.2.0.2.0

The source program displays:

11.2.0.2.0

This is the Oracle Database version reported by the connection used in the document.

The important concept is not to memorize this particular version number.

The important concept is:

con.version

returns the version information for the Oracle Database to which the program is connected.

If another Oracle Database version is used, the output can be different.

The final statement is:

con.close()

After completing the Database operation, the connection is closed.

Closing the connection releases the Database resource associated with that connection.

Oracle Connection
      │
      ▼
Operation Completed
      │
      ▼
con.close()
      │
      ▼
Connection Closed

A Database connection is a resource.

After the application completes its required Database operations, the connection should be released.

Therefore, we use:

con.close()

This follows the resource-cleanup principle introduced in the standard Python Database Programming steps.

Open Connection
      │
      ▼
Use Database
      │
      ▼
Complete Operation
      │
      ▼
Close Connection

Line	Statement	Purpose
1	import cx_Oracle	Imports the Oracle Database module
2	con = cx_Oracle.connect('scott/tiger@localhost')	Establishes the Oracle Database connection
3	print(con.version)	Displays the connected Oracle Database version
4	con.close()	Closes the Database connection

START
  │
  ▼
Import cx_Oracle
  │
  ▼
cx_Oracle Module Available
  │
  ▼
Call connect()
  │
  ▼
Use
scott/tiger@localhost
  │
  ▼
Connect to Oracle Database
  │
  ▼
Connection Successful
  │
  ▼
Connection Object Stored in con
  │
  ▼
Access con.version
  │
  ▼
Print Oracle Database Version
  │
  ▼
11.2.0.2.0
  │
  ▼
Call con.close()
  │
  ▼
Close Database Connection
  │
  ▼
END

The simplified program flow given by the document is:

Import cx_Oracle
       │
       ▼
Create Connection
       │
       ▼
Print Database Version
       │
       ▼
Close Connection

Method / Attribute	Purpose
cx_Oracle.connect()	Establishes a connection with Oracle Database
con.version	Returns the Oracle Database version
con.close()	Closes the Database connection

Operation	Code	Purpose
Connect	cx_Oracle.connect(...)	Creates a Database connection
Get Version	con.version	Gets the connected Oracle Database version
Close	con.close()	Releases the Database connection

connect()
    │
    ▼
Connection
    │
    ▼
version
    │
    ▼
Database Version
    │
    ▼
close()

In the standard Database Programming steps, we learned about creating a Cursor object:

cursor = con.cursor()

But this program does not execute any SQL statement.

It only accesses an attribute of the Connection object:

con.version

Therefore, no Cursor object is required for this particular program.

Need to Execute SQL?
       │
       ├── Yes → Create Cursor
       │
       └── No
            │
            ▼
      Cursor may not
      be required

This program does not perform any DML operation such as:

• INSERT
• UPDATE
• DELETE

It only connects to Oracle and reads the Database version.

Therefore:

con.commit()

is not required.

Program
  │
  ▼
Only Reads Version
  │
  ▼
No Data Modification
  │
  ▼
No commit() Required

If the connection information is correct and Oracle Database is available, this statement succeeds:

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

The program can then execute:

print(con.version)

and display the Database version.

Valid Connection Details
        +
Oracle Database Available
        │
        ▼
Connection Established
        │
        ▼
Version Displayed

If the connection cannot be established, the program will not reach the version-printing step normally.

Possible causes in a real environment can include:

• Incorrect username
• Incorrect password
• Incorrect connection information
• Oracle Database service not available
• Required Oracle client/driver configuration missing

connect()
   │
   ▼
Connection Successful?
   │
 ┌─┴─┐
 │   │
Yes  No
 │   │
 ▼   ▼
Print Error /
Version Exception

Later examples in the document introduce try, except, and finally for safer Database programming.

cx_Oracle.connect(...)
        │
        ▼
Connection Object Created
        │
        ▼
Stored in con
        │
        ▼
Use Connection
        │
        ▼
con.version
        │
        ▼
Operation Completed
        │
        ▼
con.close()
        │
        ▼
Connection Closed

D:\python_classes>py db1.py
11.2.0.2.0

Here:

Output	Meaning
D:\python_classes>	Command Prompt location shown in the source example
py db1.py	Runs the Python program
11.2.0.2.0	Oracle Database version returned by con.version

• Import the cx_Oracle module before connecting to Oracle.
• Use cx_Oracle.connect() to establish the Database connection.
• The source program uses scott as the username.
• The source program uses tiger as the password.
• The source program uses localhost as the Database connection location.
• The Connection object is stored in the variable con.
• The version attribute displays the Oracle Database version.
• The document's example output is 11.2.0.2.0.
• The actual version output depends on the Oracle Database being used.
• A Cursor is not required here because no SQL query is executed.
• commit() is not required because the program does not modify Database data.
• Always close the connection using close() after completing Database operations.

Step	Description
Import Module	import cx_Oracle
Connect Database	cx_Oracle.connect('scott/tiger@localhost')
Store Connection	con = ...
Print Version	print(con.version)
Close Connection	con.close()

The complete program is:

import cx_Oracle
con = cx_Oracle.connect('scott/tiger@localhost')
print(con.version)
con.close()

Output shown in the document:

11.2.0.2.0

App1
 │
 ▼
import cx_Oracle
 │
 ▼
cx_Oracle.connect()
 │
 ▼
'scott/tiger@localhost'
 │
 ▼
Connection Object
 │
 ▼
con
 │
 ├───────────────┐
 │               │
 ▼               ▼
con.version   con.close()
 │               │
 ▼               ▼
11.2.0.2.0   Connection
              Closed

                     START
                       │
                       ▼
              import cx_Oracle
                       │
                       ▼
        cx_Oracle.connect(
        'scott/tiger@localhost'
        )
                       │
                       ▼
             Connection Created
                       │
                       ▼
              Stored in "con"
                       │
                       ▼
               con.version
                       │
                       ▼
             print(con.version)
                       │
                       ▼
                 11.2.0.2.0
                       │
                       ▼
                 con.close()
                       │
                       ▼
              Connection Closed
                       │
                       ▼
                      END

Part 4 Completed

Next: Part 5 — App2: Program to Create Employees Table in Oracle Database

In this application, we will create a table named employees in the Oracle Database.

The employees table contains the following columns:

• eno → Employee Number
• ename → Employee Name
• esal → Employee Salary
• eaddr → Employee Address

This application also demonstrates how to use:

• try
• except
• finally
• cursor()
• execute()
• rollback()
• close()

These concepts help us perform Database operations safely.

Python Program
      │
      ▼
Connect to Oracle
      │
      ▼
Create Cursor
      │
      ▼
Execute CREATE TABLE
      │
      ▼
Create employees Table

The program creates the following employees table:

Column	Data Type
eno	number
ename	varchar2(10)
esal	number(10,2)
eaddr	varchar2(10)

employees
+---------------------------+
| eno    : number           |
| ename  : varchar2(10)     |
| esal   : number(10,2)     |
| eaddr  : varchar2(10)     |
+---------------------------+

Column	Purpose	Data Type
eno	Stores employee number	number
ename	Stores employee name	varchar2(10)
esal	Stores employee salary	number(10,2)
eaddr	Stores employee address	varchar2(10)

The complete program given in the document is:

The program performs the following operations:

Import cx_Oracle
      │
      ▼
Connect to Oracle
      │
      ▼
Create Cursor
      │
      ▼
Execute CREATE TABLE
      │
      ▼
Create employees Table
      │
      ▼
Display Success Message
      │
      ▼
Close Resources

If an Oracle Database error occurs, the program handles it using the except block.

The first statement is:

import cx_Oracle

The cx_Oracle module is imported to communicate with the Oracle Database.

Python
  │
  ▼
cx_Oracle
  │
  ▼
Oracle Database

The Database operations are written inside a try block:

try:
    ...

The try block contains the code that may generate an Oracle Database error.

In this application, the following operations are performed inside try:

• Connect to Oracle Database
• Create the Cursor object
• Execute the CREATE TABLE statement
• Display the success message

try
 │
 ├── Connect
 ├── Create Cursor
 ├── Execute SQL
 └── Print Success Message

The connection is created using:

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

A Connection object is created using the following details:

Detail	Value
Username	scott
Password	tiger
Database	localhost

The returned Connection object is stored in:

con

cx_Oracle.connect(
'scott/tiger@localhost'
)
        │
        ▼
Connect to Oracle
        │
        ▼
Connection Object
        │
        ▼
       con

After establishing the Database connection, the program creates a Cursor object:

cursor = con.cursor()

The Cursor object is required to execute SQL statements.

The returned Cursor object is stored in:

cursor

Connection Object
      │
      ▼
con.cursor()
      │
      ▼
Cursor Object
      │
      ▼
cursor

In Part 4, we only printed the Database version, so a Cursor was not required.

In this application, however, we need to execute an SQL statement:

CREATE TABLE employees

Therefore, a Cursor object is required.

Need to Execute SQL?
        │
        ▼
       Yes
        │
        ▼
Create Cursor Object
        │
        ▼
cursor = con.cursor()

The program executes the following SQL statement:

cursor.execute(
    "create table employees("
    "eno number,"
    "ename varchar2(10),"
    "esal number(10,2),"
    "eaddr varchar2(10))"
)

The execute() method executes the SQL statement that creates the employees table.

If we write the SQL statement separately, it looks like this:

create table employees(
    eno number,
    ename varchar2(10),
    esal number(10,2),
    eaddr varchar2(10)
)

This SQL command creates a table named:

employees

with four columns.

CREATE TABLE is an SQL statement used to create a new table in a Database.

General Syntax

CREATE TABLE table_name(
    column1 datatype,
    column2 datatype,
    ...
)

In this Program

CREATE TABLE employees(
    eno number,
    ename varchar2(10),
    esal number(10,2),
    eaddr varchar2(10)
)

eno number

eno represents the Employee Number.

Its data type is:

number

Therefore, it is used to store numeric employee numbers.

ename varchar2(10)

ename represents the Employee Name.

Its data type is:

varchar2(10)

It stores character data with a maximum size of 10 characters in the table definition used by the tutorial.

esal number(10,2)

esal represents Employee Salary.

The source program uses:

number(10,2)

This allows numeric salary values with decimal precision according to the Oracle numeric definition.

eaddr varchar2(10)

eaddr represents Employee Address.

Its data type is:

varchar2(10)

It stores character data with the size specified in the source table definition.

cursor
   │
   ▼
execute(SQL)
   │
   ▼
CREATE TABLE employees
   │
   ▼
Oracle Database
   │
   ▼
employees Table Created

The Cursor object sends the SQL statement to Oracle Database for execution.

After executing the CREATE TABLE statement, the program executes:

print("Table created successfully")

If the SQL statement executes successfully, the following message is displayed:

Table created successfully

This confirms that the program reached the success statement after executing the table-creation query.

The program uses:

try
except
finally

for safe Database programming.

             try
              │
              ▼
       Database Operation
              │
              ▼
          Error?
          /    \
        No      Yes
        │        │
        ▼        ▼
     Success   except
        │        │
        └───┬────┘
            ▼
          finally
            │
            ▼
      Close Resources

The exception block is:

except cx_Oracle.DatabaseError as e:

If an Oracle Database error occurs inside the try block, execution moves to this except block.

The error object is stored in:

e

Part	Meaning
cx_Oracle.DatabaseError	Oracle Database-related exception class used in the program
as e	Stores the exception object in variable e

Inside the except block, the program checks:

if con:
    con.rollback()

If the Connection object exists, the program calls:

con.rollback()

rollback() is used to roll back temporary Database changes when an error occurs.

Database Error
      │
      ▼
Is con Available?
      │
      ▼
     Yes
      │
      ▼
con.rollback()
      │
      ▼
Rollback Changes

The program uses:

if con:
    con.rollback()

The condition checks whether a usable Connection object is available before attempting to call rollback().

if con
  │
  ├── True  → con.rollback()
  │
  └── False → Skip rollback()

After rollback, the program executes:

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

This displays the SQL-related error information stored in:

e

The general output structure is:

There is a problem with sql <Oracle Database error>

If the employees table already exists, executing the CREATE TABLE statement again can result in an Oracle Database error.

CREATE TABLE employees
        │
        ▼
Does employees already exist?
       / \
     No   Yes
     │     │
     ▼     ▼
 Create  DatabaseError
 Table      │
            ▼
          except

The exact Oracle error depends on the Database environment.

The program contains:

finally:

The finally block executes whether an exception occurs or not.

It is used here to close Database resources.

try
 │
 ├── Success ───┐
 │              │
 └── Error ─────┤
                ▼
             finally
                │
                ▼
        Close Resources

Inside the finally block:

if cursor:
    cursor.close()

If the Cursor object exists, it is closed using:

cursor.close()

This releases the Cursor resource.

After closing the Cursor, the program closes the Connection:

if con:
    con.close()

The Database connection is closed using:

con.close()

This releases the Database connection resource.

The Cursor was created after the Connection:

Connection
   │
   ▼
Cursor

Resources are normally closed in the reverse order:

Cursor
   │
   ▼
Connection

Therefore, the program performs:

cursor.close()
con.close()

START
  │
  ▼
Import cx_Oracle
  │
  ▼
try
  │
  ▼
Create Connection
  │
  ▼
Create Cursor
  │
  ▼
Execute CREATE TABLE
  │
  ▼
SQL Successful?
  │
 ┌┴──────────────┐
 │               │
Yes              No
 │               │
 ▼               ▼
Print          DatabaseError
"Table created    │
successfully"     ▼
 │             except
 │               │
 │          if con:
 │          rollback()
 │               │
 │          Print Error
 │               │
 └───────┬───────┘
         ▼
       finally
         │
         ▼
    Close Cursor
         │
         ▼
  Close Connection
         │
         ▼
        END

The program flow can be represented as:

Import cx_Oracle
      │
      ▼
Create Connection
      │
      ▼
Create Cursor
      │
      ▼
Execute CREATE TABLE SQL
      │
      ▼
Table Created Successfully
      │
      ▼
   Exception?
    /      \
  No        Yes
  │          │
  ▼          ▼
Close     Rollback
Resources Print Error
  │          │
  └────┬─────┘
       ▼
 Program Ends

START
  │
  ▼
Connect to Oracle
  │
  ▼
Create Cursor
  │
  ▼
Execute CREATE TABLE
  │
  ▼
Table Created
  │
  ▼
Print Success Message
  │
  ▼
finally
  │
  ▼
Close Cursor
  │
  ▼
Close Connection
  │
  ▼
END

START
  │
  ▼
Database Operation
  │
  ▼
DatabaseError
  │
  ▼
except
  │
  ▼
if con
  │
  ▼
rollback()
  │
  ▼
Print Error
  │
  ▼
finally
  │
  ▼
Close Cursor
  │
  ▼
Close Connection
  │
  ▼
END

Method	Purpose
connect()	Creates a Database connection
cursor()	Creates a Cursor object
execute()	Executes the SQL query
rollback()	Cancels temporary changes if an error occurs
cursor.close()	Closes the Cursor
con.close()	Closes the Database connection

Object / Variable	Purpose
con	Stores the Oracle Connection object
cursor	Stores the Cursor object used to execute SQL
e	Stores the Oracle Database exception object

Concept	Purpose
CREATE TABLE	SQL statement that defines and creates the table
cursor.execute()	Python Database API operation used to send the SQL statement for execution

Python
  │
  ▼
cursor.execute()
  │
  ▼
CREATE TABLE employees(...)
  │
  ▼
Oracle Database
  │
  ▼
employees Table

• The program creates a table named employees.
• The table contains four columns: eno, ename, esal, and eaddr.
• The cx_Oracle module is used to communicate with Oracle Database.
• Use connect() to establish the Database connection.
• Use cursor() before executing SQL statements.
• The execute() method executes the CREATE TABLE query.
• The program uses try-except-finally for safe execution.
• cx_Oracle.DatabaseError handles Oracle Database-related errors.
• If an SQL error occurs, rollback() is executed when a connection is available.
• The error object is stored in e.
• The finally block executes whether an exception occurs or not.
• The Cursor and Connection are closed inside the finally block.
• The Cursor is closed before the Connection.

Topic	Description
Table Name	employees
Columns	eno, ename, esal, eaddr
SQL Statement	CREATE TABLE
Connection	cx_Oracle.connect()
Cursor Method	cursor()
Execution Method	execute()
Exception Class	cx_Oracle.DatabaseError
Rollback Method	rollback()
Resource Cleanup	cursor.close() and con.close()

App2
 │
 ▼
import cx_Oracle
 │
 ▼
try
 │
 ▼
connect()
 │
 ▼
con
 │
 ▼
cursor()
 │
 ▼
cursor
 │
 ▼
execute()
 │
 ▼
CREATE TABLE employees
 │
 ▼
Success?
 │
 ├── Yes → "Table created successfully"
 │
 └── No  → DatabaseError
              │
              ▼
           rollback()
              │
              ▼
          Print Error
              │
              ▼
           finally
              │
              ▼
       cursor.close()
              │
              ▼
          con.close()

                  START
                    │
                    ▼
            import cx_Oracle
                    │
                    ▼
                   try
                    │
                    ▼
         Connect to Oracle Database
                    │
                    ▼
             Create Cursor
                    │
                    ▼
        Execute CREATE TABLE Query
                    │
                    ▼
           employees Table
                    │
             ┌──────┴──────┐
             │             │
          Success         Error
             │             │
             ▼             ▼
      Print Success    DatabaseError
                            │
                            ▼
                       rollback()
                            │
                            ▼
                       Print Error
             │             │
             └──────┬──────┘
                    ▼
                  finally
                    │
                    ▼
             Close Cursor
                    │
                    ▼
           Close Connection
                    │
                    ▼
                   END

Part 5 Completed

Next: Part 6 — App3: Program to Drop Employees Table from Oracle Database

In the previous application, we created an employees table in Oracle Database.

Now we will learn how to drop the employees table from Oracle Database using a Python program.

To remove an existing table, SQL provides the:

DROP TABLE

command.

This application demonstrates:

• Connecting Python with Oracle Database
• Creating a Cursor object
• Executing a DROP TABLE SQL statement
• Handling Database errors
• Using try-except-finally
• Using rollback()
• Closing Cursor and Connection resources

Python Program
      │
      ▼
Connect to Oracle
      │
      ▼
Create Cursor
      │
      ▼
Execute DROP TABLE
      │
      ▼
employees Table Removed

DROP TABLE is an SQL command used to remove an existing table from a Database.

General Syntax

DROP TABLE table_name

For the employees table:

DROP TABLE employees

After this statement is executed successfully, the employees table is removed from the Database.

SQL Command	Purpose
CREATE TABLE	Creates a new table
DROP TABLE	Removes an existing table

CREATE TABLE employees
         │
         ▼
 employees Created


DROP TABLE employees
         │
         ▼
 employees Removed

The program follows the same Database-programming structure used in the previous application.

The important SQL statement in this application is:

drop table employees

The program contains four major sections:

1. Import the Oracle module
2. Perform Database operations inside try
3. Handle Database errors inside except
4. Close resources inside finally

import cx_Oracle
       │
       ▼
      try
       │
       ├── Connect
       ├── Create Cursor
       ├── DROP TABLE
       └── Print Success
       │
       ▼
     except
       │
       ├── rollback()
       └── Print Error
       │
       ▼
     finally
       │
       ├── Close Cursor
       └── Close Connection

The program starts with:

import cx_Oracle

This imports the Oracle Database module used throughout the source tutorial.

It provides the functionality required to:

• Connect to Oracle Database
• Create Cursor objects
• Execute SQL statements
• Handle Oracle Database errors
• Manage transactions

Python Program
      │
      ▼
cx_Oracle
      │
      ▼
Oracle Database

The Database operations are placed inside:

try:

The try block contains code that may generate a Database-related exception.

In this program, it contains:

con = cx_Oracle.connect(...)

cursor = con.cursor()

cursor.execute("drop table employees")

print("Table dropped successfully")

The program connects to Oracle Database using:

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

The connection information is:

Part	Value
Username	scott
Password	tiger
Database / Location	localhost

The returned Connection object is stored in:

con

cx_Oracle.connect(
'scott/tiger@localhost'
)
        │
        ▼
Oracle Database
        │
        ▼
Connection Established
        │
        ▼
Connection Object
        │
        ▼
       con

After creating the Connection object, the program creates a Cursor object:

cursor = con.cursor()

The Cursor is required because we need to execute an SQL statement.

Connection
    │
    ▼
con.cursor()
    │
    ▼
Cursor Object
    │
    ▼
cursor

The SQL command:

DROP TABLE employees

must be sent to Oracle Database for execution.

The Cursor object performs this operation.

Python
  │
  ▼
Cursor
  │
  ▼
SQL Statement
  │
  ▼
Oracle Database

Therefore:

cursor = con.cursor()

is required before calling execute().

The most important statement in this application is:

cursor.execute("drop table employees")

The execute() method sends the SQL statement to Oracle Database.

The SQL statement is:

drop table employees

Its purpose is to remove the employees table.

Consider:

cursor.execute("drop table employees")

Part	Meaning
cursor	Cursor object
execute()	Method used to execute an SQL statement
drop table employees	SQL statement being executed

cursor
   │
   ▼
execute()
   │
   ▼
"drop table employees"
   │
   ▼
Oracle Database
   │
   ▼
Locate employees Table
   │
   ▼
Drop Table
   │
   ▼
employees Removed

Before

Oracle Database
      │
      ▼
+----------------------+
| employees            |
+----------------------+
| eno                  |
| ename                |
| esal                 |
| eaddr                |
+----------------------+

After

Oracle Database
      │
      ▼
employees table
no longer exists

DROP TABLE removes the table definition and its associated table data according to Oracle's DROP TABLE behavior.

If the SQL statement executes successfully, the next statement is:

print("Table dropped successfully")

Therefore, the success message is:

Table dropped successfully

This indicates that execution reached the success statement after the DROP operation.

Connect to Oracle
       │
       ▼
Create Cursor
       │
       ▼
DROP TABLE employees
       │
       ▼
Table Dropped
       │
       ▼
Print
"Table dropped successfully"
       │
       ▼
finally
       │
       ▼
Close Resources

If an Oracle Database-related error occurs, the program uses:

except cx_Oracle.DatabaseError as e:

The exception object is stored in:

e

This allows the program to display information about the Database error.

cx_Oracle.DatabaseError is the exception class used by the source program for Database-related problems.

try
 │
 ▼
Database Operation
 │
 ▼
Database Error?
 │
 ├── No  → Continue
 │
 └── Yes
        │
        ▼
cx_Oracle.DatabaseError
        │
        ▼
        e

One possible problem is attempting to drop the employees table when it does not exist.

DROP TABLE employees
        │
        ▼
Does employees exist?
      /     \
    Yes      No
     │        │
     ▼        ▼
   Drop    Database
   Table    Error

When such a Database error occurs, control moves to the except block.

The except block contains:

if con:
    con.rollback()

If a Connection object is available, the program calls:

con.rollback()

The source program follows the same exception-handling pattern used in the previous application.

DatabaseError
      │
      ▼
if con
      │
      ▼
con.rollback()

DROP TABLE is a DDL (Data Definition Language) operation.

Oracle performs implicit transaction handling around many DDL statements.

Therefore, although the source program calls:

con.rollback()

inside its general Database-error handler, you should not interpret this as meaning that a successfully executed Oracle DROP TABLE can normally be undone using rollback().

This is an important distinction:

Operation Type	Examples	Typical Transaction Handling
DML	INSERT, UPDATE, DELETE	commit() / rollback()
DDL	CREATE TABLE, DROP TABLE	Oracle normally performs implicit commits around successful DDL

After the rollback check, the program executes:

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

This prints the message:

There is a problem with sql

along with the actual Oracle Database exception information stored in e.

cursor.execute(
"drop table employees"
)
       │
       ▼
Database Error
       │
       ▼
except cx_Oracle.DatabaseError as e
       │
       ▼
if con
       │
       ▼
con.rollback()
       │
       ▼
Print
"There is a problem with sql"
       +
Error Information
       │
       ▼
finally

The program uses:

finally:

The finally block is used for resource cleanup.

It executes whether:

• The table is dropped successfully
• A Database error occurs

             try
              │
       ┌──────┴──────┐
       │             │
    Success        Error
       │             │
       │           except
       │             │
       └──────┬──────┘
              ▼
           finally
              │
              ▼
      Close Resources

The program checks whether the Cursor exists:

if cursor:
    cursor.close()

If it exists, the Cursor is closed.

cursor
   │
   ▼
cursor.close()
   │
   ▼
Cursor Resource Released

The Connection is then closed:

if con:
    con.close()

If a Connection object exists:

con.close()

releases the Database connection.

The resources were created in this order:

1. Connection
2. Cursor

They are closed in reverse order:

1. Cursor
2. Connection

Opening	Closing
Connection	Cursor
Cursor	Connection

                     START
                       │
                       ▼
               import cx_Oracle
                       │
                       ▼
                      try
                       │
                       ▼
            Connect to Oracle
                       │
                       ▼
              Create Cursor
                       │
                       ▼
       execute("drop table employees")
                       │
                 ┌─────┴─────┐
                 │           │
              Success       Error
                 │           │
                 ▼           ▼
          employees      DatabaseError
          Dropped            │
                 │           ▼
                 │       if con
                 │           │
                 │           ▼
                 │      rollback()
                 │           │
                 ▼           ▼
          Print Success   Print Error
                 │           │
                 └─────┬─────┘
                       ▼
                    finally
                       │
                       ▼
                Close Cursor
                       │
                       ▼
              Close Connection
                       │
                       ▼
                      END

Statement	Purpose
import cx_Oracle	Imports Oracle Database support
try:	Starts protected Database operations
con = cx_Oracle.connect(...)	Establishes the Database connection
cursor = con.cursor()	Creates a Cursor object
cursor.execute("drop table employees")	Drops the employees table
print("Table dropped successfully")	Displays the success message
except cx_Oracle.DatabaseError as e:	Handles Database errors
con.rollback()	Performs rollback as part of the source's general error-handling pattern
print(..., e)	Displays error information
finally:	Runs resource-cleanup code
cursor.close()	Closes the Cursor
con.close()	Closes the Connection

Feature	App2	App3
Purpose	Create employees table	Drop employees table
SQL	CREATE TABLE	DROP TABLE
Connection	connect()	connect()
Cursor	cursor()	cursor()
Execute SQL	execute()	execute()
Error Handling	DatabaseError	DatabaseError
Cleanup	finally	finally

Method / Statement	Purpose
cx_Oracle.connect()	Connects Python to Oracle Database
con.cursor()	Creates the Cursor
cursor.execute()	Executes the DROP TABLE statement
con.rollback()	Used by the source's Database-error handling pattern
cursor.close()	Closes the Cursor
con.close()	Closes the Database connection

1. DROP TABLE is used to remove an existing table.
2. The source application drops the employees table.
3. A Database connection is established before executing the SQL statement.
4. A Cursor object is required to execute the DROP TABLE statement.
5. The SQL statement is executed using cursor.execute().
6. If the operation succeeds, the program prints Table dropped successfully.
7. The program handles Oracle Database errors using cx_Oracle.DatabaseError.
8. The exception object is stored in variable e.
9. The source uses con.rollback() in its common Database-error handling pattern.
10. DROP TABLE is DDL. In Oracle, successful DDL normally involves implicit commit behavior, so a successful DROP should not be treated like ordinary DML that can simply be undone with rollback().
11. The finally block is used to release Database resources.
12. The Cursor is closed before the Connection.
13. Dropping a table removes the table itself, so this operation should be performed carefully.

Topic	Description
Application	App3
Purpose	Drop employees table
SQL Command	DROP TABLE employees
Connection	cx_Oracle.connect()
Cursor	con.cursor()
Execution	cursor.execute()
Exception	cx_Oracle.DatabaseError
Error Handling	rollback() pattern used in source
Cleanup	finally
Close Cursor	cursor.close()
Close Connection	con.close()

App3
 │
 ▼
import cx_Oracle
 │
 ▼
try
 │
 ▼
connect()
 │
 ▼
cursor()
 │
 ▼
execute()
 │
 ▼
DROP TABLE employees
 │
 ▼
Successful?
 │
 ├── Yes
 │     │
 │     ▼
 │   Table dropped successfully
 │
 └── No
       │
       ▼
   DatabaseError
       │
       ▼
    rollback()
       │
       ▼
   Print Error
       │
       ▼
     finally
       │
       ▼
 cursor.close()
       │
       ▼
   con.close()

                    START
                      │
                      ▼
              import cx_Oracle
                      │
                      ▼
                     try
                      │
                      ▼
              Create Connection
                      │
                      ▼
                Create Cursor
                      │
                      ▼
       cursor.execute(
       "drop table employees"
       )
                      │
             ┌────────┴────────┐
             │                 │
          Success             Error
             │                 │
             ▼                 ▼
       employees Table     DatabaseError
          Dropped              │
             │                 ▼
             │             rollback()
             ▼                 │
       Print Success           ▼
                         Print Error
             │                 │
             └────────┬────────┘
                      ▼
                   finally
                      │
                      ▼
             cursor.close()
                      │
                      ▼
                con.close()
                      │
                      ▼
                     END

Part 6 Completed

Next: Part 7 — App4: Insert a Single Row into Employees Table

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

According to the document, 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.

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

DML stands for:

Data Manipulation Language

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

The document gives the following examples:

• INSERT
• UPDATE
• DELETE

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, two important transaction methods are:

commit()
rollback()

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');

The program follows these main steps:

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

If an Oracle Database error occurs, the program uses rollback() and displays the error.

The program starts with:

import cx_Oracle

The cx_Oracle module is imported to communicate with Oracle Database.

Python Program
      │
      ▼
cx_Oracle
      │
      ▼
Oracle Database

The connection is established using:

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

The connection details used in the document are:

Detail	Value
Username	scott
Password	tiger
Database	localhost

The Connection object is stored in:

con

cx_Oracle.connect(
'scott/tiger@localhost'
)
        │
        ▼
Connect to Oracle Database
        │
        ▼
Connection Object
        │
        ▼
       con

The Cursor object is created using:

cursor = con.cursor()

The Cursor object is required to execute SQL statements.

Connection Object
      │
      ▼
con.cursor()
      │
      ▼
Cursor Object
      │
      ▼
cursor

In this program, the Cursor executes the INSERT statement.

The program executes:

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

The SQL statement is:

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

This statement inserts one row into the employees table.

The general SQL syntax is:

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

In this application:

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

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   |
+-----+-------+------+-------+

cursor
   │
   ▼
execute()
   │
   ▼
INSERT INTO employees
VALUES(100,'Durga',1000,'Hyd')
   │
   ▼
Oracle Database
   │
   ▼
employees Table
   │
   ▼
New Row Added to Transaction

At this stage, the transaction must be committed to save the change permanently.

After executing the INSERT statement, the program calls:

con.commit()

The commit() method permanently saves the inserted record in the Database.

INSERT Record
      │
      ▼
Transaction Change
      │
      ▼
con.commit()
      │
      ▼
Change Saved

commit() is a transaction-management method.

It is called using the Connection object:

con.commit()

Its purpose is to make the current transaction changes permanent.

Method	Purpose
commit()	Permanently saves transaction changes

The source document gives an important rule:

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

In this program:

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

Therefore, commit() is an important part of this application.

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

That is why the source emphasizes:

con.commit()

After committing the transaction, the program executes:

print("Record Inserted Successfully")

The output is:

Record Inserted Successfully

This indicates 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 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.

The exception object is stored in:

e

try
 │
 ▼
Database Operation
 │
 ▼
Error?
 ├── No  → Continue
 │
 └── Yes
       │
       ▼
DatabaseError
       │
       ▼
       e

Inside the exception handler:

if con:
    con.rollback()

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.

SQL Error
    │
    ▼
if con
    │
    ▼
rollback()
    │
    ▼
Pending Changes Cancelled

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()

            DML Operation
                 │
                 ▼
           Transaction
             /       \
            /         \
           ▼           ▼
      Successful      Error
           │           │
           ▼           ▼
       commit()    rollback()
           │           │
           ▼           ▼
         Save         Cancel

After rollback, the program executes:

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

This displays:

There is a problem with sql

along with the Oracle Database error information stored in e.

The program uses:

finally:

The finally block executes whether an exception occurs or not.

Its purpose is to release Database resources.

           try
            │
      ┌─────┴─────┐
      │           │
   Success       Error
      │           │
      │         except
      │           │
      └─────┬─────┘
            ▼
         finally
            │
            ▼
     Close Resources

The program checks:

if cursor:
    cursor.close()

If the Cursor object exists, it is closed using:

cursor.close()

This releases the Cursor resource.

The program then checks:

if con:
    con.close()

If the Connection object exists, it is closed using:

con.close()

This releases the Database connection.

The resources are opened in this order:

Connection
    │
    ▼
Cursor

They are closed in reverse order:

Cursor
    │
    ▼
Connection

Therefore:

cursor.close()
con.close()

                     START
                       │
                       ▼
               import cx_Oracle
                       │
                       ▼
                      try
                       │
                       ▼
             Create Connection
                       │
                       ▼
               Create Cursor
                       │
                       ▼
            Execute INSERT Query
                       │
                       ▼
 INSERT INTO employees
 VALUES(100,'Durga',1000,'Hyd')
                       │
                       ▼
                Record Added
                       │
                       ▼
                  con.commit()
                       │
                       ▼
             Record Permanently
                   Saved
                       │
                       ▼
       "Record Inserted Successfully"
                       │
                       ▼
                    finally
                       │
                       ▼
               Close Cursor
                       │
                       ▼
             Close Connection
                       │
                       ▼
                      END

                  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

Import cx_Oracle
      │
      ▼
Create Connection
      │
      ▼
Create Cursor
      │
      ▼
Execute INSERT Query
      │
      ▼
Commit Changes
      │
      ▼
Record Inserted Successfully
      │
      ▼
Exception?
   ┌──┴──┐
   │     │
  No    Yes
   │     │
   ▼     ▼
Close  Rollback
Resources Print Error
   │     │
   └──┬──┘
      ▼
Program Ends

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

While performing DML Operations (insert | update | delete), compulsory we have to use commit() method, then only the results will be reflected in the database.

In simple words:

INSERT
UPDATE
DELETE
   │
   ▼
DML Operations
   │
   ▼
Perform Changes
   │
   ▼
commit()
   │
   ▼
Save Changes Permanently

The tutorial therefore explicitly calls:

con.commit()

after the successful INSERT operation.

• The application inserts one employee record into the employees table.
• The SQL operation used is INSERT.
• INSERT is a DML operation.
• The inserted employee number is 100.
• The inserted employee name is Durga.
• The inserted salary is 1000.
• The inserted address is Hyd.
• The Cursor executes the INSERT statement using execute().
• The program uses con.commit() after successful insertion.
• commit() permanently saves the transaction changes.
• If an SQL error occurs before commit, the program uses rollback().
• rollback() cancels pending transaction changes.
• The program catches Oracle Database errors using cx_Oracle.DatabaseError.
• The finally block executes whether an error occurs or not.
• The Cursor and Connection are closed in the finally block.
• The Cursor is closed before the Connection.

Topic	Description
Application	App4
SQL Operation	Insert
Table Name	employees
SQL Statement	INSERT INTO employees VALUES(100,'Durga',1000,'Hyd')
Inserted Employee	(100, 'Durga', 1000, 'Hyd')
Execution Method	execute()
Required Transaction Method	commit()
Error Handling	rollback()
Exception	cx_Oracle.DatabaseError
Resource Cleanup	cursor.close() and con.close()

App4
 │
 ▼
INSERT Single Row
 │
 ▼
(100, 'Durga', 1000, 'Hyd')
 │
 ▼
cursor.execute()
 │
 ▼
Transaction Changed
 │
 ▼
con.commit()
 │
 ▼
Record Saved
 │
 ▼
"Record Inserted Successfully"

If Error
   │
   ▼
DatabaseError
   │
   ▼
rollback()
   │
   ▼
Cancel Pending Changes

Finally
   │
   ▼
cursor.close()
   │
   ▼
con.close()

                       START
                         │
                         ▼
                 import cx_Oracle
                         │
                         ▼
                        try
                         │
                         ▼
                Create Connection
                         │
                         ▼
                  Create Cursor
                         │
                         ▼
              Execute INSERT Query
                         │
                         ▼
     INSERT INTO employees VALUES
       (100,'Durga',1000,'Hyd')
                         │
                         ▼
                Transaction Changed
                         │
                 ┌───────┴───────┐
                 │               │
              Success           Error
                 │               │
                 ▼               ▼
             commit()       DatabaseError
                 │               │
                 ▼               ▼
            Save Record       rollback()
                 │               │
                 ▼               ▼
        Print Success       Print Error
                 │               │
                 └───────┬───────┘
                         ▼
                      finally
                         │
                         ▼
                  Close Cursor
                         │
                         ▼
                Close Connection
                         │
                         ▼
                        END

Part 7 Completed

Next: Part 8 — App5: Insert Multiple Rows using executemany()

In the previous application, we inserted one record into the employees table.

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

The executemany() method is used to execute a parameterized SQL query for multiple records at once.

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()
• Using rollback() when an error occurs
• Using try-except-finally
• Closing Database resources

Python Program
      │
      ▼
Connect to Oracle
      │
      ▼
Create Cursor
      │
      ▼
Create Parameterized SQL
      │
      ▼
Create Records List
      │
      ▼
executemany()
      │
      ▼
Insert Multiple Rows
      │
      ▼
commit()

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.

General Idea

cursor.executemany(sql, records)

Here:

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

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

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 follows this sequence:

Import cx_Oracle
      │
      ▼
Connect to Oracle
      │
      ▼
Create Cursor
      │
      ▼
Create SQL Statement
      │
      ▼
Create Records List
      │
      ▼
executemany(sql, records)
      │
      ▼
Insert All Records
      │
      ▼
commit()
      │
      ▼
Print Success Message
      │
      ▼
Close Resources

The program starts with:

import cx_Oracle

The cx_Oracle module is imported to communicate with Oracle Database.

Python
  │
  ▼
cx_Oracle
  │
  ▼
Oracle Database

The program establishes a connection using:

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

The connection details used in the document are:

Detail	Value
Username	scott
Password	tiger
Database	localhost

The returned Connection object is stored in:

con

cx_Oracle.connect(
'scott/tiger@localhost'
)
        │
        ▼
Oracle Database
        │
        ▼
Connection Established
        │
        ▼
Connection Object
        │
        ▼
       con

After establishing the connection, the program creates a Cursor:

cursor = con.cursor()

The Cursor object is used to execute SQL statements.

Connection
    │
    ▼
con.cursor()
    │
    ▼
Cursor Object
    │
    ▼
cursor

In this application, the Cursor will execute the parameterized INSERT statement for multiple employee records.

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

These placeholders receive the actual values from each employee record.

The SQL statement is:

insert into employees
values(: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 program uses:

:eno
:ename
:esal
:eaddr

The actual values are supplied separately through the records collection.

Parameterized SQL
       │
       ├── :eno
       ├── :ename
       ├── :esal
       └── :eaddr
       │
       ▼
Actual Values from Records

A parameterized SQL statement allows the same SQL structure to be reused for multiple records.

For example:

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

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 it 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 variable:

records

contains all the employee records that will be inserted.

The outer structure is a Python list:

[
    ...,
    ...,
    ...
]

Each employee record is represented using a tuple:

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

Therefore, the complete structure is:

List
 │
 ├── Tuple 1
 │
 ├── Tuple 2
 │
 └── Tuple 3

or:

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

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

Column	Value
eno	200
ename	Sunny
esal	2000
eaddr	Mumbai

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

Column	Value
eno	300
ename	Chinny
esal	3000
eaddr	Hyd

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

Column	Value
eno	400
ename	Bunny
esal	4000
eaddr	Hyd

The SQL statement contains four placeholders:

:eno
:ename
:esal
:eaddr

Each tuple also contains four values.

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

The same mapping is used for every employee record.

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.

Record 1

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

Record 2

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

Record 3

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

In the actual Python program, however, 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 Part 7 and Part 8 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 in the previous application, while App5 inserts the remaining three records.

After inserting the records, the program executes:

con.commit()

The commit() method permanently saves all inserted records in the Database.

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

INSERT is a DML operation.

The document explains that for DML operations such as:

INSERT
UPDATE
DELETE

we use commit() to save the transaction changes permanently.

INSERT Multiple Rows
        │
        ▼
Temporary Transaction Changes
        │
        ▼
con.commit()
        │
        ▼
Records Permanently Saved

After committing the transaction, the program executes:

print("Records Inserted Successfully")

The output is:

Records Inserted Successfully

This message indicates that execution reached the success statement after executemany() and commit().

The program handles Oracle Database errors using:

except cx_Oracle.DatabaseError as e:

If an SQL-related Database error occurs, the exception object is stored in:

e

try
 │
 ▼
executemany()
 │
 ▼
Error?
 ├── No  → commit()
 │
 └── Yes
       │
       ▼
cx_Oracle.DatabaseError
       │
       ▼
       e

If an error occurs during insertion, the program executes:

if con:
    con.rollback()

If the Connection object exists:

con.rollback()

cancels the uncommitted transaction changes.

Insertion Error
      │
      ▼
DatabaseError
      │
      ▼
if con
      │
      ▼
rollback()
      │
      ▼
Cancel Pending Changes

Multiple employee records are being processed as part of the transaction.

If a Database error occurs before commit(), the program uses:

con.rollback()

to roll back the pending transaction changes.

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)

This displays the SQL error along with the exception information stored in e.

There is a problem with sql <Database Error>

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()

             try
              │
       ┌──────┴──────┐
       │             │
    Success         Error
       │             │
       │           except
       │             │
       └──────┬──────┘
              ▼
           finally
              │
              ▼
      Close Resources

The Cursor is closed using:

if cursor:
    cursor.close()

This releases the Cursor resource after the Database operation is complete.

The Database Connection is closed using:

if con:
    con.close()

This releases the connection to Oracle Database.

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

The document uses executemany() together with a parameterized SQL statement and a list of tuples.

For a single employee:

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

This inserts one record.

execute()
   │
   ▼
Single Record

For multiple employees:

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

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

cursor.executemany(sql, records)

executemany()
      │
      ▼
Multiple Records

execute()
   │
   ▼
SQL + One Record
   │
   ▼
One Row


executemany()
   │
   ▼
Parameterized SQL
   +
List of Records
   │
   ▼
Multiple Rows

                       START
                         │
                         ▼
                 import cx_Oracle
                         │
                         ▼
                        try
                         │
                         ▼
                Create Connection
                         │
                         ▼
                  Create Cursor
                         │
                         ▼
              Create Parameterized SQL
                         │
                         ▼
                 Create Records
                         │
                         ▼
            cursor.executemany(
                 sql, records
            )
                         │
                         ▼
               Insert 3 Records
                         │
                         ▼
                  con.commit()
                         │
                         ▼
              Save All Records
                         │
                         ▼
       "Records Inserted Successfully"
                         │
                         ▼
                      finally
                         │
                         ▼
                  Close Cursor
                         │
                         ▼
                Close Connection
                         │
                         ▼
                        END

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

Import cx_Oracle
      │
      ▼
Create Connection
      │
      ▼
Create Cursor
      │
      ▼
Create Parameterized SQL
      │
      ▼
Create Records List
      │
      ▼
executemany(sql, records)
      │
      ▼
Commit Changes
      │
      ▼
Records Inserted Successfully
      │
      ▼
Exception?
   ┌──┴──┐
   │     │
  No    Yes
   │     │
   ▼     ▼
Close  Rollback
Resources Print Error
   │     │
   └──┬──┘
      ▼
Program Ends

Statement	Purpose
import cx_Oracle	Imports Oracle Database support
cx_Oracle.connect(...)	Establishes the Oracle connection
con.cursor()	Creates a Cursor object
sql = "insert ..."	Creates the parameterized INSERT statement
records = [...]	Stores multiple employee records
cursor.executemany(sql, records)	Executes the INSERT statement for all records
con.commit()	Permanently saves the inserted records
print(...)	Displays the success message
except cx_Oracle.DatabaseError as e	Handles Oracle Database errors
con.rollback()	Cancels pending changes when an error occurs
cursor.close()	Closes the Cursor
con.close()	Closes the Connection

Method	Purpose
connect()	Creates a Database connection
cursor()	Creates a Cursor object
executemany()	Executes the same parameterized SQL statement for multiple records
commit()	Saves all inserted records permanently
rollback()	Cancels pending changes if an error occurs
close()	Closes the Cursor and Connection

• The application inserts multiple rows into the employees table.
• The method used is executemany().
• The SQL statement is parameterized.
• The SQL statement contains the named bind variables :eno, :ename, :esal, and :eaddr.
• The employee data is stored in a list of tuples.
• Each tuple represents one employee record.
• The program inserts three employee records.
• The employees inserted are Sunny, Chinny, and Bunny.
• executemany() applies the same SQL statement to all records.
• We do not need to manually call execute() once for every record.
• commit() saves all inserted records permanently.
• If a Database error occurs before the transaction is committed, rollback() cancels pending changes.
• cx_Oracle.DatabaseError is used for Oracle Database exception handling.
• The finally block is used to release Database resources.
• The Cursor is closed before the Connection.

Topic	Description
Application	App5
SQL Operation	Insert Multiple Rows
Table	employees
Method Used	executemany()
SQL Statement	insert into employees values(:eno,:ename,:esal,:eaddr)
Data Source	List of tuples (records)
Number of Records	3
Employees	Sunny, Chinny, Bunny
Commit Required	Yes
Commit Method	con.commit()
Error Handling	rollback()
Exception	cx_Oracle.DatabaseError
Resource Cleanup	cursor.close() and con.close()

App5
 │
 ▼
Insert Multiple Rows
 │
 ▼
Create Parameterized SQL
 │
 ├── :eno
 ├── :ename
 ├── :esal
 └── :eaddr
 │
 ▼
Create records List
 │
 ├── Sunny
 ├── Chinny
 └── Bunny
 │
 ▼
cursor.executemany(sql, records)
 │
 ▼
Insert 3 Records
 │
 ▼
con.commit()
 │
 ▼
Records Saved

If Error
 │
 ▼
DatabaseError
 │
 ▼
con.rollback()

Finally
 │
 ▼
cursor.close()
 │
 ▼
con.close()

                         START
                           │
                           ▼
                   import cx_Oracle
                           │
                           ▼
                          try
                           │
                           ▼
                  Create Connection
                           │
                           ▼
                    Create Cursor
                           │
                           ▼
            Create Parameterized SQL
                           │
                           ▼
                   Create List
                    of Tuples
                           │
              ┌────────────┼────────────┐
              │            │            │
              ▼            ▼            ▼
            Sunny        Chinny       Bunny
              │            │            │
              └────────────┼────────────┘
                           ▼
              executemany(sql, records)
                           │
                           ▼
                  Insert 3 Records
                           │
                    ┌──────┴──────┐
                    │             │
                 Success         Error
                    │             │
                    ▼             ▼
                 commit()    DatabaseError
                    │             │
                    ▼             ▼
              Save Records    rollback()
                    │             │
                    ▼             ▼
              Print Success   Print Error
                    │             │
                    └──────┬──────┘
                           ▼
                        finally
                           │
                           ▼
                    Close Cursor
                           │
                           ▼
                  Close Connection
                           │
                           ▼
                          END

Part 8 Completed

Next: Part 9 — App6: Insert Multiple Rows with Dynamic Input from Keyboard

In the previous application, we inserted multiple employee records by creating a fixed list of tuples inside the Python program.

In this application, we will make the program dynamic.

The employee information will be taken from the user through the keyboard.

The program repeatedly asks the user to enter:

• Employee Number
• Employee Name
• Employee Salary
• Employee Address

After inserting one employee record, the program asks whether the user wants to insert another record.

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

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

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

This is fixed data.

In App6, employee information is entered at runtime:

eno = int(input("Enter Employee Number:"))
ename = input("Enter Employee Name:")
esal = float(input("Enter Employee Salary:"))
eaddr = input("Enter Employee Address:")

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

The main purpose of App6 is to insert an unknown number of employee records.

We do not know beforehand how many employees the user wants to enter.

Therefore, the program uses:

while True:

After every insertion, it asks:

Do you want to insert one more record [Yes|No]:

If the answer is:

Yes

the loop continues.

If the answer is:

No

the program executes:

break

and terminates the loop.

The program starts with:

import cx_Oracle

The cx_Oracle module provides the required functionality to communicate with Oracle Database.

Python
  │
  ▼
cx_Oracle
  │
  ▼
Oracle Database

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 / Host	localhost

The returned Connection object is stored in:

con

The Cursor object is created using:

cursor = con.cursor()

The Cursor is required to execute the INSERT SQL statements.

Connection Object
       │
       ▼
  con.cursor()
       │
       ▼
 Cursor Object
       │
       ▼
     cursor

The program uses:

while True:

Since the condition is always True, this creates an infinite loop.

The loop continues until the program explicitly executes:

break

while True
    │
    ▼
Read Employee
    │
    ▼
Insert Record
    │
    ▼
Insert Another?
    │
 ┌──┴──┐
 │     │
Yes    No
 │     │
 │     ▼
 │   break
 │
 └────► Repeat

The number of employee records is not fixed.

For example, one user may want to insert:

2 employees

while another user may want to insert:

10 employees

Therefore, instead of fixing the number of iterations, the program continues until the user chooses to stop.

Unknown Number of Records
          │
          ▼
      while True
          │
          ▼
Continue Until User Says No

The employee number is read using:

eno = int(input("Enter Employee Number:"))

input() returns keyboard input as a string.

Because employee number should be numeric, the program converts it using:

int()

Example:

Enter Employee Number:500

Now:

eno = 500

The employee name is read using:

ename = input("Enter Employee Name:")

No explicit type conversion is required because input() already returns a string.

Example:

Enter Employee Name:Sachin

Now:

ename = "Sachin"

The salary is read using:

esal = float(input("Enter Employee Salary:"))

The keyboard value is converted into a floating-point number using:

float()

Example:

Enter Employee Salary:5000

Internally:

esal = 5000.0

The employee address is read using:

eaddr = input("Enter Employee Address:")

Example:

Enter Employee Address:Mumbai

The value is stored in:

eaddr = "Mumbai"

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 tutorial creates the INSERT query as:

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

The query contains format specifiers:

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

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

The mapping is:

%d      → eno

'%s'    → ename

%f      → esal

'%s'    → eaddr

Notice that string values are placed inside single quotes:

'%s'

because SQL string values require quotes.

The program executes:

cursor.execute(sql % (eno, ename, esal, eaddr))

The expression:

sql % (eno, ename, esal, eaddr)

substitutes the current employee values into the SQL string.

For example:

eno   = 500
ename = "Sachin"
esal  = 5000.0
eaddr = "Mumbai"

The generated SQL becomes conceptually:

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

The SQL statement is executed using:

cursor.execute(sql % (eno, ename, esal, eaddr))

The execution flow is:

Keyboard Input
      │
      ▼
Python Variables
      │
      ▼
Format SQL String
      │
      ▼
cursor.execute()
      │
      ▼
Oracle Database
      │
      ▼
employees Table
      │
      ▼
New Record

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.

A safer form is:

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

cursor.execute(
    sql,
    eno=eno,
    ename=ename,
    esal=esal,
    eaddr=eaddr
)

This keeps SQL code and user-provided values separate.

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

After executing the INSERT statement, the program displays:

print("Record Inserted Successfully")

Output:

Record Inserted Successfully

This message is displayed for every successfully executed INSERT statement.

The program asks:

option = input("Do you want to insert one more record [Yes|No]:")

The user's answer is stored in:

option

For example:

Do you want to insert one more record [Yes|No]:Yes

or:

Do you want to insert one more record [Yes|No]:No

The program checks:

if option == "No":
    break

If the user enters exactly:

No

the break statement terminates the while loop.

Otherwise, the loop continues and the program asks for another employee record.

break immediately terminates the nearest loop.

if option == "No":
    break

Execution:

option
  │
  ▼
Is option == "No"?
   /        \
 Yes        No
  │          │
  ▼          ▼
break      Continue
  │          │
  ▼          │
Exit Loop    │
             └──► Next Employee

Suppose the user enters:

Yes

The condition:

option == "No"

becomes:

False

Therefore, break is not executed.

The while True loop starts its next iteration.

Yes
 │
 ▼
Condition False
 │
 ▼
No break
 │
 ▼
Repeat Loop
 │
 ▼
Enter Next Employee

Suppose the user enters:

No

The condition:

option == "No"

becomes:

True

Therefore:

break

is executed and the loop terminates.

No
 │
 ▼
Condition True
 │
 ▼
break
 │
 ▼
Exit while Loop
 │
 ▼
con.commit()

After the user finishes inserting records, the program executes:

con.commit()

The INSERT operations are DML operations.

commit() permanently saves the inserted employee records.

Employee 1 INSERT
Employee 2 INSERT
Employee 3 INSERT
        │
        ▼
Pending Transaction
        │
        ▼
con.commit()
        │
        ▼
All Changes Saved

In this program, commit() is placed after the while loop.

while True:
    ...
    cursor.execute(...)
    ...

con.commit()

This means the program can execute multiple INSERT operations first.

After 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

Suppose the user enters only one employee:

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]:No

The user enters No, so the loop terminates and the transaction is committed.

Suppose the user wants to insert three records:

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

After No is entered, the loop terminates and commit() saves the transaction.

                 while True
                     │
                     ▼
              Read Employee
                     │
                     ▼
              Execute INSERT
                     │
                     ▼
           Print Success Message
                     │
                     ▼
            Ask Yes / No
                     │
              ┌──────┴──────┐
              │             │
             Yes            No
              │             │
              ▼             ▼
         Next Iteration    break
                            │
                            ▼
                         commit()

The program handles Oracle Database errors using:

except cx_Oracle.DatabaseError as e:

If an Oracle Database error occurs, the exception object is stored in:

e

Database Operation
       │
       ▼
DatabaseError?
   ┌───┴───┐
   │       │
  No      Yes
   │       │
   ▼       ▼
Continue  except
           │
           ▼
           e

If a Database error occurs, the program checks:

if con:
    con.rollback()

If the Connection object exists:

con.rollback()

cancels the pending transaction changes.

Multiple INSERT Operations
          │
          ▼
Pending Transaction
          │
          ▼
Database Error
          │
          ▼
rollback()
          │
          ▼
Cancel Pending Changes

Because commit() is executed only after the loop finishes, the inserted records belong to the current transaction until that commit occurs.

Consider:

Record 1 → INSERT successful
Record 2 → INSERT successful
Record 3 → DatabaseError

If the transaction has not yet been committed, the exception handler calls:

con.rollback()

This rolls back the pending changes in that transaction.

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

The program displays the Database error using:

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

The general output structure is:

There is a problem with sql <Oracle Database Error>

The finally block is:

finally:
    if cursor:
        cursor.close()

    if con:
        con.close()

It executes whether the program completes successfully or a Database exception occurs.

Its main purpose is resource cleanup.

The Cursor is closed using:

if cursor:
    cursor.close()

If the Cursor object exists, close() releases the Cursor resource.

The Connection is closed using:

if con:
    con.close()

This releases the Oracle Database connection.

                         START
                           │
                           ▼
                   import cx_Oracle
                           │
                           ▼
                          try
                           │
                           ▼
                  Create Connection
                           │
                           ▼
                    Create Cursor
                           │
                           ▼
                      while True
                           │
                           ▼
                   Read eno, ename,
                    esal, eaddr
                           │
                           ▼
                   Create SQL Query
                           │
                           ▼
                   cursor.execute()
                           │
                           ▼
                   Record Inserted
                           │
                           ▼
                  Ask Yes or No
                           │
                  ┌────────┴────────┐
                  │                 │
                 Yes                No
                  │                 │
                  ▼                 ▼
             Repeat Loop          break
                                    │
                                    ▼
                               con.commit()
                                    │
                                    ▼
                             Save All Records
                                    │
                                    ▼
                                 finally
                                    │
                                    ▼
                              Close Cursor
                                    │
                                    ▼
                            Close Connection
                                    │
                                    ▼
                                   END

                     START
                       │
                       ▼
               Database Operation
                       │
                       ▼
                 DatabaseError
                       │
                       ▼
       except cx_Oracle.DatabaseError as e
                       │
                       ▼
                    if con
                       │
                       ▼
                con.rollback()
                       │
                       ▼
           Cancel Pending Changes
                       │
                       ▼
               Print SQL Error
                       │
                       ▼
                    finally
                       │
                       ▼
                cursor.close()
                       │
                       ▼
                  con.close()
                       │
                       ▼
                      END

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()

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

Statement	Purpose
while True	Repeats employee insertion
if option == "No"	Checks whether the user wants to stop
break	Terminates the loop
try	Contains Database operations
except	Handles Database errors
finally	Performs resource cleanup

• App6 inserts multiple employee records dynamically.
• Employee information is accepted from the keyboard using input().
• int() converts employee number into an integer.
• float() converts employee salary into a floating-point value.
• The program uses while True because the number of employee records is not fixed.
• The loop continues until the user enters No.
• break terminates the loop.
• The original tutorial constructs the INSERT statement using %d, %s, and %f format specifiers.
• cursor.execute() executes one INSERT statement during each loop iteration.
• After every successful INSERT, the program asks whether another employee should be inserted.
• commit() is placed after the loop and saves the transaction.
• If a Database error occurs before commit, rollback() cancels pending transaction changes.
• cx_Oracle.DatabaseError handles Oracle Database-related errors.
• The finally block closes the Cursor and Connection.
• For modern real-world code, bind variables should be preferred over constructing SQL directly from keyboard input.

Topic	Description
Application	App6
Purpose	Insert multiple records dynamically
Input	Keyboard
Input Function	input()
Loop	while True
Stop Statement	break
SQL Operation	INSERT
Execution Method	execute()
Save Transaction	commit()
Cancel Transaction	rollback()
Exception	cx_Oracle.DatabaseError
Cleanup	finally

App6
 │
 ▼
Dynamic Multiple INSERT
 │
 ▼
while True
 │
 ▼
Read eno
Read ename
Read esal
Read eaddr
 │
 ▼
Create INSERT SQL
 │
 ▼
cursor.execute()
 │
 ▼
Record Inserted
 │
 ▼
Ask Yes / No
 │
 ├── Yes → Repeat
 │
 └── No
       │
       ▼
      break
       │
       ▼
   con.commit()
       │
       ▼
  Records Saved

If Error
   │
   ▼
DatabaseError
   │
   ▼
rollback()

Finally
   │
   ▼
Close Cursor
   │
   ▼
Close Connection

                           START
                             │
                             ▼
                     import cx_Oracle
                             │
                             ▼
                            try
                             │
                             ▼
                    Create Connection
                             │
                             ▼
                      Create Cursor
                             │
                             ▼
                        while True
                             │
                             ▼
                 Take Employee Details
                    from Keyboard
                             │
                             ▼
                    Execute INSERT
                             │
                             ▼
                    Record Inserted
                             │
                             ▼
                  Ask Another Record?
                       /           \
                     Yes            No
                      │              │
                      │              ▼
                      │            break
                      │              │
                      └── Repeat     ▼
                                 commit()
                                    │
                              ┌─────┴─────┐
                              │           │
                           Success       Error
                              │           │
                              │           ▼
                              │      DatabaseError
                              │           │
                              │           ▼
                              │       rollback()
                              │           │
                              └─────┬─────┘
                                    ▼
                                 finally
                                    │
                                    ▼
                              Close Cursor
                                    │
                                    ▼
                            Close Connection
                                    │
                                    ▼
                                   END

Part 9 Completed

Next: Part 10 — App7: Update Employee Record

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 program accepts two values from the keyboard:

Input	Purpose
Increment Salary	Amount to add to the existing salary
Salary Range	Maximum salary limit used for selecting employees

For example:

Increment Salary = 500
Salary Range     = 5000

The program increases salaries by 500 for employees whose current salary is less than 5000.

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 program starts with:

import cx_Oracle

The cx_Oracle module is imported to communicate with Oracle Database.

Python Program
      │
      ▼
cx_Oracle
      │
      ▼
Oracle Database

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

The Cursor object is created using:

cursor = con.cursor()

The Cursor is required to execute the UPDATE SQL statement.

Connection
    │
    ▼
con.cursor()
    │
    ▼
Cursor Object
    │
    ▼
cursor

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

The salary increment is read using:

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

Example:

Enter Increment Salary:500

The entered value is converted into a floating-point number:

increment = 500.0

This value will be added to the existing employee salary.

The salary range is read using:

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

Example:

Enter Salary Range:5000

The value becomes:

salrange = 5000.0

Only employees whose salary is less than this value will be updated.

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

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

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

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.

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

Employees satisfying:

esal < 5000

are updated.

Employees that do not satisfy the condition are not updated.

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

The example given in the tutorial is:

Increment Salary → 500

Salary Range → 5000

After substituting these values, the SQL statement becomes:

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

This means:

Find every employee
whose salary is below 5000

        │
        ▼

Add 500 to that employee's salary

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
Salary Range     = 5000

The condition is:

esal < 5000

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:

<=

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

+-----+--------+------+
| 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
salrange  = 5000

results in:

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

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

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.

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.

The transaction should be committed to permanently save the updated values.

UPDATE
  │
  ▼
Modify Records
  │
  ▼
commit()
  │
  ▼
Save Changes

The same transaction concept applies to:

INSERT
UPDATE
DELETE

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

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()	rollback()
Saves changes permanently	Cancels uncommitted changes
Used after successful UPDATE	Used when an error occurs
con.commit()	con.rollback()

            UPDATE
              │
              ▼
         Transaction
          /       \
         /         \
        ▼           ▼
    Success        Error
       │             │
       ▼             ▼
   commit()      rollback()
       │             │
       ▼             ▼
      Save          Cancel

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.

The finally block executes whether an exception occurs or not.

finally:
    if cursor:
        cursor.close()

    if con:
        con.close()

Its purpose is to release Database resources.

            try
             │
       ┌─────┴─────┐
       │           │
    Success       Error
       │           │
       │         except
       │           │
       └─────┬─────┘
             ▼
          finally
             │
             ▼
      Close Resources

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.

Import cx_Oracle
      │
      ▼
Create Connection
      │
      ▼
Create Cursor
      │
      ▼
Read Increment Salary
      │
      ▼
Read Salary Range
      │
      ▼
Create UPDATE SQL
      │
      ▼
Execute UPDATE Query
      │
      ▼
Records Updated Successfully
      │
      ▼
Commit Changes
      │
      ▼
Exception?
   ┌──┴──┐
   │     │
  No    Yes
   │     │
   ▼     ▼
Close  Rollback
Resources Print Error
   │     │
   └──┬──┘
      ▼
Program Ends

                      START
                        │
                        ▼
                import cx_Oracle
                        │
                        ▼
               Create Connection
                        │
                        ▼
                 Create Cursor
                        │
                        ▼
              Enter Increment Salary
                        │
                        ▼
               Enter Salary Range
                        │
                        ▼
                Create UPDATE SQL
                        │
                        ▼
                cursor.execute()
                        │
                        ▼
             Find Matching Employees
                        │
                        ▼
               Increase Salaries
                        │
                        ▼
       "Records Updated Successfully"
                        │
                        ▼
                  con.commit()
                        │
                        ▼
                Save Changes
                        │
                        ▼
                    finally
                        │
                        ▼
                 Close Cursor
                        │
                        ▼
               Close Connection
                        │
                        ▼
                       END

                   START
                     │
                     ▼
              Execute UPDATE
                     │
                     ▼
               DatabaseError
                     │
                     ▼
except cx_Oracle.DatabaseError as e
                     │
                     ▼
                   if con
                     │
                     ▼
              con.rollback()
                     │
                     ▼
          Cancel Temporary Changes
                     │
                     ▼
              Print SQL Error
                     │
                     ▼
                  finally
                     │
                     ▼
              cursor.close()
                     │
                     ▼
                con.close()
                     │
                     ▼
                    END

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.

• App7 updates employee salaries in the employees table.
• The SQL statement used is UPDATE.
• UPDATE is a DML operation.
• The increment amount is entered dynamically from the keyboard.
• The salary range is also entered dynamically.
• The variable increment stores the amount added to employee salary.
• The variable salrange stores the salary limit.
• Only employees whose salary is less than the specified salary range are updated.
• The original SQL statement contains two %f format specifiers.
• The first %f represents the increment amount.
• The second %f represents the salary range.
• esal=esal+increment increases the existing salary.
• The WHERE clause controls which records are updated.
• execute() executes the UPDATE statement.
• commit() permanently saves the updated salary values.
• rollback() cancels uncommitted changes if an error occurs.
• The program uses try-except-finally for safe Database programming.
• The Cursor and Connection are closed inside the finally block.