#coding=utf-8
importpymssqlimportredefconnectDB():
conn= pymssql.connect(server='****', user='User', password='****', database='*****',
charset='cp936')
cur=conn.cursor()
sql= 'select ProductID,Content from Products WHERE (not Content IS NULL )'cur.execute(sql)
row=cur.fetchone()
resultList=[]whilerow:#print("ProductID=%s,Content=%s" % (row[0], row[1]))
result = parseContent(row[1])ifresult:
tmp=[]#print("解析出的img为:")
#print(result)
#tmp.append(int(row[0])) # 将productID转成int类型,方便下面的比较
tmp.append(row[0])
tmp.append(result)
resultList.append(tmp)try:
row=cur.fetchone()exceptUnicodeDecodeError:continueconn.close()returnresultListdefparseContent(content):
pattern= ']*/>'result=re.findall(pattern, content)returnresultdefsaveImg(resultList):
productIdList=getExtraBookProductIDList()
conn= pymssql.connect(server='****', user='User', password='****', database='*****',
charset='cp936')
cur=conn.cursor()for result in resultList: #遍历解析出来的imgList
if result[0] inproductIdList:#提取src
imgSrc = getImgSrc(result[1])for img inimgSrc:
sql_1= """update ExtraBookInfo set YImage='{img}' WHERE ProductID='{pID}'""".format(
img=img, pID=result[0])print(sql_1)
cur.execute(sql_1)
conn.commit()else:#sql_2 = """insert into ExtraBookInfo (ProductID,YImage) values( '{pID}','{img}')""".format(
#pID=result[0], img=tmp)
for img inimgSrc:
cur.execute('insert into ExtraBookInfo ProductID,YImage values(%s,%s)', (result[0], img))
conn.commit()
conn.close()defgetExtraBookProductIDList():
conn= pymssql.connect(server='****', user='User', password='****', database='*****',
charset='cp936')
cur=conn.cursor()
sql= 'select ProductID from ExtraBookInfo'cur.execute(sql)
productIdList=[]
row=cur.fetchone()whilerow:
productIdList.append(row[0])try:
row=cur.fetchone()exceptUnicodeDecodeError:continueconn.close()returnproductIdListdefgetImgSrc(result):for r inresult:
pattern_2= 'http.*?\.jpg'p2=re.findall(pattern_2, r)print(p2)returnp2
resultList=connectDB()
saveImg(resultList)