def update_userInfo(self, itemDict, screenName):
sql = """
update account set accountName=%s, screenName=%s,
twitterId=%s, location=%s,
description=%s, url=%s,
statusesCount=%s, friendsCount=%s,
followersCount=%s, favoritesCount=%s,
accountTime=%s, profileImage=%s,
bannerUrl=%s where screenName=%s
"""
try:
self.cursor.execute(
sql, (itemDict["accountName"], itemDict["screenName"],
itemDict["twitterId"], itemDict["location"],
itemDict["description"], itemDict["url"],
itemDict["statusesCount"], itemDict["friendsCount"],
itemDict["followersCount"], itemDict["favoritesCount"],
itemDict["accountTime"], itemDict["profileImage"],
itemDict["bannerUrl"], itemDict["screenName"]))
self.conn.commit()
# print("更新 %s 账户信息成功" % itemDict["screenName"])
except Exception as e:
self.conn.rollback()
logger.info("更新 %s 账户信息失败,%s" % (itemDict["screenName"], str(e)))
return "error"
def login(cfg):
try:
conn = imaplib.IMAP4_SSL(cfg.emailIP, cfg.emailPort)
conn.login(cfg.emailUser, cfg.emailPass)
logger.info("successful login")
return conn
except Exception:
logger.error("Login Error")
return None
def __init__(self):
try:
self.conn = pymysql.connect('localhost',
'root',
'123',
'twittershowtest',
charset='utf8mb4')
self.cursor = self.conn.cursor()
except Exception as e:
logger.info('连接数据库失败：%s' % str(e))
def get_accountId(self, twitterId):
sql = "select accountId from account where twitterId =%s" % twitterId
try:
self.cursor.execute(sql)
accountId = self.cursor.fetchone()
return accountId[0]
except Exception as e:
self.conn.rollback()
logger.info("执行sql语句失败%s:%s" % (str(e), sql))
return ""
def get_screenName(self):
sql = "SELECT screenName FROM account"
try:
self.cursor.execute(sql)
nameTuple = self.cursor.fetchall()
nameList = [item[0] for item in nameTuple]
return nameList
except Exception as e:
self.conn.rollback()
logger.info("执行sql语句失败%s:%s" % (str(e), sql))
return []
def get_twitterIdList(self):
sql = "select twitterId from account"
# cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
try:
self.cursor.execute(sql)
idTuple = self.cursor.fetchall()
idList = [item[0] for item in idTuple]
return idList
except Exception as e:
self.conn.rollback()
logger.info("执行sql语句失败%s:%s" % (str(e), sql))
return []
def get_sinceId(self, accountId):
sql = "SELECT tweets.twitterId from tweets where accountId=%s ORDER BY tweets.tweetsId desc LIMIT 1" % accountId
try:
self.cursor.execute(sql)
sinceId = self.cursor.fetchone()
if sinceId != None:
return sinceId[0]
else:
return None
except Exception as e:
self.conn.rollback()
logger.info("执行sql语句失败%s" % str(e))
return None
def timeFun(sched_timedo):
flag = 0
while True:
now = datetime.datetime.now()
if sched_timedo < now < sched_timedo + datetime.timedelta(seconds=1):
flag = 1
time.sleep(1)
runTask()
else:
if flag == 1:
sched_timedo = sched_timedo + datetime.timedelta(hours=3)
logger.info(f"sched_timedo change:{sched_timedo}")
print(f"sched_timedo change:{sched_timedo}")
flag = 0
def insert_tweetInfo(self, itemDict, flag):
sql = """
INSERT INTO tweets (accountId, tweetsText, tweetsUrl, videoUrl, imageUrl, retweetCount,
tweetFavCount, tweetTime, twitterId) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
try:
self.cursor.execute(
sql, (itemDict["accountId"], itemDict["tweetsText"],
itemDict["tweetsUrl"], itemDict["videoUrl"],
itemDict["imageUrl"], itemDict["retweetCount"],
itemDict["favoriteCount"], itemDict["tweetTime"],
itemDict["twitterId"]))
self.conn.commit()
# print("插入推文信息成功")
flag += 1
return flag
except Exception as e:
self.conn.rollback()
logger.info("插入 %s 推文信息失败 %s" % (itemDict["twitterId"], str(e)))
return flag
def insert_userInfo(self, itemDict):
sql = """
INSERT INTO account (accountName, twitterId, screenName, location, description,
url, statusesCount, friendsCount, followersCount, favoritesCount,
accountTime, profileImage, bannerUrl) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
try:
self.cursor.execute(
sql, (itemDict["accountName"], itemDict["twitterId"],
itemDict["screenName"], itemDict["location"],
itemDict["description"], itemDict["url"],
itemDict["statusesCount"], itemDict["friendsCount"],
itemDict["followersCount"], itemDict["favoritesCount"],
itemDict["accountTime"], itemDict["profileImage"],
itemDict["bannerUrl"]))
self.conn.commit()
# print("插入 %s 账户信息成功" % itemDict["screenName"])
except Exception as e:
self.conn.rollback()
logger.info("插入 %s 账户信息失败 %s" % (itemDict["screenName"], str(e)))
return "error"
def runTask():
logger.info(f"执行任务开始,时间开始于 {datetime.datetime.now()}")
os.system("python main.py")
logger.info(f"执行任务开始,时间结束于 {datetime.datetime.now()}")
def getTweetsUser(api, username, twitterPip):
try:
idList = twitterPip.get_twitterIdList()
screenNameList = twitterPip.get_screenName()
mkdirThreeFiles()
userInfoDict = {}
tweetInfoDict = {}
userInfo = api.get_user(username)
accountName = userInfo.name
screenName = userInfo.screen_name
twitterId = userInfo.status.id
location = userInfo.location
description = userInfo.description
url = userInfo.url
if hasattr(userInfo, "profile_banner_url"):
bannerUrl = getBannerUrl(userInfo.profile_banner_url)
else:
bannerUrl = ""
profileImage = getProfileImg(userInfo.profile_image_url)
statusesCount = userInfo.statuses_count
friendsCount = userInfo.friends_count
followersCount = userInfo.followers_count
favoritesCount = userInfo.favourites_count
accountTime = userInfo.created_at
userInfoDict["accountName"] = accountName
userInfoDict["screenName"] = screenName
userInfoDict["twitterId"] = str(twitterId)
userInfoDict["location"] = location
userInfoDict["description"] = description
userInfoDict["url"] = url
userInfoDict["bannerUrl"] = bannerUrl
userInfoDict["profileImage"] = profileImage
userInfoDict["statusesCount"] = statusesCount
userInfoDict["friendsCount"] = friendsCount
userInfoDict["followersCount"] = followersCount
userInfoDict["favoritesCount"] = favoritesCount
userInfoDict["accountTime"] = accountTime
# 插入数据库
if screenNameList != None and screenName in screenNameList:
twitterPip.update_userInfo(userInfoDict, screenName)
elif screenNameList == None or len(screenNameList) == 0 or (
screenNameList != None and screenName not in idList):
twitterPip.insert_userInfo(userInfoDict)
# 获取当前账户下的推文
accountId = twitterPip.get_accountId(userInfo.status.id)
sinceId = twitterPip.get_sinceId(accountId)
if sinceId != None:
public_tweets = api.user_timeline(
screenName, since_id=int(sinceId), count=200)
else:
public_tweets = api.user_timeline(screenName, count=20)
try:
public_tweets.reverse() # 将列表翻转序列
flag = 0
startTime = datetime.now()
for tweet in public_tweets:
tweetsText = tweet.text
tweetsUrl = "https://twitter.com/%s/status/%d" % (
tweet.user.screen_name, tweet.id)
twitterId = str(tweet.id)
imgvideoUrl = get_imgvideoUrl(tweet)
videoUrl = imgvideoUrl[0]
imageUrl = imgvideoUrl[1]
retweetCount = tweet.retweet_count
favoriteCount = tweet.favorite_count
tweetTime = tweet.created_at
tweetInfoDict["accountId"] = accountId
tweetInfoDict["tweetsText"] = tweetsText
tweetInfoDict["tweetsUrl"] = tweetsUrl
tweetInfoDict["twitterId"] = twitterId
tweetInfoDict["videoUrl"] = videoUrl
tweetInfoDict["imageUrl"] = imageUrl
tweetInfoDict["retweetCount"] = retweetCount
tweetInfoDict["favoriteCount"] = favoriteCount
tweetInfoDict["tweetTime"] = tweetTime.strftime(
"%Y-%m-%d %H:%M:%S")
tweetNum = public_tweets.index(tweet) + 1
# print("第%d条记录：" % tweetNum)
flag = twitterPip.insert_tweetInfo(tweetInfoDict, flag)
except Exception as e:
print("错误信息:", e)
finally:
endTime = datetime.now()
runningTime = endTime - startTime
# print("花费时间为：%s" % runningTime)
# print("账户：%s,一共更新: %d 条记录" % (accountName, flag))
logger.info(
"花费时间为：%s账户：%s,一共更新: %d 条记录" % (runningTime, accountName, flag))
except Exception as e:
logger.warning("%s %s" % (username, str(e)))
def emailFetch():
if getattr(sys, 'frozen', False):
module_path = os.path.dirname(sys.executable)
elif __file__:
module_path = os.path.dirname(__file__)
# cfgPath = os.path.join(os.path.dirname(__file__), 'config.ini')
cfgPath = os.path.join(module_path, 'config.ini')
cfg = InitConfig(cfgPath)
conn = login(cfg)
if conn:
conn.select("INBOX")
#提取了文件夹中所有邮件的编号，search功能在本邮箱中没有实现……
resp, mails = conn.uid('search', None, 'ALL')
# print(mails[0].split(b' '))
email_num = len(mails[0].split(b' ')) - 1
count = 0
for message in range(email_num, int(cfg.cutOff), -1):
resp, data = conn.uid('fetch', mails[0].split(b' ')[message],
'(RFC822)')
mail = email.message_from_bytes(data[0][1])
flag = False
sender = utils.parseaddr(mail['FROM'])[1]
if sender.strip() == "*****@*****.**":
fileName = 'No Attachment'
#获取邮件附件名称
for part in mail.walk():
if part.get_content_maintype() == 'multipart':
continue
if part.get('Content-Disposition') is None:
continue
fileName = part.get_filename()
#如果文件名为纯数字、字母时不需要解码，否则需要解码
try:
fileName = decode_header(fileName)[0][0].decode(
decode_header(fileName)[0][1])
if not re.match('PLANNING SUMMARY-\d{8}\.xlsx',
fileName):
continue
except:
pass
#如果获取到了文件，则将文件保存在制定的目录下
if fileName != 'No Attachment':
filePath = os.path.join(cfg.emailSavePath, fileName)
# print (filePath)
if not os.path.isfile(filePath):
fp = open(filePath, 'wb')
fp.write(part.get_payload(decode=True))
fp.close()
logger.info("附件已经下载，文件名为：" + fileName)
flag = True
count += 1
cfg.setCutOff(message)
break
else:
logger.info("附件已经存在，文件名为：" + fileName)
flag = True
cfg.setCutOff(message)
break
if flag:
break
if count == 0:
logger.info("附件已经存在或本次查询没有找到需下载邮件附件")
conn.close()
conn.logout()
logger.info("successful exit")
else:
logger.info("conn is None")
from tweetInfo import getTweetsUser
from datetime import datetime
from loggingModule import logger
# 主函数
def main():
# 用户配置初始化
apiConfig = init()
# 实例化对象
twitterPip = TwitterPip()
# 获取Tweeter账号信息
for accountName in apiConfig[1]:
getTweetsUser(apiConfig[0], accountName.strip(), twitterPip)
twitterPip.close()
if __name__ == '__main__':
starTime = datetime.now()
logger.info("程序开始运行")
# print("程序开始运行")
# print("---------------")
main()
endTime = datetime.now()
# print("---------------")
# print("程序执行完毕，共耗时%s" % (endTime - starTime))
logger.info("程序执行完毕，共耗时%s" % (endTime - starTime))
