-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathmain.py
143 lines (101 loc) · 4.88 KB
/
main.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
import tweepy # Library required for Twitter API
import pandas as pd
import pip
package = 'tweepy' # Just replace the package name with any package to install it.
pip.main(['install', package])
consumer_key = "eYR5Ni67Nd12rgEb9H8LIapD8"
consumer_secret = "CPeb11EARYCvVW6AyidiX3WvxGRxa8Aq7bi4ihYdJs3Fv2DfBn"
Bearer_Token = "AAAAAAAAAAAAAAAAAAAAAL6XiQEAAAAACK8IPhfixHObQ4cTIXWHy0RuYHU%3DlFlmefdzhrX3wwnc63PcdZpmVoUNV2S6h7fg5vKrGhdWFwVPag"
access_key = "1583205059231240192-Vtu4zLpMvO8CJZhyB7aZ4VVYJvEn3d"
access_secret = "VRgRTWjPcGuvt7o9p5rCVR9EOM4ZPKu9Fj4g1OMpzE44z"
auth = tweepy.auth.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_key, access_secret)
api = tweepy.API(auth)
outtweets = [] # initialize master list to hold our ready tweets
for tweet in tweepy.Cursor(api.search_tweets, q="#neucourserepo", count=100, # The q variable holds the hashtag
lang="en").items():
outtweets.append([tweet.id_str, tweet.created_at, tweet.text.encode("utf-8"), tweet.user.location])
tweetdf = pd.DataFrame(outtweets, columns=["ID", "Created_at", "Text", "Location"])
df = tweetdf.to_csv("tweet_csv.csv", sep='\t', encoding='utf-8')
#sql connection
import mysql.connector
neucourse = mysql.connector.connect(host='localhost', user='root', passwd='password', database='neu_course_repository')
mycursor = neucourse.cursor()
#test table
mycursor.execute("Truncate neu_course_repository.test_table1")
neucourse.commit()
for i,row in tweetdf.iterrows():
sql = "INSERT INTO neu_course_repository.test_table1 values(%s,%s,%s,%s)"
mycursor.execute(sql,tuple(row))
neucourse.commit()
print("Record inserted in test table")
for tweet in tweepy.Cursor(api.search_tweets, q="#neucourserepo", count=100, # The q variable holds the hashtag
lang="en").items():
outtweets.append([tweet.id_str, tweet.created_at, tweet.text.encode("utf-8"), tweet.user.location])
#users_table
user = api.get_user(screen_name="NEU_CourseRepo")
users =[]
for follower in user.followers():
users.append([follower.screen_name, follower.name, follower.profile_image_url, follower.description,
follower.followers_count, follower.following])
userdf = pd.DataFrame(users, columns=["Twitter_handle", "name", "profile_image", "description", "followers_count", "following_count"])
userdf
print("Done")
mycursor.execute("Truncate neu_course_repository.User")
neucourse.commit()
for i,row in userdf.iterrows():
sql = "INSERT INTO neu_course_repository.User values(%s,%s,%s,%s,%s,%s)"
mycursor.execute(sql,tuple(row))
neucourse.commit()
print("Record inserted in User Values table")
# Tweet Details Table
outweets_user = []
for tweet in tweepy.Cursor(api.search_tweets, q="#neucourserepo", count=100, # The q variable holds the hashtag
lang="en").items():
outweets_user.append(
[tweet.id_str, tweet.author.screen_name, tweet.source_url, tweet.author.name, tweet.created_at, tweet.text])
tweet_user = pd.DataFrame(outweets_user,columns = ["tweet_id","twitter_handle","tweet_source_url","name","created_at","tweet"])
tweet_user
mycursor.execute("Truncate neu_course_repository.Tweet_Details")
neucourse.commit()
for i,row in tweet_user.iterrows():
sql = "INSERT INTO neu_course_repository.Tweet_Details values(%s,%s,%s,%s,%s,%s)"
mycursor.execute(sql,tuple(row))
neucourse.commit()
print("Record inserted in Tweet Details Table")
# tags table
tags = []
tweet_tags = []
for tweet in tweepy.Cursor(api.search_tweets,
q="#neucourserepo",
lang="en").items():
s = ""
for i in (tweet.entities.get("hashtags")):
s = s + " #" + i["text"]
# print("xxxxxxx")
if (len(s) == 0):
tags.append([tweet.id_str])
else:
tags.append([tweet.id_str, s])
tweet_tags = pd.DataFrame(tags,columns = ["ID","Tags"])
mycursor.execute("Truncate neu_course_repository.Tweet_Tag")
neucourse.commit()
for i,row in tweet_tags.iterrows():
sql = "INSERT INTO neu_course_repository.Tweet_Tag values(%s,%s)"
mycursor.execute(sql,tuple(row))
neucourse.commit()
print("Record inserted in tweet tag table")
# tweet mentions
outweets_mentions = []
for tweet in tweepy.Cursor(api.search_tweets, q="#neucourserepo", count=100, # The q variable holds the hashtag
lang="en").items():
outweets_mentions.append([tweet.id_str, tweet.author.screen_name, tweet.author.name, tweet.in_reply_to_screen_name])
mentiondf = pd.DataFrame(outweets_mentions,columns = ["tweet_id","twitter_handler", "source_user", "target_user"])
mentiondf
mycursor.execute("Truncate neu_course_repository.Tweet_Mention")
neucourse.commit()
for i,row in mentiondf.iterrows():
sql = "INSERT INTO neu_course_repository.Tweet_Mention values(%s,%s,%s,%s)"
mycursor.execute(sql,tuple(row))
neucourse.commit()
print("Record inserted in tweet mentions")