SQLAlchemy_PG.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy as db\n",
    "from sqlalchemy import create_engine\n",
    "from datetime import datetime\n",
    "from app import db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "from database import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "40.712776,-74.005974,100km\n",
      "Queens \n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "Brooklyn \n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "No Location\n",
      "\n",
      "Queens \n",
      "\n",
      "No Location\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import tweepy\n",
    "from decouple import config\n",
    "\n",
    "auth = tweepy.OAuthHandler(config('CONSUMER_KEY'), config('CONSUMER_SECRET'))\n",
    "api = tweepy.API(auth)\n",
    "\n",
    "test_tweet = None\n",
    "\n",
    "LAT = 40.712776\n",
    "LONG = -74.005974\n",
    "\n",
    "# May not work for Gujarati cities. Not enough people posting with location info\n",
    "\n",
    "geocode = str(LAT) + ',' + str(LONG) + ',100km'\n",
    "print(geocode)\n",
    "\n",
    "for tweet in tweepy.Cursor(api.search, q='meme', geocode=geocode).items(100):\n",
    "    json_dict = tweet._json\n",
    "    try:\n",
    "        print(json_dict['place']['name'], '\\n')\n",
    "    except TypeError:\n",
    "        print('No Location\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import sessionmaker\n",
    "Session = sessionmaker(bind=db.engine)\n",
    "session = Session()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'text': 'corona', 'indices': [0, 7]}, {'text': 'gocorona', 'indices': [8, 17]}, {'text': 'coronavirusoutbreak', 'indices': [18, 38]}, {'text': 'coronaindia', 'indices': [41, 53]}, {'text': 'quarantine', 'indices': [54, 65]}, {'text': 'quarantineandchill', 'indices': [66, 85]}, {'text': 'kannada', 'indices': [86, 94]}, {'text': 'chummatalks', 'indices': [95, 107]}]\n",
      "corona\n",
      "gocorona\n",
      "coronavirusoutbreak\n",
      "coronaindia\n",
      "quarantine\n",
      "quarantineandchill\n",
      "kannada\n",
      "chummatalks\n"
     ]
    }
   ],
   "source": [
    "hashtags = test_tweet['entities']['hashtags']\n",
    "print(hashtags)\n",
    "tags = [Hashtag(hashtag=hashtags[i]['text']) for i in range(len(hashtags))]\n",
    "for tag in tags:\n",
    "    print(tag.hashtag)\n",
    "    session.add(tag)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "place_id = test_tweet['place']['id']\n",
    "name = test_tweet['place']['name']\n",
    "country = test_tweet['place']['country']\n",
    "country_code = test_tweet['place']['country_code']\n",
    "place = Place(place_id=1, name=name, country=country, country_code=country_code)\n",
    "# print(place_id)\n",
    "session.add(place)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(test_tweet)\n",
    "tweet_obj = BaseTweet(tweet_id=test_tweet['id'],\n",
    "                     tweet_id_str=test_tweet['id_str'],\n",
    "                     source=test_tweet['source'],\n",
    "                     favorited=test_tweet['favorited'],\n",
    "                     retweeted=test_tweet['retweeted'],\n",
    "                     favorite_count=test_tweet['favorite_count'],\n",
    "                     retweet_count=test_tweet['retweet_count'],\n",
    "                     result_type=test_tweet['metadata']['result_type'],\n",
    "                     created_at=datetime.strptime(\"21/11/06 16:30\", \"%d/%m/%y %H:%M\"),\n",
    "                     lang='en',\n",
    "                     possibly_sensitive=False,\n",
    "                     reply_count=0,\n",
    "                     place_id=1)\n",
    "tweet_obj.place = place\n",
    "tweet_obj.hashtags = tags\n",
    "# print(tweet_obj.hashtags)\n",
    "session.add(tweet_obj)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(BaseTweet).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "session.rollback()"
   ]
  }
 ],
 "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.2"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": true,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}