0

After looking around, I wrote this insertion/retrieval code to read in a .mat file, send it off to a bytea postgres database column, and then try and retrieve it and recreate the file.

I use psycopg2 for database interaction.

Insertion:

    full_file_path = os.path.join( folder_path, single_file )
    f = open(full_file_path,'rb')
    file_data = psycopg2.Binary( f.read() )

    cur.execute( "INSERT INTO file_data_table "
        "( id, file_name, file_data, insertion_date) "
        "VALUES ( DEFAULT, %s, %s, %s)",
        (single_file, file_data, timestamp))

    f.close()
    conn.commit()
    print single_file + " inserted"

Trying to retrieve and save it to file( the file_name is "something.mat" )

cur = conn.cursor()

cur.execute( "SELECT encode( file_data, 'hex' ), file_name FROM file_data_table")
result = cur.fetchall()

print result[0][0]

for row in result:
    print row[1]
    full_file_path = os.path.join(folder_path, row[1])
    f = open(full_file_path,'w')
    f.write(row[0])
    f.close()

It retrieves data from the database and successfully saves it in a file, but the file doesn't open as a mat file, and the file size is much larger( roughly twice as big ) as the original file I tried to store in the database.

I assume some data transformation is happening that I am not handling properly.

Any assistance is greatly appreciated.

Carthage
  • 87
  • 1
  • 7
  • 1
    I think this sample would work fine without the encode( file_data, 'hex' ) business -- just select file_data directly, and write it out as you are doing now. – Josh Kupershmidt Feb 04 '15 at 22:26

1 Answers1

0

Using Josh Kupershmidt's tip, I figured out what was wrong.

By removing the encode and converting the retrieved buffer to a string, it seems to work now.

cur = conn.cursor()

cur.execute( "SELECT file_data, file_name FROM file_data_table")
result = cur.fetchall()

print result[0][0]

for row in result:
    print row[1]
    full_file_path = os.path.join(folder_path, row[1])
    f = open(full_file_path,'w')
    f.write(str(row[0]))
    f.close()
Carthage
  • 87
  • 1
  • 7