def tick():
"""
步骤：1.构建mapping；2.构建sql语句；3.开始执行sql语句
:return:
"""
mark = str(time.time())
logger.info('%s' % mark)
connect = Path.CONNECT.value
sqlPath = Path.SQLPATH.value
# 1.先建mapping
logger.info(
'==============================开始构建mapping=============================='
)
expMapping = DrawMapping(conn=connect)
expMapping.build_from_db()
# 2.根据mapping构建sql
logger.info(
'==============================开始构建SQL==============================')
DrawSql(conn=connect).start()
# 3.开始执行sql
logger.info(
'==============================开始执行SQL文件=============================='
)
threadList = []
cnt = 1
db = con(f"mysql+pymysql://{connect}")
fileName = []
for root, dirs, files in os.walk(sqlPath):
for file in files:
if file.endswith('sql'):
fileName.append(file)
file_ = os.path.join(root, file)
with open(file_, 'r') as f:
logger.info('正在处理【%s】文件' % f.name)
thr = CmdThread(cnt, f'hive -f "{f.name}"')
threadList.append(thr)
for t in threadList:
t.start()
for t in threadList:
t.join()
if t.isSuccess:
table = str(t.cmd).split('/')[2].split('.')[0]
sql = f"update dangan.asset_managament_info set is_load=3 where table_final_name='{table}'"
# sql = f"update dangan.tb_ml_temp_t1 set is_load=3 where tbl_name='{table}'"
execute = db.execute(sql, autocommit=True)
if execute > 0:
os.remove(os.path.join(sqlPath, table + '.sql'))
if os.path.isdir(sqlPath):
os.removedirs(sqlPath)
logger.info(
'==============================All Job Done!=============================='
)
def __init__(self, connect, database):
self.db = con(f"mysql+pymysql://{connect}")
self.database = database
self.tbls = {}
self.tbl_en = ''
self.tbl_cn = ''
self.sheetName = ''
self.title_table = [
'数据库', '表名', '表注释', '类型', '数据量', '创建时间', '更新时间', '表结构'
]
self.title_column = ['表名', '字段序号', '字段', '类型', '主键', '字段描述']
def __init__(self, database, outPutPath='../mappings'):
self.database = database
# self.db = con(f"mysql+pymysql://root:[email protected]:3306/{self.database}")
self.db = con(
f"mysql+pymysql://root:[email protected]:3306/{self.database}")
self.filePath = outPutPath
self.table = ''
self.fieldTransMapping = {
"uuid": "uuid",
"身份证": "sfzh",
"身份证号": "sfzh",
"用户名": "user_name",
"微信": "user_name",
"QQ": "user_name",
"邮箱": "email",
"手机号": "phoneno",
"手机号码": "phoneno",
"密码": "password",
"密码-加密": "password",
"明文密码": "password"
}
self.sourceTransMapping = {
"12306": "12306",
"126": "126",
"163": "163",
"7k7k": "7k7k",
"acfun": "acfun",
"csdn": "csdn",
"renren": "renren",
"tianya": "tianya",
"xiaomi": "xiaomi",
"珍爱网": "zhenaiwang",
"52房地产": "52fangdichan",
"92hacker": "92hacker",
"118faka": "118faka",
"open": "open",
"zp": "zp",
"曹长青": "caochangqing",
"汉庭": "hanting",
"鲸鱼": "jingyu",
"缅华": "mianhua",
"台湾海外网": "taiwanhaiwaiwang",
"一亿": "yiyi",
"web": "web"
}
def __init__(self, conn, outPutPath=Path.MAPPINGPATH.value):
self.db = con(f"mysql+pymysql://{conn}")
self.filePath = outPutPath
self.fieldTransMapping = Path.FIELDMAPPING.value
self.sourceTransMapping = Path.SOURCEMAPPING.value
def __init__(self, conn, sqlPath=Path.SQLPATH.value, mappingPath=Path.MAPPINGPATH.value):
self.sqlPath = sqlPath
self.mappingPath = mappingPath
self.db = con(f"mysql+pymysql://{conn}")