def addTweetDetails(conn, params):
try:
cursor = conn.cursor()
tweetData = {}
tweetData["tweet"] = params["tweetID"]
tweetData["favCount"] = params["favCount"]
tweetData["rtCount"] = params["rtCount"]
tweetData["language"] = params["tweetLang"]
# print(params.keys())
if "coordinates" in params.keys():
tweetData["coordinates"] = str(params["coordinates"])
if "rtSource" in params.keys():
tweetData["rtSource"] = params["rtSource"]
if "inReplyToStatusID" in params.keys():
tweetData["inReplyToStatusID"] = params["inReplyToStatusID"]
query = formatInsertQuery("tweetDetails", tweetData)
cursor.execute(query["query"], query["values"])
cursor.close()
conn.commit()
except Error as e:
errorString = "Error while adding tweetDetails to tweet %s\n" % params["tweetID"]
errorString2 = str(e)
gf.logError(errorString)
gf.logError(errorString2)
def getTweetCount(conn):
try:
cursor = conn.cursor()
query = "SELECT COUNT(tweetID) from tweets"
cursor.execute(query)
return cursor.fetchall()[0][0]
except Error as e:
errorString = "Error while getting tweet count.\n"
errorString2 = str(e)
gf.logError(errorString)
gf.logError(errorString2)
# gf.logError(str(e))
# print("ERRROR: Failed to get current number of tweets\n")
# print(e)
# print("-----------------")
return None
def checkTweet(conn, tweetID):
try:
query = "SELECT tweetID from tweets WHERE tweetID = %s" % ("'" + tweetID + "'")
cursor = conn.cursor()
cursor.execute(query)
cursor.fetchall()
if cursor.rowcount > 0:
return True
else:
return False
except Error as e:
gf.logError("Error while checking tweet: %s\n" % tweetID)
# gf.logError(str(e))
# print("Failed to check if tweet %s exists\n" % tweetID)
# print(e)
# print("-----------------")
return None
def checkUser(conn, userID):
try:
# print(userID)
query = "SELECT userID from users WHERE userID = %s" % ("'" + userID + "'")
cursor = conn.cursor()
cursor.execute(query)
cursor.fetchall()
if cursor.rowcount > 0:
return True
else:
return False
except Error as e:
errorString = "Error while checking user: %s\n" % userID
errorString2 = str(e)
gf.logError(errorString)
gr.logError(errorString2)
# gf.logError(str(e))
# print("Failed to check if user %s exists\n" % userID)
# print(e)
# print("-----------------")
return None
def getLatestTweet(conn):
try:
cursor = conn.cursor()
query = "SELECT tweetID FROM tweets \
ORDER BY datePosted DESC LIMIT 1"
cursor.execute(query)
user = cursor.fetchall()
if len(user) > 0:
return user[0][0].encode("utf-8")
else:
return None
except Error as e:
errorString = "Error while getting latest tweet.\n"
errorString2 = str(e)
gf.logError(errorString)
gf.logError(errorString2)
# gf.logError(str(e))
# print("Error: Failed to return latest tweet\n")
# print(e)
# print("-----------------")
return None
def addTweetToDB(conn, params):
add_tweet = "INSERT INTO tweets " "(tweetID, tweetText, user, datePosted) " "VALUES (%s, %s, %s, %s)"
try:
# print("adding tweet to database")
# print(params)
if checkUser(conn, params["userID"]): # if user already exists
pass
else:
addUser(conn, params["userDetails"])
cursor = conn.cursor()
tweetData = (params["tweetID"], params["tweetText"], params["userID"], params["datePosted"])
cursor.execute(add_tweet, tweetData)
cursor.close()
addTweetDetails(conn, params)
conn.commit()
except Error as e:
errorString = "Error while adding tweet %s\n"
errorString2 = str(e)
gf.logError(errorString)
gf.logError(errorString2)
def addUser(conn, params):
add_user = "******" "(userID, screenName, followerCount) " "VALUES (%s, %s, %s)"
try:
cursor = conn.cursor()
if "followerCount" in params.keys():
followerCount = params["followerCount"]
else:
followerCount = 0
userData = (params["userID"], params["screenName"], followerCount)
cursor.execute(add_user, userData)
# cursor.fetchall()
cursor.close()
conn.commit()
except Error as e:
errorString = "Error while adding user: %s\n" % params["userID"]
errorString2 = str(e)
gf.logError(errorString)
gf.logError(errorString2)
# gf.logError(str(e))
# print(e)
# print("Failed to add user %s to DB." % params['userID'])
# print("-----------------")
pass
def getOldestTweet(conn):
try:
cursor = conn.cursor()
maxDate = datetime.datetime.now() - datetime.timedelta(days=7)
query = "SELECT tweetID FROM tweets WHERE datePosted >= %s ORDER BY datePosted ASC LIMIT 1" % (
"'" + maxDate.strftime("%Y-%m-%d") + "'"
)
print (query)
cursor.execute(query)
user = cursor.fetchall()
if len(user) > 0:
return user[0][0].encode("utf-8")
else:
return None
except Error as e:
errorString = "Error while getting oldest tweet.\n"
errorString2 = str(e)
gf.logError(errorString)
gf.logError(errorString2)
# gf.logError(str(e))
# print("Error: Failed to return oldest tweet\n")
# print(e)
# print("-----------------")
return None