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.
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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.