Python导出SQL数据至CSV生成多行空白问题排查

Hey there! As a fellow developer who stumbled through this exact issue when I was starting out with Python, let's break down why you're getting those frustrating blank lines and how to fix them quickly.

Most Common Culprit: Missing newline='' When Opening the File #

The #1 reason for extra blank lines is usually how you're opening the output file. Python's csv module handles newline characters on its own, but if you don't specify newline='' when opening the file, your system's default newline handling will clash with it—resulting in those annoying empty rows.

Wrong Code (Causes Blank Lines): #

import csv
import sqlite3  # Swap with your DB connector (like psycopg2 for PostgreSQL)

conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM your_target_table")
rows = cursor.fetchall()

# Missing newline='' here causes conflicting newline handling
with open('exported_data.csv', 'w') as f:
    writer = csv.writer(f)
    # Write column headers
    writer.writerow([desc[0] for desc in cursor.description])
    # Write all rows
    writer.writerows(rows)

conn.close()

Fixed Code: #

import csv
import sqlite3

conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM your_target_table")
rows = cursor.fetchall()

# Add newline='' to prevent duplicate newlines
with open('exported_data.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    writer.writerow([desc[0] for desc in cursor.description])
    writer.writerows(rows)

conn.close()

Other Possible Issues to Check #

• Empty Rows in Your SQL Results: Sometimes the problem isn't code—it's data! Run your SQL query directly in your database tool to see if it's returning empty or null-only rows. If so, filter them out before exporting:

  # Filter rows that have at least one non-empty/non-null value
  filtered_rows = [row for row in rows if any(field not in (None, '') for field in row)]
  writer.writerows(filtered_rows)
  

• Accidental Empty Writes: Double-check your code to make sure you aren't calling writer.writerow() with an empty list/tuple somewhere (like in a loop that runs an extra iteration by mistake).

Quick Recap #

1. Always add newline='' when opening the CSV file for writing with the csv module.
2. Verify your SQL query isn't returning empty rows.
3. Scan your code for unintended empty write calls.

That should clear up those blank lines in no time!

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