Naively copy a table from a database to another with SQLSoup.

copy_table.py

"""
usage: copy_table.py [-h] source target tablename

Copy a table from one database to another.

positional arguments:
  source      URL of source database. i.e.: mysql://root@localhost/example
  target      URL of target database. i.e.: sqlite:///:memory:
  tablename   Name of table to copy

optional arguments:
  -h, --help  show this help message and exit
"""
import argparse
from datetime import datetime

from sqlalchemy.exc import NoSuchTableError
import sqlsoup


def copy_table(source_db, target_db, tablename):
    try:
        entity = source_db.entity(tablename)
    except NoSuchTableError:
        print 'Table %s does not exist on %s' % (tablename, source_db.bind.url)
        return

    source_table = entity._table

    # check if table exist on target db
    try:
        target_db.entity(tablename)
    except NoSuchTableError:
        try:
            source_table.create(target_db.bind)  # Create table on target db
        except:
            print ('Unable to create table on target db. :-(')
            raise
    else:
        print 'Table %s alredy exists on %s' % (tablename, target_db.bind.url)

    column_names = source_table.c.keys()
    target_entity = target_db.entity(tablename)

    print 'Get rows from %s...' % source_db.bind.url

    begin = datetime.now()

    resultset = entity.all()

    end = datetime.now()

    print '%s rows found in %s\n' % (len(resultset), end - begin)

    print 'Create in %s' % target_db.bind.url

    begin = datetime.now()

    for row in resultset:
        # dict comprehensions are cool (http://goo.gl/Ywd2xx):
        columns_dict = {column_name: getattr(row, column_name)
                        for column_name in column_names}
        target_entity(**columns_dict)

    end = datetime.now()

    print 'Done in %s' % (end - begin)


if __name__ == '__main__':
    parser = argparse.ArgumentParser(
        description='Copy a table from one database to another.',
        formatter_class=argparse.RawDescriptionHelpFormatter,
        )
    parser.add_argument(
        'source',
        help='URL of source database. i.e.: mysql://root@localhost/example',
        )

    parser.add_argument(
        'target',
        help='URL of target database. i.e.: sqlite:///:memory:',
        )

    parser.add_argument(
        'tablename',
        help='Name of table to copy',
        )
    arguments = parser.parse_args()

    source_db = sqlsoup.SQLSoup(arguments.source)
    target_db = sqlsoup.SQLSoup(arguments.target)

    copy_table(source_db, target_db, arguments.tablename)