Python - Sql query to excel is showing as a single column value

Question

I'm exporting a SQL query result to excel in Python. I'm using pyodbc connection for establishing database connection.

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)

I'm able to export the query result to dataframe and then to excel, the issue I'm facing is the exported excel is displaying the result as a comma separated string in one column. What I need is a proper excel worksheet with columns with headers,

if totalrows > 0:
       try:
           df = pd.DataFrame(allrows)
           
           print(df)
           with pd.ExcelWriter(r'C:\Users\CSV\Staffdata.xlsx') as writer:
               df.to_excel(writer,
                           sheet_name='WorkPassExpiryReport',
                           header=True,index=False)
           
           
       except Exception as e:
           print("Could not write to Excel")
           print(f"Error: {str(e)}")

and the date is not showing correctly

Answer 1

I manage to fix the issue. Here is the change i have done.

       col_headers = [ i[0] for i in cursor.description ]           
       rows = [ list(i) for i in allrows]           
       df = pd.DataFrame(rows, columns=col_headers)

Now data is available in excel file as expected