Extract SQL from excel

extract_sql_excel.py

from os.path import join, dirname, abspath
import xlrd

xlrd.xlsx.ensure_elementtree_imported(False, None)
xlrd.xlsx.Element_has_iter = True

fname = join(dirname(dirname(abspath(__file__))), 'dir_name', 'file_name.xlsx')

xl_workbook = xlrd.open_workbook(fname)

sheet_names = xl_workbook.sheet_names()
print('Sheet Names', sheet_names)

tables_names = {'schema_name.short_table_name': 'schema_name.short_table_name', 
                'schema_name.very_long_table_name_max': 'schema_name.very_long_table_name_max_24_char'}

with open('policy.sql', 'w') as file:
    for name in sheet_names:
        xl_sheet = xl_workbook.sheet_by_name(name)
        table_name = tables_names.get(xl_sheet.name)
        if not table_name:
            continue

        num_cols = xl_sheet.ncols

        cols_names = [xl_sheet.cell(0, i).value for i in range(num_cols)]
        cols_names_tuple = tuple(cols_names)
        cols_names_str = ",".join([str(s) for s in list(cols_names_tuple)])

        file.write('\n')
        for row_idx in range(1, xl_sheet.nrows):
            cols_values = [xl_sheet.cell(row_idx, i).value for i in range(num_cols)]
            cols_value_tuple = tuple(cols_values)
            query = f"insert into {table_name}({cols_names_str}) values{cols_value_tuple}"
            print(query)
            file.write(query+'\n')