How To connect To Oracle Database Using Python

In this oracle tutorial, we will learn multiple approaches to connect to Oracle database using Python.

How To connect To Oracle Database Using Python

• Python provides various libraries that work with databases of all kinds.
• Python offers two modules using which we can connect to the Oracle database. Those modules are:
  • cx_Oracle
  • Pyodbc
• The Python cx_Oracle module is easy and popular because it can connect to the Oracle database with few details and does not have any dependencies.
• Python pyodbc module requires Microsoft Visual C++ 14.0 or higher to be installed on Windows operating system.
• In our blog, “How to Fetch Data from Oracle Database in Python—SQL Server Guides,” we have explained how to use Python cx_Oracle.
• The upcoming section will explain how to connect to the Oracle database using the Python pyodbc module.

Read How to create a database in Oracle 19c

Approach-1 Using Python Pyodbc

In this section, we will learn to connect to Oracle database using Python pyodbc.

• The Python Pyodbc module is fully dedicated to the Oracle database, and using this module, we can execute all the queries of the Oracle database.
• This module is not limited to Oracle database only. It works perfectly with other databases as well like PL/SQL, SQLite, Microsoft SQL, etc.
• Python pyodbc module requires Microsoft Visual C++ 14.0 or higher to be installed through pip.
• Here is the code to connect with the Oracle database using Python pyodbc module.

conn = pyodbc.connect('''
        DRIVER={Oracle in OraDB19Home1};
        DATABASE=orcl;
        UID=c##sqlserverguides;
        PWD=root
''')

• Description:
  • DRIVER: Provide the name of the driver for Oracle
  • Database: Provide the name of the global database, Default is ‘orcl’.
  • UID: Provide the user name through which you want to connect.
  • PWD: Enter the password for that user.
• If you are unaware of the driver, use the below code to display all the drivers present in your system. We did lots of research on this, and we highly recommend that you use Oracle in OraDB19Home1 for Oracle database 19c.

import pyodbc

print(pyodbc.drivers())

• Here is the demonstration of how to connect oracle database using python pyodbc module.

Source Code:

In this source code, the database we are connecting with is ‘orcl‘ & we are connecting through a common user c##sqlserverguides. Table name is life_expectancy & it holds the record of countries and their life expectancy.

import pyodbc

conn = pyodbc.connect('''
        DRIVER={Oracle in OraDB19Home1};
        DATABASE=orcl;
        UID=c##sqlserverguides;
        PWD=root
''')

query = ' select * from life_expectancy'

cursor = conn.execute(query)

for item in cursor:
    print(item)

Output:

In this output, all the four records have been displayed in the VS Code terminal. The records have been fetched from the Oracle database using Python.

Read Oracle Database vs MySQL Database

Installation on Windows

Using pip package manager we can install python pyodbc module on the windows operating system. Please note that you will see an error message incase Microsoft Visual C++ 14 or later is not installed.

pip install pyodbc

Installation on MacOSX & later

You can install python pyodbc on MacOSX operating system using the brew package manager. Use the below command to install python pyodbc on macOSX & later

brew install unixodbc

Installation on Linux Ubuntu

You can install python pyodbc on linux using pip package manager. Apart from pip you may require unixoodbc-dev and dev packages to support pyodbc.

sudo apt install python3-pip python3-dev unixodbc-dev
pip3 install --user pyodbc

Installation on Linux Debian (other than ubuntu)

Use the below code to install python pyodbc if you are using Linux debian distribution other than ubuntu like mint os.

apt-get update
apt-get install g++ unixodbc-dev
pip install --user pyodbc

Installation on Linux CentOS 7 and later

Use the below code to install python pyodbc on RHEL CenOS 7 machine.

sudo yum install epel-release
sudo yum install python3-pip gcc-c++ python3-devel unixODBC-devel
pip3 install --user pyodbc

Installation on Linux Fedora

Use the below code to install python pyodbc on fedora linux machine.

sudo dnf install redhat-rpm-config gcc-c++ python3-devel unixODBC-devel
pip3 install --user pyodb

Installation on Linux OpenSUSE

sudo zypper install gcc-c++ python3-devel python3-pip unixODBC-devel
pip3 install --user pyodbc

Installation on Amazon Linux

sudo yum install gcc-c++ python3-devel unixODBC-devel
# replace <release_num> with the current release
sudo ln -s /usr/libexec/gcc/x86_64-amazon-linux/<release_num>/cc1plus /usr/bin/
pip3 install --user pyodbc

Installation on Alpine Linux

apk add python3 python3-dev g++ unixodbc-dev
python3 -m ensurepip
pip3 install --user pyodbc

Read Connect Excel to Oracle Database

Approach-2 Using Python without cx_Oracle

In this section, we will learn how to connect to the Oracle database using Python without cx_Oracle.

• Python cx_Oracle is an important module, using which we can connect to the Oracle database using Python.
• Due to its ease of connection without any dependency, it is very popular and most preferable when it comes to connecting Oracle databases with Python.
• If you don’t want to connect to the Oracle database using the Python module cx_Oracle, you can use the Python product module.
• To learn more about Python Pyodbc, refer to our previous section, Connect to Oracle Database using Python Pyodbc.

Approach-3 Using Python SQLAlchemy

In this section, we have connected to the Oracle database using the Python SQLAlchemy module.

• SQLAlchemy module in Python allows the developer to use the full potential of SQL by writing queries in a Python editor.
• Use the code below to install the Python SQLAlchemy module on your system using pip or conda package manager.

# for pip users
pip install SQLAlchemy

# for conda users
conda install sqlalchemy

• Once the module is installed, go ahead and import it on your Python editor (vscode, jupyter, etc).
• Python SQLAlchemy provides a module create_engine() using which we can connect to Oracle database.

Source Code:

In this code, we demonstrated how to connect the Oracle database with the Python SQLAlchemy module. We established the connection and then passed a query.

# module
from sqlalchemy import create_engine
import cx_Oracle

# pre-requisits for connection
host='localhost'
port=1521
sid='orcl'
user='c##sqlserverguides'
pwd='root'

preq = cx_Oracle.makedsn(host, port, sid=sid)

# connection string
con_string = f'oracle://{user}:{pwd}@{preq}'

# using SQLAlchemy to create connection
engine = create_engine(
    con_string,
    convert_unicode=False,
    pool_recycle=10,
    pool_size=50,
    echo=True
)

# execute query 
query = 'select * from life_expectancy'
output = engine.execute(query)

# print record
for row in output:
    print(row)

Output:

In this output, we connect to the Oracle database using Python SQLAlchemy and then we fetched data using Python SQLAlchemy.

Read How to Check Oracle Database Version

Approach-4 Using Python in Linux

In this section, we will learn how to connect to the Oracle database using Python in Linux.

• Python provides two modules to connect to the Oracle database in Linux.
  • pyodbc
  • cx_Oracle
• Both modules can be installed using the pip package manager in Linux, and you can use one of them to connect to the Oracle database using Python in Linux.
• We are using the Python cx_Oracle module to connect to the Oracle database. The procedure is the same as in Windows.

pip install cx_Oracle

or 

python -m pip install cx_oracle --upgrade --user

• Once the Python cx_Oracle module is installed on the Linux operating system, it’s time to connect to the database.

import cx_Oracle 
connection = cx_Oracle.connect(
	user="c##sqlserverguides",
	password = "root"
	dsn = "localhost/orcl")

• Description of Syntax:
  • user: user is the username to which we will connect.
  • password: password of the user
  • dsn: dsn is divided into two parts, server and sid. In this case, the server is localhost and the SID is orcl.
• Once the connection is established, you are good to go to run other Oracle database queries.

Read How to Get List all Tables in Oracle Database

Approach-5 Using Python Pandas

In this section, we will learn how to connect to the Oracle database using Python pandas.

• Python pandas is an advanced library used for reading & analyzing the data from various sources like csv, excel, sql, etc.
• Using the Python cx_Oracle module, we can connect to Python pandas and display the data in a dataframe.
• In our example, we have connected to the Oracle database using Python pandas, and then we have executed a fetch query. All the information is displayed in a dataframe format.

Source Code:

In this source code, we have used the Python cx_Oracle module to establish a connection between Oracle and Python. We have also imported the pandas module, using which we have created a dataframe of the fetched information.

# import module
import cx_Oracle
import pandas as pd

# database info
host = 'localhost'
port = 1521
SID = 'orcl'

# pre-requisits for connection
preq = cx_Oracle.makedsn(host, port, SID)

# establish connection
connection = cx_Oracle.connect('c##sqlserverguides', 'root', preq)

# query to fecth all records
query = 'SELECT *  FROM life_expectancy'

# read sql using pondas
df = pd.read_sql(query, con=connection)

# print dataframe on jupyter notebook
df

# print dataframe on other editors
print(df)

Output:

In this output, four rows and four columns are created. The data displayed here as a dataframe is fetched from the Oracle database using Python pandas.

Read: Oracle Add Row Number

Example

In this section, we will see an example to connect to the Oracle database using Python.

• In our example, we have fetched data from a table ‘life_expectancy‘. This table has 4 rows and three columns.
• All the queries are written in Python and connection is established using the Python cx_Oracle module.
• The common user’s name is c##sqlserverguides, and we connected to a global database, ‘orcl’.

# module
import cx_Oracle

# establish connection
connection = cx_Oracle.connect(
    user="c##sqlserverguides", 
    password="root",
    dsn="localhost/orcl")

# create cursor
cursor = connection.cursor()
qry = 'select * from life_expectancy'
cursor.execute(qry)

# fetch all the rows 
res = cursor.fetchall()


# print each rows
for row in res:
    print(row)

Output:

This output displays countries, years, and life expectancy. Each country has a different life expectancy, and all this data is fetched from the Oracle database using Python.

Also. Take a look at some more Oracle tutorials.

• Oracle Drop Database

In this tutorial, we have learned how to connect to an oracle database using python.