如何将SQL查询结果逐行导出至CSV/TXT文件？

Hey there! Let's break down why your data ends up all squished in one line when writing to a file, even though it prints nicely line-by-line with your loop.

The core issue is this: Python's print() function automatically adds a newline character (\n) at the end of each output. But when you write directly to a file (like using file.write(str(data))), you're dumping the raw string representation of your entire list—no automatic newlines included.

Here are a few straightforward solutions to fix this:

1. Write Rows Line-by-Line with Manual Newlines #

This mirrors your print loop behavior, but explicitly adds a newline after each row when writing to the file:

# Assume your database cursor is 'c' and you've fetched data with data = c.fetchall()
sql = 'SELECT * FROM batch'
c.execute(sql)
data = c.fetchall()

# Write to TXT
with open('output.txt', 'w') as txt_file:
    for row in data:
        # Convert the tuple to a string and add a newline
        txt_file.write(f"{row}\n")

The \n ensures each row gets its own line in the file, just like your print statements.

2. Use the csv Module for Proper CSV Formatting #

If you want a valid CSV file (not just a text file with rows), Python's built-in csv module is the way to go—it handles edge cases like commas inside values and adds proper line breaks automatically:

import csv

sql = 'SELECT * FROM batch'
c.execute(sql)
data = c.fetchall()

# Write to CSV
with open('output.csv', 'w', newline='') as csv_file:
    writer = csv.writer(csv_file)
    # Optional: Write column headers (pull them from the cursor's description)
    headers = [desc[0] for desc in c.description]
    writer.writerow(headers)
    # Write all rows at once
    writer.writerows(data)

This gives you a clean CSV with optional headers (great for spreadsheets) and each row on its own line.

3. Join Rows Into a Single String (Concise Syntax) #

If you prefer a shorter approach, convert all rows to strings, join them with newlines, and write everything in one go:

sql = 'SELECT * FROM batch'
c.execute(sql)
data = c.fetchall()

with open('output.txt', 'w') as txt_file:
    # Map each row to a string, then join with newlines
    txt_file.write('\n'.join(map(str, data)))

This works perfectly for simple text files and avoids writing a full loop.

内容的提问来源于stack exchange，提问作者DevOps