def dbplotffnetwork():
graph = nx.DiGraph()
#Plot following/follower network
#restrict to those with 3 tweets or more
mintime = 1358090418
maxtime = 1363963163
mygetter = DBTweetGetter(None, None)
con = lite.connect("tweetsdb.db")
cur = con.cursor()
ucon = lite.connect("userdb.db")
ucur = ucon.cursor()
tusers = []
users = []
cur.execute(
"SELECT ScreenName FROM htglobalwarming WHERE ConvertedTime > " +
str(mintime) + " AND ConvertedTime < " + str(maxtime) +
" COLLATE NOCASE")
temp = cur.fetchall()
for item in temp:
tusers.append(item[0].lower())
for item in tusers:
if not (item in users):
if tusers.count(item) > 7:
users.append(item)
print len(users)
# cur.execute("SELECT ScreenName FROM htclimatechange WHERE ConvertedTime > "+str(mintime)+" AND ConvertedTime < " + str(maxtime) + " COLLATE NOCASE")
# temp=cur.fetchall()
# tusers=[]
# for item in temp:
# tusers.append(item[0].lower())
# for item in tusers:
# if not (item in users):
# if tusers.count(item)>29:
# users.append(item)
# print len(users)
# cur.execute("SELECT ScreenName FROM htagw WHERE ConvertedTime > "+str(mintime) + " COLLATE NOCASE")
# temp=cur.fetchall()
# tusers=[]
# for item in temp:
# tusers.append(item[0].lower())
# for item in tusers:
# if not (item in users):
# if tusers.count(item)>2:
# users.append(item)
# print len(users)
#aim for 380
#sys.exit("Hammertime")
i = 0
try:
users.remove("undercoverzen")
users.remove("jivelad")
users.remove("anabananazavala")
#TODO Formalise this
except:
pass
for user in users:
print "User " + str(i) + "/" + str(len(users))
i += 1
#For each user check which other users are in friends, followers
ucur.execute("SELECT FriendId FROM friends WHERE ScreenName='" +
user.lower() + "' COLLATE NOCASE")
frl = []
temp = ucur.fetchall()
skip = False
if len(temp) == 0:
#get friends
print "Downloading friends for " + user.lower()
friendslist = mygetter.getFriends(user.lower(), [], -1)
if friendslist != "FAIL":
for friend in friendslist:
ucur.execute("INSERT INTO friends VALUES('" +
user.lower() + "'," + str(friend) + ")")
frl = friendslist
else:
skip = True
try:
users.remove(user.lower())
except:
pass
sleep(10)
else:
for item in temp:
frl.append(item[0])
ucur.execute("SELECT FollowerId FROM followers WHERE ScreenName='" +
user + "' COLLATE NOCASE")
fol = []
temp = ucur.fetchall()
skip = False
if len(temp) == 0:
#get friends
print "Downloading followers for " + user.lower()
followerslist = mygetter.getFollowers(user.lower(), [], -1)
if followerslist != "FAIL":
for follower in followerslist:
ucur.execute("INSERT INTO followers VALUES('" +
user.lower() + "'," + str(follower) + ")")
fol = followerslist
else:
skip = True
try:
users.remove(user.lower())
except:
pass
sleep(10)
else:
for item in temp:
fol.append(item[0])
ucon.commit()
if skip == False:
graph.add_node(user.lower())
for other in users:
skip2 = False
ucur.execute("SELECT UserId FROM usermap WHERE ScreenName='" +
other.lower() + "' COLLATE NOCASE")
temp = ucur.fetchall()
if len(temp) == 0:
#get ID from web
print "Downloading userid for " + other.lower()
x = mygetter.getIDfromUser(other.lower())
if x != "FAIL":
ucur.execute("INSERT INTO usermap VALUES('" +
other.lower() + "'," + x + ")")
sid = x
ucon.commit()
else:
try:
users.remove(other.lower())
except:
pass
skip2 = True
sleep(10)
else:
sid = temp[0][0]
if skip2 == False:
if sid in fol:
graph.add_edge(other.lower(), user.lower())
if sid in frl:
graph.add_edge(user.lower(), other.lower())
print "Built graph"
nx.write_gml(graph, "newfriendfollowerhtccgt29.gml")
ucon.commit()
con.close()
ucon.close()
print "Wrote graph"
#{node: [parents],[children]}
print "Number of retweets:" + str(len(subset))
kl=0
for item in subset:
print "Set: " + str(cl)+"/"+str(len(retweets)) + ", Tweet "+str(kl)+"/"+str(len(subset))
kl+=1
if item[2].lower()!="-" and item[2].lower()!="''" and item[2].lower()!=None:
skip=False
#check if user is following source, need idmap and user details
#pull data if necessary
ucur.execute("SELECT FriendId FROM friends WHERE ScreenName='"+item[1].lower()+"' COLLATE NOCASE")
fl=ucur.fetchall()
if len(fl)==0:
#grab friends
print "Downloading friends for " + item[1].lower()
friendslist=mygetter.getFriends(item[1].lower(), [], -1)
if friendslist!="FAIL":
for friend in friendslist:
ucur.execute("INSERT INTO friends VALUES('" + item[1].lower() + "'," + str(friend) + ")" )
fl=friendslist
else:
skip=True
sleep(10)
#should drop deleted user datas here
else:
l2=fl
fl=[]
for le in l2:
fl.append(le[0])
#get source id
ucur.execute("SELECT UserId FROM usermap WHERE ScreenName='"+item[2].lower()+"' COLLATE NOCASE")
def dbplotffnetwork():
graph=nx.DiGraph()
#Plot following/follower network
#restrict to those with 3 tweets or more
mintime=1358090418
maxtime=1363963163
mygetter=DBTweetGetter(None,None)
con = lite.connect("tweetsdb.db")
cur=con.cursor()
ucon = lite.connect("userdb.db")
ucur=ucon.cursor()
tusers=[]
users=[]
cur.execute("SELECT ScreenName FROM htglobalwarming WHERE ConvertedTime > "+str(mintime) +" AND ConvertedTime < " + str(maxtime) + " COLLATE NOCASE")
temp=cur.fetchall()
for item in temp:
tusers.append(item[0].lower())
for item in tusers:
if not (item in users):
if tusers.count(item)>7:
users.append(item)
print len(users)
# cur.execute("SELECT ScreenName FROM htclimatechange WHERE ConvertedTime > "+str(mintime)+" AND ConvertedTime < " + str(maxtime) + " COLLATE NOCASE")
# temp=cur.fetchall()
# tusers=[]
# for item in temp:
# tusers.append(item[0].lower())
# for item in tusers:
# if not (item in users):
# if tusers.count(item)>29:
# users.append(item)
# print len(users)
# cur.execute("SELECT ScreenName FROM htagw WHERE ConvertedTime > "+str(mintime) + " COLLATE NOCASE")
# temp=cur.fetchall()
# tusers=[]
# for item in temp:
# tusers.append(item[0].lower())
# for item in tusers:
# if not (item in users):
# if tusers.count(item)>2:
# users.append(item)
# print len(users)
#aim for 380
#sys.exit("Hammertime")
i=0
try:
users.remove("undercoverzen")
users.remove("jivelad")
users.remove("anabananazavala")
#TODO Formalise this
except:
pass
for user in users:
print "User " + str(i)+"/"+str(len(users))
i+=1
#For each user check which other users are in friends, followers
ucur.execute("SELECT FriendId FROM friends WHERE ScreenName='"+user.lower()+"' COLLATE NOCASE")
frl=[]
temp=ucur.fetchall()
skip=False
if len(temp)==0:
#get friends
print "Downloading friends for " + user.lower()
friendslist=mygetter.getFriends(user.lower(), [], -1)
if friendslist!="FAIL":
for friend in friendslist:
ucur.execute("INSERT INTO friends VALUES('" + user.lower() + "'," + str(friend) + ")" )
frl=friendslist
else:
skip=True
try:
users.remove(user.lower())
except:
pass
sleep(10)
else:
for item in temp:
frl.append(item[0])
ucur.execute("SELECT FollowerId FROM followers WHERE ScreenName='"+user+"' COLLATE NOCASE")
fol=[]
temp=ucur.fetchall()
skip=False
if len(temp)==0:
#get friends
print "Downloading followers for " + user.lower()
followerslist=mygetter.getFollowers(user.lower(), [], -1)
if followerslist!="FAIL":
for follower in followerslist:
ucur.execute("INSERT INTO followers VALUES('" + user.lower() + "'," + str(follower) + ")" )
fol=followerslist
else:
skip=True
try:
users.remove(user.lower())
except:
pass
sleep(10)
else:
for item in temp:
fol.append(item[0])
ucon.commit()
if skip==False:
graph.add_node(user.lower())
for other in users:
skip2=False
ucur.execute("SELECT UserId FROM usermap WHERE ScreenName='"+other.lower()+"' COLLATE NOCASE")
temp=ucur.fetchall()
if len(temp)==0:
#get ID from web
print "Downloading userid for " + other.lower()
x=mygetter.getIDfromUser(other.lower())
if x!="FAIL":
ucur.execute("INSERT INTO usermap VALUES('" +other.lower()+ "'," + x + ")" )
sid=x
ucon.commit()
else:
try:
users.remove(other.lower())
except:
pass
skip2=True
sleep(10)
else:
sid=temp[0][0]
if skip2==False:
if sid in fol:
graph.add_edge(other.lower(), user.lower())
if sid in frl:
graph.add_edge(user.lower(), other.lower())
print "Built graph"
nx.write_gml(graph, "newfriendfollowerhtccgt29.gml")
ucon.commit()
con.close()
ucon.close()
print "Wrote graph"