Pickeling results of postgresql query via psycopg2

I’m querying the PostgreSQL database via the psycopg2 library. The response of the query this way is cursor object file. Because of the size of the query, I’m trying to avoid re-query it and instead – save the query result as a pickle.

Unfortunately, when I trying to execute code for that:

import psycopg2
import pickle

# Connect to an existing database
conn = psycopg2.connect(dbname="DB", user="my_user", password="****", host="12.34.56.78")

# Open a cursor to perform database operations
cur = conn.cursor()

# Query the database and obtain data as Python objects
cur.execute("SELECT * FROM my_table[...];")

# Attempt to pickle the output
pickle_out = open("output.pickle","wb")
pickle.dump(cur, pickle_out)
pickle_out.close()

# Close communication with the database
cur.close()
conn.close()

An error massage raised:

TypeError: can't pickle psycopg2.extensions.cursor objects

What is a straight forward way to save the results of SQL query via python for future use?

Note: I am not obligated to use the pickle. It just seemed like an optimal solution to me.

Answer

I think you need cur.fetchall() after cur.execute() and its variations. https://psycopg.org/docs/cursor.html for more details.

For example:

import numpy as np
import psycopg2
import pickle

# Connect to an existing database
conn = psycopg2.connect(dbname="DB", user="my_user", password="****", host="12.34.56.78")

# Open a cursor to perform database operations
cur = conn.cursor()

# Query the database and obtain data as Python objects
cur.execute("SELECT * FROM my_table[...];")
cur_out = np.asarray(cur.fetchall())

# Attempt to pickle the output
# Attempt to pickle the output
pickle_out = open("output.pickle","wb")
pickle.dump(cur_out, pickle_out)
pickle_out.close()