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 getNewRequests(requestURL, Host): import requests import Public.PublicFun as PublicFun request = requests.Session() JobID = PublicFun.createID() Chromedriver = PublicFun.getWebDriver("chrome", DataFolderName=JobID) Chromedriver.get(requestURL) cookies = Chromedriver.get_cookies() userAgent = Chromedriver.execute_script("return navigator.userAgent") PublicFun.closeWebDriver(JobID, Chromedriver) header = { 'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8', 'Accept-Encoding': 'gzip, deflate, br', 'Accept-Language': 'zh-TW,zh;q=0.9,en-US;q=0.8,en;q=0.7', 'Connection': 'keep-alive', 'DNT': '1', 'Host': Host, 'Upgrade-Insecure-Requests': '1', 'User-Agent': userAgent } request.headers.update(header) for cookie in cookies: request.cookies.set(cookie['name'], cookie['value']) return request
def save_company_info(db_connection, company_info): print(company_info) company_guid = BIASDataIO.CheckCompanyMappingList( db_connection, company_info["factory_name"], NewCompanyGUID=False) if company_guid == "": company_guid = PublicFun.createID() try: companys = Engine.Query(db_connection, Companys.Companys(), "GUID=?", company_guid) except Exception as message: logger.logger.error(message) if companys.GUID == "": companys.GUID = company_guid companys.D_INSERTUSER = "******" else: companys.D_MODIFYUSER = "******" companys.Companys003 = company_info.get("factory_name", '') companys.Companys005 = company_info.get("factory_address", '') if not companys.Companys010 or "暫不提供" in companys.Companys010: companys.Companys010 = company_info.get("factory_phone", '') try: Engine.UpdateData(db_connection, companys) except Exception as message: logger.logger.error(message) return company_guid
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 getPageSize(requestURL): JobID=PublicFun.createID() Chromedriver=PublicFun.getWebDriver("chrome",DataFolderName=JobID) Chromedriver.get(requestURL) time.sleep(2) Soup = bs(Chromedriver.page_source, "html.parser") pageSize=len(Soup.select("select.b-clear-border.js-paging-select.gtm-paging-top option")) if pageSize==0: pageSize=len(Soup.select("select.page-select.js-paging-select.gtm-paging-top option")) PublicFun.closeWebDriver(JobID,Chromedriver) return pageSize
def CheckCompanyMappingList(dbcon, CompanyName, CompanyGUID=None): import Public.PublicFun as PublicFun MAPCompanyGUID = CheckMappingList(dbcon, "CompanyName", CompanyName) if MAPCompanyGUID is None or len(MAPCompanyGUID) == 0: if CompanyGUID is None: CompanyGUID = findCompany(dbcon, CompanyName) if (CompanyGUID is None or CompanyGUID == ""): CompanyGUID = PublicFun.createID() insertMappingList(dbcon, "CompanyName", CompanyName, CompanyGUID) else: CompanyGUID = MAPCompanyGUID[0].MAPAAA003 return CompanyGUID
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 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 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 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 save_company_info(db_connection, company_info): """新增或更新公司資訊 """ company_guid = BIASDataIO.CheckCompanyMappingList(db_connection, company_info["NAME"], NewCompanyGUID=False) if company_guid == "": company_guid = PublicFun.createID() companys = Engine.Query(db_connection, Companys.Companys(), "GUID=?", (company_guid, )) if companys.GUID == "": companys.GUID = company_guid companys.D_INSERTUSER = "******" else: companys.D_MODIFYUSER = "******" companys.Companys003 = company_info["NAME"] companys.Companys005 = company_info["ADDRESS"] if companys.Companys010 == None or len( companys.Companys010) == 0 or "暫不提供" in companys.Companys010: companys.Companys010 = company_info["TEL"] Engine.UpdateData(db_connection, companys) return company_guid
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))
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 requestHost = SettingReader.getSetting("global", "requestHost") req = RequestsHandler.getNewRequests(companyURL, requestHost) res = req.get(companyURL) res.encoding = 'utf8' CompanySoup = bs(res.text, "html.parser") contentInfo = CompanySoup.select("div.intro dl") contentInfo = contentInfo[0].text.splitlines() for n in range(len(contentInfo)):
ScanDate = str(jsondata["ScanDate"]) DBConnect = SQLConnect.DBConnect(publicSetting=True) DBConnect.ConnectDB() CompanyGUID = BIASDataIO.CheckCompanyMappingList( DBConnect, companyName) requestHost = SettingReader.getSetting("global", "requestHost") req = RequestsHandler.getNewRequests(jobURL, requestHost) res = req.get(jobURL) res.encoding = 'utf8' JobSoup = bs(res.text, "html.parser") objJob = Engine.Query(DBConnect, JOB.JOBAAA(), "JOBAAA009 = '" + jobURL.strip() + "'") if (objJob.GUID == ""): objJob.GUID = PublicFun.createID() objJob.D_INSERTUSER = "******" else: objJob.D_MODIFYUSER = "******" objJob.JOBAAA001 = CompanyGUID objJob.JOBAAA002 = "104" objJob.JOBAAA003 = jobName.strip() objJob.JOBAAA005 = jobAREA.strip() objJob.JOBAAA009 = jobURL.strip() contentInfo = JobSoup.find("dt", text="聯絡人:") if contentInfo is not None: objJob.JOBAAA006 = contentInfo.find_next_sibling("dd").text.strip() contentInfo = JobSoup.find("dt", text="電洽:") if contentInfo is not None: contentInfo = contentInfo.find_next_sibling("dd").find( "img")["src"]