How to Save Data in Oracle Database Using Python?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • Python
  • 1 min read

Use connection.commit() method to save data in Oracle Database using Python. The below is an example:

Save Data in Oracle Database Using Python Example

In the following Python program, it will create the connection using the cx_Oracle library in variable CONN and then will execute an update statement to update EMP table's comm column. After that, before closing the Oracle database connection, it will save the changes using the COMMIT() method.

import cx_Oracle

conn = cx_Oracle.connect("scott", "tiger", "localhost:1521/orcl")

cur = conn.cursor()

cur.execute('update emp set comm = sal * 10 / 100')

conn.commit()
cur.close()
conn.close()

See also:

This Post Has 2 Comments

  1. Imran Syed August 24, 2020

    Hi,

    I need python script to pull the data from Oracle database and insert into Postgresql database.

    could you please help me.

    Regards,
    Imran

    1. Vinish Kapoor August 24, 2020

      Below is an example of pulling data from Oracle in Python:

      import cx_Oracle

con = cx_Oracle.connect('hr/hrpsw@localhost/orcl')

def display_emp(n_empno):
    cur = con.cursor()
    cur.execute("select first_name, last_name, phone_number, hire_date, salary from employees where employee_id = :n_empno", {'n_empno': (n_empno)})
    for fname, lname, phnumber, hdate, sal in cur:
        print('First Name: ', fname)
        print('Last Name: ', lname)
        print('Phone: ', phnumber)
        print('Hire Date: ', hdate)
        print('Salary: ', sal)
    cur.close()

while True:
    empno = input('Enter Employee Number: (enter 0 to exit)')
    if empno == '0':
        break
        con.close()
    else:
        display_emp(empno)

      The above Python code is pulling data from Oracle and printing on the screen in the For loop.

      You can insert the data into PostgresSQL in the For Loop section instead of printing it.

      Check the following tutorial for the Inserting records into PostgresSQL from Python.

Comments are closed.