Python Script to Connect Oracle Database

Python is a modern programming language used in various domains like web development, automation, and data analysis. One of Python’s powerful features is the ability to connect to databases. This ability makes it a useful tool for data-driven applications. In this article, you will understand how to connect Oracle database using Python Script, execute queries, and demonstrate a real-world use case. You will also learn a practical, real-life example to demonstrate its use.

Many enterprise-level applications like Oracle EBS are known for handling large volumes of data. Oracle databases are widely used in those enterprise-level applications. However, directly interacting with an Oracle database through traditional methods can be cumbersome. Python simplifies this process by providing libraries like SQLAlchemy, oracledv(previously cx_Oracle), which allow developers to easily connect to and manipulate Oracle databases. You can understand easily how you can connect Oracle Database (Oracle EBS) using OracleDB library.

Python ccript to connect oracle database Using SQLAlchemy Engine – Process Flow

SQLAlchemy is a powerful Python library for working with relational databases using an ORM (Object-Relational Mapper) or raw SQL queries. Instead of relying on cx_Oracle, we can use SQLAlchemy’s engine along with the oracledb to establish a connection.

Prerequisites

1. Python Installed: Download from python.org
2. Oracle Database Access: Ensure you have a valid username, password, and database connection details.
3. Install Required Libraries:
   pip install sqlalchemy pandas oracledb
4. Oracle Instant Client: OracleDB(cx_Oracle) requires the Oracle Instant Client to interact with Oracle databases. Download from Oracle’s website. Extract the Oracle Instant Client and note the location. You need to use the location to connect Oracle Database using oracledb library.

Connecting to Oracle Using SQLAlchemy Engine Example

Let’s connect to Oracle e-Business Suite (Oracle Apps) database using oracledb and SQLAlchemy. You will understand how to connect the Oracle database and execute query. You will also understand how to show the query output in a beautiful Pandas datafarme. In this example you will prepare a query to fetch General Leader Import program details and display the result. In this way you can automate important Oracle Apps business process. By connecting Oracle database from Python you can monitoring critical concurrent programs and concurrent managers.

Python script to connect Oracle database: Establishing the Connection

Let’s write a Python script to connect to an Oracle database. Here, we will use a simple real-world example where a Python application connects to a customer database to fetch customer details.

Script Overview:

1. Import necessary libraries(oracledb, SQLAlchemy, Pandas)
2. Define Oracle database access details(user, password, host, port, service)
3. Construct the Connection String
4. Define SQL Query
5. Initialize SQLAlchemy Engine with oracledb
6. Establish a connection to the Oracle database.
7. Execute an SQL query to retrieve data.
8. Handle exceptions.
9. Close the connection after use.

import oracledb
from sqlalchemy import create_engine
import pandas as pd
# Define database access details
username = "p_username"
password = "p_password"
p_host = "oracle_db_host"
p_port = "port_number"
p_service_name = "Oracle_service_name"
# SQL query to fetch Concurrent Program Details
query="""SELECT fcpt.user_concurrent_program_name,
       fcp.concurrent_program_name,
       fat.application_name,
       fet.executable_name,
       fate.application_name exe_application,
       flv.meaning           execution_method,
       fet.execution_file_name,
       fcp.enable_trace
  FROM fnd_concurrent_programs_vl fcpt,
       fnd_concurrent_programs    fcp,
       fnd_application_vl         fat,
       fnd_executables            fet,
       fnd_application_vl         fate,
       fnd_lookup_values_vl       flv
 WHERE fcpt.concurrent_program_id = fcp.concurrent_program_id
   AND fcpt.application_id = fcp.application_id
   AND fcp.application_id = fat.application_id
   AND fcpt.application_id = fat.application_id
   AND fcp.executable_id = fet.executable_id
   AND fcp.executable_application_id = fet.application_id
   AND fet.application_id = fate.application_id
   AND flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
   AND flv.lookup_code = fet.execution_method_code
   AND fcpt.user_concurrent_program_name='Active Users'
"""
# Define database connection parameters
connstr = f"{p_host}:{p_port}/{p_service_name}"
client=  r'C:\instantclient_23_6'
#
thick_mode = {"lib_dir":client}
conp = oracledb.ConnectParams()
conp.parse_connect_string(connstr)
#
# Create an SQLAlchemy engine using oracleDB
engine = create_engine(f'oracle+oracledb://{username}:{password}@{conp.host}:{conp.port}/?service_name={conp.service_name}', thick_mode=thick_mode)
try:
    # Test the connection
    with engine.connect() as conn:
        print("Successfully connected to the Oracle database!")
        # Read data into a Pandas DataFrame
        df = pd.read_sql(query, conn)
        # Display results
        print("\nDetail of the General Ledger Program")
        display(df)
except Exception as e:
    print(f"Error: {e}")
finally:
    engine.dispose()  # Close the engine
    print("Connection closed.")

Understand the Python Script to Connect Oracle Database

1. Import Necessary Modules

• oracledb: Used for connecting to Oracle databases.
• sqlalchemy.create_engine: Helps in creating a database connection using SQLAlchemy.
• pandas: Used for handling and processing tabular data.

2. Define Database Connection Details

username = "p_username"
password = "p_password"
p_host = "oracle_db_host"
p_port = "port_number"
p_service_name = "Oracle_service_name"

• These variables store login credentials and database details.

3. Define SQL Query

query="""SELECT fcpt.user_concurrent_program_name,
       fcp.concurrent_program_name,
       fat.application_name,
       fet.executable_name,
       fate.application_name exe_application,
       flv.meaning           execution_method,
       fet.execution_file_name,
       fcp.enable_trace
  FROM fnd_concurrent_programs_vl fcpt,
       fnd_concurrent_programs    fcp, 
       fnd_application_vl         fat,
       fnd_executables            fet,
       fnd_application_vl         fate,
       fnd_lookup_values_vl       flv
 WHERE fcpt.concurrent_program_id = fcp.concurrent_program_id 
   AND fcpt.application_id = fcp.application_id
   AND fcp.application_id = fat.application_id
   AND fcpt.application_id = fat.application_id 
   AND fcp.executable_id = fet.executable_id
   AND fcp.executable_application_id = fet.application_id
   AND fet.application_id = fate.application_id 
   AND flv.lookup_type = 'CP_EXECUTION_METHOD_CODE'
   AND flv.lookup_code = fet.execution_method_code
   AND fcpt.user_concurrent_program_name = 'Journal Import'
"""

• This SQL query retrieves details of a Concurrent Program from Oracle EBS.
• It joins multiple tables to fetch program names, executable details, execution methods, and trace enablement status.

4. Establish Connection Parameters

connstr = f"{p_host}:{p_port}/{p_service_name}"
client=  r'C:\instantclient_23_6'
thick_mode = {"lib_dir":client}
conp = oracledb.ConnectParams()
conp.parse_connect_string(connstr)

• connstr: Constructs the Oracle database connection string.
• client: Defines the Oracle Instant Client directory (needed for Thick mode).
• thik_mode: Specifies the Oracle client library location.
• oracledb.ConnectParams(): Parses and organizes the connection parameters.

5. Create SQLAlchemy Engine

engine = create_engine(f'oracle+oracledb://{username}:{password}@{conp.host}:{conp.port}/?service_name={conp.service_name}', thick_mode=thick_mode)

• This initializes the SQLAlchemy engine to interact with the Oracle database using OracleDB’s thick client mode.

6. Execute Query and Fetch Data

try:    # Test the connection
    with engine.connect() as conn:
        print("Successfully connected to the Oracle database!")
        # Read data into a Pandas DataFrame
        df = pd.read_sql(query, conn)
        # Display results
        print("\nDetail of the General Ledger Program")
        display(df)
except Exception as e:
    print(f"Error: {e}")
finally:
    engine.dispose()  # Close the engine
    print("Connection closed.")

• Try Block:
  • Establishes a database connection and executes the SQL query.
  • Fetches the result into a Pandas DataFrame.
  • Displays the retrieved data.
• Except Block:
  • Catches and prints any errors that occur during execution.
• Finally Block:
  • Ensures that the database connection is closed properly.

Key Highlights(Python Script to Connect Oracle Database)

🔌 Uses OracleDB for connectivity.
⚡ Implements SQLAlchemy for easy database interaction.
📊 Fetches Concurrent Program Details from Oracle EBS.
📉 Uses Pandas for data handling and visualization.
🛡️ Ensures error handling and proper connection disposal.

Conclusion: Python Script to Connect Oracle Database

Python’s integration with Oracle databases is a valuable tool for developers working with enterprise-level applications. The oracledb library simplifies connecting, querying, and manipulating Oracle databases. The example presented in this article demonstrates how Python can interact with an Oracle database to fetch data from Oracle Apps R12.

This real-life use case of connecting Python to Oracle databases is just one of many applications. Whether you’re managing concurrent programs information, performing automated data analysis, or integrating Python with business-critical systems, Python provides a powerful solution for database interaction.

By mastering Python and Oracle integration, you can enhance your workflow, improve productivity, and create scalable, efficient database-driven applications.

FAQs