'Adding timestamp index column with a column header to existing csv (python)

#gets rid of spaces in existing csv headers
def getColumns(readCSV):    
    return [column.replace(' ','') for column in next(readCSV)]

#insert format used to avoid hard-coded headers in script
def insertData(tableName, columns, readCSV):
    print("Inserting Data")
    query = 'INSERT INTO {}({}) VALUES ({})'.format(
            tableName,
            ','.join(columns),
            ','.join('?' * len(columns))
            )
    for data in readCSV:
        cursor.execute(query, data)
    con.commit()

def updateTable(csvPath, tableName):
    print("Updating table...")
    print("Reading file contents and uploading into db table...")

    ## insert timestamp column into existing csv. Does not incorporate header correctly for timestamp
    rows = []
    with open(csvPath, 'r', newline='') as csvFile:
        readCSV = csv.reader(csvFile, delimiter=',')
        for row in readCSV:
            rows.append(row)
        with open(csvPath, 'w', newline='')as writeFile:
            file_write = csv.writer(writeFile)
            for val in rows:
                timestamp = datetime.now()
                val.insert(0, timestamp)
                file_write.writerow(val)
                
    with open(csvPath) as csvFile:
        readCSV = csv.reader(csvFile, delimiter=',')
        columns = getColumns(readCSV)
        insertData(tableName, columns, readCSV)
    print ("Upload complete")

Above is a snippet of the code I'm working on. I am gathering data from a csv to insert into a SQL database. Currently, the csv does not have a timestamp column and without that, the import wont work as it sees duplicate data. I found a solution at https://www.geeksforgeeks.org/how-to-add-timestamp-to-csv-file-in-python/ for adding a timestamp column and have incorporated it into the code, but it does not add a header for the column. I'm sure it's an easy fix, but I am new to python and cant find the solution anywhere else. Also, if you see something inefficient with the coding in updateTable, let me know so that I can recognize it and learn a better way.



Solution 1:[1]

I ended up using a different method with import pandas as pd

def updateTable(csvPath, tableName):
    print("Updating table...")
    print("Reading file contents and uploading into db table...")

    timestamp = datetime.now()        
    df = pd.read_csv(csvPath)
    df.insert(0, 'timestamp',timestamp)    
    df.to_csv(csvPath, index=False)

    with open(csvPath) as csvFile:
       readCSV = csv.reader(csvFile, delimiter=',')
       columns = getColumns(readCSV)
       insertData(tableName, columns, readCSV)                
    print ("Upload complete")

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 BabaZuri