Any optimize way to iterate excel and provide data into pd.read_sql() as a string one by one

Question

#here I have to apply the loop which can provide me the queries from excel for respective reports:
    df1 = pd.read_sql(SQLqueryB2, con=con1)
    df2 = pd.read_sql(ORCqueryC2, con=con2)
    if (df1.equals(df2)):
        print(Report2 +" : is Pass")

Can we achieve above by something doing like this (by iterating ndarray) df = pd.read_excel(path) for col, item in df.iteritems(): OR do the only option left to read the excel from "openpyxl" library and iterate row, columns and then provide the values. Hope I am clear with the question, if any doubt please comment me.

Answer 1

You are trying to loop through an excel file, run the 2 queries, see if they match and output the result, correct?

import pandas as pd
from sqlalchemy import create_engine

# add user, pass, database name
con = create_engine(f"mysql+pymysql://{USER}:{PWD}@{HOST}/{DB}")
file = pd.read_excel('excel_file.xlsx')

file['Result'] = ''  # placeholder
for i, row in file.iterrows():
    df1 = pd.read_sql(row['SQLQuery'], con)
    df2 = pd.read_sql(row['Oracle Queries'], con)
    file.loc[i, 'Result'] = 'Pass' if df1.equals(df2) else 'Fail'

file.to_excel('results.xlsx', index=False)

This will save a file named results.xlsx that mirrors the original data but adds a column named Result that will be Pass or Fail.

Example results.xlsx: