def tweet_selector():
con = db_connect()
cursor = con.cursor()
# Select all users' screen names from db as tuple
user_select_query = "SELECT ScreenName from USERS"
cursor.execute(user_select_query)
user_select = cursor.fetchall()
# print(user_select)
tw_list = []
# make a list and remove some special characters
user_select = list(sum(user_select, ()))
#print(user_select)
for user in user_select:
t_clean_en = []
print("Processing user: "******"""SELECT TweetText FROM `TWEETS` where ScreenName = %s""",
(user, ))
# fetching user tweets and saving it to a list
fetchall_usertweets = cursor.fetchall()
fetchall_usertweets = list(sum(fetchall_usertweets, ()))
# setting up a counter which counts the tweets of each user
counter = 0
# examine each user's tweet separately
for tweet in fetchall_usertweets:
counter += 1
#print("\tprocessing tweet {}: {}".format(counter,tweet))
# Reset counter when we process all user's tweets
#if counter ==fetchall_usertweets[-1]:
# counter=0
# bring tokenized tweets
tw_pr = None
tw_pr = tweet_process(tweet, t_clean_en)
#if(tw_pr != None)
#print("\t{}\tresult: {}".format(counter,tw_pr) )
print(t_clean_en)
usercats = {}
for k in category_dict.keys():
usercats[k] = 0
for word in t_clean_en:
for k in category_dict.keys():
for v in category_dict[k]:
if v == word:
usercats[k] = 1 + usercats[k]
print(usercats)
def user_select():
try:
con = db_connect()
curs = con.cursor()
curs.execute("SELECT UserId FROM USERS")
row = curs.fetchone()
while row is not None:
print(row)
row = curs.fetchone()
return row
except MySQLdb.Error as e:
print(e)
except:
print("Unknown error occurred")
finally:
curs.close()
con.close()
def store_db(created_at, user_id, screen_name, tweet, place, location):
"""
connect to MySQL database and insert twitter data
"""
try:
con = db_connect()
con.set_character_set('utf8')
cursor = con.cursor()
cursor.execute('SET NAMES utf8;')
cursor.execute('SET CHARACTER SET utf8;')
cursor.execute('SET character_set_connection=utf8;')
if con:
print("Connection successful")
#Insert twitter data
cursor.execute(cr_t_activity)
query = "INSERT INTO USER_ACTIVITY (Created_at, UserId, ScreenName, TweetText, Place, Location) VALUES (%s, %s, %s, %s, %s, %s)"
cursor.execute(
query,
(created_at, user_id, screen_name, tweet, place, location))
else:
print('connection unsaccesful')
con.commit()
cursor.close()
con.close()
print("MySQL connection is closed")
return
except MySQLdb.Error as e:
print(e)
""" Python Program using tweepy. Mysql and twitter API modules. Based on code created by
Github user Yanofsky who created this program https://gist.github.com/yanofsky/5436496.
I altered the code so that results are saved into a mysql db instead of a csv file.
What this code does: Selects each user from a user table, finds user's latest tweets and insert them fulltext to a
"Tweets" table for further processing"""
import datetime
import tweepy
from authenticator import authentication
from mysql_conn import db_connect
import MySQLdb
con = db_connect()
curs = con.cursor()
def user_select():
try:
curs.execute("SELECT UserId FROM USERS")
row = curs.fetchone()
while row is not None:
print(row)
row = curs.fetchone()
return row
curs.close()
con.close()