Using Pandas to write file creates blank lines

Question

I am using the pandas library to write the contents of a mysql database to a csv file.

But when I write the CSV, every other line is blank:

Also, it's printing line numbers to the left that I do not want. The first column should be 'Account Number'.

Here is my code:

destination = 'output_file.txt'
read_sql = """ SELECT LinkedAccountId,ProductName,ItemDescription,ResourceId,UnBlendedCost,UnBlendedRate,Name,Owner,Engagement FROM billing_info ;"""
fieldnames = ['Account Number', 'Product Name', 'Item Description', 'Resource ID', 'UnBlended Cost', 'UnBlended Rate', 'Name', 'Owner', 'Engagement']
# Open the file
f = open(destination, 'w')
cursor.execute(read_sql)
while True:
    # Read the data
    df = pd.DataFrame(cursor.fetchmany(1000))
    # We are done if there are no data
    if len(df) == 0:
        break
    # Let's write to the file
    else:
        df.to_csv(f, header=fieldnames)

Why is it printing blank lines between the lines with data? How can I get it to create the file without blank lines and without the line number column to the left?

Answer 1

Have a look at the options for to_csv: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html

For convenience, I have posted some items of interest here:

line_terminator : string, optional

The newline character or character sequence to use in the output file. Defaults to os.linesep, which depends on the OS in which this method is called (‘n’ for linux, ‘rn’ for Windows, i.e.).

And

index : bool, default True

Write row names (index).

Are probably what you're looking for. As for the empty lines, try explicitly specifying a single newline:

df.to_csv(f, header=fieldnames, index=False, line_terminator='\n')