使用Python向PostgreSQL保存数据失败求助（Rails+AWS S3场景）

Hey there! Let's tackle this Python-to-PostgreSQL data saving issue you're hitting in your Rails app. I’ve worked through similar workflows before, so here’s a breakdown of the most likely fixes and best practices:

1. Ensure your Python script connects to PostgreSQL correctly #

First, make sure your Python script uses the same database configuration as your Rails app—don’t hardcode credentials. Use environment variables (matching what’s in your Rails .env or database.yml) and a reliable PostgreSQL library like psycopg2 or sqlalchemy. Here’s a practical example with psycopg2:

import psycopg2
from psycopg2.extras import Json
from dotenv import load_dotenv
import os

# Load shared environment variables
load_dotenv()

def save_to_db(s3_image_url, processed_data, image_id):
    conn = None
    try:
        # Connect using Rails-aligned credentials
        conn = psycopg2.connect(
            dbname=os.getenv('DB_NAME'),
            user=os.getenv('DB_USER'),
            password=os.getenv('DB_PASSWORD'),
            host=os.getenv('DB_HOST'),
            port=os.getenv('DB_PORT')
        )
        cur = conn.cursor()

        # Use PostgreSQL's JSON type for structured processed data
        cur.execute("""
            UPDATE images 
            SET s3_url = %s, processed_data = %s, updated_at = NOW()
            WHERE id = %s
        """, (s3_image_url, Json(processed_data), image_id))

        conn.commit()
        print("Data saved successfully!")
    except Exception as e:
        print(f"Error saving to DB: {str(e)}")
        if conn:
            conn.rollback()  # Critical to avoid stuck transactions
    finally:
        # Clean up connections to prevent leaks
        if cur:
            cur.close()
        if conn:
            conn.close()

Key notes here:

• Use psycopg2.extras.Json to properly store structured data in PostgreSQL JSON/JSONB columns
• Always handle exceptions and rollback on failure
• Close connections in a finally block to avoid exhausting your DB’s connection pool

2. Align your Rails and Python workflow to avoid race conditions #

Since you’re uploading to S3 first, make sure the Python script only runs after S3 upload confirms success. Use a background job (like Sidekiq) in Rails to orchestrate this flow:

class ProcessImageJob
  include Sidekiq::Job

  def perform(image_id)
    image = Image.find(image_id)
    # Step 1: Upload image to S3
    s3_url = upload_to_s3(image.uploaded_file)

    if s3_url.present?
      # Step 2: Pass S3 URL and image ID to Python script
      python_output = `python3 app/scripts/image_processor.py --s3-url "#{s3_url}" --image-id #{image_id}`
      
      # Verify Python script ran successfully
      unless $?.success?
        raise "Image processing failed: #{python_output}"
      end
    else
      raise "S3 upload failed for image #{image_id}"
    end
  end

  private

  def upload_to_s3(file)
    # Your AWS S3 upload logic here (using aws-sdk-s3)
    s3 = Aws::S3::Resource.new
    obj = s3.bucket(ENV['S3_BUCKET']).object(file.filename)
    obj.upload_file(file.path, acl: 'public-read')
    obj.public_url
  end
end

Then update your Python script to accept command-line arguments with argparse:

import argparse

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument('--s3-url', required=True)
    parser.add_argument('--image-id', required=True)
    args = parser.parse_args()

    # Run your image/data processing logic here
    processed_data = analyze_image(args.s3_url)

    # Save results to DB using the function from earlier
    save_to_db(args.s3_url, processed_data, args.image_id)

if __name__ == "__main__":
    main()

3. Fix common pitfalls that break DB writes #

• Database permissions: Double-check that your PostgreSQL user has INSERT/UPDATE access to the target table. Test this manually with psql to rule out permission issues.
• Environment mismatches: If you’re testing in development vs production, ensure your Python script loads the correct environment variables (e.g., production DB host instead of localhost).
• Data type mismatches: If your Rails model uses a jsonb column for processed data, don’t pass stringified JSON—use the Json adapter from psycopg2 as shown earlier.
• Connection limits: Make sure your Python script doesn’t leave open connections; the finally block in the first example prevents this.

4. Alternative: Let Rails handle DB writes (simpler for many cases) #

If the Python script’s only job is image processing, you could have it output the processed data as JSON, then let Rails handle the DB write. This keeps all database logic in your Rails app and avoids duplicating DB connections:

Python script output:

import json

# After processing
print(json.dumps({
    "processed_data": processed_result,
    "s3_url": s3_url
}))

Rails job handles the save:

result = `python3 app/scripts/image_processor.py --image-id #{image.id}`
processed_data = JSON.parse(result)
image.update!(
  s3_url: processed_data["s3_url"],
  processed_data: processed_data["processed_data"]
)

This approach often reduces complexity since Rails is already configured with your database and models.

内容的提问来源于stack exchange，提问作者César Correa