# Configuration file for Oracle EXtracts
# Author: Mahesh Birajdar

#################### Oracle-- ODS
Oracle:

  dsn: localhost:1521/XEPDB1
  username: SALES
  password: Admin

#!/usr/bin/env python
import cx_Oracle
import pandas as pd
import yaml 
import xlsxwriter

# Load config.yaml file
with open("config.yaml", "r") as f:
    config = yaml.safe_load(f)

# Get Oracle connection details
OracleuserID = config["Oracle"]["username"]
Oraclepassword = config["Oracle"]["password"]
Oracledsn = config["Oracle"]["dsn"]


def printheader():
    print('\n################### Oracle Extracts Automation Framework #########################')

def Oracle_connection(username,password,dsn):
    global conn
    global cur
    print('\n####################### Establishing Connections to Oracle DB ##############################')
    try:
        conn = cx_Oracle.connect(user=OracleuserID, password=Oraclepassword,
                               dsn=Oracledsn,
                               encoding="UTF-8")
        print('\nConnected to Oracle DB')
        cur=conn.cursor()

    except Exception as err:
        # logger.error('Error while connecting to Oracle {}'.format(err))
        print('\nError while connecting to Oracle')
        print(err)

printheader()

# Set the output file path
output_path = 'D:\Automation\Table_Export.xlsx'

# Create the connection to the Oracle database
Oracle_connection(OracleuserID,Oraclepassword,Oracledsn)

# Define the list of tables to retrieve data from
tables = ['OFFICER', 'CUSTOMER', 'PRODUCT']

# Export data from each table to a separate sheet in an Excel file
with pd.ExcelWriter(output_path) as writer:
    #for table in tables:
        # Define the SQL query to retrieve data from the table
        query = f"SELECT * FROM OFFICER ORDER BY INS_DTTM"

        # Use pandas to read the data from the Oracle database into a DataFrame
        dataframe = pd.read_sql(query, Oracle_connection)

        # Write the DataFrame to a new sheet in the Excel file
        dataframe.to_excel(writer, sheet_name=table, index=False)

# Close the database connection
print('\n####################### Closing Connections to Oracle DB ##############################')
cur.close()
conn.close()
print('\nDisconnected from Oracle DB')

Error:
PS D:\Automation> python .\Oracle_Extracts.py
Traceback (most recent call last):
  File "D:\Automation\Oracle_Extracts.py", line 6, in <module>
    from sqlalchemy import create_engine
ModuleNotFoundError: No module named 'sqlalchemy'
PS D:\Automation> python .\Oracle_Extracts.py

################### Oracle Extracts Automation Framework #########################

####################### Establishing Connections to Oracle DB ##############################

Connected to Oracle DB
D:\Automation\Oracle_Extracts.py:57: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  dataframe = pd.read_sql(query, Oracle_connection)
Traceback (most recent call last):
  File "D:\Automation\Oracle_Extracts.py", line 57, in <module>
    dataframe = pd.read_sql(query, Oracle_connection)
  File "C:\Users\Mahes\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py", line 633, in read_sql
    return pandas_sql.read_query(
  File "C:\Users\Mahes\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py", line 2264, in read_query
    cursor = self.execute(sql, params)
  File "C:\Users\Mahes\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\sql.py", line 2198, in execute
    cur = self.con.cursor()
AttributeError: 'function' object has no attribute 'cursor'