gettweets.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "from requests_oauthlib import OAuth1\n",
    "\n",
    "import json\n",
    "\n",
    "# Let's define consumer and access keys and secrets for getting access to Twitter API through your application\n",
    "consumer_key = 'ex8R74QkZc2dSMnqa23UVCo00'\n",
    "consumer_secret = 'w5eyduTNznYqEwyNy2hpuYNlrfrNGF3aSLPR9LETN1UKmNtx2H'\n",
    "access_token = '796895774449733634-2HDV8yzn7BgfH5jM5kCVCdCQLAsKFHl'\n",
    "access_secret = 'FUSPKORsxwdrlfDUWyr6fHXHK0DchqYXSNHMsVi8DoBo7'\n",
    "\n",
    "# You will authenticate yourself using OAuth1 object\n",
    "auth = OAuth1(consumer_key, consumer_secret, access_token, access_secret)\n",
    "print(auth)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# If the authentication was successful, you should see the name of the account print(out)\n",
    "url_1 = 'https://api.twitter.com/1.1/account/verify_credentials.json'\n",
    "res = requests.get(url_1, auth=auth)\n",
    "\n",
    "print(\"My name is\", res.json()[\"name\"])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import urllib\n",
    "\n",
    "query = '-RT \"the internet is\"'\n",
    "encoded_query = urllib.parse.quote(query)\n",
    "\n",
    "params = {\n",
    "    \"count\": 100, \n",
    "    \"lang\": 'en',\n",
    "    \"q\": encoded_query\n",
    "}\n",
    "\n",
    "url_2 = 'https://api.twitter.com/1.1/search/tweets.json'\n",
    "res = requests.get(url_2, auth=auth, params=params)\n",
    "\n",
    "print(res, res.status_code, res.headers['content-type'])\n",
    "print(res.url)\n",
    "\n",
    "tweets = res.json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import MySQLdb as mdb\n",
    "import sys\n",
    "\n",
    "con = mdb.connect(host = 'localhost',\n",
    "                      user = 'root', \n",
    "                      passwd = 'dwdstudent2015', \n",
    "                      charset = 'utf8', use_unicode=True);\n",
    "\n",
    "\n",
    "def createDatabase():\n",
    "    # Create the Citibike database\n",
    "    db_name = 'tweets_v8'\n",
    "    create_db_query = \"CREATE DATABASE IF NOT EXISTS {db} DEFAULT CHARACTER SET 'utf8'\".format(db=db_name)\n",
    "\n",
    "    # Create a database\n",
    "    cursor = con.cursor()\n",
    "    cursor.execute(create_db_query)\n",
    "    cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def createTable_tweets():\n",
    "    cursor = con.cursor()\n",
    "    db_name = 'tweets_v8'\n",
    "    table_name = 'tweets'\n",
    "    # Create a table to store trends:\n",
    "    create_table_query = '''CREATE TABLE IF NOT EXISTS {db}.{table} \n",
    "                                    (\n",
    "                                    primary_id varchar(250),\n",
    "                                    text varchar(250),\n",
    "                                    PRIMARY KEY(primary_id)\n",
    "                                    )'''.format(db=db_name, table=table_name)\n",
    "    cursor.execute(create_table_query)\n",
    "    cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def createTable_metadata():\n",
    "    cursor = con.cursor()\n",
    "    db_name = 'tweets_v8'\n",
    "    table_name = 'metadata'\n",
    "\n",
    "    create_table_query = '''CREATE TABLE IF NOT EXISTS {db}.{table} \n",
    "                                    (\n",
    "                                    primary_id varchar(250),\n",
    "                                    tweet_id varchar(250),\n",
    "                                    author varchar(250),\n",
    "                                    date datetime,\n",
    "                                    location varchar(250),\n",
    "                                    PRIMARY KEY(primary_id),\n",
    "                                    FOREIGN KEY(primary_id) \n",
    "                                            REFERENCES {db}.tweets(primary_id)\n",
    "                                    )'''.format(db=db_name, table=table_name)\n",
    "    cursor.execute(create_table_query)\n",
    "    cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from datetime import datetime, timedelta\n",
    "import calendar\n",
    "\n",
    "def insertTweets(timestamp):\n",
    "    query_template = '''INSERT IGNORE INTO tweets_v8.tweets(primary_id, text) \n",
    "                VALUES (%s, %s)'''\n",
    "\n",
    "    cursor = con.cursor()\n",
    "    \n",
    "    timestamp = timestamp\n",
    "    \n",
    "    for num, tweet in enumerate(tweets['statuses']):\n",
    "        \n",
    "        primary_id = '-'.join([str(tweet['id']), str(timestamp)])\n",
    "        text = tweet['text']\n",
    "\n",
    "        query_parameters = (primary_id, text)\n",
    "        cursor.execute(query_template, query_parameters)\n",
    "\n",
    "    con.commit()\n",
    "    cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from datetime import datetime, timedelta\n",
    "import calendar\n",
    "\n",
    "def insertMetadata(timestamp):\n",
    "    query_template = '''INSERT IGNORE INTO tweets_v8.metadata(primary_id, tweet_id, author, date, location) \n",
    "                VALUES (%s, %s, %s, %s, %s)'''\n",
    "\n",
    "    cursor = con.cursor()\n",
    "    \n",
    "    timestamp = timestamp\n",
    "    \n",
    "    for num, tweet in enumerate(tweets['statuses']):\n",
    "        \n",
    "        # Converting the date to EST timezone -- 'hacky' approximation:\n",
    "        date_str = tweet['created_at']\n",
    "        date =  datetime.strptime(date_str, '%a %b %d %H:%M:%S %z %Y')\n",
    "        date = date - timedelta(hours=5)\n",
    "        new_date = date.strftime('%Y-%m-%d %H:%M:%S')\n",
    "        \n",
    "        primary_id = '-'.join([str(tweet['id']), str(timestamp)])\n",
    "        tweet_id = tweet['id']\n",
    "        author = tweet['user']['screen_name']\n",
    "        date = new_date\n",
    "        location = tweet['user']['location']\n",
    "\n",
    "        query_parameters = (primary_id, tweet_id, author, date, location)\n",
    "        cursor.execute(query_template, query_parameters)\n",
    "\n",
    "    con.commit()\n",
    "    cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Current timestamp for the second part of the primary_id:\n",
    "now = datetime.now()\n",
    "timestamp = calendar.timegm(now.utctimetuple())\n",
    "\n",
    "createDatabase()\n",
    "\n",
    "createTable_tweets()\n",
    "insertTweets(timestamp)\n",
    "\n",
    "createTable_metadata()\n",
    "insertMetadata(timestamp)"
   ]
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}