python - 通过 psycopg2 提取 postgresql 查询的结果

Question

我正在通过 psycopg2 库查询 PostgreSQL 数据库。以这种方式查询的响应是游标对象文件。由于查询的大小，我试图避免重新查询它，而是将查询结果保存为pickle。

不幸的是，当我尝试为此执行代码时：

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()

出现错误消息：

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

什么是通过 python 保存 SQL 查询结果以供将来使用的直接方法？

注意：我没有义务使用泡菜。这对我来说似乎是一个最佳解决方案。

标签： pythonsqlpostgresqlpicklepsycopg2

Answer 1

我认为您需要cur.fetchall()aftercur.execute()及其变化。 https://psycopg.org/docs/cursor.html了解更多详情。

例如：

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()