Question regarding transferring tables between two databases

[mmlanger]

Hi,

I have a use case where I would like to save a (Postgres) database to a sqlite database while it is important to reuse the model code. One solution I thought of is deriving a new model and overriding the database, but this means I have to pass every field explicitly to the new model.

Seems overly complicated, is there a standard or more idiomatic way to do this?

Here a small example to give a better idea:

from peewee import DatabaseProxy, Model, BlobField, AutoField
from playhouse.apsw_ext import APSWDatabase

db_source = APSWDatabase("database1.db")
db_target = DatabaseProxy()

class BaseModel(Model):
    class Meta:
        database = db_source

class Block(BaseModel):
    id = AutoField()
    data = BlobField()

class SQLiteBlock(Block):
    class Meta:
        database = db_target

def transfer(filename: str):
    db_target.initialize(APSWDatabase(filename))
    db_target.create_tables([SQLiteBlock])

    for b in Block.select():
        SQLiteBlock.create(id=b.id, data=b.data)

db_source.create_tables([Block])

Block.create(data=b"123")
transfer("database2.db")

Best, Martin

Also, thanks for this great tool, I love it!

[coleifer]

Doing this with Peewee is probably not the greatest idea, however if you must, the best way is probably to do something like this:

db1 = SqliteDatabase('/tmp/db1.db')
db2 = SqliteDatabase('/tmp/db2.db')

class Base(Model):
    class Meta:
        database = db1

class Reg(Base):
    data = IntegerField()

db1.create_tables([Reg])
Reg.insert_many([(i,) for i in range(100)]).execute()                                    

MODELS = [Reg]  # List of all models to copy.                                            

for m in MODELS:
    class _dest(m):
        class Meta:
            database = db2
            table_name = m._meta.table_name

    _dest.create_table()
    with db2.atomic():
        for obj in m.select():
            new = _dest()
            new.__data__ = obj.__data__
            new.save(force_insert=True)