How to save files from postgreSQL to local?

Question

I have a requirement that there are a lot of files (such as image, .csv) saved in a table hosted in Azure PostgreSQL. Files are saved as binary data type. Is it possible extract them directly to local file system by SQL query? I am using python as my programming language, any guide or code sample is appreciated, thanks!

Answer 1

If you just want to extract binary files from SQL to local and save as a file, try the code below:

import psycopg2
import os

connstr = "<conn string>"
rootPath = "d:/"

def saveBinaryToFile(sqlRowData):
    destPath = rootPath + str(sqlRowData[1]) 

    if(os.path.isdir(destPath)):
        destPath +='_2'
        os.mkdir(destPath)
    else:
        os.mkdir(destPath)
        
    
    newfile = open(destPath +'/' + sqlRowData[0]+".jpg", "wb");
    newfile.write(sqlRowData[2])
    newfile.close

conn = psycopg2.connect(connstr)
cur = conn.cursor()
sql = 'select * from images'
cur.execute(sql)
rows = cur.fetchall()
print(sql)
print('result:' + str(rows))
for i in range(len(rows)):
    saveBinaryToFile(rows[i])

conn.close()

This is my sample SQL table :

Result: