如何插入新列并将其导出到Python中的CSV？

Question

我有一个按钮将数据从MySQL导出到CSV文件中。获取的数据可能具有单行或多行。我想在每行数据的左列上添加一个新列，称为项目NO（标头），并且根据行数增加了值。如果有3行，则第一行将被标记为1，然后将其标记为2和3。我的代码执行SQL查询以获取数据并将其填充在列表中。然后将值传递到另一个函数以生成和导出CSV文件。谁能建议执行此操作的最佳方法？谢谢。

当前输出： 所需的输出：

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) 

Answer 1

不确定最好的方法。但是我建议您这样做：

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

Answer 2

import pandas as pd
df=pd.read_csv(yourfile,delimiter="delimiterthatyouhave")
df.insert(0, "Item no", [1,2])
df.to_csv(yourpath, index=False)

您也可以根据DF的长度生成列表。

Answer 3

您应该远离键盘，并考虑要做什么。您在数据库中有一些数据，并有一次将其返回一行的工具。您需要将其导出在CSV文件（带有标题行）中，并添加一个行号作为第一个字段。

只需一次通过：

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(),))

        with open('Items by Company.csv','w',newline='') as f:
            w = csv.writer(f)
            w.writerow(['Item NO', 'ID', 'Type', 'Project', 'Name', 'Location'])

            for i, row in enumerate(mycursor, 1):
                w.writerow([i] + list(row))
          
        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)