Thread: [SQLObject] SQLite fails for binaries
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Joshua R. <Jos...@mi...> - 2003-09-07 01:14:56
|
In an attempt to store binary files in a database, I tried using the previously suggested method of base64 encoding them. Whereas this method works fine with PostgreSQL, it fails with SQLite. The string retrieved from the database is completely garbled from the (encoded) string entered into the database. I also tried using hexadecimal encoding with the same result. I have no idea what's causing this; is there anything I can do to work around this problem? In this instance I wish to avoid using a full RDBMS, and I was really hoping to be able to store some images in the database (both those for the same reason: at least for now I want all the data to reside in one file). I'm using python 2.3 and had this result with both the 0.4 and CVS versions of SQLObject. Ah, also, (I would respond to the proper message about this, but I wasn't subscribed when it was posted), concerning Firebird, there is a pdf at http://www.ibphoenix.com/downloads/qsg.pdf that seems to cover all the basics of creating databases, etc. (it's linked to via a small image to the left of the front page; it's not linked to under documentation or anything reasonable like that....) Joshua Rothenberg |
From: Ian B. <ia...@co...> - 2003-09-07 01:53:26
|
On Saturday, September 6, 2003, at 07:55 PM, Joshua Rothenberg wrote: > In an attempt to store binary files in a database, I tried using the > previously suggested method of base64 encoding them. Whereas this > method > works fine with PostgreSQL, it fails with SQLite. The string retrieved > from the database is completely garbled from the (encoded) string > entered > into the database. I also tried using hexadecimal encoding with the > same > result. I have no idea what's causing this; is there anything I can do > to > work around this problem? In this instance I wish to avoid using a full > RDBMS, and I was really hoping to be able to store some images in the > database (both those for the same reason: at least for now I want all > the > data to reside in one file). I'm using python 2.3 and had this result > with both the 0.4 and CVS versions of SQLObject. From reading a little documentation, it seems that SQLite cannot have binary data because it uses null-terminated strings. So, encoding is the only right way. But then you tried base64 encoding. I'm surprised that didn't work for you. Can you give a snippet of the code you were using? It should be quite reliable, so long as you are properly encoding and decoding the result, and base64-encoded values should be quite safe for insertion. Is there a possibility the data is being corrupted before you put it into the database? You might test that: image = (wherever you are getting images from) soinstance.image = image assert soinstance.image == image If that assert works, then the encoding is working. > Ah, also, (I would respond to the proper message about this, but I > wasn't > subscribed when it was posted), concerning Firebird, there is a pdf at > http://www.ibphoenix.com/downloads/qsg.pdf that seems to cover all the > basics of creating databases, etc. (it's linked to via a small image to > the left of the front page; it's not linked to under documentation or > anything reasonable like that....) Thanks, I'll look at that too. It'll all probably work well once I get it set up, but it's been a bit of a pain so far. People don't give proper credit (or blame) for Open Source's general small innovations in creating easily administered software. Anything proprietary (or in this case formally proprietary) has lots of difficulties in administration. Ian |
From: Joshua R. <jos...@mi...> - 2003-09-07 02:22:25
|
On Sat, 06 Sep 2003 21:53:27 -0400 "Ian Bicking" <ia...@co...> wrote: > image = (wherever you are getting images from) > soinstance.image = image > assert soinstance.image == image All right, I added an assert to my wrapper function: def set_img(filename, data, refpage): encodedData = base64.encodestring(data) newImage = Image.new(name = filename, content = encodedData, page = get_byname(refpage)) assert newImage.content == encodedData and the assertion fails.. Here, I'll post the rest of the file: from SQLObject import * import base64 __connection__ = SQLiteConnection('db.db') class record: def __init__(self,name,content): self.name=name self.content=content class Image(SQLObject): name = StringCol(alternateID=True) content = StringCol() page = ForeignKey('Page') class Page(SQLObject): title = StringCol(alternateID=True) content = StringCol() image = MultipleJoin('Image') Image.createTable(ifNotExists=True) Page.createTable(ifNotExists=True) def get_byname(name): return Page.byTitle(name) def get_search(name): return Page.select(LIKE(Page.q.title,('%' + name + '%'))) def set_upd(rec,oldtitle): Page.byTitle(oldtitle).set(title = rec.name, content = rec.content) def set_new(rec): Page.new(title = rec.name, content = rec.content) def set_del(name): Page.byTitle(name).destroySelf() def del_img(name): Image.byName(name).destroySelf() def get_img(name): return base64.decodestring(Image.byName(name).content) def set_img(filename, data, refpage): encodedData = base64.encodestring(data) newImage = Image.new(name = filename, content = encodedData, page = get_byname(refpage)) assert newImage.content == encodedData def get_imgsearch(name): return Image.select(Image.q.name==name) |
From: Ian B. <ia...@co...> - 2003-09-07 02:55:29
|
On Saturday, September 6, 2003, at 09:35 PM, Joshua Rothenberg wrote: > On Sat, 06 Sep 2003 21:53:27 -0400 > "Ian Bicking" <ia...@co...> wrote: >> image = (wherever you are getting images from) >> soinstance.image = image >> assert soinstance.image == image > > All right, I added an assert to my wrapper function: > > def set_img(filename, data, refpage): > encodedData = base64.encodestring(data) > newImage = Image.new(name = filename, content = encodedData, page = > get_byname(refpage)) > assert newImage.content == encodedData > > and the assertion fails.. > Here, I'll post the rest of the file: > > from SQLObject import * > import base64 > > __connection__ = SQLiteConnection('db.db') > > class record: > def __init__(self,name,content): > self.name=name > self.content=content > > class Image(SQLObject): > name = StringCol(alternateID=True) > content = StringCol() > page = ForeignKey('Page') > You should add: def _set_content(self, value): self._SO_set_content(base64.encodestring(value)) def _get_content(self): return base64.decodestring(self._SO_get_content()) I'm not sure where the problem is, but this will make the whole thing less error-prone. Ian |
From: Joshua R. <jos...@mi...> - 2003-09-07 04:22:29
|
On Sat, 06 Sep 2003 22:55:31 -0400 "Ian Bicking" <ia...@co...> wrote: > You should add: > > def _set_content(self, value): > self._SO_set_content(base64.encodestring(value)) > def _get_content(self): > return base64.decodestring(self._SO_get_content()) > > I'm not sure where the problem is, but this will make the whole thing > less error-prone. Well having made that change and tested various things extensively I still have absolutely no clue what's going on, but now when I try to read the img data it tells me "Error: Incorrect padding"... It looks like, among other things, \n's are being turned into \\n's and not being converted back properly (this is from using short strings instead of full images; everything just seems to get garbled with big images).. And it still works properly under PostgreSQL. I have no idea if the problem is with my code, SQLObject's SQLite interface, PySQLite, or SQLite. Although since it *does* work in other database systems its probably one of the latter three. Hasn't anyone had this problem before? Joshua Rothenberg |
From: Edmund L. <el...@in...> - 2003-09-07 05:28:31
|
Joshua Rothenberg wrote: > when I try to > read the img data it tells me "Error: Incorrect padding"... It looks > like, among other things, \n's are being turned into \\n's and not > being converted back properly (this is from using short strings > instead of full images; Just another data point (and a rather useless one at that)--I've seen exactly the same thing even though I use Postgresql exclusively. I don't remember the exact circumstances when this occurred, other than it was when I was dealing with encoded binary values too. ...Edmund. |
From: Ian B. <ia...@co...> - 2003-09-07 06:05:35
|
On Saturday, September 6, 2003, at 11:36 PM, Joshua Rothenberg wrote: > On Sat, 06 Sep 2003 22:55:31 -0400 > "Ian Bicking" <ia...@co...> wrote: > >> You should add: >> >> def _set_content(self, value): >> self._SO_set_content(base64.encodestring(value)) >> def _get_content(self): >> return base64.decodestring(self._SO_get_content()) >> >> I'm not sure where the problem is, but this will make the whole thing >> less error-prone. > > Well having made that change and tested various things extensively I > still have absolutely no clue what's going on, but now when I try to > read the img data it tells me "Error: Incorrect padding"... It looks > like, among other things, \n's are being turned into \\n's and not > being converted back properly (this is from using short strings > instead of full images; everything just seems to get garbled with big > images).. And it still works properly under PostgreSQL. I have no idea > if the problem is with my code, SQLObject's SQLite interface, > PySQLite, or SQLite. Although since it *does* work in other database > systems its probably one of the latter three. Hasn't anyone had this > problem before? Hmm... you're right, SQLite is having some problem with quoting. It seems backslash quoting just doesn't work in SQLite. Perhaps because ' is quoted as '', and all other characters are allowed in the literal without quoting. Except \0, which isn't allowed anywhere. Hrm. As a stop-gap, I think you can simply remove all the \n's from the base64-encoded string, and it's still valid base64 (whitespace is ignored). I was hoping some of this database portability stuff could be avoided because database would all act the same (like string literal quoting). But maybe that's not the case. Now... introduce portability in the Converters library, or use DBAPI parameters and let the driver do the quoting? Ian |
From: Nicholas L. <nic...@pl...> - 2003-09-07 06:41:07
|
On Sun, Sep 07, 2003 at 12:36:01AM -0400, Joshua Rothenberg wrote: > images).. And it still works properly under PostgreSQL. I have no > idea if the problem is with my code, SQLObject's SQLite interface, > PySQLite, or SQLite. Although since it *does* work in other database > systems its probably one of the latter three. Hasn't anyone had this > problem before? Have you tried encoding a file to text file, then running an insert from the sqlite cmd-line tool? doing a select, then checking the two results. Then the same with straight PySQLite, and no SQLO. -- Nicholas Lee - nj.lee at plumtree.co dot nz, somewhere on the fish Maui caught. gpg. 8072 4F86 EDCD 4FC1 18EF 5BDD 07B0 9597 6D58 D70C icq. 1612865 Quixotic Eccentricity |
From: Javier R. <javier_ruere@HotPOP.com> - 2003-09-07 23:54:02
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Joshua Rothenberg wrote: > > Well having made that change and tested various things extensively I still have absolutely no clue what's going on, but now when I try to read the img data it tells me "Error: Incorrect padding"... It looks like, among other things, \n's are being turned into \\n's and not being converted back properly (this is from using short strings instead of full images; everything just seems to get garbled with big images).. And it still works properly under PostgreSQL. I have no idea if the problem is with my code, SQLObject's SQLite interface, PySQLite, or SQLite. Although since it *does* work in other database systems its probably one of the latter three. Hasn't anyone had this problem before? > > Joshua Rothenberg Joshua, I had the same problem some time ago. Make your getter function use unQuote. def _get_stuff(self): def unQuote(val): # This list is from SQLObject/Converters.py. sqlStringReplace = [ ('\\', '\\\\'), ('\'', '\\\''), ('\000', '\\0'), ('\b', '\\b'), ('\n', '\\n'), ('\r', '\\r'), ('\t', '\\t') ] for final, original in sqlStringReplace: val = val.replace(original, final) return val # Using pickle instead of base64. # I don't think it makes a difference. from cPickle import loads return loads(unQuote(self._SO_get_valor())) -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE/W8Vj8XQC840MeeoRAtP2AJ0QTtkjvr6m6+RWFaaaFKjRVg5XtQCeJvjx EfLO6arjeiADPPnDdxBzvIk= =DPch -----END PGP SIGNATURE----- |
From: Joshua R. <ad...@my...> - 2003-09-08 00:18:04
|
Thanks. Your function has solved my problem. Joshua Rothenberg ad...@my... |