Re: blob problems in pysqlite
Woohoo! You rock, Gerhard. That's inspired. I'm sure I can sort this out now. Both you and Tim have been an enormous help. Cheers, Al. (Oh, and Tim: tip noted. I feel bad about not posting complete code- I do normally (not in python), but can't for this one. Thanks for wading your way through it anyway.) -- http://mail.python.org/mailman/listinfo/python-list
Re: blob problems in pysqlite
[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
RE: blob problems in pysqlite
[EMAIL PROTECTED] | I'm trying to use cPickle.loads(data) (code attached), and I get a: | "TypeError: loads() argument 1 must be string, not list" | | Is there a workaround for this? I've tried converting the | List of files | to a String before cPickling it, but the same result (expected). OK, This one looks more straightforward. The result of fetchall () is a list of tuples (even if it is a list of one tuple and even if that tuple has only one element). This means that KnownFilesResult is a list. cPickle.loads wants a string. You can, obviously, convert the list to a string, but that would only result in something like "[(blah,), (blah,)]" which is clearly not what you want. Depending on what you think you're going to get back, you either need to use fetchone in place of fetchall, which will return one tuple -- in this case containing one buffer -- or you need to iterate over the list, or unpack it, again depending on your expectation of the data. Here, something like this fragment should work (untested): . . # Iterate over the result set and unpack # each tuple into its elements. KnownFilesResult = c.fetchall () for row in KnownFilesResult: (pickled_result,) = row unpickled_result = cPickle.loads (pickled_result) # do things with unpickled result | ps. Tim: Your clean take on my code made me realise that I | had stupidly included quotes in the URL argument passed to | sqlite ... It's the little things that matter... By the way, it's a lot easier for others (like me) to help if you post a self-contained code sample. In both cases, while your explanation has been lucid enough for me to see / guess what's going one, I've had to add bits to your posted code so that they work. It helps if you add enough to the top of the code to -- in this case -- create the table you're using so that all anyone has to do is to cut-and-paste the code into an interpreter. (I realise that I haven't actually done this in return this time, but that's partly because it might obscure the point I'm trying to make and partly because it's a drop-in replacement for your own code). TJG This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- http://mail.python.org/mailman/listinfo/python-list
Re: blob problems in pysqlite
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"
Is there a workaround for this? I've tried converting the List of files
to a String before cPickling it, but the same result (expected). I've
read, in a post of Gerhard's somewhere, about marshal, but I don't see
how this would work for the HTML. (where I have the same problem.)
Cheers, Al.
ps. Tim: Your clean take on my code made me realise that I had stupidly
included quotes in the URL argument passed to sqlite, ie I'd ended up
with, effectively, DBURL= '"http://www.myhomepage.com";', rather than =
'http://www.myhomepage.com'. It's the little things that matter...
Thanks for your help. Oh, and your 'representative samples' are spot
on!
#code starts
URL = "http://www.myhomepage.com";
db = sqlite.connect("ImageInfoDatabase.db")
c = db.cursor()
DBURL = str(URL)
print DBURL
c.execute("select Images from FileURLInfo where URL= ?;", (DBURL,))
KnownFilesResult = c.fetchall()
print KnownFilesResult#where I get a r/w buffer, as expected
cPickle.loads(KnownFilesResult)#where I get the error described
above.
#code ends.
--
http://mail.python.org/mailman/listinfo/python-list
RE: blob problems in pysqlite
[EMAIL PROTECTED]
| I'm a long-time lurker and (I think) first time poster.
| Only relatively new to python, and I'm trying to get pysqlite to work
| with binary data, and having a tough time of it.
| I want to set up a table with:
| - a URL,
| - some filenames related to that URL,
| - and some simple generated HTML.
|
| Problem is, when I try to do this, and query, say, the filenames from
| the filename field 'Images', I'm not getting a result. Just []...
| I've been googling this one for days (documentation for this seems
| really scant), and I've tried a whole bunch of things, but my code as
| it is now is attached.
Just to confirm, I slightly reformatted your
code so that it would run without your data,
guessing from your comments what the input
data would be, and it ran fine. I'm a little
bemused at the way you're doing things, but as
Gerhard said elsewhere, you don't seem to be
doing anything wrong.
import cPickle
from pysqlite2 import dbapi2 as sqlite
#
# TJG: Added sample inputs, hopefully representative
#
# outputHTML is a standard html page
OutputHTML = ""
# DBfileList is a list of filenames in the form ['XXX.jpg', 'XXX.jpg'
etc]
DBfilelist = ['xxx.jpg', 'yyy.jpg']
HTMLoutputFile = open('ImageResults.html', 'wb')
cPickle.dump(OutputHTML, HTMLoutputFile)
HTMLoutputFile.close()
DBfilelistFile = open('DBFilesList.txt', 'wb')
cPickle.dump(DBfilelist, DBfilelistFile)
DBfilelistFile.close()
DBURL = 'http://www.myhomepage.html'
blobdata = open('ImageResults.html', 'rb').read()
blobfiles = open('DBFilesList.txt', 'rb').read()
#
# TJG: Used :memory: db to simplify slightly
#
db = sqlite.connect(":memory:")
c = db.cursor()
c.execute("create table FileURLInfo (URL CHAR(100), Images, HTML)")
c.execute("INSERT INTO FileURLInfo VALUES (?,?,?);", (DBURL,
sqlite.Binary(blobfiles), sqlite.Binary(blobdata)),)
c.execute("select Images from FileURLInfo where URL =
'http://www.myhomepage.html'",)
DBImageResult = c.fetchall()
print DBImageResult
gives me:
[(,)]
Can you clarify a bit, give some sample of your data, etc?
TJG
This e-mail has been scanned for all viruses by Star. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
--
http://mail.python.org/mailman/listinfo/python-list
Re: blob problems in pysqlite
[EMAIL PROTECTED] wrote: > Hi there. > > I'm a long-time lurker and (I think) first time poster. > Only relatively new to python, and I'm trying to get pysqlite to work > with binary data, and having a tough time of it. [...] It seems to me that you're using pysqlite correctly. Where exactly is the problem? Is the fetchall() not delivering what you think it should? If so, please explain what exactly it yields, and what you expect it to yield. -- Gerhard -- http://mail.python.org/mailman/listinfo/python-list
