porrbotscraper.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from twitterscraper import query_tweets\n",
    "import sqlite3\n",
    "\n",
    "#CREATE TABLE users(id INTEGER PRIMARY KEY, tweetid TEXT UNIQUE, user TEXT, timestamp TEXT, text TEXT);\n",
    "conn = sqlite3.connect('porrtweets.sqlite3') \n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"porrbotar.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "listofusernames = []\n",
    "\n",
    "for d in df.iterrows():\n",
    "    if d[1][1].startswith(\"@\"):\n",
    "        listofusernames.append(d[1][1][1:])\n",
    "    else:\n",
    "        listofusernames.append(d[1][1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tweet ID: 949924340186931200\n",
      "User: VeritasPharma\n",
      "Timestamp: 2018-01-07 08:43:06\n",
      "Text: @personperson923 what do you think is going to be the most active marijuana stock of 2018?\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 952015363205681152\n",
      "User: SmthnglikeKites\n",
      "Timestamp: 2018-01-13 03:12:04\n",
      "Text: @FoxSeeOne @Ness_Divad18 @personperson923 @SubtainNazir2  Follow SomethingLikeKites on Instagram\n",
      "I Motivate, I’m Verified & Follow back \n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 952056711963140097\n",
      "User: itstimwhite\n",
      "Timestamp: 2018-01-13 05:56:23\n",
      "Text: @personperson923 thanksss for following \n",
      "would you mind following Spotify too?\n",
      "https://t.lnk.to/spTw ?!\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 463772234331353088\n",
      "User: jkell150\n",
      "Timestamp: 2014-05-06 20:08:11\n",
      "Text: Pretty dam good week so far and gonna be a good weekend  @TrishaKellogg @kburgess484 @babich_jake @StoutMX73 \n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 475992390298525698\n",
      "User: jkell150\n",
      "Timestamp: 2014-06-09 13:26:44\n",
      "Text: Pretty damn good weekend with @TrishaKellogg @StoutMX73\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 475992626458796032\n",
      "User: jkell150\n",
      "Timestamp: 2014-06-09 13:27:40\n",
      "Text: @StoutMX73 @TrishaKellogg Hahahahaha\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 479594968907284480\n",
      "User: jkell150\n",
      "Timestamp: 2014-06-19 12:02:05\n",
      "Text: Road tripping to Malone with @StoutMX73  to get a rzr 1000 4\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 481862131659595776\n",
      "User: urdadkicksass\n",
      "Timestamp: 2014-06-25 18:10:59\n",
      "Text: @StoutMX73 miss you too\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 481862324933132289\n",
      "User: urdadkicksass\n",
      "Timestamp: 2014-06-25 18:11:45\n",
      "Text: @StoutMX73 let's fish\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 481862513743917056\n",
      "User: urdadkicksass\n",
      "Timestamp: 2014-06-25 18:12:30\n",
      "Text: @StoutMX73 duck that\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 481865508049137664\n",
      "User: urdadkicksass\n",
      "Timestamp: 2014-06-25 18:24:24\n",
      "Text: @StoutMX73 so\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 495746222112923648\n",
      "User: HUSStle_FLow\n",
      "Timestamp: 2014-08-03 01:41:24\n",
      "Text: Gettin rowdy up here! stoutmx73 cbaird254 bing_bing_rippa859 bcook320 http://instagram.com/p/rN9JVKxh_4/ \n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 540502207477469185\n",
      "User: jkell150\n",
      "Timestamp: 2014-12-04 13:45:42\n",
      "Text: @StoutMX73 yea buddy 6 more\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 589479366536667137\n",
      "User: Violettt6\n",
      "Timestamp: 2015-04-18 17:23:27\n",
      "Text: First day back on the pit bike track @cbaird254 @kswagga420 stoutmx73 @mike_huss_839 matt_huss… https://instagram.com/p/1n-CzBIZUB/ \n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 610987242006413312\n",
      "User: claude84jr\n",
      "Timestamp: 2015-06-17 01:48:04\n",
      "Text: If you want some prime snapchats check out mine @StoutMX73 and @CarliiBartlett\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 685654380150943744\n",
      "User: CarliiBartlett\n",
      "Timestamp: 2016-01-09 02:48:37\n",
      "Text: @ratiekeilly @StoutMX73\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 117997462974758912\n",
      "User: pornplus_bot\n",
      "Timestamp: 2011-09-25 16:22:42\n",
      "Text: #porn #sex Karlie montana and jasmine neels 24:00min. #xxx #PornPlus_bot http://www.PornPlus.org/karlie-montana-and-jasmine-neels-video204743.html …\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 122310820280532993\n",
      "User: FreePorniaNet\n",
      "Timestamp: 2011-10-07 14:02:26\n",
      "Text: #porn #sex Karlie montana and jasmine neels 24:00min. #xxx #TeenPorns http://www.TeenPorns.org/karlie-montana-and-jasmine-neels-video204743.html …\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 127817701765824512\n",
      "User: Porn911_bot\n",
      "Timestamp: 2011-10-22 18:44:49\n",
      "Text: #porn #sex Karlie montana and jasmine neels 24:00min. #xxx #Porn911 http://www.Porn911.org/karlie-montana-and-jasmine-neels-video204743.html …\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 206731999044575232\n",
      "User: neels24\n",
      "Timestamp: 2012-05-27 13:02:04\n",
      "Text: http://yfrog.com/nygv0qgj \n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 209267858532876288\n",
      "User: neels_24\n",
      "Timestamp: 2012-06-03 12:58:40\n",
      "Text: @SrBachchan - lot of good wishes Mr bacchan !! Cheers\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 209863815028944901\n",
      "User: neels_24\n",
      "Timestamp: 2012-06-05 04:26:47\n",
      "Text: \"Accept responsibility for your life. Know that it is you who will get you where you want to go, no one else.\"\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 231842558765043712\n",
      "User: neels24\n",
      "Timestamp: 2012-08-04 20:02:28\n",
      "Text: Trying to sleep\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 284363825187282944\n",
      "User: neels_24\n",
      "Timestamp: 2012-12-27 18:23:13\n",
      "Text: @BeingSalmanKhan - Happy Bday Salman... :-)\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 405711931937280000\n",
      "User: neels_24\n",
      "Timestamp: 2013-11-27 14:57:16\n",
      "Text: Rudra...pic.twitter.com/iLETwbNBlK\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 762052603211644928\n",
      "User: curiousdinah\n",
      "Timestamp: 2016-08-06 22:27:53\n",
      "Text: alta envidia le tengo a alissa seguro se chapa a neels 24/7 ahre\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 868195660314443776\n",
      "User: Dotdyl\n",
      "Timestamp: 2017-05-26 20:02:30\n",
      "Text: I need Neels 24/7 because yo he makes me smile\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 946726743959851010\n",
      "User: neels_24\n",
      "Timestamp: 2017-12-29 12:56:59\n",
      "Text: hey darling.. take me. check url in profile..pic.twitter.com/6p5tF17q4y\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 946727141848289281\n",
      "User: neels_24\n",
      "Timestamp: 2017-12-29 12:58:34\n",
      "Text: pic.twitter.com/4kFvfzN6zq\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 946727315089907713\n",
      "User: neels_24\n",
      "Timestamp: 2017-12-29 12:59:15\n",
      "Text: ...It's a sign..pic.twitter.com/AbpDATYKAa\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 946727563967287296\n",
      "User: neels_24\n",
      "Timestamp: 2017-12-29 13:00:15\n",
      "Text: Hey worldpic.twitter.com/UcTJToRekR\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 385129161305509888\n",
      "User: CCharras\n",
      "Timestamp: 2013-10-01 19:48:42\n",
      "Text: @CRoseveare Knowing what to pack is always a head-scratcher for me! Are the red jeans coming this year? #SAMCoventry\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 386948032186826752\n",
      "User: FuturemotoRace\n",
      "Timestamp: 2013-10-06 20:16:14\n",
      "Text: We done for our rider @samcoventry he did he's very best !!\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 387651229548965888\n",
      "User: trudyhanson6\n",
      "Timestamp: 2013-10-08 18:50:29\n",
      "Text: #SAMCoventry http://tinyurl.com/lmxnupf  RT @trutherbot 95% of America's problems disappear if we abolish the Federal Reserve! #EndTheFed\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 389269794672746497\n",
      "User: icegilub\n",
      "Timestamp: 2013-10-13 06:02:06\n",
      "Text: #SAMCoventry http://j.mp/1e17h8O  When going to shake hands at a job interview slip a $5 bill and some ChapStick into their hand.\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 389852137288503296\n",
      "User: qivopusodi\n",
      "Timestamp: 2013-10-14 20:36:07\n",
      "Text: #SAMCoventry http://j.mp/16X3Oc8  all i could think about is food, im so hungrrrry .\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 402355272548904960\n",
      "User: ContiMoto\n",
      "Timestamp: 2013-11-18 08:39:07\n",
      "Text: #Continental #Motorcycle are proud to be associated with Team Futuremoto Racing and #SamCoventry pic http://pinterest.com/pin/554998354050821404/ …\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 415986855537291264\n",
      "User: LuciaPlumridge\n",
      "Timestamp: 2013-12-25 23:26:09\n",
      "Text: #comical #crying  danielharney24 katrinaplumridge samcoventry http://instagram.com/p/iXOyOkoGQR/ \n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 423172587364302848\n",
      "User: _RyanSawyer\n",
      "Timestamp: 2014-01-14 19:19:41\n",
      "Text: @kavosholidays @Kavos 28th July- 5th August!! GONNA GET MESSY'!!! @Reggie_Farmer @hopperx18 @samcoventry \n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 433336640317050882\n",
      "User: JackCozzi\n",
      "Timestamp: 2014-02-11 20:28:00\n",
      "Text: Just robbed samcoventry mac book pro. I'll take £500 cash right now, any takers? http://instagram.com/p/kSgjbkOIHP/ \n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 446441272858124289\n",
      "User: dceline96_\n",
      "Timestamp: 2014-03-20 00:21:08\n",
      "Text: @samcoventry i just remembered i gave you my headphones and your reply was \"who are these made for? dumbo?\" \n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 461304072138944512\n",
      "User: _RyanSawyer\n",
      "Timestamp: 2014-04-30 00:40:36\n",
      "Text: Only 89 days until Kavosssss!  @QuaysideVillage @mykavosholiday @kavosholidays @Reggie_Farmer @hopperx18 @samcoventry\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 461884257653506048\n",
      "User: DrNezCon\n",
      "Timestamp: 2014-05-01 15:06:03\n",
      "Text: Taking #ThrowbackThursday to #SAMsterDAM- last year at #SAMCoventry with @AfyaHauser @acutemedicinepic.twitter.com/dcyL796pJ8\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 471300414436941824\n",
      "User: frostydeee\n",
      "Timestamp: 2014-05-27 14:42:29\n",
      "Text: #Amsterdam2013 samcoventry http://instagram.com/p/ogQeopI6JB/ \n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 493491212981387265\n",
      "User: DrNezCon\n",
      "Timestamp: 2014-07-27 20:20:48\n",
      "Text: @keakester @CRoseveare @csubbe I will never forget that moment with that maroon shirt in #SAMCoventry\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 531574373237268481\n",
      "User: FuturemotoRace\n",
      "Timestamp: 2014-11-09 22:29:41\n",
      "Text: Are rider @samcoventry was out testing on he's crosser today!!!\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 571388144144273408\n",
      "User: DrNezCon\n",
      "Timestamp: 2015-02-27 19:15:24\n",
      "Text: Just put this outfit on, and realised it was far too \"Roseveare\" \n",
      "#SAMCoventry #FashionPolice\n",
      "@acutemedicinepic.twitter.com/Hbkn3JJEwk\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 592740399086772225\n",
      "User: _megzjackson\n",
      "Timestamp: 2015-04-27 17:21:38\n",
      "Text: pub times @hopperx18 @samcoventry pic.twitter.com/ogH6zju6YI\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 595685383389712384\n",
      "User: DrNezCon\n",
      "Timestamp: 2015-05-05 20:23:57\n",
      "Text: Been 3 years since my 1sr @acutemedicine conference at #SAMCoventry. Really looking catching up with friends & colleagues at #SAMBristol\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 652930973152223234\n",
      "User: _RyanSawyer\n",
      "Timestamp: 2015-10-10 19:37:29\n",
      "Text: Pub Crawl tonight @samcoventry\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n",
      "Tweet ID: 944841036920246272\n",
      "User: itstimwhite\n",
      "Timestamp: 2017-12-24 08:03:52\n",
      "Text: @samcoventry thanks 4 the follow \n",
      "would you mind following Spotify too?\n",
      "https://f.tw.wtf/ ?\n",
      "SUCCESS! Added to database.\n",
      "--------------------------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "for l in listofusernames:\n",
    "    for tweet in query_tweets(l, 100):\n",
    "        print(\"Tweet ID: \" + tweet.id)\n",
    "        print(\"User: \" + tweet.user)\n",
    "        print(\"Timestamp: \" + str(tweet.timestamp))\n",
    "        print(\"Text: \" + tweet.text)\n",
    "        try:\n",
    "            cursor.execute('''INSERT INTO users(tweetid, user, timestamp, text)\n",
    "                      VALUES(?, ?, ?, ?)''', (tweet.id, tweet.user, tweet.timestamp, tweet.text))\n",
    "            conn.commit()\n",
    "            print(\"SUCCESS! Added to database.\")\n",
    "        except sqlite3.IntegrityError:\n",
    "            print(\"Already in database, continuing....\")\n",
    "        print(\"-\" * 80)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "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": 2
}