1st Part - Final Function for Twitter Retrive & Perform Sentiment.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7849d2ee-6774-4874-b0a4-a6d7957059e0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import tweepy\n",
    "import re\n",
    "import emoji #!pip install emoji\n",
    "from datetime import datetime\n",
    "import mysql.connector\n",
    "from mysql.connector import Error\n",
    "from textblob import TextBlob\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a365b77c-0b36-4940-8b44-90a6c815ba5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "consumerKey = \"erm0vjWKHjKM0ky*****\"\n",
    "consumerSecret = \"EVB5y1R12xlIM3a2CHCgS7Obnq****\"\n",
    "accessToken = \"1482385434500083715-86r8zXO4u5****\"\n",
    "accessTokenSecret = \"KTX7tCrMDk2EvLOEQCOjo0TQ*********\"\n",
    "auth = tweepy.OAuthHandler(consumerKey, consumerSecret)\n",
    "auth.set_access_token(accessToken, accessTokenSecret)\n",
    "api = tweepy.API(auth)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5438dea6-ecc9-4d00-9bce-0f49f711b60e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def tweetsparse(key, number):\n",
    "    \n",
    "    global keyword\n",
    "    keyword= key\n",
    "    noOfTweet = number\n",
    "    tweets = tweepy.Cursor(api.search_tweets, q=keyword).items(noOfTweet) #connect with the Twitter API and proceed with the request\n",
    "    \n",
    "    global listOfTweets\n",
    "    listOfTweets = [] #create a list to keep the data we prefer\n",
    "    \n",
    "    global EmptyTweets\n",
    "    EmptyTweets = [] #create a list to keep if tweet containts empty only spaces values: #True or #False\n",
    "\n",
    "\n",
    "    #parse the specific info for the tweets after checking if it is a retweet or not since the way Favorites are stored changes\n",
    "    for tweet in tweets:    \n",
    "    \n",
    "        is_retweet = hasattr(tweet, 'retweeted_status') #check if tweet is a retweet\n",
    "        if is_retweet == True :\n",
    "\n",
    "            dict_ = {'User Name': tweet.user.screen_name, \n",
    "                 'Followers': tweet.user.followers_count,\n",
    "                 'Tweets Count': tweet.user.statuses_count,\n",
    "                 'Tweet Text': tweet.text,\n",
    "                 'Tweet Created at': tweet.created_at,\n",
    "                 'Location': tweet.user.location,\n",
    "                 'Hashtags': tweet.entities['hashtags'],\n",
    "                 'Retweets':tweet.retweet_count,\n",
    "                 'Likes': tweet.retweeted_status.favorite_count}\n",
    "\n",
    "        else:\n",
    "\n",
    "            dict_ = {'User Name': tweet.user.screen_name, \n",
    "                 'Followers': tweet.user.followers_count,\n",
    "                 'Tweets Count': tweet.user.statuses_count,\n",
    "                 'Tweet Text': tweet.text,\n",
    "                 'Tweet Created at': tweet.created_at,\n",
    "                 'Location': tweet.user.location,\n",
    "                 'Hashtags': tweet.entities['hashtags'],\n",
    "                 'Retweets':tweet.retweet_count,\n",
    "                 'Likes': tweet.favorite_count } \n",
    "\n",
    "\n",
    "        listOfTweets.append(dict_)\n",
    "    \n",
    "    #clean the data we extracted\n",
    "    \n",
    "    for i in range(0,len(listOfTweets)):\n",
    "        \n",
    "        #insert @ in the username using for loop in our list\n",
    "        listOfTweets[i]['User Name'] = re.sub(r'([\\w.-]+)', r'@\\1', listOfTweets[i]['User Name'])\n",
    "        \n",
    "        #Clear RT and user who made the initial tweet if it was a retweet\n",
    "        matchRT = re.search(r'RT', listOfTweets[i]['Tweet Text'])\n",
    "        if matchRT:\n",
    "            listOfTweets[i]['Tweet Text'] = re.sub(r'RT', \"\", listOfTweets[i]['Tweet Text'])\n",
    "            listOfTweets[i]['Tweet Text'] = re.sub(r'@([\\w.-]+:)', \"\", listOfTweets[i]['Tweet Text'])\n",
    "\n",
    "        #Clear all a) mentions b) hashtags to text c) URLs and d) \\n\\n from a tweet\n",
    "\n",
    "        matchMentions = re.findall(r'@([\\w.-]+)', listOfTweets[i]['Tweet Text'])\n",
    "        if matchMentions:\n",
    "            listOfTweets[i]['Tweet Text'] = re.sub(r'@([\\w.-]+)', \"\", listOfTweets[i]['Tweet Text'])\n",
    "\n",
    "        #Match a Hashtag and remove the #\n",
    "        matchHashtags = re.findall(r'#([\\w.-]+)', listOfTweets[i]['Tweet Text'])\n",
    "        if matchHashtags:\n",
    "            listOfTweets[i]['Tweet Text'] = re.sub(r'#([\\w.-]+)', r'\\1', listOfTweets[i]['Tweet Text'])\n",
    "     \n",
    "        #Regex for matching a URL\n",
    "        regex = r\"(?i)\\b((?:https?://|www\\d{0,3}[.]|[a-z0-9.\\-]+[.][a-z]{2,4}/)(?:[^\\s()<>]+|\\(([^\\s()<>]+|(\\([^\\s()<>]+\\)))*\\))+(?:\\(([^\\s()<>]+|(\\([^\\s()<>]+\\)))*\\)|[^\\s`!()\\[\\]{};:'\\\".,<>?«»“”‘’]))\"\n",
    "\n",
    "        #Match a URL\n",
    "        matchURL = re.findall(regex, listOfTweets[i]['Tweet Text'])\n",
    "        if matchURL:\n",
    "            listOfTweets[i]['Tweet Text'] = re.sub(regex, \"\", listOfTweets[i]['Tweet Text'])\n",
    "        \n",
    "        #Match a \\n\\n\n",
    "        matchURL = re.findall('([^.])\\.?\\n\\n', listOfTweets[i]['Tweet Text'])\n",
    "        if matchURL:\n",
    "            listOfTweets[i]['Tweet Text'] = re.sub('([^.])\\.?\\n\\n', \"\", listOfTweets[i]['Tweet Text'])\n",
    "        \n",
    "               \n",
    "        #Clear all emojis from text and location after inserting a regex\n",
    "        \n",
    "        emoj = re.compile(\"([\"\n",
    "        \"\\U0001F1E0-\\U0001F1FF\"  # flags (iOS)\n",
    "        \"\\U0001F300-\\U0001F5FF\"  # symbols & pictographs\n",
    "        \"\\U0001F600-\\U0001F64F\"  # emoticons\n",
    "        \"\\U0001F680-\\U0001F6FF\"  # transport & map symbols\n",
    "        \"\\U0001F700-\\U0001F77F\"  # alchemical symbols\n",
    "        \"\\U0001F780-\\U0001F7FF\"  # Geometric Shapes Extended\n",
    "        \"\\U0001F800-\\U0001F8FF\"  # Supplemental Arrows-C\n",
    "        \"\\U0001F900-\\U0001F9FF\"  # Supplemental Symbols and Pictographs\n",
    "        \"\\U0001FA00-\\U0001FA6F\"  # Chess Symbols\n",
    "        \"\\U0001FA70-\\U0001FAFF\"  # Symbols and Pictographs Extended-A\n",
    "        \"\\U00002702-\\U000027B0\"  # Dingbats\n",
    "        \"])\")\n",
    "        \n",
    "        matchEmojis = re.findall(emoj, listOfTweets[i]['Tweet Text'])\n",
    "        if matchEmojis:\n",
    "            listOfTweets[i]['Tweet Text'] = re.sub(emoj, \"\", listOfTweets[i]['Tweet Text'])\n",
    "        \n",
    "        LmatchEmojis = re.findall(emoj, listOfTweets[i]['Location'])\n",
    "        if LmatchEmojis:\n",
    "            listOfTweets[i]['Location'] = re.sub(emoj, \"\", listOfTweets[i]['Location'])\n",
    "            \n",
    "            \n",
    "        #add hashtags on the 'hashtags'for each tweet and convert it to a format for our database\n",
    "        for j in range(0,len(listOfTweets[i]['Hashtags'])):\n",
    "            listOfTweets[i]['Hashtags'][j]['text'] = re.sub(r'([\\w.-]+)', r'#\\1', listOfTweets[i]['Hashtags'][j]['text'])\n",
    "        lHash = []\n",
    "        for j in range(0,len(listOfTweets[i]['Hashtags'])):\n",
    "            lHash.append(listOfTweets[i]['Hashtags'][j]['text'])\n",
    "        listOfTweets[i]['Hashtags'] = \" \".join(lHash)\n",
    "        lHash\n",
    "        \n",
    "        #Convert the dates\n",
    "        listOfTweets[i]['Tweet Created at'] = datetime.strftime(listOfTweets[i]['Tweet Created at'], '%Y-%m-%d')\n",
    "        \n",
    "        #Check now if the text is empty - contains only spaces\n",
    "        EmptyTweets.append(listOfTweets[i]['Tweet Text'].isspace())\n",
    "        \n",
    "    #Now Delete the tweets that containt only spaces inside the text\n",
    "    for i in range(len(EmptyTweets)-1, 0, -1):\n",
    "        if EmptyTweets[i] == True:\n",
    "            del listOfTweets [i]\n",
    "    \n",
    "    #Finally Remove the duplicate posts of the same user. Meaning the user has made the same post multiple times\n",
    "    seen = set()\n",
    "    \n",
    "    global flistOfTweets\n",
    "    flistOfTweets = []\n",
    "\n",
    "    for dic in listOfTweets:\n",
    "        key = (dic['User Name'], dic['Tweet Text'])\n",
    "        if key in seen:\n",
    "            continue\n",
    "\n",
    "        flistOfTweets.append(dic)\n",
    "        seen.add(key)\n",
    "    \n",
    "    print('After removing Empty:', len(EmptyTweets))\n",
    "    print('After removing Duplicates:', len(flistOfTweets))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "fc3a4989-3e4a-4546-bc9f-debd21ed837a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def databaseupload(l, keyword):\n",
    "    \n",
    "    global tablename\n",
    "    \n",
    "    listOfTweets = l\n",
    "    \n",
    "    db = mysql.connector.connect(host='localhost', database='tweets', user='root', password= '****', auth_plugin='mysql_native_password') \n",
    "    if db.is_connected():\n",
    "        print(\"CONNECTED TO MYSQL DATABASE!\") \n",
    "        cur = db.cursor()\n",
    "        \n",
    "    #Get the table name based on the keyword. First check if there is a hashtag\n",
    "    checkhashtag = re.findall(r'#', keyword)\n",
    "    if checkhashtag:\n",
    "        tablename = re.sub(r'#', '', keyword)\n",
    "    else:\n",
    "        tablename = keyword\n",
    "    \n",
    "    #create the table dynamically based on the keyword the user inserted and after checking if the table already exists\n",
    "    q = \"\"\"SHOW TABLES\"\"\"\n",
    "    cur.execute(q)\n",
    "    results = cur.fetchall()\n",
    "    results_list = [item[0] for item in results] # Conversion to list of str\n",
    "    \n",
    "    #Check if the table doesn't exist in our database create one\n",
    "    if tablename not in results_list:\n",
    "        q = \"CREATE TABLE {} (id int AUTO_INCREMENT, user_name VARCHAR(50), followers INT, tweets_count INT, tweet_text VARCHAR(300), tweet_created_at DATE, location VARCHAR(200), hashtags VARCHAR(300), retweets INT, likes INT, PRIMARY KEY (id));\".format(tablename)\n",
    "        cur.execute(q)\n",
    "        \n",
    "        #we have tried to remove all the emojis but in the case that some tweets still have emojis we change the database so as to be able to accept them\n",
    "        q = \"ALTER TABLE {} MODIFY tweet_text BLOB;\".format(tablename)\n",
    "        cur.execute(q)\n",
    "        \n",
    "        q = \"ALTER TABLE {} MODIFY location BLOB;\".format(tablename)\n",
    "        cur.execute(q)\n",
    "        \n",
    "    #insert data into database \n",
    "    for i in range(0,len(listOfTweets)):\n",
    "        user_name = str(listOfTweets[i]['User Name'])\n",
    "        followers =  listOfTweets[i]['Followers']\n",
    "        tweets_Count = listOfTweets[i]['Tweets Count']\n",
    "        tweet_text = str(listOfTweets[i]['Tweet Text'])\n",
    "        tweet_created_at = listOfTweets[i]['Tweet Created at']\n",
    "        location = str(listOfTweets[i]['Location'])\n",
    "        hashtags = str(listOfTweets[i]['Hashtags'])\n",
    "        retweets = listOfTweets[i]['Retweets']\n",
    "        likes = listOfTweets[i]['Likes']\n",
    "\n",
    "        q= \"INSERT INTO {} (user_name, followers, tweets_count, tweet_text, tweet_created_at, location, hashtags, retweets, likes) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)\".format(tablename)\n",
    "        cur.execute(q, (user_name, followers, tweets_Count, tweet_text, tweet_created_at, location, hashtags, retweets, likes))\n",
    "\n",
    "    db.commit()\n",
    "    db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "1cfc343e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def SentAnalysis (table):\n",
    "    \n",
    "    tablename = table\n",
    "    \n",
    "    #connect to the database\n",
    "    db = mysql.connector.connect(host='localhost', database='tweets', user='root', password= '****', auth_plugin='mysql_native_password') \n",
    "    if db.is_connected():\n",
    "        print(\"CONNECTED TO MYSQL DATABASE!\") \n",
    "        cur = db.cursor()\n",
    "        \n",
    "    #check if the column \"sentiment\" exists in the table else add the column\n",
    "    \n",
    "    q = \"SHOW COLUMNS from {} LIKE 'sentiment';\".format(tablename)\n",
    "    cur.execute(q)\n",
    "    results = cur.fetchall()\n",
    "    results_list = [item[0] for item in results] # Conversion to list of str\n",
    "\n",
    "    if 'sentiment' not in results_list:\n",
    "        q = \"ALTER TABLE {} ADD sentiment varchar(20);\".format(tablename)\n",
    "        cur.execute(q)\n",
    "        \n",
    "    #Get the texts from Database for  analysis\n",
    "    q= \"SELECT tweet_text FROM {}\".format(tablename)\n",
    "    cur.execute(q)\n",
    "    \n",
    "    #append them on a list after converting them to strings\n",
    "    tweet_list = []\n",
    "\n",
    "    for tweet in cur:\n",
    "        tweet_list.append(str(tweet))          \n",
    "    \n",
    "    for i in range(len(tweet_list)):\n",
    "    \n",
    "        analysis = TextBlob(tweet_list[i]) #use of TextBlob\n",
    "        polarity = analysis.sentiment.polarity #polarity\n",
    "\n",
    "        #results based on the polarity\n",
    "        \n",
    "        if polarity < - 0.05:\n",
    "            text = 'negative'\n",
    "\n",
    "        elif polarity > 0.05:\n",
    "            text = 'positive'\n",
    "\n",
    "        else:\n",
    "            text = 'neutral'\n",
    "\n",
    "        id = i+1\n",
    "        \n",
    "        #append the result of each tweet analysis on the table\n",
    "        q = \"UPDATE {} SET sentiment= %s WHERE id = %s \".format(tablename)\n",
    "        cur.execute(q, (text, id))        \n",
    "        db.commit()\n",
    "\n",
    "    db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20c8af9b",
   "metadata": {},
   "source": [
    "## Get the tweets and upload them into Database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90068493",
   "metadata": {},
   "source": [
    "### Bucks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "e23b8c55",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "After removing Empty: 1000\n",
      "After removing Duplicates: 999\n"
     ]
    }
   ],
   "source": [
    "tweetsparse('#Bucks', 1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "8f12b57d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CONNECTED TO MYSQL DATABASE!\n"
     ]
    }
   ],
   "source": [
    "databaseupload(flistOfTweets, keyword)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe229821",
   "metadata": {},
   "source": [
    "### Lakers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "54a40ecd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "After removing Empty: 1000\n",
      "After removing Duplicates: 972\n"
     ]
    }
   ],
   "source": [
    "tweetsparse('#Lakers', 1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "2d0ebd41",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CONNECTED TO MYSQL DATABASE!\n"
     ]
    }
   ],
   "source": [
    "databaseupload(flistOfTweets, keyword)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce8ec09f",
   "metadata": {},
   "source": [
    "### Celtics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "7668316a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "After removing Empty: 1000\n",
      "After removing Duplicates: 995\n"
     ]
    }
   ],
   "source": [
    "tweetsparse('#Celtics', 1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "b3325602",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CONNECTED TO MYSQL DATABASE!\n"
     ]
    }
   ],
   "source": [
    "databaseupload(flistOfTweets, keyword)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1377bc08",
   "metadata": {},
   "source": [
    "## Perform Sentiment Analysis for the Table\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "d053238c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CONNECTED TO MYSQL DATABASE!\n"
     ]
    }
   ],
   "source": [
    "SentAnalysis ('Celtics')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "4e9b2492",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CONNECTED TO MYSQL DATABASE!\n"
     ]
    }
   ],
   "source": [
    "SentAnalysis ('Lakers')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "95b6a731",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CONNECTED TO MYSQL DATABASE!\n"
     ]
    }
   ],
   "source": [
    "SentAnalysis ('Bucks')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "698070a3",
   "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}