python实现excel模板生成建表sql

#! /usr/bin/python
# -*- coding: utf-8 -*-
import xlrd3
import os
import codecs


# 在postgresql中create table
def postgres_create(fields):
    stg_table_name = 'traffic.' + fields[0]['table_name']
    columns = []
    for field in fields:
        table_column = '  ' + field['column_name'] + ' ' + field[
            'type'] + ' ' + 'comment' + ' ' + "'" + field[
                'comment'] + "'" + ',\n'
        # print(table_column)
        columns.append(table_column)
    # print(columns)
    stg_create_columns = ''.join(columns)[:-2]
    # print(stg_create_columns)

    create_stg_sql = "DROP TABLE IF EXISTS %s ;\n" % (stg_table_name)

    create_stg_sql = create_stg_sql + "create table if not exists %s (\n%s\n) row format delimited fields terminated by '%s' lines terminated by '%s'\n stored as parquet;\n" % (
        stg_table_name, stg_create_columns, '&', '\\n')
    print(create_stg_sql)

    # header = "File Header"
    fd = codecs.open('./test.sql', 'a', 'utf-8')
    # print(header, file=fd)
    print(create_stg_sql, end='', file=fd)
    fd.close()


paths = [r'E:/project/excel/']
for path in paths:
    for filename in os.listdir(path):
        if filename.endswith(".xlsx"):
            worksheet = xlrd3.open_workbook(path + filename)
            table_name = worksheet.sheet_names()
            # get sheet page
            for n in range(len(table_name)):
                sheet = worksheet.sheet_by_index(n)
                nrows = sheet.nrows
                fields = []
                # get rows
                for i in range(nrows):
                    # remove 3 ，from 4 start
                    if i > 3:
                        res = sheet.row_values(i)
                        desc = {
                            'column_name': res[0].lower(),
                            'table_name': table_name[n].lower(),
                            'type': 'string',
                            'comment': res[2],
                        }
                        fields.append(desc)
                if len(fields) > 0:
                    postgres_create(fields)

其中，xlrd3包需要在python中安装