parses sql dump file into structures containing table name, description and inserts

sqlParse.py

from pprint import pprint
from string import split, strip

def getMappings(x):
    """
    add any tables which have a different name or different
    column names in the new database.

    Example:
    the table 'foo' in the legacy database is called booyaka in
    the new database and has one column 'buk' renamed to 'zak'.

    mappings = {
        'foo': {
            'name': 'booyaka',
            'columns': {
                'buk': 'zak'
            }
    }
    """
    mappings = {
        'invoices': {
            'name': 'orderInvoices'
            },
        'orderAddresses': {
            'name': 'addresses'
            },
        'promotionRewardPriceDiscounts': {
            'name': 'fooyak',
            'columns': {
                'type': 'bar'
                }
            },
        'tags': {
            'name': 'bagAndTag',
            'columns': {
                'hash': 'ganja',
                'type': 'hyper'
                }
            },
        'users': {'name': 'coolUsers'},
    }
    if x in mappings.keys():
        return mappings[x]
    return None

justUseful   = lambda x: x[:2] != '/*' and x[:2] != '--'
label        = lambda x, mapping: mapping.get(x, x)
isInsert     = lambda x: len(x.split('INSERT INTO')) > 1
hasInserts   = lambda x: len(x['inserts']) > 0
cleanCommas = lambda x: x.replace("\'", '')
blacklist = ['alerts']
ignoreBlacklist = lambda x: x['name'] not in blacklist

def parseTable(x):
    tableName, rest = split(x, '(\n')
    tableName = tableName.translate(None, '`')
    rest = split(rest, ';')
    inserts = map(strip, filter(isInsert, rest))
    return {
        'body': rest[0],
        'name': strip(tableName, ' '),
        'inserts': inserts
        }

def migrateTable(x):
    mapping = getMappings(x['name'])
    if not mapping:
        return x
    origName = x['name']
    x['name'] = mapping.get('name', x['name'])
    x['inserts'] = map(lambda i: i.replace(origName, x['name']), x['inserts'])
    if 'columns' not in mapping.keys():
        return x
    for k, v in mapping['columns'].items():
        x['body'] = x['body'].replace(k, v)
        x['inserts'] = map(lambda i: i.replace(k, v), x['inserts'])
    return x

def showTable(x):
    print "CREATE TABLE %s (\n%s;\n" % (x['name'], x['body'])
    for x in x['inserts']:
        print "%s;" % x

"""Enter sql dump filename and new database name
"""
filename = 'fullDump.sql'
database = 'tf_framework'

print 'drop database %s;' % database
print 'create database %s;' % database
print 'use %s;' % database

sql = open(filename).read()
tables = split(sql, 'CREATE TABLE ')
tables = tables[1:] # Ditch the header comments
tables = filter(justUseful, tables) # Get rid of other comments
tables = map(parseTable, tables) # Create table objects
tables = filter(hasInserts, tables) # Ignore tables without inserts
tables = filter(ignoreBlacklist, tables) # currently ignores 'alerts'
tables = map(migrateTable, tables) # map correct table and column names

for x in tables:
    showTable(x)