Python - Saving and Recovering Image/Pictures/JPEG from PostgreSQL

Question

So, I'm trying to save an image into my PostgreSQL table in Python using psycopg2

INSERT Query (Insert.py)

#Folder/Picture is my path/ID is generated/IDnum is increment
picopen = open("Folder/Picture."+str(ID)+"."+str(IDnum)+".jpg", 'rb').read()
filename = ("Picture."+str(ID)+"."+str(IDnum)+".jpg")

#Sample file name is this Picture.1116578795.7.jpg

#upload_name is where I want the file name, upload_content is where I store the image
SQL = "INSERT INTO tbl_upload (upload_name, upload_content) VALUES (%s, %s)"
data = (filename, psycopg2.Binary(picopen))
cur.execute(SQL, data)
conn.commit()

now to recover the saved Image I perform this query (recovery.py)

cur.execute("SELECT upload_content, upload_name from tbl_upload")
for row in cur:

    mypic = cur.fetchone()
    open('Folder/'+row[1], 'wb').write(str(mypic[0]))

now what happens is when I execute the recovery.py it does generate a ".jpg" file but I can't view or open it.

If it helps I'm doing it using Python 2.7 and Centos7. for the sake of additional information, I get this on the image viewer when I open the generated file.

Error interpreting JPEG image file (Not a JPEG file: starts with 0x5c 0x78)

I also tried using other formats as well (.png, .bmp)

You are reading it binary with "rb" and while inserting into DB you are converting it to binary again. with "Binary()". May be that is causing problem. as I do not see any issue in reading — lat long
– lat long, Commented Mar 13, 2018 at 9:07
Thank you for responding lat long, I've tried just using "r" and "w" after I've read your comment, however the result is still the same. — Mo Chan
– Mo Chan, Commented Mar 14, 2018 at 1:50
what about using "r" for reading, "binary()" for writing into DB and then "wb" for recovering — lat long
– lat long, Commented Mar 14, 2018 at 6:26

Mo Chan · Accepted Answer · 2018-03-15 02:11:12Z

1

I double checked my database type and apparently upload_content datatype is text its supposed to be bytea I thought I already had it set to bytea when I created my db. Problem solved.

answered Mar 15, 2018 at 2:11

Mo Chan

2173 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python - Saving and Recovering Image/Pictures/JPEG from PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related