Connect to Oracle Database using Python

Kailash Chandra Behera | Friday, July 24, 2020

Introduction

This blog provides guidelines to install the python oracle library (cx-Oracle) for python language and how to get python oracle database connection, executes SQL queries (select and sql insert query) using python programming.

Getting Started

cx-Oracle is a module in Python which provides access to connect with Oracle Server Database. Installing cx-Oracle is not a big task for Python developer. A single line of command installs cx-Oracle library into machine. Before installing cx-Oracle make sure that Python and pip is installed in your machine.

If you are new in Python, refer my articles to install Python and pip. These two articles are helping to install Python and pip. Pip is a Python Package Manager tool that helps to install Python packages or modules into the machine. Hence before installing any packages or modules pip must have installed in the machine.

The below command is helping to install cx-Oracle module into machine.

cx_oracle python install Windows

 pip install cx-Oracle

cx_oracle python install Linux

 sudo -H pip install cx-Oracle

cx_oracle python install


This demonstration is conducted in Microsoft Windows 10 and applicable in Windows only, to install the cx-Oracle, follow the below steps.

  1. Press Windows Key+R key
  2. Enter cmd.exe and press Enter
  3. The command prompt will be appeared
  4. Use below command and press Enter.
     pip install cx-Oracle
  5. The installation process will be started, if everything going fine the package or module will be installed successfully.
    python programming

    Connect to Oracle Database using Python

Python Oracle Database Connection

Below code example describes how to make python oracle connect.

 import cx_Oracle  
 try:  
   with cx_Oracle.connect(  
       "username",  
       "password",  
       "den",  
       encoding=""UTF-8"") as connection:  
   # show the version of the Oracle Database  
   print(connection.version)  
 except cx_Oracle.Error as error:  
   print(error)

SELECT SQL queries

The below example executes SQL Query and fetch data from table
 import cx_Oracle  
 try:  
   with cx_Oracle.connect(  
       "username",  
       "password",  
       "den",  
       encoding=""UTF-8"") as connection:  
 cursor=connection.cursor()  
  cursor.execute("SELECT * FROM `Student`")  
  result = cursor.fetchall()  
  for i in result:  
     print(i)  
 except cx_Oracle.Error as error:  
   print(error)  
 finally:  
   # close the database connection using close() method.  
   connection.close()

SQL Insert Query

 import cx_Oracle  
 try:  
   with cx_Oracle.connect(  
       "username",  
       "password",  
       "den",  
       encoding=""UTF-8"") as connection:  
 cursor=connection.cursor()  
  # insert query to create a new record  
   sql = "INSERT INTO `STUDENT` (`StudentID`, `Name`, `DivID`, `Class`, `roll`, `cardid`) VALUES (%s, %s, %s, %s, %s, %s)"  
  cursor.execute(sql, (1009,'Kailash',1,'V',1002,'12345'))  
  connection.commit()  
 except cx_Oracle.Error as error:  
   print(error)  
 finally:  
   # close the database connection using close() method.  
   connection.close()

Summary

In the above, we saw how to install the python oracle library (cx-Oracle) and how to get python oracle database connection, executes SQL queries (select and sql insert query) using python programming. I hope you have enjoyed it a lot.

Thanks

By at