def test(ColumnID, TableName):
ret = MyPymysql('mysql')
ave_std_n_sql = """SELECT
AVG(
CONVERT (
Ifnull(`{}`, 0),
DECIMAL (16, 4)
)
) avgdata,
STDDEV_SAMP(
CONVERT (
Ifnull(`{}`, 0),
DECIMAL (16, 4)
)
) STDEVALL,
COUNT(1) SUMDATA
FROM
{} db
INNER JOIN B_option optDB ON optDB.projectID = 7
AND optDB.columnID = '{}'
AND Ifnull(`{}`, 0) = optDB.optionID
AND optDB.effectFlag = 1
WHERE
rtrim(Ifnull(`{}`, '')) <> ''
""".format(ColumnID, ColumnID, TableName, ColumnID,
ColumnID, ColumnID)
res = ret.selectall_sql(ave_std_n_sql)
print res
def SeriesSubOptModel(ProjID, columnID):
ret = MyPymysql('mysql')
sql = "select optionID, optionNM from `b_option` where projectID={} AND columnID='{}' AND effectFlag=1 ORDER BY CONVERT (optionID, SIGNED);".format(
ProjID, columnID)
data = ret.selectall_sql(sql)
ret.close()
return data
def CompareMean(ProjID, ColumnID, ColumnEXID, TableName, Where):
sql = """
SELECT
COUNT(1) SUMDATA,
AVG(
CONVERT (
Ifnull(repDB.{}, 0),
decimal (20, 8)
)
) avgdata,
SUM(
CONVERT (
Ifnull(repDB.{}, 0),
decimal (20, 8)
)
) SUMALL,
STDDEV_SAMP(
CONVERT (
Ifnull(repDB.{}, 0),
decimal (20, 8)
)
) STDEVALL,
optDB.optionID optionID,
max(
Ifnull(optDB.optionNM, N' ')
) optionNM,
MAX(
CONVERT (
Ifnull(repDB.{}, 0),
decimal (20, 8)
)
) colMAX,
MIN(
CONVERT (
Ifnull(repDB.{}, 0),
decimal (20, 8)
)
) colMIN
FROM
`{}` repDB
INNER JOIN b_option optDB ON optDB.projectID = {}
AND optDB.columnID = '{}'
AND rtrim(Ifnull(repDB.{}, '')) = optDB.optionID
AND 1 = optDB.effectFlag
AND rtrim(Ifnull(repDB.{}, '')) <> ''
WHERE
{}
GROUP BY
optDB.optionID
ORDER BY
CONVERT (optionID, SIGNED)
""".format(ColumnID, ColumnID, ColumnID, ColumnID, ColumnID, TableName,
ProjID, ColumnEXID, ColumnEXID, ColumnEXID, Where)
ret = MyPymysql('mysql')
data = ret.selectall_sql(sql)
# print data
ret.close()
return data
def test1():
ret = MyPymysql('mysql')
ColumnID = "P2Q02"
TableName = "dc_bys2016_dataandusers"
Where = "1=1"
sumsql = "SELECT SUM(`{}`) AS sumdata, MAX(`{}`) AS maxdata,MIN(`{}`) AS mindata,AVG(`{}`) AS avgdata,STDDEV(`{}`) AS stddata FROM `{}` WHERE {};" \
.format(ColumnID, ColumnID, ColumnID, ColumnID, ColumnID, TableName, Where)
DataCount = ret.selectone_sql(sumsql)
print DataCount
ret.close()
class MyPagesAndPageDatas():
"""
from common.util.MyPaging import Pagination
res = MyPagesAndPageDatas()
dataCount = res.SelectItemPagesCountModel("2017091710094680349524135490")
obj = Pagination(dataCount, current_page=52)
start = obj.start
end = obj.end
data = res.SelectItemPagesDataModel(start, end, obj.appear_page)
print(len(data))
res.close()
"""
def __init__(self, libname="notdbMysql"):
self.libname = libname
self.res = MyPymysql(self.libname)
def SelectItemPagesCountModel(self, QuesID):
selectBaseTableSql = "select DataTableID, DataTableName, DatabaseName from db_metadata.meta_data_table WHERE `QuesID`='{}' AND DataTableStatus=1;".format(
QuesID)
self.baseTableData = self.res.selectone_sql(
selectBaseTableSql) # {'DataTableName': '', 'DataTableID': '', 'DatabaseName': ''}
dataSql = """select count(1) as count from {}.{}""".format(self.baseTableData["DatabaseName"],
self.baseTableData["DataTableName"])
result = self.res.selectone_sql(dataSql)
return result["count"]
def SelectItemPagesDataModel(self, start, end, appear_page):
sql = """select `StartTime`, `EndTime`, `NominalTime`, `SpaceName`, `Topic`, `Index`, `DataValue`, `DataDescription` from {}.{} limit {}, {};""".format(
self.baseTableData["DatabaseName"],
self.baseTableData["DataTableName"],
start,
appear_page)
result = self.res.selectall_sql(sql)
return result
def close(self):
self.res.close()
def CreateMetaProj(data):
sql = "insert into `meta_project` SET ProjectID={}, UserID={}, ProjectName='{}', ProjectOrgan='{}', ProjectSubject='{}', " \
"SubjectField={}, ProjectLevel={}, ProjectSource={}, FundsSource={}, ProjectSummary='{}', CycleType={}, CycleSpan='{}', " \
"TeamIntroduction='{}', ProjectPublic={}, ProjectStatus={}, EditUserID={};".format(
data["ProjectID"],
data["UserID"],
data["ProjectName"],
data["ProjectOrgan"],
data["ProjectSubject"],
data["SubjectField"],
data["ProjectLevel"],
data["ProjectSource"],
data["FundsSource"],
data["ProjectSummary"],
data["CycleType"],
int(data["CycleSpan"]),
data["TeamIntroduction"],
data["ProjectPublic"],
data["ProjectStatus"],
data["EditUserID"])
# print(sql)
ret = MyPymysql('metadata')
ret.idu_sql(sql)
ret.close()
def case(TableName, ColumnID, UserID, ProjectID, Where):
ret = MyPymysql('mysql')
# case_sql = "SELECT COUNT(1) SUMALL FROM `{}` repDBALL WHERE {} AND rtrim(Ifnull(repDBALL.{}, '')) <> '';".format(TableName,Where,ColumnID) # yi
case_sql = """SELECT
COUNT(1) SUMALL from `{}` where {} in (
SELECT
repDBALL.{}
FROM
`{}` repDBALL
INNER JOIN b_option optDB ON optDB.userID = {}
AND optDB.projectID = {}
AND optDB.columnID = '{}'
AND Ifnull(repDBALL.{}, ' ') like
CONCAT('%.', optDB.optionID, '.%')
WHERE
1 = 1
AND rtrim(Ifnull(repDBALL.{}, '')) <> '');""".format(TableName, ColumnID, ColumnID, TableName, UserID, ProjectID, ColumnID, ColumnID, ColumnID)
sumall_sql = "SELECT COUNT(1) SUMALL FROM `{}` repDBALL WHERE {};".format(TableName, Where)
valid_sql = """
SELECT
COUNT(1) SUMDATA ,
optDB.optionID optionID,
max(
Ifnull(optDB.optionNM, N' ')
) optionNM,
max(
Ifnull(optDB.effectFlag, 0)
) effectFlag
FROM
`{}` repDB
INNER JOIN b_option optDB ON optDB.userID = {}
AND optDB.projectID = {}
AND optDB.columnID = '{}'
AND locate(
CONCAT('.', optDB.optionID, '.'),
Ifnull(repDB.{}, ' ')
) > 0
WHERE {}
GROUP BY
optDB.optionID
ORDER BY
CONVERT (optDB.optionID, SIGNED)
""".format(TableName, UserID, ProjectID, ColumnID, ColumnID, Where)
case_data = ret.selectall_sql(case_sql)
sumall_data = ret.selectall_sql(sumall_sql)
valid_data = ret.selectall_sql(valid_sql)
ret.close()
# print case_data
# print sumall_data
# print valid_data
return case_data[0], sumall_data[0], valid_data
def MeanModel(ColumnID, ColumnName, TableName, Where):
ret = MyPymysql('mysql')
sumsql = "SELECT COUNT(1) as cnt ,SUM(`{}`) AS sumdata, MAX(`{}`) AS maxdata,MIN(`{}`) AS mindata,AVG(`{}`) AS avgdata,STDDEV(`{}`) AS stddata FROM `{}` WHERE {};" \
.format(ColumnID, ColumnID, ColumnID, ColumnID, ColumnID, TableName, Where)
sumdata = ret.selectone_sql(sumsql)
MidSql = "SELECT {} FROM {} Order by {} Asc;".format(
ColumnID, TableName, ColumnID)
MidRes = ret.selectall_sql(MidSql)
modesql = "select `{}`, count(1) as ab from `{}` where {} group by `{}` order by ab desc;".format(
ColumnID, TableName, Where, ColumnID)
modeData = ret.selectall_sql(modesql)
ret.close()
return sumdata, MidRes, modeData
def frequency_get(UserID, ProjID, QuesID, ColumnID, ColumnName, TableName,
Where):
ret = MyPymysql('mysql')
if Where != True:
sql = "SELECT {}, Count(1) AS DataCount FROM {} GROUP BY {} Order by {} Asc".format(
ColumnID, TableName, ColumnID, ColumnID)
else:
sql = "SELECT {}, Count(1) AS DataCount FROM {} WHERE GROUP BY {} Order by {} Asc".format(
ColumnID,
TableName,
# -----问题
ColumnID,
ColumnID)
OptionSql = "SELECT optionNM, effectFlag, optionID FROM {} WHERE `userID`={} AND `projectID`={} AND `columnID`='{}';".format(
"b_option", UserID, ProjID, ColumnID) # 暂时缺quesID
res = ret.selectall_sql(sql)
print res
res1 = ret.selectall_sql(OptionSql)
print res1
MidSql = "SELECT {} FROM {} Order by {} Asc;".format(
ColumnID, TableName, ColumnID)
res2 = ret.selectall_sql(MidSql)
print res2
li = []
for i in res2:
li.append(i[ColumnID])
print li
if len(li) % 2 == 0:
print(li[len(li) / 2] + li[len(li) / 2 - 1]) / 2
else:
print li[len(li) / 2]
ret.close()
def SeriesTableModel(ProjID, ColumnID, ColumnEXID, TableName, Where):
sql_info = """SELECT
COUNT(1) SUMDATA,
repDB.{} optID,
CONVERT (repDB.{}, CHAR) optEXID,
max(
Ifnull(optDB.optionNM, N' ')
) optionNM,
max(
Ifnull(optDBEX.optionNM, N' ')
) optionEXNM
FROM
`{}` repDB
INNER JOIN `b_option` optDB ON repDB.projectID = optDB.projectID
AND optDB.columnID = '{}'
AND Ifnull(repDB.{}, '') = optDB.optionID
INNER JOIN b_option optDBEX ON repDB.projectID = optDBEX.projectID
AND optDBEX.columnID = '{}'
AND rtrim(Ifnull(repDB.{}, ' ')) <> ''
AND 1 = optDBEX.effectFlag
AND Ifnull(repDB.{}, '') = optDBEX.optionID
WHERE
{}
GROUP BY
repDB.{}, repDB.{}
ORDER BY
CONVERT (repDB.{}, SIGNED),
CONVERT (repDB.{}, SIGNED)
""".format(ColumnID, ColumnEXID, TableName, ColumnID, ColumnID,
ColumnEXID, ColumnEXID, ColumnEXID, Where, ColumnEXID,
ColumnID, ColumnEXID, ColumnID)
sql_data = """
SELECT
AVG(
CONVERT (
Ifnull(repDB1.{}, 0),
DECIMAL (20, 8)
)
) avgdata,
SUM(
CONVERT (
Ifnull(repDB1.{}, 0),
DECIMAL (20, 8)
)
) SUMALL,
STDDEV_SAMP(
CONVERT (
Ifnull(repDB1.{}, 0),
DECIMAL (20, 8)
)
) STDEVALL,
COUNT(1) SUMDATA,
repDB1.{} optEXID,
max(
Ifnull(optDBEX1.optionNM, N' ')
) optionEXNM
FROM
`{}` repDB1
INNER JOIN b_option optDBEX1 ON optDBEX1.projectID = repDB1.projectID
AND optDBEX1.columnID = '{}'
AND rtrim(Ifnull(repDB1.{}, ' ')) <> ''
AND 1 = optDBEX1.effectFlag
AND Ifnull(repDB1.{}, '') = optDBEX1.optionID
WHERE
{}
GROUP BY
repDB1.{}
ORDER BY
CONVERT (optEXID, SIGNED)
""".format(ColumnID, ColumnID, ColumnID, ColumnEXID, TableName,
ColumnEXID, ColumnEXID, ColumnEXID, Where,
ColumnEXID)
ret = MyPymysql('mysql')
info_data = ret.selectall_sql(sql_info)
data = ret.selectall_sql(sql_data)
# print info_data
# print data
ret.close()
return info_data, data
def frequency_get(UserID, ProjID, QuesID, ColumnID, ColumnName, TableName,
Where):
import time
t1 = time.time()
try:
status = 2000
CountSql = "SELECT {}, Count(1) AS DataCount FROM {} WHERE {} GROUP BY {} Order by {} Asc".format(
ColumnID, TableName, Where, ColumnID, ColumnID)
ret = MyPymysql('mysql')
DataCount = ret.selectall_sql(CountSql)
if len(DataCount) == False:
status = 5002
# 注: Effectflag {1:有效, 0:无效}
OptionSql = "SELECT optionNM, effectFlag, optionID FROM {} WHERE userID={} AND projectID={} AND columnID='{}';".format(
"b_option", UserID, ProjID, ColumnID) # 暂时缺quesID
OptionRes = ret.selectall_sql(OptionSql)
overall_total = 0 # 整体合计
effective_total = 0 # 有效合计 1
missing_total = 0 # 缺失合计
li = []
for i in range(len(DataCount)):
for j in range(len(OptionRes)):
if int(OptionRes[j]["optionID"]) == int(
DataCount[i][ColumnID]) and int(
OptionRes[j]['effectFlag']) == 1:
effective_total += DataCount[i]["DataCount"]
else:
missing_total += DataCount[i]["DataCount"]
overall_total += DataCount[i]["DataCount"]
for k in range(len(DataCount)):
optionNM = u"缺失"
for v in range(len(OptionRes)):
if int(OptionRes[v]["optionID"]) == int(
DataCount[k][ColumnID]):
optionNM = OptionRes[v]["optionNM"]
break
countN = DataCount[k]["DataCount"]
if effective_total:
PER = float(countN) / float(effective_total)
countPER = "%.10f" % PER
else:
countPER = 0
ave_std_n_sql = """SELECT
AVG(
CONVERT (
Ifnull(`{}`, 0),
DECIMAL (16, 4)
)
) avgdata,
STDDEV_SAMP(
CONVERT (
Ifnull(`{}`, 0),
DECIMAL (16, 4)
)
) STDEVALL,
COUNT(1) SUMDATA
FROM
{} db
INNER JOIN B_option optDB ON optDB.projectID = {}
AND optDB.columnID = '{}'
AND Ifnull(`{}`, 0) = optDB.optionID
AND optDB.effectFlag = 1
WHERE
rtrim(Ifnull(`{}`, '')) <> ''
""".format(ColumnID, ColumnID, TableName, ProjID, ColumnID,
ColumnID, ColumnID)
ave_std_n_res = ret.selectall_sql(ave_std_n_sql)
if len(ave_std_n_res) != True:
status = 5002
else:
average = ave_std_n_res[0]["avgdata"]
stdev = ave_std_n_res[0]["STDEVALL"]
MidSql = "SELECT {} FROM {} Order by {} Asc;".format(
ColumnID, TableName, ColumnID)
midli = []
MidRes = ret.selectall_sql(MidSql)
for i in MidRes:
midli.append(i[ColumnID])
if len(midli) % 2 == 0:
midValue = (float(midli[len(midli) / 2]) +
float(midli[len(midli) / 2 - 1])) / 2
else:
midValue = midli[len(midli) / 2]
countTotal = effective_total
score = average / 5 * 100
ValueDict = {}
ValueDict["columnID"] = ColumnID
ValueDict["questionshortNM"] = ColumnName
ValueDict["optionID"] = OptionRes[k]["optionID"]
ValueDict["optionNM"] = optionNM
ValueDict["countN"] = countN
ValueDict["countPER"] = countPER
ALLPER = "%.10f" % (float(countN) / float(overall_total))
ValueDict["countALLPER"] = ALLPER # --
ValueDict["average"] = str(average)
ValueDict["stdev"] = str(stdev)
ValueDict["midValue"] = midValue
ValueDict["countTotal"] = effective_total
li.append(ValueDict)
ret.close()
except Exception as e:
app.logger.error(e)
status = 5002
li = ""
return jsonify(result(status, value=li))
def __init__(self, libname="notdbMysql"):
self.libname = libname
self.res = MyPymysql(self.libname)