def store_to_db(company_info, product_info, file_name, brand):
"""將資訊導入DB中"""
db_connection = SQLConnect.DBConnect(publicSetting=True)
db_connection.ConnectDB()
"""如果沒有廠商資訊，則不會存入該產品(代表對於銷售沒有意義)"""
if company_info.get("factory_name", ''):
company_guid = sd.save_company_info(db_connection, company_info)
if product_info.get("product_name", ''):
sd.save_product_info(db_connection, company_guid, product_info,
file_name)
logger.logger.info(f"{brand} info saved")
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 get_company_info_from_product(product_url):
"""取得製造/進口商資料
"""
product_info = create_default_product()
company_info = create_default_comapny()
logger.info("Product:" + product_url)
resp = CatchCostcoUtil.send_get_request(logger, session, product_url,
header)
resp.encoding = "utf-8"
soup = BeautifulSoup(resp.text, "lxml")
#商品規格
product_specs_soup = soup.find("div", id="collapse-PRODUCT_SPECS")
product_table = product_specs_soup.find("table")
for specs_soup in product_table.findAll("tr"):
name = specs_soup.find("td", class_="attrib").text.strip(" \t\n\r")
value_soup = specs_soup.find("td", class_="attrib-val")
value = value_soup.text.strip(" \t\n\r")
if "品名" in name:
product_info["NAME"] = value
elif "內容量/入數" in name:
product_info["SPEC"] = value
elif "成分" in name:
product_info["INGREDIENT"] = value
elif "產地" in name:
product_info["ORIGIN"] = value
elif "製造/進口商名稱" in name:
company_info["NAME"] = get_taiwan_company_info(value_soup)
elif "製造/進口商電話" in name:
company_info["TEL"] = value
elif "製造/進口商地址" in name:
company_info["ADDRESS"] = get_taiwan_company_info(value_soup)
db_connection = SQLConnect.DBConnect(publicSetting=True)
db_connection.ConnectDB()
logger.info("Save Company:" + company_info["NAME"])
if company_info["NAME"] is not None and len(company_info["NAME"]) > 0:
company_guid = save_company_info(db_connection, company_info)
logger.info("Save Product:" + product_info["NAME"])
save_product_info(db_connection, company_guid, product_info)
time.sleep(2)
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)
data = sys.argv[2]
jsondata = PublicFun.StringToJson(data)
jobdate = str(jsondata["date"])
jobName = PublicFun.SQLFilter(str(jsondata["jobName"]))
jobURL = str(jsondata["jobURL"])
jobAREA = str(jsondata["jobAREA"])
companyInfo = jsondata["companyInfo"]
try:
companyName = PublicFun.SQLFilter(str(companyInfo["companyName"]))
except Exception as ex:
companyInfo = PublicFun.StringToJson(companyInfo, Default=False)
companyName = PublicFun.SQLFilter(str(companyInfo["companyName"]))
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:
def getPhoneNumber(PhoneNumberurl):
#Regex格式驗證
import re
text_re = re.compile('Text=')
queryString = PhoneNumberurl.split("&")
for param in queryString:
if len(text_re.findall(param)) > 0:
return param[5:]
if __name__ == '__main__':
jsondata = ""
try:
DBConnect2 = SQLConnect.DBConnect(secName="QueueConnect",
publicSetting=True)
DBConnect2.ConnectDB()
dts = DBConnect2.GetDataTable(
"select * from JobQueue where SystemName ='104Job' and QueueType = 'Error'"
)
#有些是'單引號問題 需要找方法
JobID = 1
data = "{}"
DBConnect2.close()
for row in dts:
#print(row.Param)
GUID = row.GUID
JobID = 1
data = row.Param
print(data)
def getQueueDBConnect():
import Public.SQLConnect as SQLConnect
DBConnect = SQLConnect.DBConnect("QueueConnect", publicSetting=True)
DBConnect.ConnectDB()
return DBConnect