Re: [SQLObject] PickleCol+SQLite size problem/question
On Thu, Nov 29, 2007 at 04:05:55AM +0100, Bart wrote: Ah, you inserted it with something other than SQLObject then fetched it with SQLObject? No. The program that I have posted is a complete program - it INSERTs a huge BLOB and fetches it back. Works for for both BLOBCol and PickleCol. I've been hacking in my sqlobject installation to convert it from literal to parameter style. I had been converting SQLObject to paramstyles queries in this branch: http://svn.colorstudy.com/home/phd/SQLObject/paramstyles/ but stopped working on it for two reasons: -- different DB API drivers have different problems converting some datatypes; some choked on mxDateTime, some on datetime, etc. -- Luke Opperman reworked SQLObject in the trunk unifying SQLObject.select(), sqlbuilder.Select() and dbConnection.queryForSelect(); by itself it is a great job (will be in SQLObject 0.10); but it is incompatible with what I was doing for parameterized queries, so I have to start almost from beginning. It seems to work, but there's probably a reason SQLObject uses literal instead of parametric queries There is one - datatypes problem. Ian Bicking started SQLObject long ago, when DB API drivers were even worse than now. It just occurred to me that for my project at least, it would probably be cleaner to just add my own wrapping code that just updates the database without SQLObject knowing about it. Don't forget about cache coherency problem. Oleg. -- Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN. - SF.Net email is sponsored by: The Future of Linux Business White Paper from Novell. From the desktop to the data center, Linux is going mainstream. Let it simplify your IT future. http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4 ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] PickleCol+SQLite size problem/question
On Thu, Nov 29, 2007 at 05:22:18AM +0100, Bart wrote: Argh, I just don't know what data format is supposed to be, unpickling keeps choking on the data. Can you write a short test script that demonstrate the problem? Oleg. -- Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN. - SF.Net email is sponsored by: The Future of Linux Business White Paper from Novell. From the desktop to the data center, Linux is going mainstream. Let it simplify your IT future. http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4 ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] PickleCol+SQLite size problem/question
On Thu, Nov 29, 2007 at 06:11:11PM +0100, Bart wrote:
I meant that I have no idea how to format the data I should be setting.
PickleCol stores pickle strings, and expects a pickle string back from
the DB. But PickleCol inherits BLOBCol, and hence with SQLite uses base64
to encode/decode pickles.
Given that, it's trivial to give you code that doesn't work :), say...
def set_data(id,data):
I rewrote your code this way:
#! /usr/bin/env python
import base64, pickle
from pysqlite2 import dbapi2 as sqlite
data = {test1: 1, test2: 2}
con = sqlite.connect(test.sqdb) #my own variable. It needed a place.
cur = con.cursor()
cur.execute(CREATE TABLE test (id INTEGER PRIMARY KEY, test BLOB))
pickledata = pickle.dumps(data,2)
cur.execute(INSERT INTO test (test) VALUES
(?),(base64.encodestring(pickledata),))
con.commit()
cur.close()
con.close()
And get the data back in SQLObject:
from sqlobject import *
from sqlobject.sqlbuilder import *
__connection__ =
sqlite:///home/phd/work/SQLObject/test-SQLObject/test.sqdb?debug=1
class Test(SQLObject):
test = PickleCol()
for row in Test.select():
print row.test
It prints:
1/Select : SELECT test.id, test.test FROM test WHERE 1 = 1
1/QueryR : SELECT test.id, test.test FROM test WHERE 1 = 1
1/COMMIT : auto
{'test1': 1, 'test2': '2'}
I.e., the data is unpickled successfully.
Well, you didn't import sqlobject, or connect to any backing database
so I it's not exactly runnable code either, nor does it seem to apply to
sqlite.
('course, SQLObject is slightly Weird when it comes to state and connections,
so maybe you're doing something interesting)
Nothing interesting. The code that I've skipped is:
from sqlobject import *
from sqlobject.sqlbuilder import *
__connection__ =
sqlite:///home/phd/work/SQLObject/test-SQLObject/test.sqdb?debug=1
You're saying 70MB
non-parametric data work in sqlite right now without BLOBs?
Works for me with BLOBCol and PickleCol, no problem.
But sqlite has a 1MB (default) query size limit
This is the default. It seems Debian changed the default.
Actually, I suppose the quickest short-term (but not so portable)
fix for me is to recompile sqlite.
Certainly. In any case SQLObject cannot work around builtin SQLite
limits.
Oleg.
--
Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED]
Programmers don't die, they just GOSUB without RETURN.
-
SF.Net email is sponsored by: The Future of Linux Business White Paper
from Novell. From the desktop to the data center, Linux is going
mainstream. Let it simplify your IT future.
http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4
___
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] PickleCol+SQLite size problem/question
On Sat, Oct 27, 2007 at 05:12:33AM +0200, Bart wrote: http://www.initd.org/tracker/pysqlite/wiki/SnippetsBlobs ...works for me when I hand it 500MB of data. 1. The code import sys infile = open(sys.argv[1], rb) data = infile.read() infile.close() class Images(SQLObject): image = BLOBCol() # or PickleCol, no difference... Images.createTable() image = Images(image=data) Images._connection.cache.clear() image = Images.get(1) outfile = open(test.dat, wb) outfile.write(image.image) outfile.close() works for me for files up to 100M, but for bigger files OS just kills python process due to MemoryError. But if I have enough memory I don't think there would be any problem with bigger data. At least I don't see a difference between 100M and 500M. 2. The difference between the snippet and the way SQLObject uses Binary is that the snippet uses parameter(s) and SQLObject generates query strings. Well, query strings work for 100M binary files... 3. Are you sure you really want to transfer 500M in one piece to and from a database? Wouldn't it be better to use files and store filenames in the DB? Oleg. -- Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN. - SF.Net email is sponsored by: The Future of Linux Business White Paper from Novell. From the desktop to the data center, Linux is going mainstream. Let it simplify your IT future. http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4 ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] PickleCol+SQLite size problem/question
On Nov 28, 2007 4:25 PM, Oleg Broytmann [EMAIL PROTECTED] wrote: On Sat, Oct 27, 2007 at 05:12:33AM +0200, Bart wrote: http://www.initd.org/tracker/pysqlite/wiki/SnippetsBlobs ...works for me when I hand it 500MB of data. 1. The code import sys infile = open(sys.argv[1], rb) data = infile.read() infile.close() class Images(SQLObject): image = BLOBCol() # or PickleCol, no difference... Images.createTable() image = Images(image=data) Images._connection.cache.clear() image = Images.get(1) outfile = open(test.dat, wb) outfile.write(image.image) outfile.close() works for me for files up to 100M, but for bigger files OS just kills python process due to MemoryError. But if I have enough memory I don't think there would be any problem with bigger data. At least I don't see a difference between 100M and 500M. A MemoryError seems to signal the underlying malloc decides you're out of system memory - that's your computer, unrelated to the code or problem. 2. The difference between the snippet and the way SQLObject uses Binary is that the snippet uses parameter(s) and SQLObject generates query strings. Well, query strings work for 100M binary files... You meant *don't* work for...? As I recall, the query size limit is/was as low as 64KB for one of the DB-API interfaces, which would mean that SQLObject-wise, the limiting factor to BLOB-support is the specific interface and backend, and unpredictable. 3. Are you sure you really want to transfer 500M in one piece to and from a database? Wouldn't it be better to use files and store filenames in the DB? 500MB was just a test case to be very sure it was being handled with blobs. My data is actually on the order of 1 to 3MB for the most complex data I've handled (largely picking overhead), and yes, I'm sure. In my app, the data is a write-once, read-often thing, so it's basically just a cleaner variation of the explicitly file-based solution, if slightly slower. The file solution would open me up to a list of potential path access, security, dangling-reference, and cleanup problems I don't want to think about or code around just now. It's more of a hack than a solution when storing pickled data and not *files* files. I figured I could use SQLObject as a quick-and-dirty object persistance system that would also save me from having to write a lot of dull database code, but it sounds a lot like my objects are too big for its BLOB support to handle in the current design, so I'll have to design my way around it, or code for pysqlite2 directly. It's a pity, but I suppose I'll live:) Well, thanks anyway, --Bart - SF.Net email is sponsored by: The Future of Linux Business White Paper from Novell. From the desktop to the data center, Linux is going mainstream. Let it simplify your IT future. http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4 ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] PickleCol+SQLite size problem/question
On Wed, Nov 28, 2007 at 10:53:05PM +0100, Bart wrote: 2. The difference between the snippet and the way SQLObject uses Binary is that the snippet uses parameter(s) and SQLObject generates query strings. Well, query strings work for 100M binary files... You meant *don't* work for...? Work. I slurped a file of 70M, put into the DB, got it back and put into test.dat file, then compared the original file with test.dat - no difference. So SQLObject (at least with SQLite/PySQLite2) *can* handle BLOBs/pickles of that size. Oleg. -- Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN. - SF.Net email is sponsored by: The Future of Linux Business White Paper from Novell. From the desktop to the data center, Linux is going mainstream. Let it simplify your IT future. http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4 ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] PickleCol+SQLite size problem/question
On Nov 28, 2007 11:09 PM, Oleg Broytmann [EMAIL PROTECTED] wrote: On Wed, Nov 28, 2007 at 10:53:05PM +0100, Bart wrote: 2. The difference between the snippet and the way SQLObject uses Binary is that the snippet uses parameter(s) and SQLObject generates query strings. Well, query strings work for 100M binary files... You meant *don't* work for...? Work. I slurped a file of 70M, put into the DB, got it back and put into test.dat file, then compared the original file with test.dat - no difference. So SQLObject (at least with SQLite/PySQLite2) *can* handle BLOBs/pickles of that size. Ah, you inserted it with something other than SQLObject then fetched it with SQLObject? That'd mean pysqlite2 is doing its job decoding blobs while fetching, which I don't think is too surprising; it's getting the data in there that's the problem. I've been hacking in my sqlobject installation to convert it from literal to parameter style. It seems to work, but there's probably a reason SQLObject uses literal instead of parametric queries, and it's a bit of an overhaul. It just occurred to me that for my project at least, it would probably be cleaner to just add my own wrapping code that just updates the database without SQLObject knowing about it. Always fun to figure out things like that after an hour or two of hacing, particularly when you're doing it in your site packages:) --Bart - SF.Net email is sponsored by: The Future of Linux Business White Paper from Novell. From the desktop to the data center, Linux is going mainstream. Let it simplify your IT future. http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4 ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] PickleCol+SQLite size problem/question
Argh, I just don't know what data format is supposed to be, unpickling keeps choking on the data. I can't make sense of the data that's in there - it's not a straight pickle string, it doesn't decode, nor does anything I encode look horribly like it. On top of that, I get a cPickle.UnpicklingError: invalid load key whenever sqlobject tries to unpickle buffer objects. Can you help with this? --Bart - SF.Net email is sponsored by: The Future of Linux Business White Paper from Novell. From the desktop to the data center, Linux is going mainstream. Let it simplify your IT future. http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4 ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] PickleCol+SQLite size problem/question
On Oct 27, 2007 10:32 AM, Oleg Broytmann [EMAIL PROTECTED] wrote: On Sat, Oct 27, 2007 at 05:12:33AM +0200, Bart wrote: http://www.initd.org/tracker/pysqlite/wiki/SnippetsBlobs Thank you. I will look at it... will try to find time to do experiments and see what is going on... Just mailing to inquire whether this got back-burnered, or perhaps solved while I wasn't paying attention. I just want to know whether I have to write my own ORM code or dive into SQLObject myself to make a current project work. They can be demanding, and all... Regards, --Bart - SF.Net email is sponsored by: The Future of Linux Business White Paper from Novell. From the desktop to the data center, Linux is going mainstream. Let it simplify your IT future. http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4 ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] PickleCol+SQLite size problem/question
On Fri, Oct 26, 2007 at 06:38:26PM +0200, Bart wrote: My question is another one - since by default SQLObject uses VARCHAR to support BLOBCol and PickleCol on SQLite, it's not likely to use its specifc BLOB API. We simply cannot - SQLObject is written in pure Python, and use DB API drivers, PySQLite in this case. It looks like pysqlite is up to using it, so the real question seems to be whether SQLObject can be made to use it, Is there any documentation for the API in PySQLite? Oleg. -- Oleg Broytmannhttp://phd.pp.ru/[EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN. - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
Re: [SQLObject] PickleCol+SQLite size problem/question
On 10/26/07, Oleg Broytmann [EMAIL PROTECTED] wrote: On Fri, Oct 26, 2007 at 06:38:26PM +0200, Bart wrote: My question is another one - since by default SQLObject uses VARCHAR to support BLOBCol and PickleCol on SQLite, it's not likely to use its specifc BLOB API. We simply cannot - SQLObject is written in pure Python, and use DB API drivers, PySQLite in this case. It looks like pysqlite is up to using it, so the real question seems to be whether SQLObject can be made to use it, Is there any documentation for the API in PySQLite? You don't need to go down to C, it seems pysqlite seems to use it (strictly making it a superset of the DB-API). That is to say, the insertion lines in the following: http://www.initd.org/tracker/pysqlite/wiki/SnippetsBlobs ...works for me when I hand it 500MB of data. It looks like the use of pysqlite2.Binary encases the data in a buffer object, and that pysqlite uses the buffer type to decide to use the blob api (not entirely sure, but the pysqlite usage guide seems to hint at this). But that is also the thing that had and has me confused - there are mentions of pysqlite.Binary in SQLObject's SQLite code, which is why I figured I was just missing something and not using SQLObject correctly, and the other option is that SQLObject has some catch-all code that means it acually doesn't. Or maybe someone never finished the feature. I'm having a hard time digging though the code to make sure, though, since pysqlite and SQLObject are by design fairly automagical with a good bunch of wrapping and aliasing - plus I remember pysqlite's adapters seemed overly complex a system back when I looked at them. (There are also other details, like that sqlite's duck typing seems to extends into the string/blob distinction, so the schema only matters so much -- though it would make sense to use BLOB in the schema for SQLObject to know when to use this specific blobbing) ...but you can probably answer all that a lot better:) Looking forward to where this goes, --Bart - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
