using sqlparse to convert sql insert statements to sqlalchemy model calls

insert_statement_to_alchemy_model.py

import sqlparse
from sqlparse.sql import *  # noqa


stmts = sqlparse.parse(open('data.sql').read())

TABLE_MODEL_MAP = dict(
    sec_rolegroup='models.RoleGroup',
    sec_role='models.Role',
    sec_permissiongroup='models.PermissionGroup',
    sec_permission='models.Permission',
)


def statement_to_model_args(s):
    insert = s.token_next_by_instance(0, Function)
    table_name = insert.token_next_by_instance(0, Identifier).value
    x = insert.token_next_by_instance(0, Parenthesis)
    x = x.token_next_by_instance(0, IdentifierList)
    x = x.tokens
    columns = [t.value for t in x if type(t) == Identifier]

    y = s.token_next_by_instance(0, Parenthesis)
    y = y.token_next_by_instance(0, IdentifierList)
    y = y.get_identifiers()
    values = [e.value for e in y]

    tuples = zip(columns, values)
    s = TABLE_MODEL_MAP.get(table_name, table_name) + '(' + ', '.join('%s=%s' % x for x in tuples) + ')'
    s = '    session.add(%s)' % s
    return s

for s in stmts:
    try:
        print statement_to_model_args(s)
    except:
        print 'ERROR', s