import tweepy
from tweepy.streaming import StreamListener

from tweepy import OAuthHandler
from tweepy import Stream
import re
import sys
import json
import pytz
from pytz import timezone

#connect to pymysql
import pymysql

conn = pymysql.connect(host='hosting',
                             user='username',
                             password='password',
                             db='database',
                             autocommit=True,
                             charset='utf8mb4',)



consumer_key = '******'
consumer_secret = '********'
access_token = '****-****'
access_token_secret = '*****'

KEYWORDS = ['singaporepoly','singapore poly', 'sp courses', 'sp open house',
'sp mcdonald', 'sp singapore', 'sp map', 'spoh18', 'sp cca', 'sp career', 'sp library',
'sp part time', 'sp starbucks', '#speye', '#singaporepoly','sp facilities', 'sp polite',
 'sp memories','spgusto', 'sp engineering', 'sp campus','sp bowling', 'sp dmit','sp wifi',
 'sp bowling','sp FC1', 'sp FC2', 'sp FC3', 'sp FC4', 'sp FC5', 'sp FC6', 'sp mae', 'sp eee',
 'sp aircon', 'sp internet','sp mac', 'spdb', 'sp goodies','sp goodie','sp kfc', 'sp bridge',
 'sp mrt', 'sp cls','sp sb','#spbizsch', 'sp jae','sp results','sp result','sp jpsae','sp dsa',
 'sp interview', 'sp eat','sp student','sp camp','sp freshies','sp foc','sp pool','sp class',
 'sp freshmen','sp floorball','sp sports','sp semester','sp cass','sp mass comm','sp information technology',
 'sp event','sp dover','sp moberly','sp performance','spsdz','sp subway','sp chicken rice','sp mini wok',
 'sp mala','sp koufu','sp mcdonalds','Singapore Polytechnic','sp student union','spsu']

auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)

sgtz = timezone('Asia/Singapore')
utc = pytz.timezone('UTC')

class StdOutListener(StreamListener):
    def on_status(self, status):
        # Printing to Terminal
        if ('Singapore' == status.user.location) or ('Central Region, Singapore' == status.user.location) or ('North-East Region, Singapore' == status.user.location) or ('East Region, Singapore' == status.user.location) or ('West Region, Singapore' == status.user.location):
                    d = status.created_at
                    d_tz = utc.localize(d)
                    localtime = d_tz.astimezone(sgtz)
                    status_list = [localtime, status.author.screen_name, status.text]
                    print (status_list)

            #write to database
                    cur = conn.cursor()
                    cur.execute("USE database")
                    cur.execute("INSERT INTO database.tweets_sp_sg(tweet_time_sp_sg,screen_name_sp_sg,tweets_sp_sg) values (%s,%s,%s)",(status_list))

        def on_error(self, status_code):
            print ('Encountered error with status code:', status_code)
            return True

if __name__ == '__main__':
    l = StdOutListener()
    auth = OAuthHandler(consumer_key, consumer_secret)
    auth.set_access_token(access_token, access_token_secret)

    while True:
            stream = Stream(auth, l, timeout = None)
            stream.filter(track = KEYWORDS, languages=['en'])