Using the same model on multiple databases.

[devJet]

Hello,

I am attempting to cache my on-disk database using an in-memory sqlite database.

If I am understanding the peewee docs correctly, a "with Using(db, models):" statement can be used to temporarily change the database associated with a model.

Here is my code (Not including the database models):

class MemoryCache:

    def __init__(self, persistantDB, cacheDB):

        self.persistantDB = persistantDB

        self.cacheDB = cacheDB

    def get(self, modeltype, *args, **kwargs):

        try:

            with Using(self.cacheDB, [modeltype]):

                return modeltype.get(*args, **kwargs)

        except DoesNotExist as e:

            r = None

            with Using(self.persistantDB, [modeltype]):

                r = modeltype.get(*args, **kwargs)

            self.cache(r)

            return r

    def cache(self, model):

        with Using(self.cacheDB, [type(model)]):

                return model.save()

    def persist(self, model):

        with Using(self.persistantDB, [type(model)]):

            model.save()

    def uncache(self, model):

        self.persist(model)

        with Using(self.cacheDB, [type(model)]):

                return model.delete_instance()

db = SqliteDatabase("on-disk.sqlite")

db.connect()

database_proxy.initialize(db)

db.create_tables([Account, Character, Equipment, Friend, Instance, Inventory, Mail, Mission, NPC, Object, Session, Topic, World], True)

cacheDB = SqliteDatabase(':memory:')

cacheDB.connect()

with Using(cacheDB, [Account, Character, Equipment, Friend, Instance, Inventory, Mail, Mission, NPC, Object, Session, Topic, World]):

    cacheDB.create_tables([Account, Character, Equipment, Friend, Instance, Inventory, Mail, Mission, NPC, Object, Session, Topic, World])

memoryCache = MemoryCache(db, cacheDB)

#Test the cache

a = Session()

a.sessionKey = "test"

memoryCache.cache(a) #<-- This line errors with: "no such table: session"

When I run my application, the error "no such table: session" occurs on the last line of the above code. I'm not sure why this happens, as I am creating the tables in both databases.

It seems as if the tables in the cache database are somehow removed as soon as they are created. Am a doing something wrong?

Many thanks for your time.

[Charles Leifer]

I have no idea what could be going on... what's database_proxy, perhaps the issue is there?

--
You received this message because you are subscribed to the Google Groups "peewee-orm" group.
To unsubscribe from this group and stop receiving emails from it, send an email to peewee-orm+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[devJet]

Thanks for the quick reply!

A use the database proxy to specify the default database for the models to use. I used the example in peewee's docs for reference. Oddly, when I change the cache database to be on-disk rather than in-memory, the error goes away, and the code runs fine. Is there something special that needs to be done to use in-memory databases? Here is a (simplified) runnable sample with the models included: http://pastebin.com/Uurb6VR9

[Charles Leifer]

Yes, in memory databases only work for the lifetime of the connection. So you need to maintain a single, active connection. You can share memory databases between conns, but youll need to read the sqlite documentation.

[devJet]

That solved my problem. When I changed

cacheDB = SqliteDatabase(':memory:')

To

cacheDB = SqliteDatabase('file:cachedb?mode=memory&cache=shared', uri=True)

Everything worked as it should.

Thanks again for your help!

[Shirish Pokharel]

Hello, if I could just jump in here, would you show an easy way to use the same Model with multiple databases, that can be defined during runtime, please?

The original question doesn't show the Model, and the documentation on Master/Slave connections don't explain it clearly either. I tried to hack around the issue by encapsulating my Model class within a function, passing the database as a parameter, and returning the resulting Model object, but it doesn't seem write... and there could be gotchas that are unexpected? Is there an easier way to accomplish this?

Thank you! And thanks a LOT for peewee : )

On Monday, January 18, 2016 at 10:55:23 PM UTC-5, Charles wrote:

[devJet]

My models are defined normally.

[Charles Leifer]

Check out this thread:

https://github.com/coleifer/peewee/issues/825#issuecomment-173226822

[Charles LeDoux]

I'm dealing with a similar problem; I need many independent databases all using the same model.

My attempt at a solution is below. It's working so far on my very specific and simple use case, but it has not been thoroughly tested.

I'd appreciate feedback on what gotcha's to expect/why this is a really dumb idea.

The gist of the solution is that I shadow the class' _meta with an instance variable such that self._meta.database pulls from the instance variable, while anything else in self._meta is taken from the class variable.

from peewee import SqliteDatabase, Model, IntegerField

class InstanceMeta:
    def __init__(self, class_meta, database):
        self.class_meta = class_meta
        self.database = database

    def __getattr__(self, name):
        if name == 'database':
            return self.database
        else:
            return getattr(self.class_meta, name)

class InstanceModel(Model):
    """ Potentially unique DB per model instance. """

    # Store created dbs so can return same database objects.
    db_cache = {}
    # Database paths that should not be cached.
    # These will always return a new database object
    no_db_cache = [None, ':memory:']

    def __init__(self, db_path=None, *args, **kwargs):
        database = self.create_database(db_path)
        self._meta = InstanceMeta(class_meta=self._meta,
                                  database=database)
        super().__init__(*args, **kwargs)

    @staticmethod
    def create_database(db_path=None):
        factory = SqliteDatabase

        if not db_path:
            db_path = ':memory:'

        # Some paths should never be cached.
        if db_path in InstanceModel.no_db_cache:
            return factory(db_path)

        # Otherwise, there's always a cache.
        cache = InstanceModel.db_cache

        if db_path not in cache:
            cache[db_path] = factory(db_path)

        return cache[db_path]

# Example model
class MyModel(InstanceModel):
    x = IntegerField()
    y = IntegerField()

# Illustrative test cases

class TestMultipleModelInstances:
    """ Test various configurations of multiple instances of same model. """

    def test_multiple_dbs(self):
        """ Providing multiple db paths should result in each model pointing to a different database. """
        first = MyModel('/tmp/first.db')
        second = MyModel('/tmp/second.db')
        assert first._meta.database is not second._meta.database
        assert first._meta.database.database == '/tmp/first.db'
        assert second._meta.database.database == '/tmp/second.db'

    def test_same_db(self):
        """ Providing the same db string multiple times should result in the same database object. """
        first = MyModel('/tmp/first.db')
        second = MyModel('/tmp/first.db')
        assert first._meta.database is second._meta.database

    def test_multiple_in_memory(self):
        """ In memory databases should always be unique. """
        first = MyModel()
        second = MyModel()
        assert first._meta.database is not second._meta.database

[Charles Leifer]

In memory databases only exist for a single connection. My guess is you're attempting to reconnect?

On Jan 19, 2016 4:57 PM, "devJet" <luj...@gmail.com> wrote: