Storing objects in relational database

[nayden]

I started playing with python a few weeks ago after a number of years
of perl programming and I can say that my first impression is,
unsurprisingly, quite positive. ;)
The reason I am writing here is that I can't seem to figure out how to
save/restore python objects into a relational database. The way I
used to do it in perl was to 'freeze' the object before storing it
into the database and 'thaw' it before restoring it. (For those not
familiar with the perl terminology freeze and thaw are method from
perl's persistence module Storable). I found that the python's
corresponding module is Pickle but it doesn't seem to work for me.. I
do roughly the following:
class TestA:


def insert():
i = TestA('asdf')
output = cStringIO.Strin gIO()
cPickle.dump(i, output, 2)
print "output.getvalu e(): %s" % output.getvalue ()
jd = libpq.PgQuoteBy tea(output.getv alue())
print "jd %s" % jd
return dbpool.runOpera tion("insert into jobs (effective_job_ id,
job_description ) values (1, " + jd + ")")

[nayden]

Re: Storing objects in relational database

and then I try to restore the object with the following code

def success(rv):
print "success"
str = cStringIO.Strin gIO(libpq.PgUnQ uoteBytea(rv[0][0]))
i = cPickle.load(st r)
i.toString()

the execution fails just after the print statement, and I am not quite
sure why is that.

I would love to find out what people are using when they need to do
something similar --
perhaps I am trying to do it the perl way, while there is an elegant
python solution.

thanks

[bruno.desthuilliers@gmail.com]

Re: Storing objects in relational database

On 23 mai, 23:14, nayden <nay...@gmail.c omwrote:

and then I try to restore the object with the following code
>
def success(rv):
print "success"
str = cStringIO.Strin gIO(libpq.PgUnQ uoteBytea(rv[0][0]))
i = cPickle.load(st r)
i.toString()
>
the execution fails just after the print statement, and I am not quite
sure why is that.

Please reread the doc for pickle.dump, pickle.dumps, pickle.load and
pickle.loads. You just don't need StringIO here, just use the 's
versions of the functions.

I would love to find out what people are using when they need to do
something similar
perhaps I am trying to do it the perl way, while there is an elegant
python solution.

I don't know if you'd label it 'elegant', but as far as I'm concerned,
storing serialized objects as blobs in a relational database is mostly
non-sense. If I use a relational database, it's because it is a
*relational* database. If you want an OODB, then we have the ZODB,
Durus and a couple others.

[alex23]

Re: Storing objects in relational database

On May 24, 7:14 am, nayden <nay...@gmail.c omwrote:

the execution fails just after the print statement, and I am not quite
sure why is that.

It's often helpful to include the traceback, or at the very least the
last 3-4 lines of it, as it helps everyone work out the issue you're
having.

If you're not sure which line in a function is causing the issue, try
commenting out all but the first, run-and-test, re-add the next, run-
and-test etc

But the error is most likely this line:

i = cPickle.load(st r)

cPickle.load unpickles from a file, but here you're handing it a
string. You want cPickle.loads.

At the interpreter, you can always quickly check these out by looking
up the docstring via 'help(cPickle.l oads)' (or 'cPickle.loads? ' if
you're using iPythhon).

- alex23

[Jerry Hill]

Re: Storing objects in relational database

On Fri, May 23, 2008 at 5:07 PM, nayden <nayden@gmail.c omwrote:

The reason I am writing here is that I can't seem to figure out how to
save/restore python objects into a relational database.

Here's a basic version using the sqlite bindings included with Python 2.5:

import sqlite3, pickle

thing = {'date': '2008-05-21',
'event': 'Something happened this day'}

conn = sqlite3.connect (':memory:')
c = conn.cursor()
c.execute('''cr eate table stuff (idx integer, data text)''')
c.execute('''in sert into stuff values (?,?)''', (1, pickle.dumps(th ing)))
c.execute('''se lect data from stuff where idx=1''')
row = c.fetchone()
# sqlite3 stores text fields as unicode, so we need to encode to ascii
# before we unpickle
pickle_str = row[0].encode('ascii' )
thing2 = pickle.loads(pi ckle_str)

print thing
print thing2

Maybe that will set you on the right track.

--
Jerry

[bukzor]

Re: Storing objects in relational database

On May 23, 7:00 pm, alex23 <wuwe...@gmail. comwrote:

On May 24, 7:14 am, nayden <nay...@gmail.c omwrote:
>

the execution fails just after the print statement, and I am not quite
sure why is that.

>
It's often helpful to include the traceback, or at the very least the
last 3-4 lines of it, as it helps everyone work out the issue you're
having.
>
If you're not sure which line in a function is causing the issue, try
commenting out all but the first, run-and-test, re-add the next, run-
and-test etc
>
But the error is most likely this line:
>

    i = cPickle.load(st r)

>
cPickle.load unpickles from a file, but here you're handing it a
string. You want cPickle.loads.
>
At the interpreter, you can always quickly check these out by looking
up the docstring via 'help(cPickle.l oads)' (or 'cPickle.loads? ' if
you're using iPythhon).
>
- alex23

It's not a string it's a cStringIO.Strin gIO, even though his variable
name is confusing.

nayden: 'str' is a built-in variable that is the string type. Try this
for different values of x:
type(x) is str
str(x)
When you override it, it may be confusing down the line.

I'd suggest installing pychecker, which will help you catch errors
like this:

http://pychecker.sourceforge.net/

[Ville M. Vainio]

Re: Storing objects in relational database

"bruno.desthuil liers@gmail.com " <bruno.desthuil liers@gmail.com writes:

I don't know if you'd label it 'elegant', but as far as I'm
concerned, storing serialized objects as blobs in a relational
database is mostly non-sense. If I use a relational database, it's
because it is a *relational* database. If you want an OODB, then we
have the ZODB, Durus and a couple others.

.... not to forget object-relational mappers like SQLAlchemy, SQLObject...

[Piotr Chamera]

Re: Storing objects in relational database

bruno.desthuill iers@gmail.com pisze:

I don't know if you'd label it 'elegant', but as far as I'm concerned,
storing serialized objects as blobs in a relational database is mostly
non-sense. If I use a relational database, it's because it is a
*relational* database. If you want an OODB, then we have the ZODB,
Durus and a couple others.

It is sometimes convenient to store objects in mature relational
database backend (reliability, stability, support, tools,
replication, etc.). See latst efforts with RelStorage backend
for ZODB (http://wiki.zope.org/ZODB/RelStorage) - it stores
pickled Python objects in Oracle, PostgreSQL or MySQL)

[Bruno Desthuilliers]

Re: Storing objects in relational database

Ville M. Vainio a écrit :

"bruno.desthuil liers@gmail.com " <bruno.desthuil liers@gmail.com writes:
>
>

>I don't know if you'd label it 'elegant', but as far as I'm
>concerned, storing serialized objects as blobs in a relational
>database is mostly non-sense. If I use a relational database, it's
>because it is a *relational* database. If you want an OODB, then we
>have the ZODB, Durus and a couple others.

>
... not to forget object-relational mappers like SQLAlchemy, SQLObject...

Which are more IMHO another way to "bridge" RBDMS with the programming
language than a way to persist objects.

[nayden]

Re: Storing objects in relational database

Thanks everyone for the helpful suggestions.

[bruno.desthuilliers@gmail.com]

Re: Storing objects in relational database

On 24 mai, 13:01, Piotr Chamera <piotr_cham...@ poczta.onet.plw rote:

bruno.desthuill i...@gmail.com pisze:
>

I don't know if you'd label it 'elegant', but as far as I'm concerned,
storing serialized objects as blobs in a relational database is mostly
non-sense. If I use a relational database, it's because it is a
*relational* database. If you want an OODB, then we have the ZODB,
Durus and a couple others.

>
It is sometimes convenient to store objects in mature relational
database backend (reliability, stability, support, tools,
replication, etc.). See latst efforts with RelStorage backend
for ZODB (http://wiki.zope.org/ZODB/RelStorage) - it stores
pickled Python objects in Oracle, PostgreSQL or MySQL)

You mean a SQL database backend here - the conveniences that you
mention have nothing to do with being relational or not. And that's my
point: pickling objects, you loose of the convenience of a
*relational* database.