Python - Unable to export sql result to Excel

Question

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

your question is too unclear. are you getting an exception? an error of some sort? do your prints not print what you expect? print(title) what is this? you don't have title defined anywhere — Nullman
– Nullman, Commented Mar 16, 2022 at 8:51
@Nullman print(title) is printing the column names . i am able to retrive row count and resultset of sql query. i forgot to rename title to columns,actually it is print(columns) — Teena
– Teena, Commented Mar 16, 2022 at 8:57

vovakirdan · Accepted Answer · 2022-03-16 10:16:09Z

1

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

edited Mar 16, 2022 at 10:16

answered Mar 16, 2022 at 9:16

vovakirdan

3653 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Nullman Over a year ago

writer.save() is outside the with statement

vovakirdan Over a year ago

Sorry, my fault

Teena Over a year ago

@vovakirdan, thanks for the quick response.Now the error has gone, but the result is written to single column in excel .Please see the screenshot .and there is no header and my date column is displaying in different way.

vovakirdan Over a year ago

I have updated answer. Try it

Teena Over a year ago

@vovakirdan,Hi im getting these errors File "AutomatedWorkPassExpiryReport.py", line 74, in <module> read(db_connect) File "AutomatedWorkPassExpiryReport.py", line 50, in read db_connect.row_factory = sq.Row NameError: name 'sq' is not defined Can i check sq.row is for sqllite3 module .ryt?

|

Collectives™ on Stack Overflow

Python - Unable to export sql result to Excel

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related