[Tutor] string pickling and sqlite blob'ing

[Dinesh B Vadhia]

Alan

On a machine with 6gb of ram, storing very long strings in sqlite caused a "sqlite3.OperationalError: Could not decode to UTF-8 column 'j' with text" which has been resolved.  This fix then caused a memory error when reading some of the strings back from the db.  Hence, I'm trying to work out what the problem is and looking for alternative solutions.  It is strange that I can insert a long string into sqlite but a memory error is caused when selecting it.  Splitting the strings into smaller chunks is the obvious solution but I need to sort out the above first since the post-processing after the select is on the entire string.

Dinesh


--------------------------------------------------------------------------------

Message: 3
Date: Thu, 25 Jun 2009 00:44:22 +0100
From: "Alan Gauld" <alan.gauld at btinternet.com>
To: tutor at python.org
Subject: Re: [Tutor] string pickling and sqlite blob'ing
Message-ID: <h1udoj$q17$1 at ger.gmane.org>
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
reply-type=original


"Dinesh B Vadhia" <dineshbvadhia at hotmail.com> wrote 

> I want to pickle (very long) strings and save them in a sqlite db.  

Why?
Why not just store the string in the database?
If that turns out to be a problem then think about other 
options - like splitting it into chunks say?
But until you know you have a problem don't try to 
solve it!

> - Is this a good approach for storing very long strings?  

Probably not.

> - Are the pickle'd strings stored in the sqlite db as a STRING or BLOB? 

They could be stored either way, thats up to how you define 
your tables and write your SQL.

In general I expect databases to handle very large quantities of data
either as blobs or as references to a file. Is this a valid approach? 
Write the long string (assuming its many MB in size) into a text 
file and store that with a unique name. Then store the filename 
in the database.

But first check that you can't store it in the database directly or 
in chunks.


-- 
Alan Gauld
Author of the Learn to Program web site
http://www.alan-g.me.uk/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20090625/372c3ae1/attachment-0001.htm>