Twitter_bot_geolocation_v3-Campus.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import tweepy\n",
    "import json\n",
    "import pandas as pd\n",
    "import csv\n",
    "\n",
    "import mysql.connector\n",
    "from mysql.connector import Error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Twitter API credentials\n",
    "consumer_key = \"NDhGN1poxOV4el21shhNpFbbf\"\n",
    "consumer_secret = \"xk8ZtyEh6Hpq2zucqvcrqSDXm7gTredBC0T8S6T9mSCPZJhEmx\"\n",
    "access_token = \"825394860190470144-VmRgBQeYWF0MtoBYCrT4IA5ANzmDMwG\"\n",
    "access_token_secret = \"gkHs9Beab9lPsJA9bMCwCUITRqsTLkGIwdYl6xlav0jIu\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#boundingbox ciudad de Madrid obtenido de https://boundingbox.klokantech.com/\n",
    "campus = [-3.7395722584,40.4342310784,-3.7156005182,40.4612470501] \n",
    "\n",
    "\n",
    "def connect(user_id, user_name, user_loc, user_follow_count,user_friends_count, user_fav_count,user_status_count,\n",
    "            tweet_id,text,created_at,source,\n",
    "            reply_id, reply_user_id,\n",
    "            retweet_id,retweet_user_id,\n",
    "            quote_id,quote_user_id,\n",
    "            reply_count,retweet_count,favorite_count,quote_count, \n",
    "            hashtags, mention_ids,\n",
    "            place_id, place_name, coord):\n",
    "\n",
    "    con = mysql.connector.connect(host = 'localhost',\n",
    "                                  database='twitterdb', user='david', password = 'password', charset = 'utf8mb4',auth_plugin='mysql_native_password')\n",
    "    cursor = con.cursor()\n",
    "    try:\n",
    "\n",
    "        if con.is_connected():\n",
    "\n",
    "            query = \"INSERT INTO UsersCam (user_id, tweet_id,user_name, user_loc, user_follow_count,user_friends_count, user_fav_count,user_status_count) VALUES (%s,%s, %s, %s, %s, %s, %s, %s)\"\n",
    "            cursor.execute(query, (user_id, tweet_id, user_name, user_loc, user_follow_count,user_friends_count, user_fav_count,user_status_count))\n",
    "            \n",
    "               \n",
    "            query2 = \"INSERT INTO PostsCam (tweet_id,user_id,text,created_at,source,reply_id, reply_user_id,retweet_id, retweet_user_id,quote_id,quote_user_id,reply_count,retweet_count,favorite_count,quote_count,place_id, place_name, coord,hashtags, mention_ids) VALUES (%s,%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s)\"\n",
    "            cursor.execute(query2, (tweet_id,user_id,text,created_at,source,\n",
    "                                    reply_id, reply_user_id,\n",
    "                                    retweet_id, retweet_user_id,\n",
    "                                    quote_id,quote_user_id,\n",
    "                                    reply_count,retweet_count,favorite_count,quote_count,\n",
    "                                    place_id, place_name, coord,\n",
    "                                    hashtags, mention_ids))\n",
    "            \n",
    "            con.commit()   \n",
    "    except Error as e:\n",
    "        print(e)\n",
    "        print(text)\n",
    "        \n",
    "        #Carlota: He dejado este print, porque no era capaz de almacenar emojis por la codificacion. \n",
    "        #Estoy casi segura de que se ha arreglado, pero por si acaso\n",
    "    cursor.close()\n",
    "    con.close() \n",
    "    return\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting\n"
     ]
    }
   ],
   "source": [
    "class MyStreamListener(tweepy.StreamListener):\n",
    "    \n",
    "    def on_data(self,data):\n",
    "        # Twitter returns data in JSON format - we need to decode it first\n",
    "        try:\n",
    "            decoded = json.loads(data)\n",
    "            \n",
    "        except Exception as e:\n",
    "            print (\"Error on_data: %s\" % str(e)) #we don't want the listener to stop\n",
    "            return True\n",
    "        \n",
    "        #LOCATION METADATA\n",
    "        \n",
    "        #En caso de estar geolocalizado guardar la geolocalizacion\n",
    "        #Si esta geolocalizado dentro de un bounding box (no exacta)\n",
    "        if decoded.get('place') is not None:\n",
    "            place_id = decoded.get('place').get('id')\n",
    "            place_name =decoded.get('place').get('name')\n",
    "        else:\n",
    "            place_id = 'None'\n",
    "            place_name = 'None'\n",
    "            \n",
    "        #Si es localizacion exacta\n",
    "        #Geo is deprecated, they suggest to use simply coordinates\n",
    "        if decoded.get('cordinates') is not None:\n",
    "            m_coord = decoded.get('coordinates')\n",
    "            c=0\n",
    "            coord=''\n",
    "            for i in range(0, len(m_coord)-1):\n",
    "                mc=m_coord[i]\n",
    "                m_coord=coord+mc+';'#use a different separator!\n",
    "                c=c+1\n",
    "            mc=m_coord[c]\n",
    "            m_coord=coord+mc\n",
    "        else:\n",
    "            coord = 'None'\n",
    "        \n",
    "        \n",
    "        #USER METADATA\n",
    "        user_name = '@' + decoded.get('user').get('screen_name') #nombre cuenta @itdUPM \n",
    "        user_id=decoded.get('user').get('id') #id de la cuenta (int)\n",
    "        user_loc=decoded.get('user').get('location') \n",
    "        user_follow_count=decoded.get('user').get('followers_count')\n",
    "        user_friends_count=decoded.get('user').get('friends_count')\n",
    "        user_fav_count=decoded.get('user').get('favourites_count')\n",
    "        user_status_count=decoded.get('user').get('statuses_count')\n",
    "        \n",
    "        #POST METADATA\n",
    "        created_at = decoded.get('created_at') #Fecha\n",
    "        text = decoded['text'].replace('\\n',' ') #Contenido tweet\n",
    "        tweet_id = decoded['id'] #tweet id (int64)\n",
    "        source = decoded['source'] #string source (web client, android, iphone) interesante???\n",
    "        \n",
    "\n",
    "        #REPLY METADATA\n",
    "        reply_id=decoded['in_reply_to_status_id']\n",
    "        reply_user_id=decoded['in_reply_to_user_id']\n",
    "\n",
    "        #RETWEET\n",
    "        if decoded.get('retweeted_status') is not None:\n",
    "            retweet_id = decoded['retweeted_status'] ['id']\n",
    "            retweet_user_id = decoded['retweeted_status']['user']['id']\n",
    "            \n",
    "            #Carlota: Si es un retweet los campos de nº de retweets favs etc vienen dentro de retweeted status\n",
    "            #David: ok bien visto, he añadido el id de usuario retweeteado\n",
    "            reply_count = decoded['retweeted_status']['reply_count'] #Number of times this Tweet has been replied to\n",
    "            retweet_count = decoded['retweeted_status']['retweet_count'] #Number of times this Tweet has been retweeted\n",
    "            favorite_count = decoded['retweeted_status']['favorite_count'] #how many times this Tweet has been liked by Twitter users.\n",
    "            quote_count = decoded['retweeted_status']['quote_count']\n",
    "            \n",
    "            #hashtags_list=decoded.get('retweeted_status').get('entities').get('hashtags')     \n",
    "            #mentions=decoded.get('retweeted_status').get('entities').get('user_mentions')\n",
    "            #David: para esto hay que crear una cadena de texto recorriendo la lista, el\n",
    "            #código estaba en la versión anterior...\n",
    "            \n",
    "            hashtags_list=decoded['retweeted_status']['entities']['hashtags']     \n",
    "            mentions=decoded['retweeted_status']['entities']['user_mentions']\n",
    "            \n",
    "            hashtags=''\n",
    "            c=0\n",
    "            if len(hashtags_list)>0:\n",
    "                for i in range(0, len(hashtags_list)-1):\n",
    "                    mh=hashtags_list[i].get('text')\n",
    "                    hashtags=hashtags+mh+';'\n",
    "                    c=c+1\n",
    "                mh=hashtags_list[c].get('text')\n",
    "                hashtags=hashtags+str(mh)\n",
    "            else:\n",
    "                hashtags='None'\n",
    "\n",
    "            mention_ids=''\n",
    "            c=0\n",
    "            if len(mentions)>0:\n",
    "                for i in range(0, len(mentions)-1):\n",
    "                    mid=mentions[i].get('id_str')\n",
    "                    mention_ids=mention_ids+mid+';'#use a different separator!\n",
    "                    c=c+1\n",
    "                mid=mentions[c].get('id_str')\n",
    "                mention_ids=mention_ids+str(mid)\n",
    "            else:\n",
    "                mention_ids='None'\n",
    "\n",
    "            \n",
    "            #David: esto no sé si haría falta... este justo es un retweet de un post que a su ves\n",
    "            #es un quote de una noticia, osea que hay dos pasos de conexión, pero el retweet \n",
    "            #con el quote ya existe... lo guardamos pero hay que tenerlo en cuenta que es redundante\n",
    "            \n",
    "            #Carlota: Lo quito, porque tienes razon y no habia caido...\n",
    "            \n",
    "            #David. lo podemos dejar porque no son campos adicionales\n",
    "                     \n",
    "            if decoded['retweeted_status']['is_quote_status']:\n",
    "                if 'quoted_status' not in decoded['retweeted_status']:\n",
    "                    quote_id='None'\n",
    "                    quote_user_id='None'  \n",
    "                else:\n",
    "                    quote_id=decoded['retweeted_status']['quoted_status']['id']\n",
    "                    quote_user_id=decoded['retweeted_status']['quoted_status']['user']['id']\n",
    "            else:\n",
    "                quote_id='None'\n",
    "                quote_user_id='None'\n",
    "                \n",
    "        else:\n",
    "            \n",
    "            reply_count = decoded['reply_count'] #Number of times this Tweet has been replied to\n",
    "            retweet_count = decoded['retweet_count'] #Number of times this Tweet has been retweeted\n",
    "            favorite_count = decoded['favorite_count'] #how many times this Tweet has been liked by Twitter users.\n",
    "            quote_count = decoded['quote_count']\n",
    "            retweet_id = 'None'\n",
    "            retweet_user_id = 'None'\n",
    "            \n",
    "            if decoded['is_quote_status']:\n",
    "                if 'quoted_status' not in decoded:\n",
    "                    quote_id='None'\n",
    "                    quote_user_id='None'  \n",
    "                else:\n",
    "                    quote_id=decoded['quoted_status']['id']\n",
    "                    quote_user_id=decoded['quoted_status']['user']['id']\n",
    "            else:\n",
    "                quote_id='None'\n",
    "                quote_user_id='None'\n",
    "                \n",
    "            hashtags_list=decoded.get('entities').get('hashtags')     \n",
    "            mentions=decoded.get('entities').get('user_mentions')\n",
    "            \n",
    "            hashtags=''\n",
    "            c=0\n",
    "            if len(hashtags_list)>0:\n",
    "                for i in range(0, len(hashtags_list)-1):\n",
    "                    mh=hashtags_list[i].get('text')\n",
    "                    hashtags=hashtags+mh+';'\n",
    "                    c=c+1\n",
    "                mh=hashtags_list[c].get('text')\n",
    "                hashtags=hashtags+str(mh)\n",
    "            else:\n",
    "                hashtags='None'\n",
    "\n",
    "            mention_ids=''\n",
    "            c=0\n",
    "            if len(mentions)>0:\n",
    "                for i in range(0, len(mentions)-1):\n",
    "                    mid=mentions[i].get('id_str')\n",
    "                    mention_ids=mention_ids+mid+';'#use a different separator!\n",
    "                    c=c+1\n",
    "                mid=mentions[c].get('id_str')\n",
    "                mention_ids=mention_ids+str(mid)\n",
    "            else:\n",
    "                mention_ids='None'\n",
    " \n",
    "        #insert data just collected into MySQL database\n",
    "        connect(user_id, user_name, user_loc, user_follow_count,user_friends_count, user_fav_count,user_status_count,\n",
    "                tweet_id,text,created_at,source,\n",
    "                reply_id, reply_user_id,\n",
    "                retweet_id,retweet_user_id,\n",
    "                quote_id,quote_user_id,\n",
    "                reply_count,retweet_count,favorite_count,quote_count, \n",
    "                hashtags, mention_ids,\n",
    "                place_id, place_name, coord)\n",
    "        \n",
    "        #print(\"Tweet colleted at: {} \".format(str(created_at)))\n",
    "        \n",
    "    def on_error(self, status_code):\n",
    "        if status_code == 420:\n",
    "            #returning False in on_error disconnects the stream\n",
    "            return False\n",
    "\n",
    "    # returning non-False reconnects the stream, with backoff.\n",
    "\n",
    "    # returning non-False reconnects the stream, with backoff.\n",
    "\n",
    "# Writing csv titles\n",
    "if __name__ == '__main__':\n",
    "    print ('Starting')\n",
    "    #authorize twitter, initialize tweepy        \n",
    "    auth = tweepy.OAuthHandler(consumer_key, consumer_secret)\n",
    "    auth.set_access_token(access_token, access_token_secret)\n",
    "    api = tweepy.API(auth, wait_on_rate_limit=True)\n",
    "    \n",
    "    #create the api and the stream object\n",
    "    myStreamListener = MyStreamListener()\n",
    "    myStream = tweepy.Stream(auth = api.auth, listener=myStreamListener)\n",
    "    myStream.filter(locations = campus)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}