insert_from over two databases

Question

I try to move all rows from one database table (source) to another database (target). The source-DB is a local database while target-DB runs on another machine. I want to transfer rows between the two databases and found the Model.insert_from() method for that task. Unfortunately it does nothing and I can't find any reason for that.

The database model is:

databaseSource = MySQLDatabase('sourceDB', **{'host': 'localhost', 'user': 'local', 'password': ''})

databaseTarget = MySQLDatabase('targetDB', **{'host': 'externalserver', 'user': 'external', 'password': ''})

class BaseModelSource(Model):
    class Meta:
        database = databaseSource
class BaseModelTarget(Model):
    class Meta:
        database = databaseTarget

class UsersSource(BaseModelSource):
    crdate = DateTimeField(constraints=[SQL("DEFAULT CURRENT_TIMESTAMP")])
    description = TextField()
    firstName = CharField(column_name='first_name')

    class Meta:
        table_name = 'users'

class UsersTarget(BaseModelTarget):
    crdate = DateTimeField(constraints=[SQL("DEFAULT CURRENT_TIMESTAMP")])
    description = TextField()
    firstName = CharField(column_name='first_name')

    class Meta:
        table_name = 'users'

With that my task should run with:

import peewee
from dbmodels import *

dataQuery = UsersSource.select(
    UsersSource.crdate, 
    UsersSource.description,
    UsersSource.firstName)

insertQuery = UsersTarget.insert_from(dataQuery,[
    UsersTarget.crdate, 
    UsersTarget.description,
    UsersTarget.firstName]).execute()

The resulting MySQL-query is this and as you can see, the selected data is empty []:

('INSERT INTO `users` (`crdate`, `description`, `first_name`) SELECT `t1`.`crdate`, `t1`.`description`, `t1`.`first_name` FROM `users` AS `t1`', [])

When I run the SELECT query on my table it outputs:

SELECT `t1`.`crdate`, `t1`.`description`, `t1`.`first_name` FROM `users` AS `t1`;


2018-08-12 16:50:36 valid   Heinz
2018-08-12 19:34:45 valid   Hilde
2018-08-12 19:33:31 invalid Paul

I searched like hell but didn't find any hint, why my result is empty.

Does anybody know more or a better method?

Answer 1

Peewee cannot insert data between two different database servers/connections. If both databases are on the same server, however, you can use the "schema" Meta option to reference each database from a single connection, and do the INSERT FROM that way:

db = MySQLDatabase('my_db')

class UsersSource(Model):
    crdate = DateTimeField(constraints=[SQL("DEFAULT CURRENT_TIMESTAMP")])
    description = TextField()
    firstName = CharField(column_name='first_name')

    class Meta:
        database = db
        schema = 'source_db_name'
        table_name = 'users'

class UsersTarget(Model):
    crdate = DateTimeField(constraints=[SQL("DEFAULT CURRENT_TIMESTAMP")])
    description = TextField()
    firstName = CharField(column_name='first_name')

    class Meta:
        database = db
        schema = 'dest_db_name'
        table_name = 'users'

If the databases are on different servers, then you have no other option but to dump it and reload it.