Marcus Priesch wrote:
> [...]
> maybe there could be a solution to use the same table and python class
> definitions for both databases ... but i havent found out how ...
> hopefully someone can comment on this ...
You can use the same table definition, class, metadata and session for
this. All that's needed are two engines and two mappers, mapping some
instances to the ramdisk database and others to the permanent database.
I've attached an example.
A couple other comments: If key/value pairs must be unique per section,
this kind of lookup wrapper can be massively sped up by changing to a
natural primary key:
> skv_table = Table \
> ( "skv"
> , metadata
> , Column ("id" , Integer, primary_key = True)
> , Column ("section", String ())
> , Column ("key" , String ())
> , Column ("value" , String ())
> )
becomes:
svk_table = Table('skv', metadata,
Column('section', Text(), primary_key=True),
Column('key', Text(), primary_key=True),
Column('value', Text())
With that, you can do session.query(SKV).get(('section', 'key')) or
session.get(SKV, ('section', 'key') instead of a query with .filter()
and .first(). If the matching instance has already been loaded and is
still in scope somewhere, the request will be served out of SA's
identity map instead of doing a database roundtrip.
Using PickleType for .value is another thing to consider... but whether
that's going to be faster or not will depend on your usage pattern. If
you're reading many rows in each session but only changing a couple,
then what you have now is probably more efficient.
Also, be sure to close() the transactional session you're interning in
the SKV_Wrapper when you're done with the wrapper.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
#sa_db.py
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData()
skv_table = Table ("skv", metadata,
Column("id" , Integer, primary_key=True),
Column("section", Text()),
Column("key" , Text()),
Column("value" , PickleType()))
class SKV(object) :
def __init__ (self, s, k, v) :
self.section = s
self.key = k
self.value = v
skv_mapper = mapper(SKV, skv_table) # default, entity_name is None
pskv_mapper = mapper(SKV, skv_table, entity_name='permanent')
pengine = create_engine("sqlite://", echo=False)
engine = create_engine("sqlite://", echo=False)
for bind in engine, pengine:
metadata.create_all(bind)
# some sample data
engine.execute(skv_table.insert(), dict(section=1, key='b', value='b'))
pengine.execute(skv_table.insert(), dict(section=1, key='a', value='A'))
pengine.execute(skv_table.insert(), dict(section=1, key='b', value='B'))
pengine.execute(skv_table.insert(), dict(section=1, key='c', value='C'))
Sess = sessionmaker(autoflush=True, transactional=True,
binds={skv_mapper: engine,
pskv_mapper: pengine})
class SKV_Wrapper(object) :
def __init__(self, session):
self.session = session
self.skv_klass = SKV
# end def __init__
def _get(self, section, key, storage=None):
q = self.session.query(self.skv_klass, entity_name=storage)
res = q.filter_by(section = section, key = key).first()
if not res:
raise KeyError, "no value found for %s.%s" % (section, key)
return res
# end def _get
def get(self, section, key, result=None, storage=None):
try :
result = self._get(section, key, storage=storage).value
except KeyError :
pass
return result
# end def get
def set(self, section, key, value, storage=None):
try :
old = self._get(section, key, storage=storage)
old.value = value
except KeyError :
new = self.skv_klass(section, key, value)
self.session.save(new, entity_name=storage)
# end def set
def delete(self, section, key, storage=None):
try :
old = self._get (section, key, storage)
self.session.delete(old)
except KeyError :
pass
# end def delete
# end class _SKV_Wrapper
if __name__ == '__main__':
w = SKV_Wrapper(Sess())
# ramdisk storage
print w.get(1, 'b')
# same key, permanent storage
print w.get(1, 'b', storage='permanent')
# write some new entries
w.set(1, 'c', 'c')
w.set(1, 'e', 'E', storage='permanent')
w.session.commit()
# try roundtripping them
w.session.clear()
print w.get(1, 'c')
print w.get(1, 'c', storage='permanent')
print w.get(1, 'e')
print w.get(1, 'e', storage='permanent')
# some deletes
w.delete(1, 'c')
w.delete(1, 'e', storage='permanent')
w.session.commit()
w.session.clear()
print w.get(1, 'c')
print w.get(1, 'c', storage='permanent')
print w.get(1, 'e')
print w.get(1, 'e', storage='permanent')
w.session.close()
