自动生成建表脚本的python程序()-python
自动生成建表脚本的python程序()
如下
#!/usr/local/python/bin/ptyhon
# coding=utf-8
import sys,os
from job.base.JobBase import ExitCode
import job.base.ClientUtil as util
#home文件夹创建
try：
dirstr = "C:\user\ywwb1084\Downloads\ScriptCreateAuto"
os.mkdir(dirstr)
print("文件夹创建成功")
except:
print("文件夹已经存在")
#log文件夹创建
try：
logdirstr = dirstr+"\log"
os.mkdir(logdirstr)
print("log文件夹创建成功")
except:
print("log文件夹已经存在")
#模型表设计清单
table_list = ['AMS.A01_TEST_INFO:案例信息表','AMS.A01_TEST_INFO2:案例信息表2']
#skk回流表SDATA.
create_failed_list = []
failed_counter = 0
successful_counter = 0
for i in table_list:
tab_comment = ""
if len(i.split(":"))>1:
tab_comment = i.split(":")[1]
#print table_comment
sql = r'''
describe %s
'''%(i.split(":")[0])
#print sql
sql_result = util.execSql(sql,locals())
if util._ERRORCODE:
failed_counter+=1;
print i.split(":")[0] + '表不存在，请检查'
print("生成脚本失败累计次数：{}".format(failed_counter))
with open(logdirstar+"\create-log.txt","a") as file:
file.write(i.split(":")[0]+'表不存在，请检查\n')
countinue
else:
successful_counter+=1;
#print sql_result
col_str = ''
i_count = 0
for column in sql_result [:-1]:
i_count += 1;
str1 = column[0] + ' ' + column[1] + ' COMMENT ' + '\'' + column[2] + '\'\n'
if i_count == 1:
col_str += ' ' + str1.upper()
else:
col_str += ' ,' + str1.upper()
#print i_count
#print col_str
pre_sql ='''#!/user/local/python/bin/python/bin/python/bin/ptyhon
# coding=utf-8
# 脚本自动生成
import sys
from job.base.JobBase import ExitCode
import job.base.ClientUtil as util
def checkArgs(length):
util.debug('参数检查’）
util.checkArgsEx(length)
try:
#--------------------------以上脚本信息不可以修改--------------------------
AMSDB = 'AMS';
util.dropTable(AMSDB+'%s');
sql = r\'\'\'
CREATE TABLE $AMSDB$%s(
'''%(i.split(":")[0].replace('AMS',''),i.split(":")[0].replace{'AMS',''})
#print pre_sql
end_sql = '''
)PARTITIONED BY (%s VARCHAR(10) COMMENT '数据日期')
COMMENT '%s'
STORED AS PARQUET
\'\'\';
util.execSql(sql,locals())
if util._ERRORCODE:
util.exit(ExitCode.EXIT_ERROR,'CREATE TABLE %s 异常出错');
#-----------------------------SQL语句块【结束】-----------------------------
#--------------------------以下脚本信息不可以修改--------------------------
util.exit(ExitCode.EXIT_SUCCESS,'执行成功');
except Exception as e:
util.exit(ExitCode.EXIT_ERROR,'异常出错：'+e.massage);
finally:
util.destory()
'''%(sql_result[-1][0],table_comment,i.split(":")[0])
#print end_sql
print pre_sql + col_str[1:] + end_sql
with open(dirstr+"\\"+i.split(":")[0].replace('AMS.','').lower()+"_create.py","w") as file:
file.write(pre_sql + col_str[1:] + end_sql)
with open(logdirstr+"\create-log-log.txt","a") as file:
file.write("\n================\n列表长度：{}\n======================".format(len(table_list)))
file.write("\n================\n生成脚本失败次数：{}\n======================".format(failed_counter))
file.write("\n================\n生成脚本成功次数：{}\n======================".format(successful_counter))
————————
如下
#!/usr/local/python/bin/ptyhon
# coding=utf-8
import sys,os
from job.base.JobBase import ExitCode
import job.base.ClientUtil as util
#home文件夹创建
try：
dirstr = "C:\user\ywwb1084\Downloads\ScriptCreateAuto"
os.mkdir(dirstr)
print("文件夹创建成功")
except:
print("文件夹已经存在")
#log文件夹创建
try：
logdirstr = dirstr+"\log"
os.mkdir(logdirstr)
print("log文件夹创建成功")
except:
print("log文件夹已经存在")
#模型表设计清单
table_list = ['AMS.A01_TEST_INFO:案例信息表','AMS.A01_TEST_INFO2:案例信息表2']
#skk回流表SDATA.
create_failed_list = []
failed_counter = 0
successful_counter = 0
for i in table_list:
tab_comment = ""
if len(i.split(":"))>1:
tab_comment = i.split(":")[1]
#print table_comment
sql = r'''
describe %s
'''%(i.split(":")[0])
#print sql
sql_result = util.execSql(sql,locals())
if util._ERRORCODE:
failed_counter+=1;
print i.split(":")[0] + '表不存在，请检查'
print("生成脚本失败累计次数：{}".format(failed_counter))
with open(logdirstar+"\create-log.txt","a") as file:
file.write(i.split(":")[0]+'表不存在，请检查\n')
countinue
else:
successful_counter+=1;
#print sql_result
col_str = ''
i_count = 0
for column in sql_result [:-1]:
i_count += 1;
str1 = column[0] + ' ' + column[1] + ' COMMENT ' + '\'' + column[2] + '\'\n'
if i_count == 1:
col_str += ' ' + str1.upper()
else:
col_str += ' ,' + str1.upper()
#print i_count
#print col_str
pre_sql ='''#!/user/local/python/bin/python/bin/python/bin/ptyhon
# coding=utf-8
# 脚本自动生成
import sys
from job.base.JobBase import ExitCode
import job.base.ClientUtil as util
def checkArgs(length):
util.debug('参数检查’）
util.checkArgsEx(length)
try:
#--------------------------以上脚本信息不可以修改--------------------------
AMSDB = 'AMS';
util.dropTable(AMSDB+'%s');
sql = r\'\'\'
CREATE TABLE $AMSDB$%s(
'''%(i.split(":")[0].replace('AMS',''),i.split(":")[0].replace{'AMS',''})
#print pre_sql
end_sql = '''
)PARTITIONED BY (%s VARCHAR(10) COMMENT '数据日期')
COMMENT '%s'
STORED AS PARQUET
\'\'\';
util.execSql(sql,locals())
if util._ERRORCODE:
util.exit(ExitCode.EXIT_ERROR,'CREATE TABLE %s 异常出错');
#-----------------------------SQL语句块【结束】-----------------------------
#--------------------------以下脚本信息不可以修改--------------------------
util.exit(ExitCode.EXIT_SUCCESS,'执行成功');
except Exception as e:
util.exit(ExitCode.EXIT_ERROR,'异常出错：'+e.massage);
finally:
util.destory()
'''%(sql_result[-1][0],table_comment,i.split(":")[0])
#print end_sql
print pre_sql + col_str[1:] + end_sql
with open(dirstr+"\\"+i.split(":")[0].replace('AMS.','').lower()+"_create.py","w") as file:
file.write(pre_sql + col_str[1:] + end_sql)
with open(logdirstr+"\create-log-log.txt","a") as file:
file.write("\n================\n列表长度：{}\n======================".format(len(table_list)))
file.write("\n================\n生成脚本失败次数：{}\n======================".format(failed_counter))
file.write("\n================\n生成脚本成功次数：{}\n======================".format(successful_counter))