Handling PostgreSQL BLOB data in Python

import psycopg2
from config import config

# This Function will open & convert the image or file data to binary data.


def convert_To_Binary(filename):
    with open(filename, 'rb') as file:
        data = file.read()
    return data


def insert_BLOB(S_No, FileName):
    """ insert a BLOB into a table """
    conn = None
    try:

        # connect to the PostgreSQL server & creating a cursor object
        conn = psycopg2.connect(**config)

        # Creating a cursor with name cur.
        cur = conn.cursor()

        # Binary Data
        file_data = convert_To_Binary(FileName)

        # BLOB DataType
        BLOB = psycopg2.Binary(file_data)

        # SQL query to insert data into the database.
        cur.execute(
            "INSERT INTO blob_datastore(s_no,file_name,blob_data) VALUES(%s,%s,%s)", (S_No, FileName, BLOB))

        # Close the connection
        cur.close()

    except(Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            # Commit the changes to the database
            conn.commit()


insert_BLOB(1, 'newOcta.jpg')

# Complete Function to Retrieve the BLOB data into the database.
import psycopg2
from config import config

# This Function will Creates File from binary data.


def Binary_To_File(BLOB, FileName, oldFileName):
    with open(f"{FileName}", 'wb') as file:
        file.write(BLOB)
    print(f"{oldFileName} File saved With Name name {FileName}")


def retrieve_BLOB(S_No, newFileName):
    """ Retrieve a BLOB From a table """
    conn = None
    try:
        # connect to the PostgreSQL server & creating a cursor object
        conn = psycopg2.connect(**config)

        # Creating a cursor with name cur.
        cur = conn.cursor()

        # Retrieve BLOB data from the database.
        cur.execute('SELECT * FROM BLOB_DataStore')
        db = cur.fetchall()

        BLOB = db[S_No-1][2]
        # open("FromDB"+db[0][1], 'wb').write(BLOB)
        Binary_To_File(BLOB, newFileName, db[S_No-1][1])

        # Close the connection
        cur.close()

    except(Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            # Commit the changes to the database
            conn.commit()


retrieve_BLOB(1, 'OctaFromDB.jpg')

import psycopg2

conn = None
try:
    # connect to the PostgreSQL server
    conn = psycopg2.connect(
        host='localhost',
        dbname='For_Practice',
        user='postgres',
        password='321654',
        port=5432
    )

    # Creating a cursor with name cur.
    cur = conn.cursor()
    cur.execute(
        "CREATE TABLE blob_datastore (s_no serial, file_name VARCHAR ( 50 ), blob_data bytea)")
    # SQL query to insert data into the database.
    insert_script = '''
        INSERT INTO blob_datastore(s_no,file_name,blob_data) VALUES (%s,%s,%s);
    '''

    # open('File,'rb').read() is used to read the file.
    # where open(File,'rb').read() will return the binary data of the file.
    # psycopg2.Binary(File_in_Bytes) is used to convert the binary data to a BLOB data type.
    BLOB_1 = psycopg2.Binary(
        open('files\Anima.mp4', 'rb').read())       # Video
    BLOB_2 = psycopg2.Binary(
        open('files\Octa.jpg', 'rb').read())        # Image
    BLOB_3 = psycopg2.Binary(open('files\Type.gif', 'rb').read())        # GIF
    BLOB_4 = psycopg2.Binary(open('files\BlobNotes.pdf', 'rb').read())   # PDF

    # And Finally we pass the above mentioned values to the insert_script variable.
    insert_values = [(1, 'Anima.mp4', BLOB_1), (2, 'Octa.jpg', BLOB_2),
                     (3, 'Type.gif', BLOB_3), (4, 'BlobNotes.pdf', BLOB_4)]

    # The execute() method with the insert_script & insert_value as argument.
    for insert_value in insert_values:
        cur.execute(insert_script, insert_value)
        print(insert_value[0], insert_value[1],
              "[Binary Data]", "row Inserted Successfully")

    # SQL query to fetch data from the database.
    cur.execute('SELECT * FROM BLOB_DataStore')

    # open(file,'wb').write() is used to write the binary data to the file.
    for row in cur.fetchall():
        BLOB = row[2]
        open("new"+row[1], 'wb').write(BLOB)
        print(row[0], row[1], "BLOB Data is saved in Current Directory")

    # Close the connection
    cur.close()

except(Exception, psycopg2.DatabaseError) as error:
    print(error)
finally:
    if conn is not None:
      
        # Commit the changes to the database
        conn.commit()