def find_one(cls, modelType, modelName, dataSourceName):
db_session.commit()
return db_session.query(cls).filter(
cls.modelType == modelType,
cls.modelName == modelName,
cls.dsName == dataSourceName,
).first()
def find_all_dataSource(cls, modelType, modelName):
"""
"""
sql = """SELECT dataSourceName"""
sql += """ FROM ma_train WHERE modelType='%s' AND modelName='%s'
""" % (modelType, modelName)
db_session.commit()
return db_session.execute(sql).fetchall()
def find_modelNames_by_modelType(cls, modelType):
"""
"""
sql = """SELECT a.modelType modelType, a.modelName modelName"""
sql += """ FROM ma_train a WHERE a.modelType='%s' ORDER BY a.updateTime DESC
""" % (modelType)
db_session.commit()
return db_session.execute(sql).fetchall()
def find_modelNames_by_modelType_dsName(cls, modelType, dataSourceName):
"""
"""
sql = """SELECT a.modelType modelType, a.modelName modelName"""
sql += """ FROM ma_predict a WHERE a.modelType='%s' AND a.dsName='%s' AND a.predictState=2 ORDER BY a.updateTime DESC
""" % (modelType, dataSourceName)
db_session.commit()
return db_session.execute(sql).fetchall()
def find_test(cls):
"""
"""
sql = """SELECT a.modelType modelType, a.modelName modelName, a.predictDir predictDir,a.predictState predictState,b.modelParams4ml modelParams4ml FROM ma_predict a, ma_train b
WHERE a.modelType='产品质量软测量' AND a.dsName='多氟多_2#_质量软测#在线数据' AND a.modelName=b.modelName AND a.modelName IN ("质量软测量_2#", "质量软测量_2#001", "质量软测量_2#003", "质量软测量_2#004")
ORDER BY a.updateTime DESC"""
db_session.commit()
return db_session.execute(sql).fetchall()
def find_modelNames_by_dataSouce(cls, modelType, dataSourceName):
"""
"""
sql = """SELECT a.modelType modelType, b.dsName dsName, a.modelName modelName"""
sql += """ FROM ma_train a, ma_data_source b
WHERE a.dsId=b.id AND a.modelType='%s' AND b.dsName='%s'
ORDER BY a.updateTime DESC
""" % (modelType, dataSourceName)
db_session.commit()
return db_session.execute(sql).fetchall()
def find_by_model_names(cls, modelType, modelNames, dataSourceName):
"""
"""
if len(modelNames) == 1:
modelNames_t = f"('{modelNames[0]}')"
else:
modelNames_t = tuple(modelNames)
sql = """SELECT a.modelType modelType, a.modelName modelName, a.predictDir predictDir,a.dsId dsId,a.predictState predictState FROM ma_predict a
WHERE a.modelType='%s' AND a.dsName='%s' AND a.modelName IN %s ORDER BY a.updateTime DESC""" \
% (modelType, dataSourceName, modelNames_t)
db_session.commit()
return db_session.execute(sql).fetchall()
def insert(cls,
modelType,
modelName,
dsId,
modelParams,
modelParams4ml,
trainDir,
trainState=0,
state=1,
mylog=None):
"""
"""
newRecord = cls(modelType=modelType,
modelName=modelName,
dsId=dsId,
modelParams=modelParams,
modelParams4ml=modelParams4ml,
trainDir=trainDir,
trainState=trainState,
state=state)
try:
db_session.add(newRecord)
db_session.commit()
str = "insert [ma_train] ok...modelType={}...modelName={}...dsId={}".format(
modelType, modelName, dsId)
if mylog:
mylog.info(str)
else:
print(str)
return newRecord
except Exception as e:
str = "DB Error, insert [ma_train]..., {}".format(e)
if mylog:
mylog.error(str)
else:
print(str)
db_session.rollback()
return None
def insert(cls,
trainId,
dsId,
modelType,
modelName,
dsName,
predictDir="",
predictState=0,
state=1,
mylog=None):
"""
"""
newRecord = cls(trainId=trainId,
dsId=dsId,
modelType=modelType,
modelName=modelName,
dsName=dsName,
predictDir=predictDir,
predictState=predictState,
state=state)
try:
db_session.add(newRecord)
db_session.commit()
str = "insert [ma_predict] ok...modelType={}...modelName={}...dsId={}...trainId={}".format(
modelType, modelName, dsId, trainId)
if mylog:
mylog.info(str)
else:
print(str)
return newRecord
except Exception as e:
str = "DB Error, insert [ma_predict]..., {}".format(e)
if mylog:
mylog.error(str)
else:
print(str)
db_session.rollback()
return None
def count(cls, tb_name):
sql = f"SELECT count(*) FROM {tb_name}"
db_session.commit()
return db_session.execute(sql).fetchone()[0]
def mysql_update_predictState(cls, modelType, dsName):
sql = f"""UPDATE ma_predict a,ma_data_source b SET a.predictState=0 """ \
f"""WHERE a.predictState IN (-1,2) AND a.modelType="{modelType}" AND a.dsName="{dsName}" AND a.dsId=b.id;"""
db_session.execute(sql)
db_session.commit()
def delete(cls, tb_name, nums):
sql = f"DELETE FROM {tb_name} LIMIT {nums};"
db_session.execute(sql)
db_session.commit()
def reset_id(cls, tb_name, id=1):
sql = f"ALTER TABLE {tb_name} AUTO_INCREMENT={id};"
db_session.execute(sql)
db_session.commit()