如何通过Python保存PostgreSQL查询结果以便后续复用？

Ah, that's a common gotcha! Psycopg2's cursor objects can't be pickled because they're not just raw data—they're tied to your active database connection and hold dynamic state (like the current position in the result set, connection metadata, etc.). To save your query results for later use, you first need to extract the actual data from the cursor into native Python structures, then save those structures instead.

Here are several reliable methods to do this, depending on your needs:

1. Extract data to a list/dict list, then use pickle #

Even though you can't pickle the cursor, you can pickle the raw data it points to. Use fetchall() to pull all results into a list of tuples, or convert them to a list of dictionaries for more readable data:

import psycopg2
import pickle

# Connect and query
conn = psycopg2.connect(dbname="DB", user="my_user", password="****", host="12.34.56.78")
cur = conn.cursor()
cur.execute("SELECT * FROM my_table[...];")

# Option 1: Raw list of tuples
data = cur.fetchall()

# Option 2: List of dictionaries (maps column names to values)
columns = [desc[0] for desc in cur.description]
data = [dict(zip(columns, row)) for row in cur.fetchall()]

# Save the data (not the cursor!)
with open("output.pickle", "wb") as pickle_out:
    pickle.dump(data, pickle_out)

# Clean up connections
cur.close()
conn.close()

# Later, load the data like this:
with open("output.pickle", "rb") as pickle_in:
    loaded_data = pickle.load(pickle_in)

2. Save to CSV (great for cross-tool compatibility) #

If you need to share the data with tools like Excel, or just want a human-readable format, CSV is perfect. Use Python's built-in csv module to write both headers and rows:

import psycopg2
import csv

conn = psycopg2.connect(dbname="DB", user="my_user", password="****", host="12.34.56.78")
cur = conn.cursor()
cur.execute("SELECT * FROM my_table[...];")

# Get column names for the CSV header
columns = [desc[0] for desc in cur.description]

# Write to CSV
with open("output.csv", "w", newline="", encoding="utf-8") as csv_file:
    writer = csv.writer(csv_file)
    writer.writerow(columns)  # Write header
    writer.writerows(cur)  # Write all rows at once

cur.close()
conn.close()

# Load later with pandas or csv module:
import pandas as pd
df = pd.read_csv("output.csv")

3. Save to JSON (structured, cross-language format) #

JSON is ideal if you're working with web tools or scripts in other languages. Convert your results to a list of dictionaries first, then dump to JSON:

import psycopg2
import json

conn = psycopg2.connect(dbname="DB", user="my_user", password="****", host="12.34.56.78")
cur = conn.cursor()
cur.execute("SELECT * FROM my_table[...];")

columns = [desc[0] for desc in cur.description]
data = [dict(zip(columns, row)) for row in cur.fetchall()]

# Save to JSON
with open("output.json", "w", encoding="utf-8") as json_file:
    json.dump(data, json_file, indent=2)  # Indent for readability

cur.close()
conn.close()

# Load later:
with open("output.json", "r", encoding="utf-8") as json_in:
    loaded_data = json.load(json_in)

4. Use pandas for large datasets (Parquet/Feather) #

If you're dealing with very large datasets, columnar formats like Parquet or Feather are far more efficient than pickle/CSV—they save space and load faster. Pandas can directly read from your psycopg2 connection and save to these formats:

import psycopg2
import pandas as pd

conn = psycopg2.connect(dbname="DB", user="my_user", password="****", host="12.34.56.78")

# Read query results directly into a pandas DataFrame
df = pd.read_sql_query("SELECT * FROM my_table[...];", conn)

# Save to Parquet (install pyarrow or fastparquet first)
df.to_parquet("output.parquet")
# Or Feather (lighter weight for same-language use)
# df.to_feather("output.feather")

conn.close()

# Load later:
loaded_df = pd.read_parquet("output.parquet")

Quick notes: #

• Always extract all data (with fetchall(), writerows(cur), or pandas) before closing your cursor/connection—once the connection is closed, the cursor can't access the results anymore.
• For extremely large datasets that don't fit in memory, use fetchmany(size=1000) to process and write data in batches instead of fetchall().

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