PostgreSQL can't escape JpegImageFile to binary, psycopg2

Question

ubuntu 18.04 - Python 3.6.7 - psycopg2 2.7.6.- Postgresql 10.8

Saving an image in a bytea Posgtres field, cause psycopg2 to rise the following error (test code in Spyder3):

Error while connecting to PostgreSQL can't escape JpegImageFile to binary

IF I run the same code in Eclipse-pydev, the error reported is similar but gives type information: TypeError: can't escape JpegImageFile to binary

-Can you understand if the problem is related to Postgresql or to psycopg2? -How can make we succed in saving the image file in Postgresql ? -May it be a library error ?

Saving an image in Postregsql require to use a bytea field type. The image needs to be encoded in hex format or bytea escape format. If we read an url image, convert it by psycopg2 in Bynary, BUT when finally executing INSERT query, the function return the said error. the following code can reproduce the error:

import psycopg2
import urllib.request
from PIL import Image
from io import BytesIO
import requests

url = 'https://upload.wikimedia.org/wikipedia/commons/thumb/a/a3/Eq_it-na_pizza-margherita_sep2005_sml.jpg/440px-Eq_it-na_pizza-margherita_sep2005_sml.jpg'
#image = Image.open(urllib.request.urlopen(url))
response = requests.get(url)
image = Image.open(BytesIO(response.content))
#test image correctly read
width, height = image.size
print (width,height)   
try:
    connection = psycopg2.connect(user = "user",
                password = "odoo12", host = "127.0.0.1",
                port = "5432",database = "dbname")
    cursor = connection.cursor()
    query = """INSERT INTO foods (fo_image, fo_url) 
                VALUES (%s,%s) ;"""
    byteImage = psycopg2.Binary(image)        
    data = (byteImage, url )
    cursor.execute(query,data)
except (Exception, psycopg2.Error) as error :
        print ("Error while connecting to PostgreSQL", error)
finally:
        #closing database connection.
            if(connection):
                cursor.close()
                connection.close()

The image is checked against size and shows right info. Changing different images shows same result. What is going wrong ? How to fix it ?_

Surely you want to save the actual bytes, not the python Image object you have constructed from them? — Richard Huxton, May 16 '19 at 19:37
I put your idea in 2 ways below, I get NO error, BUT... NO record written in memory. 1) save Image to bytearray and then pass it to psycopg2 as in https://stackoverflow.com/questions/33101935/convert-pil-image-to-byte-array - byteImage = psycopg2.Binary(image_to_byte_arra(image)) 2) get response.content and pass it directly to psycopg2.Binary(): byteImage = psycopg2.Binary(response.content) table is CREATE TABLE public.foods( fo_food_id_pk serial NOT NULL, fo_image bytea, fo_url varchar, CONSTRAINT foods_pk PRIMARY KEY (fo_food_id_pk) Any Idea ? — Giuliano69, May 16 '19 at 21:12

score 1 · Answer 1 · answered May 16 '19 at 22:35

1

I don't think you need the psycopg2.Binary in python 3.

I suspect you aren't calling connection.commit() to commit the transaction.

answered May 16 '19 at 22:35

Richard Huxton

21,516
3
39
51

Thanks Richard for your help. I tested you ideas, and confirm BOTH options (response.contet & img2bytea) works WITHOUT the need of psycopg2.Binary: just few difference in data written: image2bytearray: 51001 ; response.content: 39043; One more question: When I’ll read back the byteimage = cursor.fetchone[0], which is the best way to generate the Image object ? Storing the object with response.content or img2ba, makes any differece in generating the Image? – Giuliano69 May 17 '19 at 09:02

PostgreSQL can't escape JpegImageFile to binary, psycopg2

1 Answers1