sql alchemy schema migration

sql_alchemy_schema_migration.py

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session, sessionmaker
from sqlalchemy import create_engine, MetaData, Table, schema, Text, Index, select, func


to_db="mysql+pymysql://root@localhost/test"
#to_db="postgres://admin:your_redshift_cluster/pwd"
from_db="mysql+pymysql://root@localhost/test"

def make_session(connection_string):
    engine = create_engine(connection_string, echo=False, convert_unicode=True)
    Session = sessionmaker(bind=engine)
    return Session(), engine

source, sengine = make_session(from_db)
smeta = MetaData(bind=sengine)
destination, dengine = make_session(to_db)

smeta.reflect(sengine, only=['dnb_customer','dnb_sold_to','dnb_ship_to'])

for t in smeta.sorted_tables:
    table = Table(t,smeta,autoload=True)
    #print select([table, func.current_date()]).execute()
    print table.name

    #make some changes to target schema
    table.name = 'tmp_' + table.name
    table.schema = 'ab'
    etl_date = schema.Column('etl_date',Text)
    table.append_column(etl_date)

    print table.schema+'.'+table.name

    table.indexes.clear()
    table.foreign_keys.clear()
    #print select([table, func.current_date()]).execute()

    table.drop(dengine, checkfirst=True)
    table.create(dengine, checkfirst=True)

    destination.commit()