Nearby lessons
154 of 159Python - MySQL to Oracle Data Transfer
- Read all employee records from the MySQL Database using SELECT and fetchall()
- Store each employee record as a tuple inside a Python list
- Connect to the Oracle Database with cx_Oracle and prepare a parameterized INSERT query
- Insert all employee tuples into Oracle using executemany() and save them with commit()
- Handle MySQL and Oracle errors separately with rollback() and proper resource cleanup
Introduction
In this application, we will copy employee records from a MySQL Database into an Oracle Database.
The employee records are already available in the employees table of the MySQL Database.
Our Python program performs two main operations:
- Read all employee records from the MySQL Database.
- Insert those employee records into the Oracle Database.
MySQL Database
│
▼
employees Table
│
▼
SELECT Records
│
▼
fetchall()
│
▼
Python List
│
▼
Oracle Database
│
▼
employees Table
Therefore, Python acts as a bridge between the two databases.
Objective of the Program
The objective of this application is:
Copy Employee Records FROM MySQL employees Table TO Oracle employees Table
The complete data-transfer process is:
Source Database
│
▼
MySQL
│
▼
Read Records
│
▼
Python List
│
▼
Insert Records
│
▼
Oracle
│
▼
Destination Database
Two Stages of the Program
The complete application is divided into two stages.
Stage 1 — Read Data from MySQL
MySQL │ ▼ SELECT * FROM employees │ ▼ fetchall() │ ▼ Python List
Stage 2 — Write Data into Oracle
Python List
│
▼
executemany()
│
▼
Oracle employees Table
│
▼
commit()
Program Overview
The program contains two separate Database operations.
Python Program
│
┌────────────┴────────────┐
│ │
▼ ▼
MySQL Operation Oracle Operation
│ │
▼ ▼
Connect to MySQL Connect to Oracle
│ │
▼ ▼
SELECT Records INSERT Records
│ ▲
▼ │
fetchall() │
│ │
▼ │
Python List ──────────────────┘
MySQL is the source Database, while Oracle is the destination Database.
Complete Program - MySQL to Oracle Transfer
The complete program transfers employee records from the MySQL Database to the Oracle Database.
The program performs the following operations:
- Connect to the MySQL Database and read all employee records.
- Store the records as tuples inside a Python list.
- Connect to the Oracle Database and insert all records.
- Commit the transaction and display the success message.
The program has two separate try-except-finally blocks - one for the MySQL operation and one for the Oracle operation.
If the transfer completes successfully, the program displays:
Records Copied from MySQL Database to Oracle Database Successfully
Part 1 - Reading Data from MySQL: Import Required Modules
The first section of the program retrieves all employee records from MySQL.
The steps are:
Import Modules
│
▼
Connect MySQL
│
▼
Create Cursor
│
▼
SELECT employees
│
▼
fetchall()
│
▼
Create List
│
▼
Store Employee Tuples
Step 1 — Import Required Modules
The program starts by importing two Database modules:
import mysql.connector import cx_Oracle
The modules have different purposes.
| Module | Database | Purpose |
|---|---|---|
mysql.connector |
MySQL | Communicates with the MySQL Database |
cx_Oracle |
Oracle | Communicates with the Oracle Database |
Python │ ├──── mysql.connector ────► MySQL │ └──── cx_Oracle ──────────► Oracle
Connecting to the MySQL Database
The program establishes a connection with MySQL using:
con = mysql.connector.connect(
host='localhost',
database='durgadb',
user='root',
password='root'
)
The connection information used in the program is:
| Parameter | Value |
|---|---|
| Host | localhost |
| Database | durgadb |
| User | root |
| Password | root |
The returned Connection object is stored in:
con
The MySQL connection flow is:
Python Program
│
▼
mysql.connector
│
▼
connect()
│
▼
MySQL Server
│
▼
durgadb
Creating the MySQL Cursor and Executing the SELECT Query
After establishing the connection, the program creates a Cursor object.
cursor = con.cursor()
The Cursor object is required for executing SQL statements.
Connection
│
▼
cursor()
│
▼
Cursor Object
│
▼
Execute SQL Queries
Step 4 — Execute SELECT Query
The program retrieves all employee records using:
cursor.execute("select * from employees")
The SQL statement is:
SELECT * FROM employees;
Here:
| Part | Meaning |
|---|---|
SELECT |
Retrieve data |
* |
All columns |
FROM employees |
Retrieve data from the employees table |
Fetching All Employee Records with fetchall()
After executing the SELECT query, the program retrieves all rows using:
data = cursor.fetchall()
fetchall() retrieves all employee records returned by the SELECT query.
The records are stored in:
data
Conceptually, the result can look like:
[
(100, 'Sachin', 1000.0, 'Mumbai'),
(200, 'Dhoni', 2000.0, 'Ranchi'),
(300, 'Kohli', 3000.0, 'Delhi')
]
SELECT Query
│
▼
fetchall()
│
▼
All Employee Rows
│
▼
data
Building the Python List of Employee Tuples
An empty list is created:
list = []
This list is used as temporary storage for employee records.
MySQL │ ▼ Employee Records │ ▼ Python List │ ▼ Oracle
Initially:
list = []
After processing the employee records, it will contain employee tuples.
Step 7 — Process Every Employee Record
The program processes the rows returned by fetchall() using a for loop:
for row in data:
During each iteration, row contains one employee record.
For example:
row = (100, 'Sachin', 1000.0, 'Mumbai')
The column values can be accessed using indexes:
| Index | Employee Information |
|---|---|
row[0] |
Employee Number |
row[1] |
Employee Name |
row[2] |
Employee Salary |
row[3] |
Employee Address |
Step 8 — Convert Each Row into a Tuple
Each employee row is explicitly converted into a tuple:
t = (row[0], row[1], row[2], row[3])
For example:
row │ ▼ (100, 'Sachin', 1000.0, 'Mumbai') │ ▼ t │ ▼ (100, 'Sachin', 1000.0, 'Mumbai')
The tuple contains all four employee values.
Step 9 — Append Tuple to the List
The employee tuple is added to the list using:
list.append(t)
The loop performs this operation for every employee.
Initial:
list = []
After Employee 1:
list = [
(100, 'Sachin', 1000.0, 'Mumbai')
]
After Employee 2:
list = [
(100, 'Sachin', 1000.0, 'Mumbai'),
(200, 'Dhoni', 2000.0, 'Ranchi')
]
After Employee 3:
list = [
(100, 'Sachin', 1000.0, 'Mumbai'),
(200, 'Dhoni', 2000.0, 'Ranchi'),
(300, 'Kohli', 3000.0, 'Delhi')
]
This list will later be passed to Oracle.
How MySQL Data is Converted
MySQL employees Table
│
▼
SELECT *
│
▼
fetchall()
│
▼
data
│
▼
for row in data
│
▼
(row[0], row[1], row[2], row[3])
│
▼
t
│
▼
list.append(t)
│
▼
Python List
MySQL Exception Handling and Resource Cleanup
MySQL Database errors are handled using:
except mysql.connector.DatabaseError as e:
If a Database error occurs, the exception object is stored in:
e
The error information is displayed using:
print("There is a problem with MySql :", e)
MySQL rollback()
If a MySQL Database error occurs, the program checks the Connection object:
if con:
con.rollback()
rollback() cancels applicable temporary or uncommitted transaction changes.
Database Error
│
▼
con exists?
│
▼
con.rollback()
│
▼
Cancel Uncommitted Changes
Close MySQL Resources
The MySQL Cursor and Connection are closed inside the finally block.
finally:
if cursor:
cursor.close()
if con:
con.close()
The resources are closed in this order:
Cursor │ ▼ cursor.close() │ ▼ Connection │ ▼ con.close()
At this point, reading from the MySQL Database is complete.
Part 2 - Writing Data into Oracle: Connection and Cursor
After retrieving the employee records from MySQL, the second part of the program inserts them into Oracle.
Python List
│
▼
Connect Oracle
│
▼
Create Cursor
│
▼
Prepare INSERT Query
│
▼
executemany()
│
▼
Oracle employees Table
│
▼
commit()
Step 10 — Connect to Oracle Database
The program establishes an Oracle Database connection using:
con = cx_Oracle.connect('scott/tiger@localhost')
The connection details are:
| Information | Value |
|---|---|
| Username | scott |
| Password | tiger |
| Database | localhost |
Python │ ▼ cx_Oracle │ ▼ connect() │ ▼ Oracle Database
Step 11 — Create Oracle Cursor
A new Cursor object is created for the Oracle connection:
cursor = con.cursor()
This Cursor will execute the Oracle INSERT statement.
Oracle Connection
│
▼
con.cursor()
│
▼
Oracle Cursor
│
▼
Execute INSERT
Preparing the Parameterized INSERT Query
The program creates a parameterized INSERT query:
sql = "insert into employees values(:eno,:ename,:esal,:eaddr)"
The placeholders are:
| Placeholder | Value |
|---|---|
:eno |
Employee Number |
:ename |
Employee Name |
:esal |
Employee Salary |
:eaddr |
Employee Address |
The query inserts one employee structure into the Oracle employees table.
Understanding the Parameterized Query
insert into employees
values(:eno, :ename, :esal, :eaddr)
│ │ │ │
▼ ▼ ▼ ▼
eno ename esal eaddr
The employee values come from the tuples stored in the Python list.
For example:
(100, 'Sachin', 1000.0, 'Mumbai') │ │ │ │ ▼ ▼ ▼ ▼ :eno :ename :esal :eaddr
Inserting All Records with executemany() and Committing
Step 13 — Insert All Records using executemany()
All employee records are inserted using:
cursor.executemany(sql, list)
executemany() executes the same parameterized INSERT statement for all tuples available in the list.
Python List
│
├── Employee 1
├── Employee 2
└── Employee 3
│
▼
executemany()
│
▼
Oracle employees Table
Therefore, we do not need to call execute() separately for every employee record.
How executemany() Works in This Program
sql
│
│ "insert into employees
│ values(:eno,:ename,:esal,:eaddr)"
│
├────────────────────────────┐
│ │
▼ ▼
Parameterized Query Python List
│
├── Tuple 1
├── Tuple 2
└── Tuple 3
│
▼
executemany()
│
▼
Insert All Employees
│
▼
Oracle Database
Step 14 — Commit the Oracle Transaction
After inserting all records, the program executes:
con.commit()
commit() permanently saves the copied employee records in the Oracle Database.
executemany()
│
▼
Records Inserted
│
▼
Uncommitted Changes
│
▼
con.commit()
│
▼
Records Permanently Saved
Step 15 — Display Success Message
After the records are committed successfully, the program displays:
Records Copied from MySQL Database to Oracle Database Successfully
The statement responsible for this output is:
print("Records Copied from MySQL Database to Oracle Database Successfully")
The employee records that existed in the MySQL employees table are now inserted into the Oracle employees table.
Oracle Exception Handling and Resource Cleanup
Oracle Database errors are handled using:
except cx_Oracle.DatabaseError as e:
If an error occurs, it is stored in:
e
The program displays the error using:
print("There is a problem with sql", e)
Oracle rollback()
If an error occurs while inserting the employee records, the program executes:
if con:
con.rollback()
This rolls back applicable uncommitted Oracle transaction changes.
INSERT Records
│
▼
Database Error
│
▼
con.rollback()
│
▼
Cancel Uncommitted Changes
Close Oracle Resources
Finally, the Oracle Cursor and Connection are closed:
finally:
if cursor:
cursor.close()
if con:
con.close()
The cleanup flow is:
Oracle Cursor
│
▼
cursor.close()
│
▼
Oracle Connection
│
▼
con.close()
Complete Data Transfer Flow
The complete program executes in the following data-transfer flow:
START
│
▼
Import mysql.connector
Import cx_Oracle
│
▼
Connect to MySQL
│
▼
Create Cursor
│
▼
SELECT * FROM employees
│
▼
fetchall()
│
▼
Employee Rows
│
▼
Create List
│
▼
for row in data
│
▼
Convert Row to Tuple
│
▼
list.append()
│
▼
More MySQL Records?
/ Yes No
│ │
└── Repeat ▼
Close MySQL
Resources
│
▼
Connect to Oracle
│
▼
Create Cursor
│
▼
Prepare INSERT SQL
│
▼
cursor.executemany()
│
▼
Insert Employee Records
│
▼
con.commit()
│
▼
Display Success
│
▼
Close Oracle Resources
│
▼
END
Source to Destination Flow
SOURCE
MySQL Database
│
▼
durgadb
│
▼
employees
│
▼
SELECT *
│
▼
fetchall()
│
▼
data
│
▼
Python List
│
▼
executemany()
│
▼
employees
│
▼
Oracle Database
DESTINATION
Role of the Python List
The Python list works as temporary storage between the two databases.
MySQL │ ▼ fetchall() │ ▼ Python List │ ▼ executemany() │ ▼ Oracle
For example:
list = [
(100, 'Sachin', 1000.0, 'Mumbai'),
(200, 'Dhoni', 2000.0, 'Ranchi'),
(300, 'Kohli', 3000.0, 'Delhi')
]
These tuples can then be passed directly to:
cursor.executemany(sql, list)
MySQL and Oracle Responsibilities
The two databases have different responsibilities in the transfer process.
| Operation | MySQL | Oracle |
|---|---|---|
| Role | Source | Destination |
| Python Module | mysql.connector |
cx_Oracle |
| Main SQL | SELECT |
INSERT |
| Fetch | fetchall() |
Not required |
| Insert | Not required in transfer stage | executemany() |
| Commit | Not required for SELECT | commit() |
Methods Used and Their Differences
The following methods are used in the program:
| Method | Purpose |
|---|---|
mysql.connector.connect() |
Connects Python to the MySQL Database |
cx_Oracle.connect() |
Connects Python to the Oracle Database |
cursor() |
Creates a Cursor object |
execute() |
Executes the MySQL SELECT query |
fetchall() |
Retrieves all employee records from MySQL |
append() |
Adds each employee tuple to the Python list |
executemany() |
Inserts multiple employee records into Oracle |
commit() |
Permanently saves copied records |
rollback() |
Rolls back applicable uncommitted changes when an error occurs |
close() |
Closes Cursor and Connection resources |
Difference Between execute(), fetchall(), and executemany()
| Method | Purpose in This Program |
|---|---|
execute() |
Executes the SELECT query against MySQL |
fetchall() |
Retrieves all rows returned by MySQL |
executemany() |
Executes the Oracle INSERT operation for multiple employee tuples |
execute() │ ▼ Run SELECT │ ▼ fetchall() │ ▼ Get Records │ ▼ executemany() │ ▼ Insert Records
Complete Program Sequence
The complete program executes in the following sequence:
- Import mysql.connector.
- Import cx_Oracle.
- Connect to the MySQL durgadb Database.
- Create the MySQL Cursor.
- Execute SELECT * FROM employees.
- Retrieve all employee records using fetchall().
- Create an empty Python list.
- Process every employee record.
- Convert every row into a tuple.
- Append every tuple to the list.
- Handle possible MySQL Database errors.
- Close the MySQL Cursor.
- Close the MySQL Connection.
- Connect to the Oracle Database.
- Create the Oracle Cursor.
- Prepare the parameterized INSERT query.
- Pass the SQL query and employee list to executemany().
- Insert all employee records into Oracle.
- Execute commit().
- Display the success message.
- Handle possible Oracle Database errors.
- Close the Oracle Cursor.
- Close the Oracle Connection.
Summary, Important Points, and Quick Revision
Here is a quick summary of the data-transfer program:
| Topic | Description |
|---|---|
| Source Database | MySQL |
| Source Database Name | durgadb |
| Destination Database | Oracle |
| Table | employees |
| MySQL Module | mysql.connector |
| Oracle Module | cx_Oracle |
| Read SQL | SELECT * FROM employees |
| Read Method | fetchall() |
| Temporary Storage | Python List |
| Record Format | Tuple |
| List Method | append() |
| Oracle Insert Method | executemany() |
| Save Changes | commit() |
| Error Handling | rollback() |
| Resource Cleanup | cursor.close() and con.close() |
Important Points
- The program works with two different Database systems.
- MySQL is used as the source Database.
- Oracle is used as the destination Database.
mysql.connectoris used to communicate with MySQL.cx_Oracleis used to communicate with Oracle.- The MySQL Database used in the program is
durgadb. - The program retrieves employee records using
SELECT * FROM employees. fetchall()retrieves all employee records from MySQL.- A Python list is used as temporary storage for the records.
- Each employee record is stored as a tuple.
list.append(t)adds each employee tuple to the list.- The Oracle INSERT statement uses placeholders such as
:eno,:ename,:esaland:eaddr. executemany()inserts all employee tuples into Oracle.commit()permanently saves the inserted Oracle records.rollback()is used when a Database error occurs.- MySQL and Oracle errors are handled separately.
- The program has separate
try-except-finallyblocks for the two Database operations. - The MySQL Cursor and Connection are closed after reading the data.
- The Oracle Cursor and Connection are closed after inserting the data.
- The Oracle
employeestable should already exist with a compatible structure before the records are inserted.
Quick Revision
import mysql.connector
import cx_Oracle
│
▼
Connect MySQL
│
▼
SELECT employees
│
▼
fetchall()
│
▼
Employee Records
│
▼
Create Python List
│
▼
Convert Rows to Tuples
│
▼
append()
│
▼
Close MySQL
│
▼
Connect Oracle
│
▼
Prepare INSERT Query
│
▼
executemany()
│
▼
commit()
│
▼
Records Copied
│
▼
Close Oracle
Final Concept
DATABASE-TO-DATABASE COPY
Python Program
│
┌─────────────┴─────────────┐
│ │
▼ ▼
mysql.connector cx_Oracle
│ │
▼ ▼
MySQL Oracle
│ ▲
▼ │
employees Table │
│ │
▼ │
SELECT * │
│ │
▼ │
fetchall() │
│ │
▼ │
Python List │
│ │
└──────► executemany() ─────┘
│
▼
commit()
│
▼
Copy Completed
In this application, we learned how to transfer employee records from a MySQL Database to an Oracle Database using Python.
The records are retrieved from MySQL using fetchall(), temporarily stored as tuples inside a Python list, and inserted into Oracle using executemany().
Finally, commit() permanently saves the copied records in Oracle, while rollback() provides transaction handling when a Database error occurs.
- MySQL is the source database and Oracle is the destination database in the transfer
- fetchall() retrieves all employee records returned by the MySQL SELECT query
- Each employee record is stored as a tuple inside a Python list used for temporary storage
- executemany() executes the parameterized INSERT statement for all employee tuples in one call
- commit() permanently saves the copied records while rollback() cancels changes when an error occurs