Python - Unable to export sql result to Excel-CodePudding

I want to export sql query result to excel file using Python. I queried the DB and able to retrieve the result set.Currently what im facing is like.Not able write the query result to Excel, Here is my Code,

def read(db_connect):
   print("Read")
   cursor = db_connect.cursor()
   Expirydate =  date.today()   timedelta(days=90)
   print(Expirydate)
   sql_query = cursor.execute("Select StaffDetails.TypeOfEmployee, StaffDetails.EmploymentType, StaffDetails.EmploymentCategory, StaffDetails.PreferredEmpFname, StaffDetails.PreferredEmpLname, StaffDetails.Location, StaffDetails.Department, StaffDetails.Section, StaffDetails.JobTitle, StaffDetails.ContractorAgencyName, StaffDetails.SupervisorName, StaffDetails.SupervisorEmail, StaffBiodata.WorkpassType, StaffBiodata.WorkpassExpiryDate from StaffDetails INNER JOIN StaffBiodata ON StaffDetails.StaffID =StaffBiodata.StaffID WHERE Department = 'Operations' AND WorkpassExpiryDate < '%s'" % Expirydate)
   allrows = sql_query.fetchall()
   for row in allrows:
      print(f'row = {row}')
      print()
   totalrows = len(allrows)
   print("Total Rows : %d" % totalrows)
   if totalrows > 0:
       try:
           columns = [i[0] for i in cursor.description]
           
           df = pd.DataFrame(allrows)
           writer = pd.ExcelWriter(r'C:\Users\CSV\Staffdata.xlsx')
           df.to_excel(writer, sheet_name='WorkPassExpiryReport',header=True,index=False)
           writer.save()
           print(title)
           
       except:
           print("Could not write to Excel")

When im running this code,its skipping to the except block.Is there anyway to figure out the issue?

---------------------EDIT---------------------

----------------EDIT 2-------------------

Thanks, Teena

CodePudding user response：

import sqlite3 as sq
db_connect.row_factory = sq.Row # this changes  output
if totalrows > 0:
       try:
           #columns = [i[0] for i in cursor.description] i think here is an error
           #it would be better if you write columns manually
           df = pd.DataFrame(allrows, columns = sql_query.keys())
           with pd.ExcelWriter(r'C:\Users\CSV\Staffdata.xlsx') as writer:
               df.to_excel(writer, 
                           sheet_name='WorkPassExpiryReport',index=False)
           print(columns)
           
       except Exception as e:
           print("Could not write to Excel")
           print(f"Error: {str(e)}")

Try it and send screenshot