Retrieve zipped file from bytea column in PostgreSQL using Python

Question

I have a table in my PostgreSQL database in which a column type is set to bytea in order to store zipped files. The storing procedure works fine. I have problems when I need to retrieve the zipped file I uploaded.

def getAnsibleByLibrary(projectId):
    con = psycopg2.connect(
        database="xyz",
        user="user",
        password="pwd",
        host="localhost",
        port="5432",
    )
    print("Database opened successfully")
    cur = con.cursor()
    query = "SELECT ansiblezip FROM library WHERE library.id = (SELECT libraryid from project WHERE project.id = '"
    query += str(projectId)
    query += "')"
    cur.execute(query)
    rows = cur.fetchall()
    repository = rows[0][0]
    con.commit()
    con.close()
    print(repository, type(repository))
    with open("zippedOne.zip", "wb") as fin:
        fin.write(repository)

This code creates a zippedOne.zip file but it seems to be an invalid archive. I tried also saving repository.tobytes() but it gives the same result. I don't understand how I can handle memoriview objects. If I try:

print(repository, type(repository))

the result is:

<memory at 0x7f6b62879348> <class 'memoryview'>

If I try to unzip the file:

chain@wraware:~$ unzip zippedOne.zip

The result is:

 Archive:  zippedOne.zip
      End-of-central-directory signature not found.  Either this file is not
      a zipfile, or it constitutes one disk of a multi-part archive.  In the
      latter case the central directory and zipfile comment will be found on
      the last disk(s) of this archive.
    unzip:  cannot find zipfile directory in one of zippedOne.zip or
            zippedOne.zip.zip, and cannot find zippedOne.zip.ZIP, period.

Trying to extract it in windows gives me the error: "The compressed (zipped) folder is invalid"

Answer 1

This code, based on the example in the question, works for me:

import io
import zipfile

import psycopg2

DROP = """DROP TABLE IF EXISTS so69434887"""
CREATE = """\
CREATE TABLE so69434887 (
  id serial primary key,
  ansiblezip bytea
)
"""

buf = io.BytesIO()
with zipfile.ZipFile(buf, mode='w') as zf:
    zf.writestr('so69434887.txt', 'abc')


with psycopg2.connect(database="test") as conn:
    cur = conn.cursor()
    cur.execute(DROP)
    cur.execute(CREATE)
    conn.commit()
    cur.execute("""INSERT INTO so69434887 (ansiblezip) VALUES (%s)""", (buf.getvalue(),))
    conn.commit()
    cur.execute("""SELECT ansiblezip FROM so69434887""")
    memview, = cur.fetchone()
with open('so69434887.zip', 'wb') as f:
    f.write(memview)

and is unzippable (on Linux, at least)

$ unzip -p so69434887.zip so69434887.txt 
abc

So perhaps the data is not being inserted correctly.

FWIW I got the "End-of-central-directory signature not found" until I made sure I closed the zipfile object before writing to the database.