Python - Connect to Oracle Database

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
  • 2 mins read

Here I am giving some examples of Python programs to connect to Oracle Database using cx_Oracle package.

1. Connect to Oracle Database in Python and Print The Version Number

In the following example, it will import the package cx_Oracle in Python program and then will make the connection to Oracle database and will get the database version.

import cx_Oracle

# scott is the username and tiger is the password. Change below according to your username and password.
# also change the localhost with your host and orcl to your database SID

con = cx_Oracle.connect("scott", "tiger", "localhost/orcl")

print(con.version)

con.close

Output

11.2.0.1.0

2. Print The Oracle SQL Query Result

Example to connect to Oracle database, perform a SQL query and print the results.

import cx_Oracle

con = cx_Oracle.connect("scott", "tiger", "localhost/orcl")

cur = con.cursor()
cur.execute('select * from dept order by deptno')
for result in cur:
    print (result)

cur.close()
con.close()

Output

(10, 'ACCOUNTING', 'NEW YORK')
(20, 'RESEARCH', 'DALLAS')
(30, 'SALES', 'CHICAGO')
(40, 'OPERATIONS', 'BOSTON')

3. Example to Update Records in Oracle Database Table and Commit

It will update the records in the EMP table of SCOTT schema and then will print the affected rows by the update and then performs a commit.

import cx_Oracle

con = cx_Oracle.connect("scott", "tiger", "localhost/orcl")

cur = con.cursor()

cur.execute("Update emp2 set comm = 80 where deptno = 20")

print ("Rows affected: ", cur.rowcount)
cur.close()

con.commit()

con.close()

Output

Rows affected: 4

See also: