Writing to excel file from Mysql Database using python

Question

I want to fetch data for a specific period from a MySQL table and print it to an excel file. I am using the DateEntry widget to specify the periods. My code is written below but it is not working.

class PrintbyDate(Tk):
            def __init__(self):
                super().__init__()
                self.maxsize(400, 200)
                self.minsize(400, 200)
                self.iconbitmap(img_resource_path("chegutuicon.ico"))
                self.title("Export Report for a specific date range.")
                self.canvas = Canvas(width=1366, height=768, bg='gray')
                self.canvas.pack()
                a = StringVar()
                StartDate=DateEntry(self, date_pattern='YYYY-MM-DD')
                StartDate.place(x=200, y=50)
                Label(self, text='Select Start Date:', bg='gray', font=('Courier new', 10, 'bold')).place(x=70, y=50)
        
                EndDate=DateEntry(self, date_pattern='YYYY-MM-DD')
                EndDate.place(x=200, y=100)
                Label(self, text='Select Start Date:', bg='gray', font=('Courier new', 10, 'bold')).place(x=70, y=100)
                            
                
                def ent():
                    con=connect(user="ngonex",passwd="2007Ngonidzashe",host="localhost",database="complains_database"       
         
                    
                    )
                    # Read the data
                  
                    df=sql.read_sql('select * from client WHERE DateReported is BETWEEN StartDate.get_date() AND EndDate.get_date()', con)
                    print(df)
                    
                    #Export the data into excel sheet
                    df.to_excel('complains_report.xlsx')
                    messagebox.showinfo("Successful", "Report generated successfully check output folder!")
        
                Button(self, text='Print', width=15, font=('arial', 10),command =ent ).place(x=70, y = 130)

Paweł Szmajda · Accepted Answer · 2022-10-04 09:26:16Z

1

The easiest way would be to use pandas.read_sql() to import data into a dataframe, then save that dataframe to Excel with pandas.DataFrame.to_excel().

https://pandas.pydata.org/docs/reference/api/pandas.read_sql.html

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html

Should be just a couple of lines.

answered Oct 4, 2022 at 9:26

Paweł Szmajda

1811 silver badge4 bronze badges

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

1 Comment

Ngonidzashe Over a year ago

Thank you for the reply. I tried what you said, if I am importing everything in the Mysql database to the dataframe and then write to excel with pandas it works but my issue is on importing records for a specific period. The user should select the period using DateEntry widget. How can i do that. I am kindly asking you to demonstrate for me with the code since you said its just a couple of lines. Thanks so much in advance

Collectives™ on Stack Overflow

Writing to excel file from Mysql Database using python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related