---
title: "Connecting Python to Oracle Database: 4 Neat Approaches"
description: "Provides four methods (cx_Oracle, pyodbc, pyoracle, sqlalchemy) to establish Python to Oracle database connectivity, enabling data analysis."
url: https://srinimf.com/2023/11/08/connecting-python-to-oracle-database-4-neat-approaches/
date: 2023-11-08
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2023/11/pexels-photo-262488.jpeg?fit=1733%2C1300&ssl=1
categories: ["Python"]
tags: ["Python to Oracle Connectivity"]
type: post
lang: en
---

# Connecting Python to Oracle Database: 4 Neat Approaches

Provides a detailed explanation of four approaches [to connect](https://srinimf.com/2017/02/11/how-to-connect-oracle-from-unix/) Python to Oracle for data analytics. Each approach is explained with code examples and instructions

Importance of database connectivity? Let me put it this way:

Python needs data for analysis. If the data is present in the database, you need a connection between Python and the database. In this case, [it is Oracle](https://srinimf.com/2022/09/17/5-top-oracle-regexp-functions-and-their-usage/).

Here’re the approaches:

- cx_Oracle
- pyodbc
- pyoracle
- sqlalchemy -> create_engine

![Python to Oracle](https://i0.wp.com/srinimf.com/wp-content/uploads/2023/11/pexels-photo-2967810.jpeg?resize=1880%2C1253&ssl=1)*Photo by Content Pixie on Pexels.com*

Table of contents

1. [Python to Oracle: cx_Oracle](https://srinimf.com/2023/11/08/connecting-python-to-oracle-database-4-neat-approaches/#python-to-oracle-cx-oracle)
2. [Python to Oracle: pyodbc](https://srinimf.com/2023/11/08/connecting-python-to-oracle-database-4-neat-approaches/#python-to-oracle-pyodbc)
3. [Python to Oracle: pyoracle](https://srinimf.com/2023/11/08/connecting-python-to-oracle-database-4-neat-approaches/#python-to-oracle-pyoracle)
4. [Python to Oracle: sqlalchemy -> create_engine](https://srinimf.com/2023/11/08/connecting-python-to-oracle-database-4-neat-approaches/#python-to-oracle-sqlalchemy-create-engine)
5. [Conclusion](https://srinimf.com/2023/11/08/connecting-python-to-oracle-database-4-neat-approaches/#conclusion)

## Python to Oracle: cx_Oracle

```
import cx_Oracle

# Connect to the Oracle database
dsn = cx_Oracle.makedsn('hostname', 'port', service_name='service_name')
connection = cx_Oracle.connect(user='username', password='password', dsn=dsn)
cursor = connection.cursor()

# Execute a query to fetch the table names
cursor.execute("SELECT table_name FROM all_tables")

# Fetch all tables
tables = cursor.fetchall()

# Read data from each table
for table in tables:
    table_name = table[0]
    cursor.execute(f"SELECT * FROM {table_name}")
    
    # Fetch data from the table
    data = cursor.fetchall()
    
    # Do something with the data
    for row in data:
        # Process each row of data

# Close the cursor and the database connection
cursor.close()
connection.close()
```

Related[Teradata- Tutorial Downloads with User Guides](https://srinimf.com/2014/02/19/terradata-tutorial/)

Replace ‘hostname’, ‘port’, ‘service_name’, ‘username’, and ‘password’ with your actual database connection details. The code connects to the database, retrieves a list of table names, and then fetches the data from each table. You can add your own logic to process or [manipulate the data](https://srinimf.com/2023/10/10/how-to-write-user-defined-iterator-best-python-example/) as needed. Finally, the cursor and connection are closed to release the resources.

Note that you may need to install the Oracle Instant Client and set up environment variables for cx_Oracle to work properly. Please refer to the cx_Oracle documentation for detailed installation instructions based on your operating system.

## Python to Oracle: pyodbc

```
import pyodbc

# Connect to the Oracle database
dsn = 'Driver={Oracle ODBC Driver};DBQ=hostname:port/service_name;'
connection = pyodbc.connect('your_connection_string')

# Create a cursor object to execute SQL statements
cursor = connection.cursor()

# Execute a query to fetch the table names
cursor.execute("SELECT table_name FROM all_tables")

# Fetch all tables
tables = cursor.fetchall()

# Read data from each table
for table in tables:
    table_name = table[0]
    cursor.execute(f"SELECT * FROM {table_name}")
    
    # Fetch data from the table
    data = cursor.fetchall()
    
    # Do something with the data
    for row in data:
        # Process each row of data

# Close the cursor and the database connection
cursor.close()
connection.close()
```

Replace ‘hostname’, ‘port’, ‘service_name’, and ‘your_connection_string’ with your actual database connection details. The code connects to the database, retrieves a list of table names, and then fetches the data from each table. You can add your own logic to process or manipulate the data as needed. Finally, the cursor and connection are closed to release the resources.

Please note that you may need to set up the appropriate ODBC driver and configure the connection string according to your specific Oracle database setup.

## Python to Oracle: pyoracle

```
import pyoracle

# Connect to the Oracle database
connection = pyoracle.connect(
    user='username',
    password='password',
    host='hostname',
    port='port',
    database='service_name'
)

# Create a cursor object to execute SQL statements
cursor = connection.cursor()

# Execute a query to fetch the table names
cursor.execute("SELECT table_name FROM all_tables")

# Fetch all tables
tables = cursor.fetchall()

# Read data from each table
for table in tables:
    table_name = table[0]
    cursor.execute(f"SELECT * FROM {table_name}")
    
    # Fetch data from the table
    data = cursor.fetchall()
    
    # Do something with the data
    for row in data:
        # Process each row of data

# Close the cursor and the database connection
cursor.close()
connection.close()
```

Replace ‘username’, ‘password’, ‘hostname’, ‘port’, and ‘service_name’ with your actual database connection details. The code connects to the database, retrieves a list of table names, and then fetches the data from each table. You can add your own logic to process or manipulate the data as needed. Finally, the cursor and connection are closed to release the resources.

Please note that you need to have the pyoracle library installed. You can install it by running pip install pyoracle.

## Python to Oracle: sqlalchemy -> create_engine

```
from sqlalchemy import create_engine

# Connect to the Oracle database
dsn = "oracle+cx_oracle://username:password@hostname:port/service_name"
engine = create_engine(dsn)

# Execute a query to fetch the table names
tables = engine.execute("SELECT table_name FROM all_tables")

# Read data from each table
for table in tables:
    table_name = table[0]
    result = engine.execute(f"SELECT * FROM {table_name}")
    
    # Fetch data from the table
    data = result.fetchall()
    
    # Do something with the data
    for row in data:
        # Process each row of data

# Close the database connection
engine.dispose()
```

Replace `"username"`, `"password"`, `"hostname"`, `"port"`, and `"service_name"` with your actual database connection details. The code connects to the database, retrieves a list of table names, and then fetches the data from each table. You can add your own logic to process or manipulate the data as needed. Finally, the database connection is closed to release the resources.

Please note that you need to have the `cx_Oracle` library installed in order to use the Oracle dialect of `sqlalchemy`. You can install it by running `pip install cx-Oracle`.

## Conclusion

It explains the common methods used for connecting to an Oracle database. It aims to help users understand these methods and approaches.

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2023%2F11%2F08%2Fconnecting-python-to-oracle-database-4-neat-approaches%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2023%2F11%2Fpexels-photo-262488.jpeg%3Ffit%3D1200%252C900%26ssl%3D1&description=Connecting%20Python%20to%20Oracle%20Database%3A%204%20Neat%20Approaches)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2023/11/08/connecting-python-to-oracle-database-4-neat-approaches/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2023/11/08/connecting-python-to-oracle-database-4-neat-approaches/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2023/11/08/connecting-python-to-oracle-database-4-neat-approaches/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2023/11/08/connecting-python-to-oracle-database-4-neat-approaches/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Connecting%20Python%20to%20Oracle%20Database%3A%204%20Neat%20Approaches&body=https%3A%2F%2Fsrinimf.com%2F2023%2F11%2F08%2Fconnecting-python-to-oracle-database-4-neat-approaches%2F&share=email)
-

### Like this:

Like Loading…