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"]