I'm creating two DataFrames; one from an Oracle SQL query, one from a SSMS query and merging them together with join specification.
It is returning: KeyError: "None of [Index(['BI.BookingItemID'], dtype='object')] are in the [columns]"
Any help is appreciated.
Here is the code with identifiers masked:
#Define Save Location
fileName = 'C:\\Users\Me\OneDrive - ' + timeStr + '.xlsx'
#Import Password
with open("C:\\Users\\Me\\OneDrive Password","r") as pwr:
pw = pwr.read()
print("Connecting to Oracle")
#Connecting - Gathering Data
Oconnection = cx_Oracle.connect("Me", pw, "Oracle Path/ORAPRD", encoding = "UTF-8", nencoding = "UTF-8")
print("Oracle Connected!")
#Defining Query
query1 = open("C:\\Users\Me\Oracle Query [py].sql","r")
print("Running Oracle Query")
#Creating Dataframe/Executing Query
Odata = pd.read_sql(query1.read(), Oconnection)
print("Creating OData Data Frame")
#Closing Connection
Oconnection.close()
query1.close()
print("Closing Oracle Connection")
#Connecting to SSMS
SSMSconnection = ("Driver={SQL Server Native Client 11.0};"
"Server=SSMS;"
"Database=DatabaseQuery;"
"Trusted_Connection=yes;")
cnxn = pyodbc.connect(SSMSconnection)
print("Setting Up SSMS Connection")
query2 = open("C:\\Users\\Me\\SQL Query [py].sql","r")
print("Running SSMS Query")
SSMSData = pd.read_sql(query2.read(), cnxn)
print("Creating SSMS Dataframe")
del cnxn
print("Closing SSMS SQL Connection")
#Merging Data Frames
merge = pd.merge(Odata,
SSMSData[['BI.BookingItemID']],
left_on = 'BI.BookingItemID',
right_on = 'CTLA.ATTRIBUTE12',
how = 'left')
writer = pd.ExcelWriter(fileName,engine='xlsxwriter')
merge.to_excel(writer,index=False,sheet_name="Export")
print("Joining Odata & SSMS Data Frames")
writer.save()
print("Saving Merged Data Frame")