用pickle在postgres表中保存python对象 [英] saving python object in postgres table with pickle

问题描述

我有一个创建一些对象的 python 脚本.我希望能够将这些对象保存到我的 postgres 数据库中以备后用.

I have a python script which creates some objects. I would like to be able to save these objects into my postgres database for use later.

我的想法是我可以腌制一个对象，然后将其存储在数据库中的一个字段中.但我正在绕圈子讨论如何存储、检索和使用数据.

My thinking was I could pickle an object, then store that in a field in the db. But I'm going round in circles about how to store and retrieve and use the data.

我尝试将pickle二进制字符串存储为text，但我不知道如何编码/转义它.那么如何将字符串作为二进制字符串加载到 unpickle 中.

I've tried storing the pickle binary string as text but I can't work out how to encode / escape it. Then how to load the string as a binary string to unpickle.

我已经尝试将数据存储为 bytea 有 psycopg2.Binary(data) 和没有.然后读入缓冲区并使用 base64.b64encode(result) 进行编码，但结果不一样并且无法解压.

I've tried storing the data as bytea both with psycopg2.Binary(data) and without. Then reading into buffer and encoding with base64.b64encode(result) but it's not coming out the same and cannot be unpickled.

是否有一种简单的方法可以在 SQL (postgres) 数据库中存储和检索 Python 对象?

Is there a simple way to store and retrieve python objects in a SQL (postgres) database?

推荐答案

根据@SergioPulgarin 的评论，我尝试了以下操作！

Following the comment from @SergioPulgarin I tried the following which worked!

N.B Edit2 来自@Tomalak 的评论

存储:

1. 将对象pickle为二进制字符串

1. Pickle the object to a binary string

pickle_string = pickle.dumps(object)

将pickle 字符串存储在postgres 的bytea(二进制)字段中.在 Psycopg2 中使用简单的 INSERT 查询

Store the pickle string in a bytea (binary) field in postgres. Use simple INSERT query in Psycopg2

检索:

1. 选择 Psycopg2 中的字段.(简单的SELECT 查询)

1. Select the field in Psycopg2. (simple SELECT query)

解压解码结果

retrieved_pickle_string = pickle.loads(decoded_result)

希望能帮助任何尝试做类似事情的人！

Hope that helps anybody trying to do something similar!

这篇关于用pickle在postgres表中保存python对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！