Using pickle to save reflected metadata

[shday]

What is the best way to save all my reflected metadata? Currently it is
taking >1 min just to start my program and I'd like to cut that down. I
suppose there is a way to use pickle to save the metadata and reload
it, but I'm not sure where to start. Here is a sample of my program
where I'd like to use pickle:

from sqlalchemy import *
import pickle

db = create_engine('oracle://myusername:somepass@somesid')

metadata = BoundMetaData(db)

pvv_table = Table('protocol_variant_value',metadata,autoload=True)
vtd_table = Table('variant_type_dictionary',metadata,autoload=True)
pvt_table = metadata.tables['protocol_variant_type']

class Vtd(object):
def __repr__(self):
return self.variant_type_name

class Pvt(object):
def __repr__(self):
return repr(self.variant_name)

class Pvv(object):
def __repr__(self):
return self.variant_value

mapper(Pvv, pvv_table)
mapper(Vtd, vtd_table)
mapper(Pvt, pvt_table, properties={
'variant_values':
relation(Pvv),'variant_name':relation(Vtd)},order_by =
pvt_table.c.vt_id)

session = create_session()

## start doing stuff here

[Michael Bayer]

you probably want to use toMetadata() on them with a plain MetaData
object so that the pickled stream does not contain any references to
engines. then on the way back in you would call toMetadata() again
with your BoundMetaData (or Dynamic, whichever you are using).

if anything itll be a great test for the toMetadata() method which
doesnt have a lot of testing.

[shday]

So I gave this a try as follows:

metadata_to_pickle = MetaData()

for table in metadata.tables.values():
table.tometadata(metadata_to_pickle)

pfile = file('pickled_tables','wb')

pickle.dump(metadata_to_pickle,pfile)

But there is an error (see below). Any ideas?

Steve

Traceback (most recent call last):
File "<pyshell#56>", line 1, in ?
pickle.dump(metadata_to_pickle,pfile)
File "C:\Python24\lib\pickle.py", line 1382, in dump
Pickler(file, protocol, bin).dump(obj)
File "C:\Python24\lib\pickle.py", line 231, in dump
self.save(obj)
File "C:\Python24\lib\pickle.py", line 338, in save
self.save_reduce(obj=obj, *rv)
File "C:\Python24\lib\pickle.py", line 433, in save_reduce
save(state)
File "C:\Python24\lib\pickle.py", line 293, in save
f(self, obj) # Call unbound method with explicit self
File "C:\Python24\lib\pickle.py", line 663, in save_dict
self._batch_setitems(obj.iteritems())
File "C:\Python24\lib\pickle.py", line 677, in _batch_setitems
save(v)
File "C:\Python24\lib\pickle.py", line 293, in save
f(self, obj) # Call unbound method with explicit self
File "C:\Python24\lib\pickle.py", line 663, in save_dict
self._batch_setitems(obj.iteritems())
File "C:\Python24\lib\pickle.py", line 677, in _batch_setitems
save(v)
File "C:\Python24\lib\pickle.py", line 338, in save
self.save_reduce(obj=obj, *rv)
File "C:\Python24\lib\pickle.py", line 433, in save_reduce
save(state)
File "C:\Python24\lib\pickle.py", line 293, in save
f(self, obj) # Call unbound method with explicit self
File "C:\Python24\lib\pickle.py", line 663, in save_dict
self._batch_setitems(obj.iteritems())
File "C:\Python24\lib\pickle.py", line 677, in _batch_setitems
save(v)
File "C:\Python24\lib\pickle.py", line 338, in save
self.save_reduce(obj=obj, *rv)
File "C:\Python24\lib\pickle.py", line 433, in save_reduce
save(state)
File "C:\Python24\lib\pickle.py", line 293, in save
f(self, obj) # Call unbound method with explicit self
File "C:\Python24\lib\pickle.py", line 663, in save_dict
self._batch_setitems(obj.iteritems())
File "C:\Python24\lib\pickle.py", line 677, in _batch_setitems
save(v)
File "C:\Python24\lib\pickle.py", line 338, in save
self.save_reduce(obj=obj, *rv)
File "C:\Python24\lib\pickle.py", line 433, in save_reduce
save(state)
File "C:\Python24\lib\pickle.py", line 293, in save
f(self, obj) # Call unbound method with explicit self
File "C:\Python24\lib\pickle.py", line 663, in save_dict
self._batch_setitems(obj.iteritems())
File "C:\Python24\lib\pickle.py", line 677, in _batch_setitems
save(v)
File "C:\Python24\lib\pickle.py", line 293, in save
f(self, obj) # Call unbound method with explicit self
File "C:\Python24\lib\pickle.py", line 663, in save_dict
self._batch_setitems(obj.iteritems())
File "C:\Python24\lib\pickle.py", line 676, in _batch_setitems
save(k)
File "C:\Python24\lib\pickle.py", line 338, in save
self.save_reduce(obj=obj, *rv)
File "C:\Python24\lib\pickle.py", line 433, in save_reduce
save(state)
File "C:\Python24\lib\pickle.py", line 293, in save
f(self, obj) # Call unbound method with explicit self
File "C:\Python24\lib\pickle.py", line 663, in save_dict
self._batch_setitems(obj.iteritems())
File "C:\Python24\lib\pickle.py", line 677, in _batch_setitems
save(v)
File "C:\Python24\lib\pickle.py", line 313, in save
rv = reduce(self.proto)
File "C:\Python24\lib\copy_reg.py", line 69, in _reduce_ex
raise TypeError, "can't pickle %s objects" % base.__name__
TypeError: can't pickle module objects

[Michael Bayer]

my guess would be the tometadata isnt working right, or something, and
the "bound" engine is still getting in there. i am pretty sure the
only time we have a reference to a "module" is in the Dialect that
references the DBAPI library (and the Dialect is in the Engine..)

ill have to take a look at it more deeply.

[Michael Bayer]

its fixed in rev 2131. give it another try.

[shday]

I still get the error:

Traceback (most recent call last):

File "U:/ADA_tools/pickle_tables.py", line 27, in ?

[Michael Bayer]

latest trunk, this test works for me:

from sqlalchemy import *
meta = BoundMetaData('mysql://user:pw@host/dbname')

table = Table('foo', meta, autoload=True)

meta2 = MetaData()

t2 = table.tometadata(meta2)

import pickle
pickle.dumps(meta2)

post your test case (with some sample table DDLs) and we'll try it out

[shday]

whoops...

I've been using IDLE to test this and I guess it has to be reopened in
order for imports to be redone (I was just closing the shell window).
pickle.dump(metadata) works fine now.

But now I get a (recursive?) error when I try to unpickle (and I have
to kill the shell):

>>> import pickle
>>> pfile = file('pickled_tables','rb')
>>> metadata = pickle.load(pfile)

Traceback (most recent call last):

File "<pyshell#7>", line 1, in -toplevel-
metadata = pickle.load(pfile)
File "C:\Python24\lib\pickle.py", line 1390, in load
return Unpickler(file).load()
File "C:\Python24\lib\pickle.py", line 872, in load
dispatch[key](self)
File "C:\Python24\lib\pickle.py", line 1235, in load_build
setstate = getattr(inst, "__setstate__", None)
File "C:\Python24\lib\site-packages\sqlalchemy\util.py", line 113, in
__getattr__
return self.__data[key]
File "C:\Python24\lib\site-packages\sqlalchemy\util.py", line 113, in
__getattr__
return self.__data[key]
File "C:\Python24\lib\site-packages\sqlalchemy\util.py", line 113, in
__getattr__
.
.
.
keeps on going

[Michael Bayer]

try 2138.

[shday]

It works!

It used to take ~70 sec for my program to start because of all the
reflection. Now it takes < 1 second.

Thanks!

Steve