def initProbs():
tweetProbs = { 'news' : 0, 'picture' : 0, 'retweet' : 0, 'skip' : 1 }
followProbs = { 'keyword' : 0, 'back' : 0, 'related' : 0, 'unfollow': 0, 'skip' : 1 }
for tType, tProb in tweetProbs.iteritems():
db.executeQuery('INSERT IGNORE INTO tweet_probs (tweet_type, tweet_prob) VALUES (%s, %s);', values=(tType, tProb))
for fType, fProb in followProbs.iteritems():
db.executeQuery('INSERT IGNORE INTO follow_probs (follow_type, follow_prob) VALUES (%s, %s);', values=(fType, fProb))
def unfollow():
" Unfollows the oldest following user that doesn't follow the bot back. "
# Find first user the bot is following.
try:
uId = db.executeQuery('SELECT user_id FROM follows WHERE active = 1 LIMIT 1', output=True)[0][0]
except Exception as e:
print "\nCouldn't find any suitable users to unfollow."
return None
# If user is following the bot, set active = 2 to prevent him from being unfollowed.
try:
if 'followed_by' in twython.lookup_friendships(user_id=int(uId))[0]['connections']:
logging.warning('BOT TWREQ unfollow1')
db.executeQuery('UPDATE follows SET active = 2 WHERE user_id = %s', (uId,))
print '\nMarked user with ID ' + str(uId) + ' as "do not unfollow".'
return unfollow()
except Exception as e:
logging.warning('Could not lookup friendship status for user ' + str(uId))
# If user is not following the bot, unfollow him.
try:
twython.destroy_friendship(user_id=uId)
logging.warning('BOT TWREQ unfollow2')
print '\nUnfollowed user with ID ' + str(uId)
except Exception as e:
logging.error('BOT ERROR unfollow: ' + str(e))
# Set active = 0 to indicate unfollow.
db.executeQuery('UPDATE follows SET active = 0 WHERE user_id = %s', (uId,))
def updateRelatedTrending(items, table):
'''
Truncates the related keywords/accounts table
before inserting new ones.
'''
db.executeQuery('TRUNCATE TABLE ' + str(table))
for item in items:
insertRelated(item, table)
def executeRouteQuery(self, queryParam1, queryParam2):
connection = createDBConnection(db_name)
statement = None
if len(queryParam1) > 0 and len(queryParam2) > 0:
statement = """
SELECT route_id, agency_name, route_short_name, route_long_name, agency_url
FROM routes, agency
WHERE routes.agency_id = agency.agency_id
AND route_id LIKE '%s'
AND route_short_name LIKE '%s'
ORDER BY route_id
""" % (queryParam1, queryParam2)
if len(queryParam1) > 0 and len(queryParam2) == 0:
statement = """
SELECT route_id, agency_name, route_short_name, route_long_name, agency_url
FROM routes, agency
WHERE routes.agency_id = agency.agency_id
AND route_id LIKE '%s'
ORDER BY route_id
""" % (queryParam1)
if len(queryParam1) == 0 and len(queryParam2) > 0:
statement = """
SELECT route_id, agency_name, route_short_name, route_long_name, agency_url
FROM routes, agency
WHERE routes.agency_id = agency.agency_id
AND route_short_name LIKE '%s'
ORDER BY route_id
""" % (queryParam2)
print(statement)
return executeQuery(connection, statement)
def fetchRelated(mode):
''' Returns a list of all related keywords/accounts in the database. '''
query = 'SELECT * FROM ' + str(mode)
relatedItems = db.executeQuery(query, output=True)
# Query output is a list of singleton tuples; convert this to a list.
if relatedItems is None: return []
return [x[0] for x in relatedItems]
def executeTripQuery(self, queryParam):
connection = createDBConnection(db_name)
statement = """
SELECT trip_id, trip_headsign, service_id
FROM trips
WHERE route_id = '%s'
""" % (queryParam)
return executeQuery(connection, statement)
def executeTripStopsQuery(self, queryParam):
connection = createDBConnection(db_name)
statement = """
SELECT stops.stop_id, stops.stop_name, stops.stop_lat, stops.stop_lon, stop_times.stop_sequence, stop_times.trip_id, stop_times.arrival_time, stop_times.departure_time
FROM stop_times, stops
WHERE stop_times.stop_id = stops.stop_id
AND stop_times.trip_id = '%s'
""" % (queryParam)
return executeQuery(connection, statement)
def executeTripQuery(self, queryParam1, queryParam2):
connection = createDBConnection(db_name)
statement = """
SELECT trips.trip_id, trip_headsign, service_id, arrival_time, departure_time
FROM trips, stop_times
WHERE trips.trip_id = stop_times.trip_id AND
(route_id = '%s' OR stop_id = '%s')
ORDER BY arrival_time, departure_time
""" % (queryParam1, queryParam2)
return executeQuery(connection, statement)
def executeDateQuery(self, queryParam):
connection = createDBConnection(db_name)
statement = """
SELECT trips.trip_id, universal_calendar.date
FROM trips, universal_calendar
WHERE trips.service_id = universal_calendar.service_id
AND trips.trip_id = '%s'
ORDER BY universal_calendar.date
""" % (queryParam)
return executeQuery(connection, statement)
def fetchProbs(mode):
'''
Returns a list of dictionary like 'probType (str) : probValue (float)'
indicating the current probability settings for `mode`.
'''
query = 'SELECT * FROM ' + str(mode) + '_probs'
currProbs = db.executeQuery(query, output=True)
# Query output is a list of tuples; convert to dict.
return {prob[0] : prob[1] for prob in currProbs}
def executeStopQuery(self, queryParam1, queryParam2):
connection = createDBConnection(db_name)
statement = """
SELECT stop_id, stop_name, stop_lat, stop_lon
FROM stops
WHERE stop_id LIKE '%s'
OR stop_name LIKE '%s'
ORDER BY stop_id
""" % (queryParam1, queryParam2.lower())
return executeQuery(connection, statement)
def executeStopRouteQuery(self, queryParam):
connection = createDBConnection(db_name)
statement = """
SELECT DISTINCT trips.route_id, route_short_name, route_long_name
FROM stop_times, trips, routes
WHERE trips.trip_id = stop_times.trip_id
AND trips.route_id = routes.route_id
AND stop_id = '%s'
ORDER BY trips.route_id
""" % (queryParam)
return executeQuery(connection, statement)
def insertTweet(tweet, url=None, bitly=None, pic=None):
' Inserts a tweet into the database. '
query = 'INSERT INTO tweets (tweet, url, bitly, pic) VALUES (%s, %s, %s, %s);'
db.executeQuery(query, (tweet, url, bitly, pic))
def insertFollower(uId, uHandle, followers):
' Inserts a follow interaction into the database. '
query = 'INSERT INTO followers (user_id, user_handle, followers) VALUES (%s, %s, %s);'
db.executeQuery(query, (uId, uHandle, followers))
def insertRelated(item, table):
' Inserts a keyword/related account into the database. '
column = table[:-1] # Column is table name minus trailing 's' (e.g. 'keywords' --> 'keyword').
query = 'INSERT IGNORE INTO ' + str(table) + ' (' + str(column) + ') VALUES (%s);'
db.executeQuery(query, (item,))
def insertRetweet(tweet_id, tweet_text, user, followers, retweets):
' Inserts a retweet into the database. '
query = 'INSERT INTO retweets (tweet_id, tweet_text, user, followers, retweets) VALUES (%s, %s, %s, %s, %s);'
db.executeQuery(query, (tweet_id, tweet_text, user, followers, retweets))
def insertFollow(uId, uHandle, followers, tweet, source):
' Inserts a follow interaction into the database. '
query = 'INSERT INTO follows (user_id, user_handle, followers, tweet_text, source) VALUES (%s, %s, %s, %s, %s);'
db.executeQuery(query, (uId, uHandle, followers, tweet, source))
def updateProb(table, probType, probValue):
' Updates a single probability value (e.g. sets "skip" to 0.8 for "tweets"). '
query = 'UPDATE ' + str(table) + '_probs SET ' + str(table) + '_prob = %s WHERE ' + str(table) + '_type = %s;'
db.executeQuery(query, (probValue, probType))
def exists(col, val, table='tweets'):
''' Checks whether a column-value pair exists, e.g.
to check if a picture has already been tweeted. '''
noOfResults = len(db.executeQuery('SELECT * FROM ' + table + ' WHERE ' + str(col) + ' = %s', values=(val,), output=True))
return True if (noOfResults > 0) else False