def main():
## 获取画图数据
colorList = yamlDict.get('colorList')
titleName = yamlDict.get('titleName')
yUnit = yamlDict.get('yUnit')
## 获取对应分支数据
longDict = series_mod(branchName='long')
shortDict = series_mod(branchName='short')
## 定义输出列表
seriesList = longDict.get('series') + shortDict.get('series')
yearList = list_uniq(longDict.get('year') + shortDict.get('year'))
legendList = [x.get('name') for x in seriesList]
outputDict = {
'title': titleName,
'legend': legendList,
'color': colorList,
'xlabel': yearList,
'yname': yUnit,
'series': seriesList
}
## 转json字符串,不转码
outputStr = json.dumps(outputDict, ensure_ascii=False)
## 存储结果到mysql数据库
mysql_update(dataLabel=lengthline.label, dataValues=outputStr)
def legend():
## 生成标签列表
_legendList = [
w.get('name') for e in yamlDict.get('textTagList')
for w in e.get('specific')
]
## 修正标签列表(添加'其他'标签)
_legendList.append(yamlDict.get('otherLabel'))
## 修正标签列表(列表去重)
_legendList = list_uniq(_legendList)
return _legendList
def series_mod(**kwargs):
## 画图类型
_picType = yamlDict.get('picType')
## hive查询数据
_hiveDB = lengthline.hiveDB or hive_conn.hiveDB
_hiveTable = lengthline.hiveTable or hive_conn.hiveTable
## wds数据分支名称
_branchName = kwargs.get('branchName')
## 获取索引列表
_textTagList = yamlDict.get(_branchName).get('textTagList')
_divUnit = yamlDict.get(_branchName).get('divUnit') or 1
_filterKeyList = [w.get('wds') for w in _textTagList]
## HQL查询语句
if len(_filterKeyList) == 1:
_HQL = "SELECT wds,year,value FROM `{}`.`{}` WHERE wds='{}' ORDER BY year".format(
_hiveDB, _hiveTable, _filterKeyList[0])
elif len(_filterKeyList) > 1:
_HQL = "SELECT wds,year,value FROM `{}`.`{}` WHERE wds IN {} ORDER BY year".format(
_hiveDB, _hiveTable, tuple(_filterKeyList))
## 数据查询
_resultList = spark_hive_query(HQL=_HQL)
## 获取查询数据年份列表并去重不排序
_yearList = [x.year for x in _resultList]
_yearList = list_uniq(_yearList)
## 计算series列表
_seriesList = []
for _x in _textTagList:
_perList = [
per_list(_y, _x.get('wds'), _resultList, _divUnit)
for _y in _yearList
]
_seriesList.append({
'name': _x.get('name'),
'type': _picType,
'data': _perList
})
## 返回数据
return {'year': _yearList, 'series': _seriesList}
def series_mod(**kwargs):
## 通过参数获取分支名称
_branchName=kwargs.get('branchName')
## 获取对应分支数据在yaml数据中
_textTagList = yamlDict.get(_branchName).get('textTagList')
_divUnit = yamlDict.get(_branchName).get('divUnit') or 1
_filterKeyList = [ w.get('wds') for w in _textTagList ]
## 根据元组长度判断sql查询语句
### python单元素元组写法是(e,), 直接用IN查询会失败
if len(_filterKeyList) == 1:
_sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds='{}' ORDER BY year".format(
mysqlDB, mysqlTB, _filterKeyList[0])
elif len(_filterKeyList) > 1:
_sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds IN {} ORDER BY year".format(
mysqlDB, mysqlTB, tuple(_filterKeyList))
## 调用数据库查询函数查询结果
_resultList = mysql_query(
cmd = _sql,
host = mysqlHost,
user = mysqlUser,
passwd = mysqlPass,
port = mysqlPort,
charset = mysqlChrt
)
## 获取X轴刻度列表
_yearList = [ y.get('year') for y in _resultList ]
## 刻度列表修正(去重)
_yearList = list_uniq(_yearList)
## 刻度列表重新排序
list.sort(_yearList)
## 获取series列表
_seriesList = [ series(_resultList, _x, _divUnit, picType, _yearList) for _x in _textTagList ]
## 返回年份列表和series列表
return {
'year':_yearList,
'series':_seriesList
}
def main():
## 获取不同分支数据
longDict = series_mod(branchName='long')
shortDict = series_mod(branchName='short')
## 获取series列表
seriesList = longDict.get('series') + shortDict.get('series')
## 统计汇总数据
totalList = [ round(sum(x),2) for x in list(zip(*[y.get('data') for y in seriesList])) ]
## 修正series列表数据(添加汇总数据)
seriesList.append({
'name' : totalLabel,
'type' : picType,
'data' : totalList
})
## 获取X轴刻度列表
yearList = list_uniq(longDict.get('year') + shortDict.get('year') )
## 获取标签列表
legendList = [ x.get('name') for x in seriesList ]
## 生成输出字典
outputDict = {
'title' : titleName,
'legend' : legendList,
'color' : colorList,
'xlabel' : yearList,
'yname' : yUnit,
'series' : seriesList
}
## 转json字符串,不转码
outputStr = json.dumps(
outputDict,
ensure_ascii = False
)
## 返回结果
return outputStr
def series_mod(**kwargs):
## 通过参数获取分支名称
_branchName = kwargs.get('branchName')
## 获取对应分支数据在yaml数据中
_textTagList = yamlDict.get(_branchName).get('textTagList')
_divUnit = yamlDict.get(_branchName).get('divUnit') or 1
_filterKeyList = [w.get('wds') for w in _textTagList]
## HQL查询语句
if len(_filterKeyList) == 1:
_HQL = "SELECT wds,year,value FROM `{}`.`{}` WHERE wds='{}' ORDER BY year".format(
hiveDB, hiveTable, _filterKeyList[0])
elif len(_filterKeyList) > 1:
_HQL = "SELECT wds,year,value FROM `{}`.`{}` WHERE wds IN {} ORDER BY year".format(
hiveDB, hiveTable, tuple(_filterKeyList))
## 结果查询
_resultList = spark_hive_query(HQL=_HQL)
## 获取年份列表并去重
_yearList = [x.year for x in _resultList]
_yearList = list_uniq(_yearList)
## 统计series列表
_seriesList = []
for x in _textTagList:
_perList = [
per_list(y, x.get('wds'), _resultList, _divUnit) for y in _yearList
]
_seriesList.append({
'name': x.get('name'),
'type': picType,
'data': _perList
})
## 返回年份列表和series列表
return {'year': _yearList, 'series': _seriesList}
def main():
## 获取不同分支列表
longDict = series_mod(branchName='long')
shortDict = series_mod(branchName='short')
## 合并分支列表
seriesList = longDict.get('series') + shortDict.get('series')
## 统计合计列表
totalList = [
round(sum(x), 2)
for x in list(zip(*[y.get('data') for y in seriesList]))
]
## 合并合计列表
seriesList.append({'name': totalLabel, 'type': picType, 'data': totalList})
## 获取年份列表
yearList = list_uniq(longDict.get('year') + shortDict.get('year'))
## 获取标签
legendList = [x.get('name') for x in seriesList]
## 定义输出格式(字典)
outputDict = {
'title': titleName,
'legend': legendList,
'color': colorList,
'xlabel': yearList,
'yname': yUnit,
'series': seriesList
}
## 转json字符串,不转码
outputStr = json.dumps(outputDict, ensure_ascii=False)
## 存储结果到mysql数据库
mysql_update(dataLabel=postal.label, dataValues=outputStr)
def main():
## ## 从yaml文件数组中获取数据
textTagList = yamlDict.get('textTagList')
legendList = [x.get('name') for x in textTagList]
colorList = yamlDict.get('colorList')
titleName = yamlDict.get('titleName')
yUnit = yamlDict.get('yUnit')
divUnit = yamlDict.get('divUnit') or 1
picType = yamlDict.get('picType')
## 获取数据库信息
mysqlDB = express.db or mysql_conn.db
mysqlTB = express.tb or mysql_conn.tb
mysqlHost = express.host or mysql_conn.host
mysqlUser = express.user or mysql_conn.user
mysqlPass = express.passwd or mysql_conn.passwd
mysqlPort = express.port or mysql_conn.port
mysqlChrt = express.charset or mysql_conn.charset
## 获取数据索引列表
keyList = [e.get('wds') for e in textTagList]
## 将索引列表转元组(方便数据库IN逻辑查询)
keyTuple = tuple(keyList)
## 根据元组长度判断sql查询语句
### python单元素元组写法是(e,), 直接用IN查询会失败
if len(keyTuple) == 1:
sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds='{}'".format(
mysqlDB, mysqlTB, keyTuple[0])
elif len(keyTuple) > 1:
sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds IN {}".format(
mysqlDB, mysqlTB, keyTuple)
## 调用数据库查询函数查询结果
resultList = mysql_query(cmd=sql,
host=mysqlHost,
user=mysqlUser,
passwd=mysqlPass,
port=mysqlPort,
charset=mysqlChrt)
## 获取X轴刻度列表
yearList = [y.get('year') for y in resultList]
## 刻度列表修正(去重)
yearList = list_uniq(yearList)
## 刻度列表重新排序
list.sort(yearList)
## 获取series列表
seriesList = [
series(resultList, x, divUnit, picType, yearList) for x in textTagList
]
## 生成输出字典
outputDict = {
'title': titleName,
'legend': legendList,
'color': colorList,
'xlabel': yearList,
'yname': yUnit,
'series': seriesList
}
## 转json字符串,不转码
outputStr = json.dumps(outputDict, ensure_ascii=False)
## 返回数据
return outputStr
def main():
## 定义相关标签及输出格式
textTagList = yamlDict.get('textTagList')
legendList = [x.get('name') for x in textTagList]
colorList = yamlDict.get('colorList')
titleName = yamlDict.get('titleName')
yUnit = yamlDict.get('yUnit')
divUnit = yamlDict.get('divUnit') or 1
picType = yamlDict.get('picType')
## 定义数据库名称，表名称及查询语句
## 备注: 测试环境性能太差，为节省时间，这里直接查询出运输业人数总数据。
hiveDB = lengthtraveller.hiveDB or hive_conn.hiveDB
hiveTable = lengthtraveller.hiveTable or hive_conn.hiveTable
## 设置一个列表收集wds值
keyList = [e.get('wds') for e in textTagList]
## wds集合转元组用于HQL查询
keyTuple = tuple(keyList)
## HQL查询语句
if len(keyTuple) == 1:
HQL = "SELECT wds,year,value FROM `{}`.`{}` WHERE wds='{}'".format(
hiveDB, hiveTable, keyTuple[0])
elif len(keyTuple) > 1:
HQL = "SELECT wds,year,value FROM `{}`.`{}` WHERE wds IN {}".format(
hiveDB, hiveTable, keyTuple)
## 获取查询结果，输出列表，详情查看functions脚本spark_hive_query模块
resultList = spark_hive_query(HQL=HQL)
## 获取年份列表
yearList = [y.year for y in resultList]
## 年份列表去重
yearList = list_uniq(yearList)
## 年份类别排序
list.sort(yearList)
## 定义series列表
seriesList = []
for x in textTagList:
## 获取dfwds.code的数据列表
perList = [
per_list(y, x.get('wds'), resultList, divUnit) for y in yearList
]
## 数据汇总列表修正(汇总列表-dfwds.code列表)
seriesList.append({
'name': x.get('name'),
'type': picType,
'data': perList
})
## 定义输出格式(字典)
outputDict = {
'title': titleName,
'legend': legendList,
'color': colorList,
'xlabel': yearList,
'yname': yUnit,
'series': seriesList
}
## 转json字符串,不转码
outputStr = json.dumps(outputDict, ensure_ascii=False)
## 存储结果到mysql数据库
mysql_update(dataLabel=lengthtraveller.label, dataValues=outputStr)
def main():
## 从yaml文件数组中获取数据
textTagList = yamlDict.get('textTagList')
legendList = [x.get('name') for x in textTagList]
colorList = yamlDict.get('colorList')
titleName = yamlDict.get('titleName')
yUnit = yamlDict.get('yUnit')
divUnit = yamlDict.get('divUnit') or 1
picType = yamlDict.get('picType')
otherTagList = yamlDict.get('otherTagList')
otherName = yamlDict.get('otherName')
## 获取数据库信息
mysqlDB = employ.db or mysql_conn.db
mysqlTB = employ.tb or mysql_conn.tb
mysqlHost = employ.host or mysql_conn.host
mysqlUser = employ.user or mysql_conn.user
mysqlPass = employ.passwd or mysql_conn.passwd
mysqlPort = employ.port or mysql_conn.port
mysqlChrt = employ.charset or mysql_conn.charset
## 获取数据索引列表
keyList = [e.get('wds') for e in textTagList]
## 将索引列表转元组(方便数据库IN逻辑查询)
keyTuple = tuple(keyList)
## 根据元组长度判断sql查询语句
### python单元素元组写法是(e,), 直接用IN查询会失败
if len(keyTuple) == 1:
sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds='{}'".format(
mysqlDB, mysqlTB, keyTuple[0])
elif len(keyTuple) > 1:
sql = "SELECT year,value,wds FROM `{}`.`{}` WHERE wds IN {}".format(
mysqlDB, mysqlTB, keyTuple)
## 同上, 另一个索引sql查询语句
if len(otherTagList) == 1:
otherSql = "SELECT year,value value FROM `{}`.`{}` WHERE wds={}".format(
mysqlDB, mysqlTB, otherTagList[0])
elif len(otherTagList) > 1:
otherSql = "SELECT year,sum(value) AS value FROM `{}`.`{}` WHERE wds IN {} group by year".format(
mysqlDB, mysqlTB, tuple(otherTagList))
## 调用数据库查询函数查询结果
resultList = mysql_query(cmd=sql,
host=mysqlHost,
user=mysqlUser,
passwd=mysqlPass,
port=mysqlPort,
charset=mysqlChrt)
## 同上, 另一个索引列表查询结果
otherResultList = mysql_query(cmd=otherSql,
host=mysqlHost,
user=mysqlUser,
passwd=mysqlPass,
port=mysqlPort,
charset=mysqlChrt)
## 获取X轴刻度列表
yearList = [y.get('year') for y in resultList]
## X轴刻度列表修正(添加另一个索引的刻度列表)
yearList += [y.get('year') for y in otherResultList]
## 刻度列表修正(去重)
yearList = list_uniq(yearList)
## 刻度列表重新排序
list.sort(yearList)
## 获取series列表
seriesList = [
series(resultList, x, divUnit, picType, yearList) for x in textTagList
]
## 获取other列表
otherPerList = [
round(w.get('value') / divUnit, 2) for y in yearList
for w in otherResultList if w.get('year') == y
]
## 修正series列表(汇总other列表)
seriesList.append({
'name': otherName,
'type': picType,
'data': otherPerList
})
## 修正标签列表
legendList.append(otherName)
## 生成输出字典
outputDict = {
'title': titleName,
'legend': legendList,
'color': colorList,
'xlabel': yearList,
'yname': yUnit,
'series': seriesList
}
## 转json字符串,不转码
outputStr = json.dumps(outputDict, ensure_ascii=False)
## 返回数据
return outputStr