CODEWITHSUNDEEP
python-3.xpostgresqlimagepsycopg2
Giuliano69
Giuliano69

Reputation: 11

PostgreSQL can't escape JpegImageFile to binary, psycopg2

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 ?_

Upvotes: 1

Views: 1835

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22953

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

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

Upvotes: 1

Related Questions