Storing and Retrieving BLOB in SqlAlchemy

[dalia]

Hi,

My intention is - to store .xls, .doc and .pdf files (with images in them) to store in a Oracle database and retrieve them. I'm using SQLAlchemy declarative code. The problem is, the data gets stored in the database but when I try to fetch the data back, it comes out as 0KB. I'm explaining the code and actions below -

The model -

class ReportFiles(Base):
  __tablename__ = 'report_files'
  id = Column(Integer, primary_key=True)
  report_file = Column(BLOB)
  file_name = Column(Unicode(50))


Storing in database -

content = cStringIO.StringIO()
def store_in_db():
   session = meta.Session()  
   self._get_file(content)     ### This is a function which generates the file (.xls / .doc / .pdf) in the content
   contentstr = content.getvalue()
   tbl = model.ReportFiles()
   tbl.file_name = "test file"
   tbl.report_file = contentstr
   session.add(tbl)
   session.commit()

After the store, if I query the database, I get this result - not sure if it has stored alright -

select file_name, length(report_file) from report_files;

Name   |  Length
--------------------------
test file |  5632

select file_name, report_file from report_files;

Name | file
------------------
test file | (BLOB)

This doesn't look right because instead of the file, it just shows (BLOB)

Retrieving from database - now I want the file to be downloaded as an excel (.xls) file

def  _get_report(self):
  tbl = model.ReportFiles
  file = meta.Session.query(tbl).get(id=1)
  contentstr = file.report_file
  response.content_type = 'application/vnd.ms-excel; charset=utf-8'
  response.headers['Content-disposition'] = 'attachment; filename = %s' %file.file_name
  response.content_length = len(contentstr)
  print "Length = %s" %len(contentstr)
  return contentstr


This downloads the .xls file fine. The print statement prints the file size as 5632. The download dialogue says the file size is 5.5KB. But when I open the file, it says 'file type not in right format'. When I open it in notepad, it is a 0 KB file. When I list the file in the directory, it is a 0KB file.

Can somebody please tell me what is wrong here? Looks like the file doesn't get stored properly? Or am I missing something obvious? Or do I need to handle BLOB types differently?

Many Thanks.

 

[Mauricio de Abreu Antunes]

Well,

I think you need to import BLOB from dialects like:

from sqlalchemy.dialects.oracle import BLOB

in your Model you can describe the field type following it:

MyBLOBColumn = Column(BLOB)

Actually, IMHO i don't think saving large contents (binary from binary files) as pure text in the database is the best option.

2013/2/26 dalia <dali...@gmail.com>

 

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--

Mauricio de Abreu Antunes

Mobile: (51)930-74-525

Skype: mauricio.abreua

[dalia]

Hi Mauricio,

Thanks for the reply. But importing BLOB specifically did not solve the problem. It is the same. In the INSERT statement, it looks like it is storing the file in Binary, but then when I query the database, it just shows (BLOB) and not the data.

My requirement is to store .xls, .pdf and .doc files which contain images inside. The maximum file size can go up to 500KB. The file I'm testing with is a .xls file (size 5.5KB).

That's why I chose to store them in a BLOB type. What do you suggest?

[Mauricio de Abreu Antunes]

Well,

I would store the pshysical file in a system folder. After this I would save the path to the file in a table.

Then you could manage your files via sqlalchemy, listing all files and working with them properly.

2013/2/26 dalia <dali...@gmail.com>

[Werner]

Hi Dalia,

On 26/02/2013 17:15, dalia wrote:

   self._get_file(content)     ### This is a function which generates the file (.xls / .doc / .pdf) in the content

What are you getting back from _get_file?  Is it in a format you can store in the db column and then restore it to a file?

Doing a bit  of googling I found this on stackoverflow which looks relatively simple, i.e. he suggest to use "open" with the 'rb' and 'wb' flag for reading and writing.

http://stackoverflow.com/questions/3379166/writing-blob-from-sqlite-to-file-using-python

You should be able to just use the non sqlite3 part of this, i.e. something like:

with open("yourfilename", "rb") as input_file:
    tbl.report_file = input_file.read()



with open("Output.bin", "wb") as output_file:
    output_file.write(tbl.report_file)

I personally just store the file name/path and keep the data on the disk, but there are obviously reasons to put it in a db.  You might want to read some of the other posts on stackoverflow, e.g.:

http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay

Werner

[Michael Bayer]

On Feb 26, 2013, at 9:44 AM, dalia <dali...@gmail.com> wrote:
> Retrieving from database - now I want the file to be downloaded as an excel (.xls) file
>
> def _get_report(self):
> tbl = model.ReportFiles
> file = meta.Session.query(tbl).get(id=1)
> contentstr = file.report_file
> response.content_type = 'application/vnd.ms-excel; charset=utf-8'
> response.headers['Content-disposition'] = 'attachment; filename = %s' %file.file_name
> response.content_length = len(contentstr)
> print "Length = %s" %len(contentstr)
> return contentstr
>
>
> This downloads the .xls file fine. The print statement prints the file size as 5632. The download dialogue says the file size is 5.5KB. But when I open the file, it says 'file type not in right format'. When I open it in notepad, it is a 0 KB file. When I list the file in the directory, it is a 0KB file.

two things:

1. when you open the file from the filesystem using the Python open() call, in preparation for persisting it to the database, make sure you're using binary mode, open("myfile.xls", "rb"), because you're on windows. It's possible the files are being stored with modified line endings otherwise.

2. the "notepad" application isn't appropriate for displaying an .xls file.

[dalia]

Hi Mauricio, Werner, Michael,

Thank you for your replies. I understand storing the files in file system might be an easier option, but that would be my last option. If I can make it work with the database, I've got good reasons to go with it.

I'm not trying to store an exisitng physical file into database. The file gets written dynamically.

From self._get_file(content), I get back a cStringIO.StringIO object.

content = cStringIO.StringIO()

fw = csv.writer(content, delimiter=',')

fw.writerow(somerecord)

So this is the object I get back and trying to store content.getvalue()  into the database.

in the Insert statement, I could see it is trying to store some binary. And it commits without any error.

[Simon King]

I don't know what web framework you are using, but in pyramid I think
you would set the "response.body" attribute to your contentstr, rather
than returning it. Does that make any difference?

Simon

[dalia]

Thanks a lot Simon. I am using Pylons, but response.body actually worked. I can get my xls file alright now.

Thanks for all your help!