def insertOption(dbcon, OptionName, OptionCode, OptionType, RelGUID=None):
import Public.PublicFun as PublicFun
GUID = PublicFun.createID()
sql = (
"INSERT INTO [dbo].[OPTAAA]([GUID],[OPTAAA001],[OPTAAA002],[OPTAAA003],[D_INSERTUSER],[D_INSERTTIME],[D_MODIFYUSER],[D_MODIFYTIME])"
+ "VALUES(?,?,?,?,'System',?,'','')")
dbcon.Execute(sql, (GUID, OptionName, OptionCode, OptionType,
PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS")))
if RelGUID is not None:
sql = (
"INSERT INTO [dbo].[OPTAAB]([GUID],[OPTAAB001],[OPTAAB002],[D_INSERTUSER],[D_INSERTTIME],[D_MODIFYUSER],[D_MODIFYTIME])"
+ "VALUES(?,?,?,'System',?,'','')")
dbcon.Execute(sql, (PublicFun.createID(), RelGUID, GUID,
PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS")))
return GUID
def addQueue(SystemName, QueueType, Path, Files, Param):
import Public.PublicFun as PublicFun
import json
GUID = PublicFun.createID()
#str(Param)
jsonData = Param
if type(Param) is not str:
jsonData = json.dumps(Param, separators=(',', ':'))
dbcon = getQueueDBConnect()
if checkQueue(SystemName, QueueType, jsonData, dbcon):
retry = 0
while True:
try:
if retry > 10:
break
else:
D_INSERTTIME = PublicFun.getNowDateTime(
"YYYY/MM/DD HH:MM:SS")
sql = (
"INSERT INTO [dbo].[JobQueue]([GUID],[SystemName],[QueueType],[Path],[Files],[Param],[D_INSERTTIME])"
+ "VALUES(?,?,?,?,?,?,?)")
dbcon.Execute(sql, (GUID, SystemName, QueueType, Path,
Files, jsonData, D_INSERTTIME))
break
except Exception as ex:
#若是寫入失敗, 則重試, 最多試10次
retry += 1
print("Retry:" + str(retry))
dbcon.close()
def insertOption(dbcon, OptionName, RelGUID=None):
import Public.PublicFun as PublicFun
GUID = PublicFun.createID()
sql = (
"INSERT INTO [dbo].[OPTAAA]([GUID],[OPTAAA001],[D_INSERTUSER],[D_INSERTTIME],[D_MODIFYUSER],[D_MODIFYTIME])"
+ "VALUES('" + GUID + "',N'" + PublicFun.SQLFilter(OptionName) +
"','System','" + PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS") +
"','','')")
dbcon.Execute(sql)
if RelGUID is not None:
sql = (
"INSERT INTO [dbo].[OPTAAB]([GUID],[OPTAAB001],[OPTAAB002],[D_INSERTUSER],[D_INSERTTIME],[D_MODIFYUSER],[D_MODIFYTIME])"
+ "VALUES('" + PublicFun.createID() + "','" + RelGUID + "','" +
GUID + "','System','" +
PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS") + "','','')")
dbcon.Execute(sql)
return GUID
def insertMappingList(dbcon, MapType, Value, RelValue):
import Public.PublicFun as PublicFun
GUID = PublicFun.createID()
sql = (
"INSERT INTO [dbo].[MAPAAA]([GUID],[MAPAAA001],[MAPAAA002],[MAPAAA003],[D_INSERTUSER],[D_INSERTTIME],[D_MODIFYUSER],[D_MODIFYTIME])"
+ "VALUES(?,?,?,?,'System',?,'','')")
dbcon.Execute(sql, (GUID, MapType, Value, RelValue,
PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS")))
return GUID
def save_product_info(db_connection, company_guid, product_info, file_name):
product_guid = check_product_info(db_connection, company_guid,
product_info["product_name"])
print(product_info)
if not product_guid:
insert_sql = (
"INSERT INTO CompanyProduct(GUID, CompanyProduct001,CompanyProduct002,CompanyProduct003,CompanyProduct004,"
"CompanyProduct005,CompanyProduct006, CompanyProduct007, CompanyProduct008, D_INSERTUSER, D_INSERTTIME)"
" VALUES (?,?,?,?,?,?,?,?,?,?,?)")
try:
db_connection.Execute(
insert_sql,
(PublicFun.createID(), company_guid, "Momo",
product_info.get("product_name",
''), product_info.get("product_format", ''),
product_info.get("other_info",
''), product_info.get("product_place", ''),
product_info.get("brand_name", ''), file_name, "MomoCrawler",
PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS")))
logger.logger.info("Insert")
print(file_name)
except Exception as message:
logger.logger.error(message)
pass
else:
update_sql = "UPDATE CompanyProduct set CompanyProduct003=?,CompanyProduct004=?,CompanyProduct005=?,CompanyProduct006=?,CompanyProduct007=?,CompanyProduct008=?, D_MODIFYUSER=?, D_MODIFYTIME=? WHERE GUID = ?"
try:
db_connection.Execute(
update_sql,
(product_info.get("product_name",
''), product_info.get("product_format", ''),
product_info.get("other_info",
''), product_info.get("product_place", ''),
product_info.get("brand_name", ''), file_name, "MomoCrawler",
PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS"),
product_guid))
# logger.logger.info("Update")
except Exception as message:
logger.logger.error(message)
pass
def Query(dbcon, DataObject, WhereClause="", parameter=None):
import Public.PublicFun as PublicFun
sql = DataObject.QueryStr
if WhereClause is not None and WhereClause != "":
WhereClause = " WHERE " + WhereClause
excuteSQL = sql + WhereClause
FindRow = dbcon.GetDataTable(excuteSQL, parameter)
if FindRow is not None and len(FindRow) > 0:
DataObject.DataRow = FindRow[0]
else:
DataObject.DataRow = [""] * len(DataObject.Fields)
DataObject.TimeStamp = PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS")
return DataObject
def save_product_info(db_connection, company_guid, product_info):
"""新增產品資訊
"""
product_guid = check_product_info(db_connection, company_guid,
product_info["NAME"])
if len(product_guid) == 0:
logger.info("Insert")
insert_sql = (
"INSERT INTO CompanyProduct(GUID, CompanyProduct001,CompanyProduct002,CompanyProduct003,CompanyProduct004,CompanyProduct005,CompanyProduct006, D_INSERTUSER, D_INSERTTIME)"
" VALUES (?,?,?,?,?,?,?,?,?)")
db_connection.Execute(
insert_sql,
(PublicFun.createID(), company_guid, "Costco",
product_info["NAME"], product_info["SPEC"],
product_info["INGREDIENT"], product_info["ORIGIN"],
"CostcoCrawler", PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS")))
else:
logger.info("Update")
update_sql = "UPDATE CompanyProduct set D_MODIFYUSER = ?, D_MODIFYTIME = ? WHERE GUID = ?"
db_connection.Execute(
update_sql,
("CostcoCrawler", PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS"),
product_guid))
def writeDBMsg(msg):
import Public.PublicFun as PublicFun
import Public.LogHandler as LogHandler
dbcon = getQueueDBConnect()
sql = ("INSERT INTO [dbo].[LogMsg]([GUID],[Message],[D_INSERTTIME])" +
"VALUES(?,?,?)")
try:
dbcon.Execute(sql, (PublicFun.createID(), PublicFun.SQLFilter(msg),
PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS")))
except:
msg = "writeDBMsg失敗：" + msg
LogHandler.writeMsg(msg)
dbcon.close()
def addQueue(SystemName, QueueType, Path, Files, Param):
import Public.PublicFun as PublicFun
GUID = PublicFun.createID()
jsonData = str(Param)
D_INSERTTIME = PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS")
dbcon = getQueueDBConnect()
sql = (
"INSERT INTO [dbo].[JobQueue]([GUID],[SystemName],[QueueType],[Path],[Files],[Param],[D_INSERTTIME])"
+ "VALUES('" + GUID + "',N'" + PublicFun.SQLFilter(SystemName) +
"',N'" + PublicFun.SQLFilter(QueueType) + "',N'" +
PublicFun.SQLFilter(Path) + "',N'" + PublicFun.SQLFilter(str(Files)) +
"',N'" + PublicFun.SQLFilter(str(jsonData)) + "','" +
str(D_INSERTTIME) + "')")
dbcon.Execute(sql)
dbcon.close()
def UpdateData(dbcon, DataObject):
import Public.PublicFun as PublicFun
sql = DataObject.QueryStr
WhereClause = " WHERE 1=1 "
whereParameterList = []
for key in DataObject.KeyFields:
WhereClause = WhereClause + " AND " + key + "=?"
whereParameterList.append(DataObject.getData(key))
excuteSQL = sql + WhereClause
FindRow = dbcon.GetDataTable(excuteSQL, tuple(whereParameterList))
excuteSQL = ""
NowTime = PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS")
updateParameterList = []
if FindRow is not None and len(FindRow) > 0:
FindRow = FindRow[0]
DataObject.D_MODIFYTIME = NowTime
if DataObject.CheckTimeStamp:
DBTime = FindRow.D_MODIFYTIME
if DBTime is None or DBTime == "":
DBTime = FindRow.D_INSERTTIME
#if DBTime >DataObject.TimeStamp:
# raise Exception("The data has been updated by others!")
excuteSQL = "UPDATE " + DataObject.TableName + " SET "
strField = ""
for Field in DataObject.Fields:
strField = strField + Field + "=?,"
updateParameterList.append(DataObject.getData(Field))
excuteSQL = excuteSQL + strField.rstrip(',') + WhereClause
updateParameterList.extend(whereParameterList)
else:
DataObject.D_INSERTTIME = NowTime
excuteSQL = "INSERT INTO " + DataObject.TableName
strField = ""
strValue = ""
for Field in DataObject.Fields:
strField = strField + Field + ","
strValue = strValue + "?,"
updateParameterList.append(DataObject.getData(Field))
excuteSQL = excuteSQL + "(" + strField.rstrip(
',') + ")VALUES(" + strValue.rstrip(',') + ")"
dbcon.Execute(excuteSQL, tuple(updateParameterList))
return True
def writeDBMsg(JobName, Param, msg, dbcon=None):
import Public.PublicFun as PublicFun
if dbcon is None:
dbcon = SQLConnect.DBConnect(publicSetting=True)
dbcon.ConnectDB()
writeDBMsg(JobName, Param, msg, dbcon)
dbcon.close()
else:
try:
sql = (
"INSERT INTO [dbo].[LogMsg]([GUID],[JOB],[Param],[Message],[D_INSERTUSER],[D_INSERTTIME],[D_MODIFYUSER],[D_MODIFYTIME])"
+ "VALUES('" + PublicFun.createID() + "',N'" +
PublicFun.SQLFilter(JobName) + "',N'" +
PublicFun.SQLFilter(str(Param)) + "',N'" +
PublicFun.SQLFilter(str(msg)) + "',N'" +
PublicFun.SQLFilter(JobName) + "','" +
PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS") + "','','')")
dbcon.Execute(sql)
writeMsg(str(msg))
except Exception as ex:
writeMsg("寫入資料庫失敗:" + ex)
def writeDBMsg(JobName, Param, msg, dbcon=None):
jsonData = Param
if type(jsonData) is not str:
jsonData = json.dumps(Param, separators=(',', ':'))
if dbcon is None:
dbcon = SQLConnect.DBConnect(secName="QueueConnect",
publicSetting=True)
dbcon.ConnectDB()
writeDBMsg(JobName, jsonData, msg, dbcon)
dbcon.close()
else:
try:
sql = (
"INSERT INTO [dbo].[LogMsg]([GUID],[JOB],[Param],[Message],[D_INSERTUSER],[D_INSERTTIME],[D_MODIFYUSER],[D_MODIFYTIME])"
+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?)")
dbcon.Execute(
sql, (PublicFun.createID(), JobName, jsonData, msg, JobName,
PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS"), "", ""))
writeMsg(str(msg))
except Exception as ex:
writeMsg("寫入資料庫失敗:" + ex)
def updateOption(dbcon, jobCategoryGuid, OptionCode):
import Public.PublicFun as PublicFun
sql = ("UPDATE OPTAAA SET OPTAAA002=?, D_MODIFYTIME=? WHERE GUID = ?")
dbcon.Execute(sql,
(OptionCode, PublicFun.getNowDateTime("YYYY/MM/DD HH:MM:SS"),
jobCategoryGuid))
DBConnect = SQLConnect.DBConnect(publicSetting=True)
DBConnect.ConnectDB()
CompanyGUID = BIASDataIO.CheckCompanyMappingList(
DBConnect, companyName)
companyInFo = Engine.Query(DBConnect, Companys.Companys(),
"GUID='" + CompanyGUID + "'")
if companyInFo.GUID == "":
companyInFo.GUID = CompanyGUID
companyInFo.D_INSERTUSER = "******"
else:
companyInFo.D_MODIFYUSER = "******"
today = PublicFun.getNowDateTime("YYYY/MM/DD")
if companyInFo.D_INSERTTIME is None or (
companyInFo.D_MODIFYTIME == ""
and companyInFo.D_INSERTTIME[:10] < today) or (
companyInFo.D_MODIFYTIME != ""
and companyInFo.D_MODIFYTIME[:10] < today):
companyIntro = Engine.Query(
DBConnect, Companys.CompanyIntro(),
"CompanyIntro001='" + CompanyGUID + "'")
if companyIntro.GUID is None or companyIntro.GUID == "":
companyIntro.GUID = PublicFun.createID()
companyIntro.D_INSERTUSER = "******"
else:
companyIntro.D_MODIFYUSER = "******"
companyIntro.CompanyIntro001 = CompanyGUID
companyInFo.Companys003 = companyName