How to add a header to an existing csv file?

Question

I know this is a very basic question. I have a CSV file, which contains data already. This file is generated automatically not using opening with Dictreader or open object.

Goal

1. I want to open an existing file
2. Append the Header in the first row (Shift the first row data)
3. Save the file
4. Return the file

Any clues?

cursor.execute(sql, params + (csv_path,))

This command generates file, without header.

Code

  sql, params = queryset.query.sql_with_params()
    sql += ''' INTO OUTFILE %s
           FIELDS TERMINATED BY ','
           OPTIONALLY ENCLOSED BY '"'
           LINES TERMINATED BY '\n' '''
    csv_path = os.path.join(settings.MEDIA_ROOT + '\\tmp', csv_filename)
cursor = connection.cursor()

cursor.execute(sql, params + (csv_path,))
columns = [column[0] for column in cursor.description] #error

Tried

SELECT `website` UNION SELECT `request_system_potentialcustomers`.`website` FROM `request_system_potentialcustomers` ORDER BY `request_system_potentialcustomers`.`revenue` DESC  
INTO OUTFILE "D:\\out.csv"
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Answer 1

Wait a minute. If you have not yet called

cursor.execute(sql, params + (csv_path,))

then you have the opportunity to write the CSV file correctly from the get-go. You should not need to write a new file with the header line, then copy all that CSV into the new file and so forth. That is slow and inefficient -- and your only choice -- if you really have to prepend a line to an existing file.

If instead you have not yet written the CSV file, and if you know the header, then you can add it to the SQL using SELECT ... UNION ... SELECT:

header = ['foo', 'bar', 'baz', ]
query = ['SELECT {} UNION'.format(','.join([repr(h) for h in header]))]
sql, params = queryset.query.sql_with_params()
query.append(sql)
sql = '''INTO OUTFILE %s
         FIELDS TERMINATED BY ','
         OPTIONALLY ENCLOSED BY '"'
         LINES TERMINATED BY '\n' '''
query.append(sql)
sql = ' '.join(query)         
csv_path = os.path.join(settings.MEDIA_ROOT + '\\tmp', csv_filename)

cursor = connection.cursor()
cursor.execute(sql, params + (csv_path,))

---

Demo:

mysql> SELECT "foo", "bar" UNION SELECT "baz", "quux" INTO OUTFILE "/tmp/out";  

Produces the file /tmp/out containing

foo bar
baz quux

Answer 2

Cursor.description attribute give you information about the result column.

cursor.execute(sql, params + (csv_path,))
columns = [column[0] for column in cursor.description]

1. Write above information to the new file.
2. Append old csv contents to the new file.
3. Rename the new file with the old file name.

Answer 3

Not quite clear if you are trying to read an existing csv file or not, but to read a csv off disk without column names:

Use dictreader/dictwriter and specify the column names in your file

Python 3:

import csv
ordered_filenames = ['animal','height','weight']

with open('stuff.csv') as csvfile, open("result.csv","w",newline='') as result:
    rdr = csv.DictReader(csvfile, fieldnames=ordered_filenames)
    wtr = csv.DictWriter(result, ordered_filenames)
    wtr.writeheader()
    for line in rdr:
        wtr.writerow(line)

With stuff.csv in the same directory:

elephant,1,200
cat,0.1,1
dog,0.2,2

and the output result file:

animal,height,weight
elephant,1,200
cat,0.1,1
dog,0.2,2