Re: blob problems in pysqlite

[Gerhard Häring]

[EMAIL PROTECTED] wrote:
Excellent. Got that working. Now, how to get the pickled data out of
the database?
I'm trying to use cPickle.loads(data) (code attached), and I get a:
"TypeError: loads() argument 1 must be string, not list" [...]
[...]
c.execute("select Images from FileURLInfo where URL= ?;", (DBURL,))
KnownFilesResult = c.fetchall()
print KnownFilesResult    #where I get a r/w buffer, as expected

No, you actually get a list of 1-tuples and each tuple has one entry: a 
read-write buffer. Because of the way you filled the table, the list is 
of length 1.

cPickle.loads(KnownFilesResult)    #where I get the error described
above.

cPickle.loads will only accept a string, not a buffer, so you need to 
convert the buffer to a string first.

So, if you want your code to work, you can use

print cPickle.loads(str(KnownFilesResult[0][0]))

FWIW there are certainly much better ways to solve the task you're 
solving here. Because right now you're pickling and unpickling data into 
a single table in a relational database. Like you're doing this, this 
buys you nothing *and* you get the complexity of relational databases 
and object (de)marshaling.

If you want to go the relational way, you can create multiple tables and 
using foreign-key relations between them instead of stuffing lists into 
columns of a single table by pickling them.

I've attached an example script that can perhaps inspire you.

-- Gerhard
from pysqlite2 import dbapi2 as sqlite

con = sqlite.connect(":memory:")
cur = con.cursor()

# Create schema

cur.executescript("""
    create table page (
      page_id integer primary key,
      page_url text
    );

    create table image (
      page_id integer references page(page_id),
      image_url text,
      imagedata blob
    );
    """)

# Insert example data

import urllib

test_data = {
    "http://python.org/"; : [
        "http://python.org/images/python-logo.gif";,
        "http://python.org/images/success/nasa.jpg";
    ],
    "http://ruby-lang.org/": [
        "http://www.ruby-lang.org/image/title.gif";
    ]
}

for url, img_url_list in test_data.items():
    cur.execute("insert into page(page_url) values (?)", (url,))
    page_id = cur.lastrowid

    for img_url in img_url_list:
        image_data = urllib.urlopen(img_url).read()
        cur.execute(
            "insert into image(page_id, image_url, imagedata) values (?, ?, ?)",
            (page_id, img_url, sqlite.Binary(image_data)))

    # We have a consistent state here, so we commit
    con.commit()

# Show the data

cur.execute("""
    select page_url, image_url, imagedata
    from page inner join image using (page_id)
    order by page_url
    """)
for page_url, image_url, imagedata in cur:
    print "page_url=", page_url
    print "image_url=", image_url
    print "len(imagedata)=", len(imagedata)
    print "-" * 50

cur.close
con.close()
-- 
http://mail.python.org/mailman/listinfo/python-list