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:UsersCSVStaffdata.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 2——————-
Thanks, Teena
Advertisement
Answer
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:UsersCSVStaffdata.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