Silly but effective migration framework for small-ish datasets. Row-by-row re-insert between arbitrary database dialects supported by SQLAlchemy.

grate.py

#!/usr/bin/env python
"""
Really silly schema migration framework, built for SQLAlchemy.

Example:
        ./grate.py "mysql://foo:bar@localhost/baz" "sqlite:///:memory:" \
                   --metadata model.meta:metadata --verbose
"""
import sqlalchemy
import sys
import logging

log = logging.getLogger(__name__)
from optparse import OptionParser

def migrate(e1, e2, metadata, convert=None, only_tables=None, skip_tables=None, limit=100000):
    metadata.bind = e1
    metadata.create_all(bind=e2)

    for table_name, table in metadata.tables.items():
        if only_tables and table_name not in only_tables:
            log.info("Skipping table: {0}".format(table_name))
            continue

        if skip_tables and table_name in skip_tables:
            log.info("Skipping table: {0}".format(table_name))
            continue

        count = e1.execute(table.count()).scalar()

        log.info("Inserting {0} rows into: {1}".format(count, table_name))
        for offset in xrange(0, count, limit):
            data = e1.execute(table.select().offset(offset).limit(limit)).fetchall()
            if not data:
                   continue

            if convert:
                data = [convert(table_name, row) for row in data]

            e2.execute(table.insert(), data)
            log.debug("-> Inserted {0} rows into: {1}".format(len(data), table_name))


def import_module(path):
    # FIXME: There's a builtin for this in Python 2.7
    module, obj = path.split(':', 1)
    o = __import__(module, fromlist=[obj])
    return getattr(o, obj)


def main():
    usage="%prog ENGINE_FROM ENGINE_TO\n" + __doc__

    # FIXME: Use argparse someday
    parser = OptionParser(usage)
    parser.add_option("--metadata", dest="metadata", help="MetaData object to use which contains the target model definition. Example: model.metadata:MetaData")
    parser.add_option("--convert", dest="convert", help="(Optional) Convert function to run data through. Example: migration.v1:convert")
    parser.add_option("--only-tables", dest="only_tables", help="Only perform migration on the given tables (comma-separated).")
    parser.add_option("--skip-tables", dest="skip_tables", help="Skip migration on the given tables (comma-separated).")
    parser.add_option("--limit", dest="limit", default=100000, help="Limit of rows to select per insert loop.")
    parser.add_option("--verbose", "-v", dest="verbose", action="count", help="Enable verbose output. Use twice to enable debug output.")
    parser.add_option("--show-sql", dest="show_sql", action="store_true", default=False, help="Echo SQLAlchemy queries.")

    options, args = parser.parse_args()

    if len(args) < 2:
        parser.error("Need to specify two engines.")

    if options.verbose > 1:
        log.setLevel(logging.DEBUG)
    elif options.verbose > 0:
        log.setLevel(logging.INFO)

    only_tables = None
    if options.only_tables:
        only_tables = [t.strip() for t in options.only_tables.split(',')]

    skip_tables = None
    if options.skip_tables:
        skip_tables = [t.strip() for t in options.skip_tables.split(',')]

    metadata = import_module(options.metadata)

    convert = None
    if options.convert:
        convert = import_module(options.convert)

    e1 = sqlalchemy.create_engine(args[0])
    e2 = sqlalchemy.create_engine(args[1], echo=options.show_sql)

    migrate(e1, e2, metadata=metadata, convert=convert, only_tables=only_tables, skip_tables=skip_tables, limit=options.limit)


if __name__ == '__main__':
    log_handler = logging.StreamHandler()
    log_handler.setFormatter(logging.Formatter('%(levelname)-8s %(message)s'))
    log.addHandler(log_handler)

    n = main()
    if n:
        sys.exit(n)