def insertTweet(tweet):
twitterID = tweet.id_str
time = tweet.created_at
#oembed endpoint https://dev.twitter.com/docs/api/1/get/statuses/oembed v1 endpoint
oembedParams = {'id': twitterID, 'omit_script': 'true'}
oembedRequest = requests.get('https://api.twitter.com/1/statuses/oembed.json', params=oembedParams)
embedResponse = json.loads(oembedRequest.text)
embed_html = embedResponse['html']
processed = True
display = True
num_images = len(tweet.termIDs)
q = 'INSERT INTO tweet (twitter_id, time, embed_html, processed, display, num_images) VALUES ( %s, %s, %s, %s, %s, %s ) RETURNING tweet_id'
tweetID = pgutils.getQueryDictionary(q, twitterID, time, embed_html, processed, display, num_images)
tweetID = tweetID[0]['tweet_id']
#insert terms
if len(tweet.termIDs) > 0:
termIDsStr = ','.join(pgCursor.mogrify("(%s,%s)", (tweetID, termID)) for termID in tweet.termIDs)
q = 'INSERT INTO tweet_has_term (tweet_id, term_id) VALUES ' + termIDsStr
pgCursor.execute(q)
#insert users
if len(tweet.screenNames) > 0:
twitterUsersStr = ','.join(pgCursor.mogrify("(%s, %s, %s)", [twitterUser['screen_name'], tweetID, twitterUser['relationship']]) for twitterUser in tweet.screenNames)
q = 'INSERT INTO tweet_has_user (screen_name, tweet_id, relationship) VALUES ' + twitterUsersStr
pgCursor.execute(q)
def searchTweetImages(tweet):
exists = pgutils.getQueryDictionary('SELECT COUNT(*) as exists FROM tweet WHERE twitter_id=%s', tweet.id_str)
if exists[0]['exists'] != 0:
return False
tweet.termIDs = []
tweet.screenNames = []
for type in tweetTypes:
typeObj = tweetTypes[type]
success = tweepyutils.fetchImages(typeObj, tweet)
if not success:
return False
return True
def insertTermImages(term_id, urls, removePreviousTermImages = False):
if len(urls) == 0:
return
if removePreviousTermImages:
pgCursor.execute('DELETE FROM image WHERE term_id=%s', [term_id])
for url in urls:
q = 'SELECT COUNT(*) as count FROM image WHERE term_id=%s AND url=%s'
exists = pgutils.getQueryDictionary(q, term_id, url);
if exists[0]['count'] == 0:
q = 'INSERT INTO image (url, term_id, retrieved_at) VALUES (%s, %s, now())'
pgCursor.execute(q, [url, term_id])
q = 'UPDATE term SET last_queried_at=now() WHERE term_id=%s'
pgCursor.execute(q, [term_id])
def get(self):
self.set_header('Content-Type','text/json')
dreams = pgutils.getQueryDictionary('SELECT * FROM tweet ORDER BY time DESC LIMIT 30')
for dream in dreams:
q = 'SELECT term.* FROM term, tweet, tweet_has_term WHERE tweet_has_term.tweet_id = tweet.tweet_id AND tweet_has_term.term_id = term.term_id AND tweet.tweet_id=%s'
terms = pgutils.getQueryDictionary(q, dream['tweet_id'])
termObjs = []
for term in terms:
q = 'SELECT url FROM image WHERE term_id=%s'
termImages = pgutils.getQueryDictionary(q, term['term_id'])
termObjs.append( {"term": term, "images": termImages})
dream['terms'] = termObjs
q = 'SELECT tweet_has_user.*, twitter_user.user_json FROM tweet_has_user, twitter_user WHERE tweet_id=%s AND tweet_has_user.screen_name=twitter_user.screen_name'
dreamPeople = pgutils.getQueryDictionary(q, dream['tweet_id'])
userProperties = [
'profile_use_background_image', 'default_profile_image', 'profile_background_image_url_https',
'profile_text_color','profile_background_color','description',
'profile_image_url_https'
]
people = []
for person in dreamPeople:
p = {
"screen_name": person['screen_name'],
"relationship": person['relationship']
}
for prop in userProperties:
p[prop] = person['user_json'][prop]
people.append( p )
dream['people'] = people
# convert python datetime to json
class DateTimeJSONEncoder(json.JSONEncoder):
def default(self, obj):
if isinstance(obj, datetime.datetime):
return int(mktime(obj.timetuple()))
return json.JSONEncoder.default(self, obj)
rtnJson = dreams
self.write(json.dumps(rtnJson, cls = DateTimeJSONEncoder))
def selectOrInsertTerm(term, term_type):
termExists = pgutils.getQueryDictionary('SELECT * FROM term WHERE term=%s and term_type_id=%s', term, term_type['term_type_id'])
termRtn = {}
if len(termExists) == 0:
termRtn['expired'] = True
#insert
q = 'INSERT INTO term (term, term_type_id) VALUES (%s, %s) RETURNING term_id'
type_id = term_type['term_type_id']
id = pgutils.getQueryDictionary(q, term, type_id)
termRtn['id'] = id[0]['term_id']
else:
#check expiration
last_queried_at = termExists[0]['last_queried_at']
if last_queried_at == None:
termRtn['expired'] = True
else:
expiredAt = datetime.now() - term_type['expires_after']
if last_queried_at < expiredAt:
termRtn['expired'] = True
else:
termRtn['expired'] = False
termRtn['id'] = termExists[0]['term_id']
return termRtn
def insertTwitterUserIfNotPresent(screen_name,term_type):
userExists = pgutils.getQueryDictionary('SELECT * FROM twitter_user WHERE screen_name=%s', screen_name)
expired = None
if len(userExists) == 0:
expired = True
#insert
q = 'INSERT INTO twitter_user (screen_name) VALUES (%s)'
pgCursor.execute(q, [screen_name])
else:
#check expiration
last_queried_at = userExists[0]['updated_at']
if last_queried_at == None:
expired = True
else:
expiredAt = datetime.now() - term_type['expires_after']
if last_queried_at < expiredAt:
expired = True
else:
expired = False
return expired
