Storing a pickled dictionary in Postgresql (Psycopg2)

Question

I am trying to store a pickled nested dictionary in Postgresql (I am aware that this is a quick & dirty method and won't be able to access dictionary contents from Postgresql - usually bad practice)

# boilerplate, preamble and upstream work.
import psycopg2


''' Inputs: nd = dictionary to be pickled '''



pickled = pickle.dumps(nd)

connection = psycopg2.connect(user = "-----",
                              password = "----",
                              host = "----",
                              port = "----",
                              database = "----")
    
name = 'database1'    
    
    
print('Connected...')
cursor = connection.cursor()
print(connection.get_dsn_parameters(),"\n")
cursor.execute("CREATE TABLE thetable (name TEXT, ablob BYTEA)")
print('Created Table...')
cursor.execute("INSERT INTO thetable VALUES(%s)",(psycopg2.Binary(pickled),))
connection.commit()
print('Added Data...')
cursor.close()
connection.close()
print('Connection closed...')

When I come to data data retrieval, I am having many issues importing the data from Postgres - essentially the data is to be opened, unpickled back to the dictionary and visualised. I have tried:

import psycopg2
from io import BytesIO

connection = psycopg2.connect(user = "----",
                              password = "----",
                              host = "----",
                              port = "----",
                              database = "----")

cursor = connection.cursor()
cursor.execute("SELECT ablob FROM thetable")
result, = cursor.fetchone()
cursor.close()
connection.rollback()

result = BytesIO(result)

print(pickle.load(result))

As per this link: https://www.oreilly.com/library/view/python-cookbook/0596001673/ch08s08.html, and consulted: Insert an image in postgresql database and: saving python object in postgres table with pickle, however have been unable to return the pickled dictionary.

Any advice in achieving this is greatly appreciated!

Are you sure your code runs without throwing an exception? Your insert should look like: `cursor.execute("INSERT INTO thetable VALUES(%s, %s)",('some name', psycopg2.Binary(pickled),))` Also, I never heard of the `psycopg2.Binary()` wrapper. I just pass the `b''` object. — Mike Organek, Jul 18 '20 at 16:55
It doesn't throw any exceptions, and when I checked the Heroku Postgres database, 'abloom' and 'name' are both present within the table (although unsure if populated) — DJW001, Jul 18 '20 at 17:20
@Mike Organek. Per the docs [Binary](https://www.psycopg.org/docs/module.html?highlight=binary#psycopg2.Binary). — Adrian Klaver, Jul 18 '20 at 17:26
I would check to see if there is data in the table. I'm with @Mike Organek, I don't see how ```"INSERT INTO thetable VALUES(%s)",(psycopg2.Binary(pickled),)``` can succeed. You either need to change to ```"INSERT INTO thetable(ablob) VALUES(%s)",(psycopg2.Binary(pickled),)``` or to what Mike suggested. — Adrian Klaver, Jul 18 '20 at 17:34
Update to my previous comment. Because you did not specify columns and only supplied one value, that value would be inserted into ```name``` as ```INSERT``` matches columns in their declared order in a 1:1 to the ```VALUES``` supplied. As long as the string to be inserted does not run afoul of character rules it would succeed. The ```ablob``` in this case would be set to ```NULL```. — Adrian Klaver, Jul 18 '20 at 18:43

score 2 · Accepted Answer · answered Jul 18 '20 at 18:12

When your CREATE TABLE lists two fields, you have to list in INSERT which ones you want to fill, unless you fill them all.

import psycopg2
import pickle

dict = {
  "foo": "bar"
}

p = pickle.dumps(dict)

connection = psycopg2.connect(database = "test")

cursor = connection.cursor()
cursor.execute("CREATE TABLE thetable (name TEXT, ablob BYTEA)")
cursor.execute("INSERT INTO thetable VALUES(%s,%s)",('test',p))
connection.commit()
cursor.close()
connection.close()

and reading

import psycopg2
import pickle

connection = psycopg2.connect(database = "test")

cursor = connection.cursor()
cursor.execute("SELECT ablob FROM thetable WHERE name='test';")
result = cursor.fetchone()

print pickle.loads(result[0])

cursor.close()
connection.close()

Storing a pickled dictionary in Postgresql (Psycopg2)

1 Answers1