def apiAct(self, id, cmd=None): if not id and not cmd: debug.LogToDebug('id and cmd is not defined (apiAct)') return False try: if not cmd: cmd = self.composeCmd(id) rst = subprocess.check_output(cmd) except Exception as e: debug.LogToDebug('func apiAct\n %s' % str(e)) return False else: try: rst = json.loads(rst.decode('utf8')) except: return True else: if 'errors' in rst or 'error' in rst: if 'errors' in rst: msg = rst['errors'] else: msg = rst['error'] debug.LogToDebug('func apiAct. Twitter API failure.\n %s' % str(msg)) return False return True
def getURLsPerTweet(): get_urls_per_tweet_cmd = """ With tbl AS ( select tweet_id, json_array_elements(tweet_obj->'entities'->'urls')->>'expanded_url' as url from tweet where tweet_obj->'entities'->'urls' is not null and resolved_urls is null UNION select tweet_id, json_array_elements(tweet_obj->'retweeted_status'->'entities'->'urls')->>'expanded_url' as url from tweet where tweet_obj->'retweeted_status'->'entities'->'urls' is not null and resolved_urls is null ) select tweet_id, array_agg(url) as urls from tbl group by tweet_id; """ db_conn = psycopg2.connect('dbname=drifter') if not db_conn: debug.LogToDebug('psql connection failed in UpdateConn in analysis.') return False rst = DBExecute(db_conn, get_urls_per_tweet_cmd, "get urls per tweet", need_commit=False, return_id=False) if not rst: return False return rst
def SaveTweetstoDB(tweets): """Save tweets to table Tweets.""" db_conn = psycopg2.connect('dbname=drifter') if not db_conn: debug.LogToDebug( 'psql connection failed in save tweets to db in analysis.') return False comm = """ INSERT INTO Tweet(tweet_obj, user_id, tweet_id, created_at) VALUES(%s, %s, %s, %s); """ flag = True for tw in tweets: tw_id = tw.id_str rst = DBExecute( db_conn, comm, 'save a Tweet to db', (json.dumps(tw._json), tw.user.id_str, tw_id, tw.created_at), True) if not rst: flag = False if db_conn: db_conn.close() return flag
def GetAllConns(whether_follower, ego_usr_screen_name=None, ego_usr_twitter_id=None, hourdelta=24): """Get all connections of a twitter user. Args: whether_follower(bool): false to get friends, and true to get followers. ego_usr_screen_name: screen_name of the ego usr. ego_usr_twitter_id: twitter id of the ego usr. One of ego_usr_screen_name and ego_usr_twitter_id must be given. ego_usr_screen_name is only supported for bots. Returns: a list of connections. """ if not (ego_usr_screen_name or ego_usr_twitter_id): return False db_conn = psycopg2.connect('dbname=drifter') if not db_conn: debug.LogToDebug('psql connection failed in GetAllConns in analysis.') return False if not ego_usr_twitter_id: comm = """ select twitter_user_id from bot where screen_name=%s;""" ego_usr_twitter_id = DBExecute(db_conn, comm, "search_twitter_id_of_a_bot", param_lst=(ego_usr_screen_name, ), need_commit=False, return_id=True) if not ego_usr_twitter_id: if db_conn: db_conn.close() return False comm = """ SELECT t_usr_id_conn FROM connections WHERE t_usr_id_ego=%s and no_connctions is false and time between %s and %s and conn_type is %s; """ current_time = datetime.now() previous_day_time = current_time - timedelta(hours=hourdelta) t_usr_ids = DBExecute(db_conn, comm, "search_conn_of_a_twitter_id", param_lst=(ego_usr_twitter_id, previous_day_time, current_time, whether_follower), need_commit=False, return_id=False) if db_conn: db_conn.close() if not t_usr_ids: return False final_usr_ids = [] for iid in t_usr_ids: final_usr_ids.append(iid[0]) return list(set(final_usr_ids))
def SaveCurrentHomeTimeline(self): try: print(self.username) source_obj = source.HomeTimeLine(self.username) source_data = source_obj.getSourceData() if not source_data: raise ValueError('cannot obtain hometimeline') except ValueError as e: debug.LogToDebug( '=======\nfailed to save hometimeline for bot %s at %s\n' % (self.username, datetime.now())) debug.LogToDebug(str(e)) print('save htl 1') if source_data: dt = datetime.now() comm_htl = """ INSERT INTO home_timeline(bot_id, checked_at) VALUES(%s, %s) RETURNING id; """ self.tl_id = self._execute(comm_htl, 'home_timeline created', (self.bot_id, dt), True, True) print('save htl 2') print('self.tl_id is %s' % self.tl_id) comm_htl_tw = """ INSERT INTO home_timeline_tweets(htl_id, tw_id) VALUES(%s, %s); """ comm_tweets = """ insert into tweet(tweet_obj, tweet_id, created_at) values(%s, %s, %s); """ for tweet in source_data: self._execute( comm_tweets, 'tweet table update', (json.dumps(tweet), tweet['id'], time.strftime( '%Y-%m-%d %H:%M:%S', time.strptime(tweet['created_at'], '%a %b %d %H:%M:%S +0000 %Y'))), True) self._execute(comm_htl_tw, 'tweet table update in home_timeline_tweets', (self.tl_id, tweet['id']), True)
def getRawResult(self, cmd=None): if not cmd: cmd = self.composeCmd() try: rst = subprocess.check_output(cmd) except Exception as e: debug.LogToDebug('func getRawResult\n %s'% str(e)) rst = False if rst and cmd: rst = json.loads(rst.decode('utf8')) if 'errors' in rst or 'error' in rst: if 'errors' in rst: msg = rst['errors'] else: msg = rst['error'] debug.LogToDebug('func getRawResult. Twitter API failure.\n %s'% str(msg)) return False return rst
def DealConnectionInfo( twitter_usr_id, connection_type, # friends or followers class_object): # 1. check what's the last time this info is saved check_last_timestamp = """ SELECT time from Connections WHERE t_usr_id_ego=%s ORDER BY time DESC NULLS LAST LIMIT 1; """ param_lst = (twitter_usr_id, ) rst = self._execute( check_last_timestamp, 'get last time from connections for bot %s' % twitter_usr_id, param_lst, False) print('latest time is :%s' % rst) if rst and rst[0][0] <= datetime.now() - timedelta( hours=conn_save_freq): print('pass save connection...') return rst[0][1] # 2. if it's longer than the config_state.conn_save_freq hours, update the table try: source_data = class_object.getSourceData() if not source_data: raise ValueError('cannot obtain %s twitter api data' % connection_type) except ValueError as e: debug.LogToDebug( '=======\nfailed to save %s for' ' bot %s at %s\n' % (connection_type, self.username, datetime.now())) debug.LogToDebug(str(e)) source_data = None if source_data: connection_type = False if connection_type == 'Friends' else True flag = self.SaveConnections(source_data, [], twitter_usr_id, connection_type) return rst
def act(self, friend_id_lst=None): """Unfollow an user weighted randomized. The weight is weighted by its time order. The older one has a higher probability to be unfollowed. This function may need to be modified if its Twitter API changed. At this time, results from '/1.1/followers/ids.json' are ordered with the most recent following first, but Twitter may change it without announcement. Check it if we still want to select the id based on its time data. https://developer.twitter.com/en/docs/accounts-and-users/follow-search-get-users/api-reference/get-followers-ids """ if not friend_id_lst: friend_obj = source.Friends(self.username, whether_user_entity=False) friend_id_lst = friend_obj.getSourceData() if not friend_id_lst: return False if len(friend_id_lst) <= minimum_friends: current_action = Follow(self.username) if_suc = current_action.act() self.select_source = current_action.select_source if not if_suc: return if_suc else: return -if_suc # never unfollow the seeds try: friend_id_lst.remove(initial_friends[self.username][0]) except: debug.LogToDebug('failed to remove seed for user %s' % self.username) if len(friend_id_lst) < 4999: return False selected_id = None if self.unfollow_method == 'weighted': selected_id = cal_helper.randomizeObjs(friend_id_lst, whether_weighted=True, weighted_order=True) elif self.unfollow_method == 'uniform': selected_id = cal_helper.randomizeObjs(friend_id_lst) if_suc = self.apiAct(selected_id) if not if_suc: return False return selected_id
def saveURLStoTweetTbl(tweet_id, urls): comm = """ UPDATE Tweet SET resolved_urls = %s WHERE tweet_id = %s; """ db_conn = psycopg2.connect('dbname=drifter') if not db_conn: debug.LogToDebug('psql connection failed in UpdateConn in analysis.') return False rst = DBExecute(db_conn, comm, "save resolved urls to db.", param_lst=(urls, tweet_id), need_commit=True, return_id=False) if not rst: return False return rst
def act(self): # 1. take the latest mention action from the database, # and its quoted tweet id (in urls ->[{expanded_url:<url>}]) and its ts # the expanded_url is something like db = DataManager(self.username) since_id = db.TheLastMentionResult() del db # 2. select all tweets published after last replied tweet and randomize one to reply. source_obj = source.MentionTimeLine(self.username, since_id=since_id, count=mention_tl_num) if not source_obj: return False source_data = source_obj.getSourceData() # a list of tweets if not source_data: return False tweet = cal_helper.randomizeObjs(source_data) tweet_id = tweet['id'] if 'full_text' in tweet: tweet_text = tweet['full_text'] else: tweet_text = tweet['text'] response = self.GenerateReply(tweet_text) if not response: debug.LogToDebug('chattbot not working.') response = cal_helper.RandomizeAnTweetFromFile() response = '@%s %s' % (tweet['user']['screen_name'], response) cmd = self.composeCmd(response, reply_id=tweet_id) # TODO: fix subprocess problem? os_cmd = "" for c in cmd: os_cmd += "%s " % c try: os.system(os_cmd) except: return False return True
def UpdateConn(target_friends_ids, user_id, hourdelta=24): comm = """ UPDATE Connections SET conn_tweet_update_time = %s WHERE t_usr_id_conn = %s and t_usr_id_ego = %s and time between %s and %s; """ current_time = datetime.now() previous_day_time = current_time - timedelta(hours=hourdelta) db_conn = psycopg2.connect('dbname=drifter') if not db_conn: debug.LogToDebug('psql connection failed in UpdateConn in analysis.') return False for f_id in target_friends_ids: t_usr_ids = DBExecute( db_conn, comm, "update connections table with conn_tweet_update_time", param_lst=(current_time, f_id, user_id, previous_day_time, current_time), need_commit=True, return_id=False)
def SaveCurrentAction(self, source_name, action, result_code): try: content = {} if action == 'follow' and result_code and result_code < 0: action = 'unfollow' if result_code: result_code = -result_code if action == 'unfollow' and result_code and result_code < 0: action = 'follow' result_code = -result_code if action == 'unfollow': source_name = unfollow_method if action == 'like' or action == 'retweet': if result_code: source_obj = source.TweetObj(self.username, result_code) content = source_obj.getSourceData() if not content: raise Exception('ahh0.') elif action == 'follow' or action == 'unfollow': if result_code: source_obj = source.UserObj("", user_id=result_code) content = source_obj.getSourceData() if not content: raise Exception('ahh1.') elif action == 'replymention': if result_code: source_obj = source.UserTimeLine(self.username, 1) content = source_obj.getSourceData() if content: content = content[0] else: raise Exception('ahh2.') else: # tweet if result_code: source_obj = source.UserTimeLine(self.username, 1) content = source_obj.getSourceData() if content: content = content[0] else: raise Exception('ahh3.') except Exception as e: content = { "error": "restore result object failed via using Twitter API.", "result_code": result_code } debug.HandleException(sys.exc_info()) debug.LogToDebug('savecurrentaction failure:\n %s' % str(e)) insert_conn = """ INSERT INTO Action(bot_id, source, action, result, tl_id) VALUES(%s, %s, %s, %s, %s); """ try: content_json = json.dumps(content) param_lst = (self.bot_id, source_name, action, content_json, self.tl_id) rst = self._execute(insert_conn, 'insert_action_table', param_lst, True, return_id=False) except Exception as e: debug.HandleException(sys.exc_info()) debug.LogToDebug(str(content)) return rst