将Pandas DataFrame写入Amazon RedShift表时出错求助

Since you didn’t share the exact error message, let’s walk through the most common issues with your copy_from approach and how to fix them:

1. Mismatched Column Count or Order #

Redshift’s COPY operation expects input data to match the target table’s column count and order exactly (unless you explicitly specify columns). If your DataFrame’s columns don’t align with the table, you’ll get a mismatch error.

Fix:

• Verify your DataFrame report has the same number of columns as the Redshift table table_name, in the correct sequence.
• If the order doesn’t match, explicitly list columns in the copy_from call to avoid confusion:

  cur.copy_from(output, table_name, columns=list(report.columns), null="")
  

2. Data Type Incompatibility #

Redshift is strict about data type consistency. For example, a float in your DataFrame where the table expects an integer, or a string with special characters that conflict with the column’s charset, will trigger errors.

Fix:

• Compare your DataFrame’s dtypes to the Redshift table schema (use report.dtypes to inspect your DataFrame).
• Convert incompatible columns before exporting:

  # Example: Convert float column to integer (fill nulls first if needed)
  report['int_column'] = report['int_column'].fillna(0).astype(int)
  

3. Permissions Issues #

Your database user might lack INSERT or COPY permissions on the target table, or access to the cluster itself.

Fix:

• Ensure your user has the necessary permissions by running this SQL in Redshift:

  GRANT INSERT ON table_name TO your_username;
  GRANT SELECT ON table_name TO your_username; -- For verification purposes
  

• Also confirm your network allows access to the Redshift cluster’s security group (port 5439 by default).

4. Null Handling & Delimiter Conflicts #

Your null="" setting might not match Redshift’s default null representation (\N), or your data contains tabs (since you’re using sep='\t') that break the delimiter.

Fix:

• Use Redshift’s standard null marker and switch to a delimiter that doesn’t appear in your data (like |):

  report.to_csv(output, sep='|', header=False, index=False, na_rep='\\N')
  cur.copy_from(output, table_name, sep='|', columns=list(report.columns), null='\\N')
  

5. Encoding Problems with StringIO #

Psycopg2’s copy_from expects byte data, but io.StringIO deals with text. This can cause encoding errors, especially with non-ASCII characters.

Fix:
Switch to io.BytesIO and encode your CSV content to UTF-8 (Redshift’s default encoding):

output = io.BytesIO()
csv_content = report.to_csv(sep='|', header=False, index=False, na_rep='\\N')
output.write(csv_content.encode('utf-8'))
output.seek(0)

6. Improved Code with Error Handling #

Adding error handling will help you pinpoint the exact issue. Here’s a revised version of your code with best practices:

from sqlalchemy import create_engine
import psycopg2
import io
import traceback

engine = create_engine('postgresql+psycopg2://username:password@host:port/database')

try:
    with engine.raw_connection() as conn:
        with conn.cursor() as cur:
            # Use BytesIO for proper encoding
            output = io.BytesIO()
            # Use pipe delimiter to avoid tab conflicts
            csv_content = report.to_csv(sep='|', header=False, index=False, na_rep='\\N')
            output.write(csv_content.encode('utf-8'))
            output.seek(0)
            
            # Explicitly specify columns to match table order
            cur.copy_from(
                output, 
                table_name, 
                sep='|', 
                columns=list(report.columns), 
                null='\\N'
            )
        conn.commit()
    print("Data successfully written to Redshift!")
except Exception as e:
    print(f"Error occurred: {str(e)}")
    traceback.print_exc()

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