使用Python+psycopg2从PostgreSQL取数失败问题求助

Hey there! It sounds like you’re hitting a memory exhaustion issue—creating 160k+ objects from a tens-of-GB dataset can quickly eat up your local RAM, especially if you’re loading all the data into memory at once. Let’s walk through the most common fixes for this scenario:

1. Use a Server-Side Cursor to Batch Data Retrieval #

By default, psycopg2 uses a client-side cursor, which pulls the entire result set from the database to your local machine immediately. For large datasets, this is a recipe for memory overload. Instead, switch to a server-side (named) cursor, which lets PostgreSQL hold the result set on the server and send it to you in manageable batches.

Here’s how to implement it:

import psycopg2
from psycopg2.extras import RealDictCursor  # Optional: for dict-like row access

# Optimized data object with __slots__ to cut memory footprint
class YourDataObject:
    __slots__ = ["id", "name", "value"]  # List all your object attributes here
    def __init__(self, id, name, value):
        self.id = id
        self.name = name
        self.value = value

# Establish connection to your local PostgreSQL
conn = psycopg2.connect("dbname=your_db user=your_user host=localhost")

# Create a named server-side cursor
with conn.cursor(name="large_result_cursor", cursor_factory=RealDictCursor) as cur:
    cur.execute("SELECT id, name, value FROM your_large_table")
    
    # Fetch and process data in batches (adjust size based on your available RAM)
    batch_size = 1000
    while True:
        rows = cur.fetchmany(batch_size)
        if not rows:
            break
        
        # Create objects and process them (don't store all unless absolutely necessary!)
        for row in rows:
            obj = YourDataObject(row["id"], row["name"], row["value"])
            # Process the object here (e.g., write to file, transform, analyze)
            # If you don't need to retain all objects, skip adding them to a large list!

conn.close()

Key notes:

• The named cursor tells PostgreSQL to manage the result set server-side instead of pushing everything to your machine at once.
• fetchmany() pulls only a subset of rows each time, keeping memory usage low.
• If you don’t need to keep all objects in memory long-term, process each one immediately and discard it instead of building a massive list.

2. Optimize Your Object’s Memory Footprint #

Even with batching, 160k objects can add up if each instance is bloated. Use these tricks to shrink each object:

• Add __slots__ to your class: This disables the dynamic __dict__ that default Python objects use, cutting memory usage per instance by 30-50% (as shown in the code above).
• Use dataclasses with slots=True: For Python 3.7+, this creates lightweight objects with minimal overhead:

  from dataclasses import dataclass
  
  @dataclass(slots=True)
  class YourDataObject:
      id: int
      name: str
      value: float
  

• Switch to tuples: If you don’t need methods on your data, tuples are far more memory-efficient than custom objects.

3. Explicitly Manage Memory and Garbage Collection #

If you must keep a large number of objects in memory, help Python clean up unused data:

• Delete references to objects you no longer need with del obj.
• Trigger garbage collection manually (as a last resort) with import gc; gc.collect() after processing each batch.

Quick Check: Confirm the Error Type #

If you haven’t already, take a look at the exact error message. If it’s a MemoryError, the fixes above will solve it. If it’s a psycopg2-specific error (like connection timeouts), share the details and we can troubleshoot further!

内容的提问来源于stack exchange，提问作者Michal Fašánek