Fetch Follower Info.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tweepy\n",
    "import re\n",
    "import json\n",
    "\n",
    "import sqlite3 as lite\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "import datetime, time, os, sys\n",
    "import argparse, configparser\n",
    "Config = configparser.ConfigParser()\n",
    "Config.read('config.cnf')\n",
    "\n",
    "consumer_key = Config.get('twittersfupubresearch', 'consumer_key')\n",
    "consumer_secret = Config.get('twittersfupubresearch', 'consumer_secret')\n",
    "access_token = Config.get('twittersfupubresearch', 'access_token')\n",
    "access_token_secret = Config.get('twittersfupubresearch', 'access_token_secret')\n",
    "\n",
    "auth = tweepy.OAuthHandler(consumer_key, consumer_secret)\n",
    "auth.set_access_token(access_token, access_token_secret)\n",
    "# set up access to the Twitter API\n",
    "api = tweepy.API(auth, wait_on_rate_limit=True, wait_on_rate_limit_notify=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "litecon = lite.connect('data/swcc.db')\n",
    "\n",
    "with litecon:\n",
    "\n",
    "# set up SQL tables\n",
    "    litecur = litecon.cursor()\n",
    "    # the users that were found\n",
    "    litecur.execute(\"CREATE TABLE IF NOT EXISTS users (user_id TEXT, screen_name TEXT, user_object TEXT, fetch_followers TEXT, error TEXT, user_modified TEXT)\")\n",
    "    litecur.execute(\"CREATE UNIQUE INDEX IF NOT EXISTS users_user_id ON users (user_id)\")\n",
    "    litecur.execute(\"CREATE INDEX IF NOT EXISTS users_screen_name ON users (screen_name)\")    \n",
    "\n",
    "    litecur.execute(\"CREATE TABLE IF NOT EXISTS followers (user_id TEXT, follower_id TEXT, modified TEXT)\")\n",
    "    litecur.execute(\"CREATE INDEX IF NOT EXISTS followers_user_id ON followers (user_id)\")\n",
    "    litecur.execute(\"CREATE UNIQUE INDEX IF NOT EXISTS followers_user_follower_id ON followers (user_id, follower_id)\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_missing_users():\n",
    "    df = pd.read_excel('data/swcc-twitter.xlsx')\n",
    "    seed_data = set(df['ID-Author'].str.lower())\n",
    "    del df\n",
    "\n",
    "    with litecon:\n",
    "    # set up SQL tables\n",
    "        litecur = litecon.cursor()\n",
    "        cur = litecur.execute(\"SELECT screen_name FROM users\")\n",
    "        fetched = cur.fetchall()\n",
    "        fetched = [x[0].lower() for x in fetched]\n",
    "\n",
    "    ret = seed_data.difference(fetched)\n",
    "    print(\"%s remaining\" % len(ret))\n",
    "    return ret"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def __insert_user(user, fetch_followers = 0):\n",
    "    now = datetime.datetime.today().strftime(\"%Y-%m-%d %H:%M:%S\")\n",
    "    with litecon:\n",
    "        litecur = litecon.cursor()\n",
    "        try:\n",
    "            litecur.execute('INSERT INTO users (user_id, screen_name, user_object, fetch_followers, user_modified) VALUES (?, ?, ?, ?, ?)', (user.id, user.screen_name, json.dumps(user._json), fetch_followers, now))\n",
    "        except lite.IntegrityError:\n",
    "            # don't worry about duplicates\n",
    "            pass\n",
    "        \n",
    "def __insert_user_error(error, user_id = None, screen_name = None):\n",
    "    now = datetime.datetime.today().strftime(\"%Y-%m-%d %H:%M:%S\")\n",
    "    with litecon:\n",
    "        litecur = litecon.cursor()\n",
    "        try:\n",
    "            litecur.execute('INSERT INTO users (user_id, screen_name, error, user_modified) VALUES (?, ?, ?, ?)', (user_id, screen_name, error, now))\n",
    "        except lite.IntegrityError:\n",
    "            # don't worry about duplicates\n",
    "            pass\n",
    "\n",
    "def get_user(user_id):\n",
    "    try:\n",
    "        user = api.get_user(user_id)\n",
    "        __insert_user(user, fetch_followers=1)\n",
    "    except tweepy.TweepError as error:\n",
    "        __insert_user_error(error.response.reason, user_id=None, screen_name=screen_name)\n",
    "\n",
    "for screen_name in get_missing_users():\n",
    "    get_user(screen_name)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "saving 1242\n",
      "saving 490\n",
      "saving 1035\n",
      "saving 4123\n",
      "saving 121\n",
      "saving 11166\n",
      "saving 304\n",
      "saving 608\n",
      "saving 422\n",
      "saving 1668\n",
      "saving 2\n",
      "saving 5910\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Rate limit reached. Sleeping for: 897\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "saving 1337\n",
      "saving 1471\n",
      "saving 739\n",
      "saving 4297\n",
      "saving 1481\n",
      "saving 139\n",
      "saving 583\n",
      "saving 800\n",
      "saving 117\n",
      "saving 95\n",
      "saving 716\n",
      "saving 1423\n",
      "saving 3926\n",
      "saving 400\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Rate limit reached. Sleeping for: 897\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "saving 1744\n"
     ]
    },
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-31-3744e124703b>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     51\u001b[0m             \u001b[0;32mpass\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     52\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 53\u001b[0;31m \u001b[0mget_followers\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m<ipython-input-31-3744e124703b>\u001b[0m in \u001b[0;36mget_followers\u001b[0;34m(user_id)\u001b[0m\n\u001b[1;32m     36\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     37\u001b[0m             \u001b[0;32mfor\u001b[0m \u001b[0muser_id\u001b[0m \u001b[0;32min\u001b[0m \u001b[0musers\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 38\u001b[0;31m                 \u001b[0mids\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mget_followers\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0muser_id\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     39\u001b[0m     \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     40\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<ipython-input-31-3744e124703b>\u001b[0m in \u001b[0;36mget_followers\u001b[0;34m(user_id)\u001b[0m\n\u001b[1;32m     40\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     41\u001b[0m             \u001b[0mids\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 42\u001b[0;31m             \u001b[0;32mfor\u001b[0m \u001b[0mpage\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mtweepy\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mCursor\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mapi\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfollowers_ids\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mid\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0muser_id\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpages\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     43\u001b[0m                 \u001b[0mids\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mextend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpage\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     44\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/site-packages/tweepy/cursor.py\u001b[0m in \u001b[0;36m__next__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m     47\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     48\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__next__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 49\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnext\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     50\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     51\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mnext\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/site-packages/tweepy/cursor.py\u001b[0m in \u001b[0;36mnext\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m     73\u001b[0m         data, cursors = self.method(cursor=self.next_cursor,\n\u001b[1;32m     74\u001b[0m                                     \u001b[0;34m*\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 75\u001b[0;31m                                     **self.kargs)\n\u001b[0m\u001b[1;32m     76\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mprev_cursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnext_cursor\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcursors\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     77\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/site-packages/tweepy/binder.py\u001b[0m in \u001b[0;36m_call\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    248\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    249\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 250\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    251\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    252\u001b[0m     \u001b[0;31m# Set pagination mode\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/site-packages/tweepy/binder.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    162\u001b[0m                                     \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwait_on_rate_limit_notify\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    163\u001b[0m                                         \u001b[0mlog\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwarning\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Rate limit reached. Sleeping for: %d\"\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0msleep_time\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 164\u001b[0;31m                                     \u001b[0mtime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msleep\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msleep_time\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;36m5\u001b[0m\u001b[0;34m)\u001b[0m  \u001b[0;31m# sleep for few extra sec\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    165\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    166\u001b[0m                 \u001b[0;31m# if self.wait_on_rate_limit and self._reset_time is not None and \\\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "def __save_followers(user_id, ids):\n",
    "    '''\n",
    "    Do the actual SQLite update with the info collected\n",
    "    '''\n",
    "    now = datetime.datetime.today().strftime(\"%Y-%m-%d %H:%M:%S\")\n",
    "    with litecon:\n",
    "        litecur = litecon.cursor()\n",
    "        \n",
    "        print( 'saving', len(ids))\n",
    "        for f in ids:\n",
    "            try: \n",
    "                litecur.execute('INSERT INTO followers (user_id, follower_id, modified) VALUES (?, ?, ?)', (user_id, f, now))\n",
    "            except lite.IntegrityError:\n",
    "                pass # ignore duplicates, they wont change the network\n",
    "            \n",
    "def __save_follower_error(user_id):\n",
    "    now = datetime.datetime.today().strftime(\"%Y-%m-%d %H:%M:%S\")\n",
    "    with litecon:\n",
    "        litecur = litecon.cursor() \n",
    "    litecur.execute('INSERT INTO followers (user_id, follower_id, modified) VALUES (?, ?, ?)', (user_id, -1, now))\n",
    "    \n",
    "    \n",
    "def get_followers(user_id = None):\n",
    "    '''\n",
    "    Get the followers list for all users (or for a specific user)\n",
    "    '''\n",
    "    if user_id is None:       \n",
    "        while (True):\n",
    "            with litecon:\n",
    "                litecur = litecon.cursor()\n",
    "                litecur.execute('SELECT u.user_id FROM users u LEFT JOIN followers f ON (u.user_id = f.user_id) WHERE f.user_id IS NULL and fetch_followers = 1')\n",
    "        \n",
    "            # go 100 at a time so we're not hitting the DB so much\n",
    "            users = [u[0] for u in litecur.fetchmany(100)]\n",
    "            if not users: break\n",
    "\n",
    "            for user_id in users:\n",
    "                ids = get_followers(user_id)\n",
    "    else: \n",
    "        try:\n",
    "            ids = []\n",
    "            for page in tweepy.Cursor(api.followers_ids, id=user_id).pages():\n",
    "                ids.extend(page)\n",
    "\n",
    "            if len(ids) > 0: \n",
    "                __save_followers(user_id, ids)\n",
    "            else:\n",
    "                __save_follower_error(user_id)\n",
    "\n",
    "        except tweepy.TweepError as error: \n",
    "            pass\n",
    "\n",
    "get_followers()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_users_of_followers():\n",
    "    while (True):\n",
    "        with litecon:\n",
    "            litecur = litecon.cursor()\n",
    "            litecur.execute('SELECT f.follower_id FROM followers f LEFT JOIN users u ON (f.follower_id = u.user_id) WHERE u.user_id IS NULL')\n",
    "\n",
    "        # go 100 at a time so we're not hitting the DB so much\n",
    "        users = [u[0] for u in litecur.fetchmany(100)]\n",
    "        if not users: break\n",
    "\n",
    "        for user_id in users:\n",
    "            get_user(user_id)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}