Skip to content

Instantly share code, notes, and snippets.

@christopherkullenberg
Created January 18, 2018 07:20
Show Gist options
  • Save christopherkullenberg/4d9e3b975ca431ef5ff05335395d38c8 to your computer and use it in GitHub Desktop.
Save christopherkullenberg/4d9e3b975ca431ef5ff05335395d38c8 to your computer and use it in GitHub Desktop.
Display the source blob
Display the rendered blob
Raw
{
"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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment