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? On Tuesday, February 26, 2013 2:55:36 PM UTC, Mauricio de Abreu Antunes wrote: > > 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 <javascript:>> > >> 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. >> >> >> >> -- >> 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 <javascript:>. >> To post to this group, send email to sqlal...@googlegroups.com<javascript:> >> . >> 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 > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@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.
