def keyContent():
sql = "SELECT id, ratio_content FROM Weibo"
itemList = queryAllMysqlDB(sql)
for item in itemList:
if item[1] != '':
p2 = item[1].find("=(") + len("=(")
p3 = item[1].find("/", p2)
keyContent = item[1][p2:p3]
sql = 'UPDATE Weibo SET key_content="{}" WHERE id="{}"'.format(
keyContent, item[0])
updateMysqlDB(sql)
def addKeywordId():
sql = "SELECT * FROM Weibo_keyword"
keywordList = queryAllMysqlDB(sql)
id = 0
while True:
sql = 'SELECT id, product_seg, circle, keyword FROM Weibo WHERE keyword_id=0 and id>"{}" LIMIT 1'.format(
id)
result = queryOneMysqlDB(sql)
if result == None:
break
id = result[0]
print("{} {} {} {}".format(result[0], result[1], result[2], result[3]))
for keyword in keywordList:
if result[1] == keyword[1] and result[2] == keyword[2] and result[
3] == keyword[3]:
sql = 'UPDATE Weibo SET keyword_id="{}" WHERE id="{}"'.format(
keyword[0], result[0])
updateMysqlDB(sql)
break
time.sleep(0.1)
def account2DB(keywordId, link):
keywordList = []
try:
keywordList = queryKeywordMysqlDB(queryCircleMysqlDB(keywordId)[0])
except Exception as ex:
print(ex)
print(keywordList)
influencer, fans, description, avgForward, avgComment, resForward, resComment, lastPostTime, postType, resArticleRead, countContent, pageId = personCrawler(keywordId, link, keywordList)
if fans != -1:
updateFansNumberMysqlDB(fans, link)
if fans < MIN_FANS:
return
postType = ("%d-%d-%d-%d" % (postType[1], postType[2], postType[3], postType[0]))
offcial = (1 if "官方" in description or "有限公司" in description else 0)
# sql = 'UPDATE Weibo SET follower="{}", share_avg="{}", comment_avg="{}", share_range="{}", comment_range="{}", lastPostTime="{}", \
# ori_share_avg="{}", ori_comment_avg="{}", ori_share_range="{}", ori_comment_range="{}", ori_lastPostTime="{}", \
# key_share_avg="{}", key_comment_avg="{}", key_share_range="{}", key_comment_range="{}", \
# post_type="{}", description="{}", article_read="{}", key_content="{}", official="{}", update_time="{}" WHERE keyword_id="{}" AND link="{}"'\
# .format(str(fans), str(avgForward[0]), str(avgComment[0]), resForward[0], resComment[0], (str(lastPostTime[0]) if lastPostTime[0] != DEFAULT_DATE else ''), \
# str(avgForward[1]), str(avgComment[1]), resForward[1], resComment[1], (str(lastPostTime[1]) if lastPostTime[1] != DEFAULT_DATE else ''), \
# str(avgForward[2]), str(avgComment[2]), resForward[2], resComment[2], \
# postType, description, resArticleRead, countContent, offcial, datetime.datetime.utcnow(), keywordId, link)
sql = 'INSERT INTO Weibo (keyword_id, influencer, follower, share_avg, comment_avg, share_range, comment_range, lastPostTime, \
ori_share_avg, ori_comment_avg, ori_share_range, ori_comment_range, ori_lastPostTime, \
key_share_avg, key_comment_avg, key_share_range, key_comment_range, \
post_type, description, article_read, key_content, official, update_time, link) \
VALUES ("{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}")' \
.format(keywordId, influencer, str(fans), \
str(avgForward[0]), str(avgComment[0]), resForward[0], resComment[0], (str(lastPostTime[0]) if lastPostTime[0] != DEFAULT_DATE else ''), \
str(avgForward[1]), str(avgComment[1]), resForward[1], resComment[1], (str(lastPostTime[1]) if lastPostTime[1] != DEFAULT_DATE else ''), \
str(avgForward[2]), str(avgComment[2]), resForward[2], resComment[2], \
postType, description, resArticleRead, countContent, offcial, datetime.datetime.utcnow(), link)
updateMysqlDB(sql)
if avgForward[1] >= 10 or avgComment[1] >= 10:
appendPageidMysqlDB(keywordId, pageId)
def delPageidMysqlDB(keyword_id, page_id):
sql = 'DELETE FROM Weibo_page WHERE keyword_id="{}" AND page_id="{}"'.format(
keyword_id, page_id)
updateMysqlDB(sql)
def markAsFinishedMysqlDB(keyword_id, keyword):
sql = 'UPDATE Weibo_keyword SET finished=1 WHERE id="{}" AND keyword="{}"'.format(
keyword_id, keyword)
updateMysqlDB(sql)
def appendMysqlDB(keyword_id, influencer, link, searchType):
sql = 'INSERT INTO Weibo_leads (keyword_id, influencer, link, search_type) VALUES ("{}", "{}", "{}", "{}")'.format(
keyword_id, influencer, link, searchType)
updateMysqlDB(sql)
def updateFansNumberMysqlDB(fans, link):
sql = 'UPDATE Weibo_leads SET fans="{}" WHERE link="{}"'.format(fans, link)
updateMysqlDB(sql)
def delAccountMysqlDB(link):
sql = 'DELETE FROM Weibo WHERE link="{}"'.format(link)
updateMysqlDB(sql)
def appendPageidMysqlDB(keywordId, pageId):
sql = 'INSERT INTO Weibo_page (keyword_id, page_id) VALUES ("{}", "{}")'.format(keywordId, pageId)
updateMysqlDB(sql)
def appendDB(product_seg, circle, keyword):
sql = 'INSERT INTO Weibo_keyword (product_seg, circle, keyword, create_at) VALUES ("{}", "{}", "{}", "{}")'.format(
product_seg, circle, keyword, datetime.datetime.utcnow())
updateMysqlDB(sql)