python脚本自动生成mysql建表语句

自动生成mysql建表语句

平时在设计完表结构后，还得写建表语句，其实可以让脚本来完成这样重复的工作，python代码如下

# -*- coding=utf-8 -*-
#coding=utf-8
import xlrd
import os

def convert_type(data_type):
    """Normalize MySQL `data_type`"""
    if 'CHAR' == data_type:
        return 'varchar'
    elif 'CLNT' == data_type:
        return 'varchar'
    elif 'NUMC' == data_type:
        return 'numeric'
    elif 'DATS' == data_type:
        return 'timestamp'
    elif 'QUAN' == data_type:
        return 'varchar'
    elif 'CUKY' == data_type:
        return 'varchar'
    elif 'CURR' == data_type:
        return 'varchar'
    elif 'DEC' == data_type:
        return 'varchar'
    elif 'INT4' == data_type:
        return 'varchar'
    elif 'TIMS' == data_type:
        return 'varchar'
    elif 'string' == data_type:
        return 'varchar'

    else:
        return data_type

# 在mysql中创建表
def mysql_create(fields):
    stg_table_name = 'db.' + fields[0]['table_name']
    columns = []
    primary_key = []
    for field in fields:
        if field['primary_key'] == 'Y':
            primary_key.append(field['column_name'])
        table_column = field['column_name'] + '          ' + field['type'] + '          ' + 'COMMENT ' + "'" + field['column_exp'] + "'" + ',\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 {};\ncreate table {} (\n{});".format(
    stg_table_name,stg_table_name, stg_create_columns)
    # print(create_stg_sql)
    return create_stg_sql


# print(os.getcwd())
par_path = os.getcwd()
paths = [par_path+'\\']
print('---------------paths-------------------')
print(paths)
for path in paths:
    for filename in os.listdir(path):
        print(filename)
        if filename.endswith(".xlsx") or filename.endswith(".xls"):
            result_sql = ''
            print(path)
            print(filename)
            print(path + filename)
            worksheet = xlrd.open_workbook(path + filename)
            table_names = worksheet.sheet_names()
            for table_name in range(len(table_names)):
                sheet = worksheet.sheet_by_index(table_name)
                nrows = sheet.nrows
                fields = []
                for i in range(1,nrows):
                    res = sheet.row_values(i)
                    desc = {
                        'table_name': table_names[table_name].lower(),
                        'column_exp': res[0],
                        'column_name': res[1].lower(),
                        'type': convert_type(res[2]).upper(),
                        'primary_key': res[3],
                    }
                    fields.append(desc)
                #print(fields)
                result_sql += mysql_create(fields) + '\n\n'
            with open(path+'\\'+filename[:-5]+'.sql', "w", encoding='utf-8') as f:
                f.write(str(result_sql))
                f.close()

测试使用的表结构设计为

https://download.youkuaiyun.com/download/csdnshenjiaye/12171672

将python脚本和设计表的excel放在同一目录下即可，excel中sheet名就是表名，生成的建表语句保存在了脚本所在目录。

此脚本只是实现了基本功能，脚本还可以扩展，支持更多可配置功能，比如配置库名传参，支持不同的数据库等，欢迎大家补充！