Apr-15-2023, 11:59 AM
Hi,
I am creating a code which would export data from multiple tables in oracle to one excel file having each sheet as one tbale export. I have created 2 files for the same. Executing same on Windows 11.
One is config file as below.
I am creating a code which would export data from multiple tables in oracle to one excel file having each sheet as one tbale export. I have created 2 files for the same. Executing same on Windows 11.
One is config file as below.
# Configuration file for Oracle EXtracts # Author: Mahesh Birajdar #################### Oracle-- ODS Oracle: dsn: localhost:1521/XEPDB1 username: SALES password: AdminAnd my python file is as below.
#!/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')But i am getting error as below.
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'