Twitter_bot_keywords_v7.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "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\n",
    "\n",
    "#imports for catching the errors\n",
    "from ssl import SSLError\n",
    "from requests.exceptions import Timeout, ConnectionError\n",
    "from urllib3.exceptions import ReadTimeoutError"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Twitter API credentials\n",
    "consumer_key = 'pe7gsS8WNkANobhPvKU5q9PPv'\n",
    "consumer_secret = 'EF8F0wezQz8HPBRp17DMXTEBrxTXJLBs1mzPnzjLLDlYsHql7D'\n",
    "access_token = '468248438-aR7mCZ40MKTFq2TNIOGN0IWEegXUBGKDpN3QtDyT'\n",
    "access_token_secret = 'K5dXjiphLnk17QfWTxigJHOJjgM3005T3c2OZj2X79FA8'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Definicion palabras claves busqueda\n",
    "keywords = ['ODS', 'sostenibilidad', 'desarrollo', 'sostenible', 'cooperación']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "\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",
    "    connect to MySQL database and insert twitter data\n",
    "    \"\"\"\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",
    "            Insert twitter data\n",
    "            \"\"\"\n",
    "        \n",
    "            query = \"INSERT INTO UsersODS (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 PostsODS (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",
    "            \n",
    "    except Error as e:\n",
    "        print(e)\n",
    "        print(text)\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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "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('coordinates') is not None:\n",
    "            m_coord = decoded.get('coordinates')['coordinates']\n",
    "            #print(m_coord)\n",
    "            coord=str(m_coord[0])+';'+str(m_coord[1])\n",
    "            #print(coord)\n",
    "            #time.sleep(100)\n",
    "            \n",
    "        else:\n",
    "            coord = 'None'\n",
    "            #print(place_id)\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",
    "        tweet_id = decoded['id'] #tweet id (int64)\n",
    "        source = decoded['source'] #string source (web client, android, iphone) interesante???\n",
    "        \n",
    "        if decoded.get('truncated'):\n",
    "            text = decoded['extended_tweet']['full_text'].replace('\\n',' ')\n",
    "        else: \n",
    "            text = decoded['text'].replace('\\n',' ') #Contenido tweet\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",
    "            if decoded['retweeted_status']['truncated']:\n",
    "                text = decoded['retweeted_status']['extended_tweet']['full_text'].replace('\\n',' ')\n",
    "            else: \n",
    "                text = decoded['retweeted_status']['text'].replace('\\n',' ') #Contenido tweet\n",
    "            \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",
    "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",
    "    #Filter the stream by keywords\n",
    "    myStream.filter(track = keywords)\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
}