I'm using Tweepy's Streaming API to listen to tweets for an upcoming event. There are keywords specified to listen to and write to my database hosted on a server(gearhost).
However, when there are no tweets coming in for a set period of time, it will display this error for the next tweet coming in and won't be able to write to my database.
pymysql.err.OperationalError: (2006, MySQL server has gone away(BrokenPipeError(32, 'Broken Pipe'))")
I've searched up a few solutions such as: Correct way of keeping mysql connection but it doesn't seem to work for me. It displayed this error:
AttributeError: 'NoneType' object has no attribute 'cursor'
I searched up another solution but its saying I should use a connection pool instead but I'm new to python and don't really know how connection pools work.
Can anyone help me? Here's my code:
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'])
