How can I INSERT a python object into postgreSQL

Question

I found this question saving python object in postgres table with pickle in stackoverflow.

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. Following the comment from @SergioPulgarin I tried the following which worked!

Storing:

Pickle the object to a binary string pickle_string = pickle.dumps(object)

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

Retrieval:

Select the field in Psycopg2. (simple SELECT query)

Unpickle the decoded result retrieved_pickle_string = pickle.loads(decoded_result)

I dumped the pickle file. Now I'm trying to load it into the database. My problem is that I cannot find the right syntax to do that...

I've tried it within a python file with psycopg2: I've already dumped the pickle object and connected to the database. I tried to change the datatype to byte and to insert then the pickle into the DB:

pickle_obj = bytes(pickle_file)
query = "INSERT INTO schema.table (col1, ...) VALUES (%%s, ...)" % pickle_obj
cursor.execute(query)

I got this error:

Traceback (most recent call last):
  File "/path/to/file.py", line 18, in <module>
    cursor.execute(query)
psycopg2.errors.SyntaxError: syntax error at or near "K"
LINE 1: ...e1_h\x15Nh\x16K\x00h\x17Nh\x18Nh\x19\x89h&K\x0bh\'K\x01h(h+h...
                                                         ^

Process finished with exit code 1

Then I tried it to insert it directly from postgreSQL (But I am not sure about this code either, as it didn't work as well.)

INSERT INTO table SELECT PG_Read_File('/home/...)

I think my syntax isn't 100% correct? So how did you do it? Thanks for your tips!

Try to wrap this object with psycopg2.Binary as stated in the docs. https://www.psycopg.org/docs/usage.html#adapt-binary

The posts seems to be deleted, but as it worked for me I just add it here. Thank you!

Mike Organek · Accepted Answer · 2020-07-06T16:00:06.457

You should always use parameterized queries to substitute values into queries. String construction or formatting opens you to SQL injection attacks. Beyond the safety advantages, Using parameterized queries allows the driver to deal with escaping and quoting for you.

The shortest path to accomplishing what you are asking is to use a bytea column in your table.

If your table looks like this:

  Column   |  Type   
-----------+---------
 id        | integer serial primary key
 bytesdata | bytea

Then this insert will work:

cursor.execute("insert into mytable (bytesdata) values (%s) returning id", (pickle_obj, ))
inserted_id = cursor.fetchone()[0]
connection.commit()

To retrieve this object later, you can execute:

cursor.execute("select bytesdata from mytable where id = %s", (inserted_id, ))
row = cursor.fetchone()
retrieved_pickle_obj = row[0]

My id type is SERIAL. I thought a serial datatype would add itself? So that one does not have to add it, when entering a new row? — soph, Jul 06 '20 at 15:58

How can I INSERT a python object into postgreSQL

1 Answers1