def getUser(tid, tbName):
"""
:param tid: one tid
:param tbName: table name
:return: associated user ID of the input tid
"""
# sql = "select tu_id from " + tbName + " where tid = '" + str(tid) + "'"
sql = "select tu_name from " + tbName + " where tid = '" + str(tid) + "'"
userID = queryFromDB.freeQuery(dbConnect, sql)[0][0]
return userID
def allCoor(tb):
"""
:param tb:
:return:
"""
sql = "select eid, concat(lat, ', ', lng) as coor, tids from " + tb + " where lat is not null"
print(sql)
coorList = queryFromDB.freeQuery(dbConnect, sql)
return coorList
def getMaxRT(tidList):
"""
:param tb:
:return:
"""
tids = ', '.join(tidList)
sql = "select max(t_recount) from original where tid in (" + tids + ")"
result = queryFromDB.freeQuery(dbConnect, sql)[0][0]
print('Max re tweet number:', result)
return result
def getData(col, eid):
"""
Input event id from credibility table, and return a list of supporting tids
:param col: column name
:param eid: event id
:return: list of associated data
"""
sql = "select " + col + " from " + tb_out_Event + " where eid = '" + str(eid) + "'"
data = queryFromDB.freeQuery(dbConnect, sql)[0][0]
if isinstance(data, str):
data = data.split(', ')
print(col, data)
return data
def getTIDs(eid, tbName):
"""
Input event id from credibility table, and return a list of supporting tids
:param eid: event id
:param tbName: table name
:return: list of supporting tids
"""
sql = "select tids from " + tbName + " where eid = '" + str(eid) + "'"
data = queryFromDB.freeQuery(dbConnect, sql)[0][0]
if isinstance(data, str):
data = data.split(', ')
print(data)
return data
def getEvlScore(tidList):
"""
Input a list of supporting tids for certain event from credibility table, and locate their post time in original
tweet table
:param tidList: supporting tids
:return: list of post time
"""
tb_out_Name = "original"
timeLine = []
for tid in tidList:
sql = "select tcreate from " + tb_out_Name + " where tid = '" + str(tid) + "'"
pt = queryFromDB.freeQuery(dbConnect, sql)[0][0]
timeLine.append(pt)
return timeLine
def getRTs(tid, tbName):
"""
find all the tids and users of associated retweets of the input tids of original twitter
:param tid: tid of original twitter (supporting tweets under certain event)
:param tbName: table name
:return: tids of associated retweets
"""
RTids = []
sql = "select tid from " + tbName + " where t_reid = '" + str(tid) + "'"
RTid = queryFromDB.freeQuery(dbConnect, sql)
if len(RTid) > 0:
for id in RTid:
RTids.append(id[0])
return RTids
def matchTime(col, tidList):
"""
:param colName:
:param tidList:
:return:
"""
sql = "select " + col + " from " + tb_join2 + " where tid in (" + tidList[
-1] + ")"
timeList = queryFromDB.freeQuery(dbConnect, sql)
spaceTime = []
for t in range(len(timeList)):
if tidList[0] is not None and tidList[
1] is not None and timeList[t][0] != '':
spaceTime.append(
(tidList[0], tidList[1], tidList[2] + ',' + tidList[3],
timeList[t][0], str(timeList[t][1])))
return spaceTime
def get_followers(dbc, uname_list):
"""
:param dbc:
:param uname_list: list of user names or ids
:return: retrieve follower info from db
"""
resultList = []
count, listLen = 0, len(uname_list)
for uname in uname_list:
print(count, listLen)
try:
sql = "select max(tu_Followers) from tweets where tu_Name = '" + uname + "'"
result = queryFromDB.freeQuery(dbc, sql)
result = result[0][0]
resultList.append((uname, result))
count += 1
except:
print(uname)
count += 1
return resultList
