twitter-user-stats.py

from twython import Twython, TwythonAuthError, TwythonError
from pprint import pprint
import json, time
import sqlite3, mysql.connector as sql

def connect():
    db_params = {
        'user' : "",
        'password' : "",
        'host' : "192.168.1.106",
        'port' : 3306,
        'database' : "hrt",
        'charset' : 'utf8',
        'collation' : 'utf8_general_ci',
        'buffered' : True
        }
    return sql.connect(**db_params)

def insertUserStats(conn,hospital):
    cursor = conn.cursor()
    prefix ="INSERT INTO hospitalsontwittercount(screen_name,follower_count,friends_count,statuses_count,listed_count)" 
    suffix = "values ( %s, %s, %s, %s, %s)"
    values = [
        hospital["screen_name"],
        hospital["followers_count"],
        hospital["friends_count"],
        hospital["statuses_count"],
        hospital["listed_count"]
        ]
    
    query = prefix + suffix
    
    try:
        cursor.execute(query,values)
        conn.commit()
        return True
    except sql.Error as err:
        print str(err)
        return False
    finally:
        cursor.close()
        

app_key='your_app_key'
app_secret='your_secret'
oauth_token='your_oauth'
oauth_token_secret='your_token'

t = Twython(app_key, app_secret,
            oauth_token, oauth_token_secret)

#pprint(r)
conn_sqlite=sqlite3.connect("data/hospital")
cur = conn_sqlite.cursor()

s= ''
for r in cur.execute("select TwitterHandle from HospitalsOnTwitter h, survey s where h.organization=s.Hospital_name and substr(h.state,0,3)=s.State"):
    s+=r[0]+', '
handle_list = s.split(',')
handle_batch_0 = handle_list[0:100]
handle_batch_1 = handle_list[101:200]
handle_batch_2 = handle_list[200:]


conn_mysql = connect()
hospitals_0=t.lookup_user(screen_name=','.join(handle_batch_0), inlcude_entities=True)
for hospital in hospitals_0: insertUserStats(conn_mysql, hospital)
time.sleep(60)
hospitals_1=t.lookup_user(screen_name=','.join(handle_batch_1), inlcude_entities=True)
for hospital in hospitals_1: insertUserStats(conn_mysql, hospital)
time.sleep(60)
hospitals_2=t.lookup_user(screen_name=','.join(handle_batch_2), inlcude_entities=True)
for hospital in hospitals_2: insertUserStats(conn_mysql, hospital)