Connect to Oracle Database using Python

In this oracle tutorial, we will learn how to connect to an oracle database using python. Also, we will cover these topics.

• Connect to Oracle Database using Python
• Connect to Oracle Database using Python Pyodbc
• Connect to Oracle Database using Python without cx_Oracle
• Connect to Oracle Database using Python SQLAlchemy
• Connect to Oracle Database using Python in Linux
• Connect to Oracle Database using Python Pandas
• Connect to Oracle Database using Python Example

Connect to Oracle Database using Python

In this section, we will learn how to connect to oracle database using pyython.

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

Read How to create a database in Oracle 19c

Connect to Oracle Database using Python Pyodbc

In this section, we will learn how to connect to oracle database using python pyodbc module.

• Python Pyodbc module is fully dedicated to oracle database and using this module we can execute all the queries of 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.
• Incase you are unaware about the driver then using the below code you display all the drivers present in your system. we did lots of research on this & we highly recommend you to 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, 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 has been displayed in the vscode terminal. The records has 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

Connect to Oracle Database using Python without cx_Oracle

In this section, we will learn how to connect to oracle database using python without cx_Oracle.

• Python cx_Oracle is an important module using which we can connect to oracle database using python.
• Due to its property of easy to connect without any dependency it very popular & most preferable when it comes to connect oracle database with python.
• In case you don’t want to connect to oracle database using python module cx_Oracle. In that case you can use python pyodbc module.
• To learn more about python pyodbc refer to our previous section Connect to Oracle Database using Python Pyodbc.

Connect to Oracle Database using Python SQLAlchemy

In this section, we have connected to oracle database using python SQLAlchemy module.

• SQLAlchemy module in python allows developer to use full potential of SQL by writing queries on python editor.
• Use the below code to install 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 modue is install go ahead and import it on your python editor (vscode, jupyter, etc).
• Python SQLAlchemy provides a module create_engine() using which wen can connect to oracle database.

Source Code:

In this code, we demonstrated how to connect oracle database with python SQLAlchemy module. We have 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 oracle database using python SQLAlchemy and then we fetched data using python SQLAlchemy.

Read How to Check Oracle Database Version

Connect to Oracle Database using Python in Linux

In this section, we will learn how to connect to oracle database using python in Linux.

• Python provides two modules using which we can connect to oracle database in linux.
  • pyodbc
  • cx_Oracle
• Both the modules can be installed using pip package manager in linux and you can use one of them to connect to oracle database using python in linux.
• We are using python cx_Oracle module to connect with the oracle database. The procedure is same as in windows.

pip install cx_Oracle

or 

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

• Once 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 are going to connect with.
  • password: password of the user
  • dsn: dsn is divided into two parts, server and sid. In this case server is localhost and sid is orcl.
• Once the connection established you are good to go to run other oracle database queries.

Read How to Get List all Tables in Oracle Database

Connect to Oracle Database using Python Pandas

In this section, we will learn how to connect to oracle database using python pandas.

• Python pandas is an advance library used for reading & analyzing the data from various sources like csv, excel, sql, etc.
• Using python cx_Oracle module we can connect to python pandas and display the data in a dataframe.
• In our example, we have connected to 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 python cx_Oracle module to establish connection between oracle and python. We have also imported 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 4 columns are created. the data displayed here in the form of dataframe is fetched from the oracle database using python pandas.

Read: Oracle Add Row Number

Connect to Oracle Database using Python Example

In this section, we will see an example to connect to oracle database using python.

• In our example, we have fetched a data from a table ‘life_expectancy‘. This table has 4 rows and 3 columns.
• All the queries are written in python and connection is established using python cx_Oracle module.
• Name of the common user 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:

In this output, countries, year and life expectancy is displayed. Each country has different life expectancy and all this data is fetched from 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. Also, we have covered these topics.

• Connect to Oracle Database using Python
• Connect to Oracle Database using Python Pyodbc
• Connect to Oracle Database using Python without cx_Oracle
• Connect to Oracle Database using Python SQLAlchemy
• Connect to Oracle Database using Python in Linux
• Connect to Oracle Database using Python Pandas
• Connect to Oracle Database using Python Example