Search code examples
pythonexport-to-csvmysql-python

How to insert a new column and export it to CSV in Python?

I have a button that exports data from MySQL into a CSV file. The fetched data may have single or multiple rows. I would like to add a new Column on the left most column called Item No(Header) for each row of data, and it's value increases based on the number of rows. If there are 3 rows, then the first row would be labelled as 1 and subsequently 2 and 3. My code performs a SQL query to fetch the data and populate it in a list. Then pass the values to another function to generate and export the CSV file. Could anyone suggest the best way to perform this? Thank you.

Current Output: current output Desired Output: desired output

def Company_Excel(asset_Box, ID_Box, newid_Entry, temp_Entry, gps_Entry, current_Entry, PID_Box, projectid_Entry, projectname_Entry, projectpic_Entry, piccontact_Entry, plocation_Entry, name_Entry, company_Entry, email_Entry, contact_Entry, issue_Entry, return_Entry, status_Entry, remarks_Entry, tree, company_Box):

    try:  
        company = company_Box.get()  
        if (company_Box.get() != "Please select one"):
            
            ncommand = ("SELECT `ID`, `Type`, `Project`, `Name`, `Location` from `History` WHERE `Company` = %s ORDER BY `Transaction ID`")
            mycursor.execute(ncommand,(company_Box.get(),))
            nresult = mycursor.fetchall()

            array6 = []
            for e in range(len(nresult)):
                nresult[e] = list(nresult[e])
                array6.append(nresult[e])
                
            write_to_csv_company(array6)
            
            if os.path.exists("Company.csv"):
              os.remove("Company.csv")
            else:
              print("The file does not exist")
              
            tkinter.messagebox.showinfo("Exported", "Success!")

        elif (company_Box.get() == "Please select one"):
            valueErrorMessage = "Please select one from the drop down menu"
            tkinter.messagebox.showerror("Error", valueErrorMessage)
            
        else:
            valueErrorMessage = "Not found, please try again"
            tkinter.messagebox.showerror("Error", valueErrorMessage)
        
    except PermissionError:
            valueErrorMessage = "Please close all the excel files before exporting"
            tkinter.messagebox.showerror("Error", valueErrorMessage)
            
def write_to_csv_company(result):

    with open('Company.csv', 'w+', newline='') as f:
        w = csv.writer(f, dialect='excel')
        for record in result:
            w.writerow(record)
            
    with open('Company.csv',newline='') as f:
        r = csv.reader(f)
        data = [line for line in r]
    
    with open('Items by Company.csv','w',newline='') as f:
        w = csv.writer(f)
        w.writerow(['ID', 'Type', 'Project', 'Name', 'Location'])
        w.writerows(data)
Solution

  • Not sure about the best way. But I suggest you can do it like this:

    ...
array6 = []
for e in range(len(result)):
    array6.append([e+1] + list(nresult[e]))
...
w.writerow(['Item NO', 'ID', 'Type', 'Project', 'Name', 'Location'])
...