PostgreSQL公共同有模式下获取表行数写入CSV文件时出现跳行问题求助

The issue with skipped/blank lines in your CSV file is a common pitfall when using Python's csv module on Windows. Here's why it happens and how to fix it:

Root Cause #

When you open the file with just 'w' mode, Python adds extra carriage return characters (\r) alongside the newline (\n), since the default line ending for text files on Windows is \r\n. The csv.writer also adds its own line endings, resulting in \r\r\n which appears as blank lines in many spreadsheet editors or text viewers.

The Fix #

Add newline='' to your open() call. This tells Python to let the csv module handle line endings properly, regardless of your operating system. I've also added a couple of best practices to make your code more robust:

import psycopg2
import csv
from psycopg2 import sql  # For safe table name quoting

conn = psycopg2.connect(host="localhost", database="practice_db", user="postgres", password="admin")
print('connected')
cursor = conn.cursor()
cursor.execute("select version()")
print(cursor.fetchall())

# Get list of base tables in public schema
sql_query = sql.SQL("select table_name from information_schema.tables where table_type='BASE TABLE' AND TABLE_SCHEMA='public'")
cursor.execute(sql_query)
list_table = [item[0] for item in cursor.fetchall()]
print(list_table)

# Write to CSV with proper newline handling
with open('python_count_psql.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    writer.writerow(['Table', 'Count'])
    for table_name in list_table:
        # Use safe identifier quoting to avoid SQL injection and handle special table names
        count_query = sql.SQL('SELECT COUNT(*) FROM {}').format(sql.Identifier(table_name))
        cursor.execute(count_query)
        writer.writerow([table_name, cursor.fetchone()[0]])

# Clean up database connections
cursor.close()
conn.close()

Key Improvements Explained #

1. Newline Handling: The newline='' parameter disables Python's automatic line ending conversion, ensuring the csv module writes the correct line endings for your system (either \n on Unix-like systems or \r\n on Windows).
2. Safe Table Name Quoting: Using psycopg2.sql.Identifier properly quotes table names, so tables with special characters (like spaces) or reserved words (like user) won't break your query, and it protects against potential SQL injection risks.
3. Connection Cleanup: Explicitly closing the cursor and connection prevents dangling database connections, which is a good practice for resource management.

Content of the question originates from Stack Exchange, question author: zeeshan12396