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