Scraper_Runs_Mongo.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing libraries\n",
    "import os\n",
    "import requests\n",
    "import time\n",
    "from time import sleep\n",
    "from random import uniform\n",
    "from datetime import date, timezone, datetime, timedelta\n",
    "import csv\n",
    "\n",
    "import re\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import json\n",
    "\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv()\n",
    "\n",
    "from pymongo import MongoClient\n",
    "from pymongo.collection import Collection\n",
    "\n",
    "#from gspread_formatting import *\n",
    "#import gspread\n",
    "#from gspread_pandas import Spread"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get data from factchecking sites MongoDB\n",
    "def initialize_mongo():\n",
    "    mongo_url = \"mongodb+srv://\"+os.environ.get(\"FACTCHECK_DB_USERNAME\")+\":\"+os.environ.get(\"FACTCHECK_DB_PASSWORD\")+\"@tattle-data-fkpmg.mongodb.net/test?retryWrites=true&w=majority&ssl=true&ssl_cert_reqs=CERT_NONE\"   \n",
    "    cli = MongoClient(mongo_url)\n",
    "    db = cli[os.environ.get(\"DB_NAME\")]\n",
    "    coll = db[os.environ.get(\"COLL_NAME\")]\n",
    "    if coll.count_documents({}) > 0:\n",
    "        return coll \n",
    "    else:\n",
    "        print(\"Error accessing Mongo collection\")\n",
    "        sys.exit()\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "ename": "ConfigurationError",
     "evalue": "The DNS operation timed out after 20.004527807235718 seconds",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTimeout\u001b[0m                                   Traceback (most recent call last)",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pymongo/srv_resolver.py\u001b[0m in \u001b[0;36m_resolve_uri\u001b[0;34m(self, encapsulate_errors)\u001b[0m\n\u001b[1;32m     71\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__slen\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__plist\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 72\u001b[0;31m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__slen\u001b[0m \u001b[0;34m<\u001b[0m \u001b[0;36m2\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     73\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mConfigurationError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0m_INVALID_HOST_MSG\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mfqdn\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/dns/resolver.py\u001b[0m in \u001b[0;36mquery\u001b[0;34m(qname, rdtype, rdclass, tcp, source, raise_on_no_answer, source_port, lifetime)\u001b[0m\n\u001b[1;32m   1099\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1100\u001b[0;31m     return get_default_resolver().query(qname, rdtype, rdclass, tcp, source,\n\u001b[0m\u001b[1;32m   1101\u001b[0m                                         \u001b[0mraise_on_no_answer\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msource_port\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/dns/resolver.py\u001b[0m in \u001b[0;36mquery\u001b[0;34m(self, qname, rdtype, rdclass, tcp, source, raise_on_no_answer, source_port, lifetime)\u001b[0m\n\u001b[1;32m    899\u001b[0m                 \u001b[0;32mfor\u001b[0m \u001b[0mnameserver\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mnameservers\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 900\u001b[0;31m                     \u001b[0mtimeout\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_compute_timeout\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstart\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlifetime\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    901\u001b[0m                     \u001b[0mport\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnameserver_ports\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnameserver\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mport\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/dns/resolver.py\u001b[0m in \u001b[0;36m_compute_timeout\u001b[0;34m(self, start, lifetime)\u001b[0m\n\u001b[1;32m    798\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mduration\u001b[0m \u001b[0;34m>=\u001b[0m \u001b[0mlifetime\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 799\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mTimeout\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtimeout\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mduration\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    800\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mmin\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlifetime\u001b[0m \u001b[0;34m-\u001b[0m \u001b[0mduration\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtimeout\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mTimeout\u001b[0m: The DNS operation timed out after 20.004527807235718 seconds",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mConfigurationError\u001b[0m                        Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-28-5c37d521919a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mcoll\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0minitialize_mongo\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m<ipython-input-27-3707d4dfb43b>\u001b[0m in \u001b[0;36minitialize_mongo\u001b[0;34m()\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0minitialize_mongo\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m     \u001b[0mmongo_url\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"mongodb+srv://\"\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0mos\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0menviron\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"FACTCHECK_DB_USERNAME\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0;34m\":\"\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0mos\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0menviron\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"FACTCHECK_DB_PASSWORD\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0;34m\"@tattle-data-fkpmg.mongodb.net/test?retryWrites=true&w=majority&ssl=true&ssl_cert_reqs=CERT_NONE\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 4\u001b[0;31m     \u001b[0mcli\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mMongoClient\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmongo_url\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      5\u001b[0m     \u001b[0mdb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcli\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mos\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0menviron\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"DB_NAME\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m     \u001b[0mcoll\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdb\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mos\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0menviron\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"COLL_NAME\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pymongo/mongo_client.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, host, port, document_class, tz_aware, connect, type_registry, **kwargs)\u001b[0m\n\u001b[1;32m    637\u001b[0m            \u001b[0mThe\u001b[0m\u001b[0;31m \u001b[0m\u001b[0;31m`\u001b[0m\u001b[0;31m`\u001b[0m\u001b[0mcopy_database\u001b[0m\u001b[0;31m`\u001b[0m\u001b[0;31m`\u001b[0m \u001b[0mmethod\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0mremoved\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msee\u001b[0m \u001b[0mthe\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    638\u001b[0m            \u001b[0;34m:\u001b[0m\u001b[0mdoc\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;31m`\u001b[0m\u001b[0mcopy_database\u001b[0m \u001b[0mexamples\u001b[0m \u001b[0;34m<\u001b[0m\u001b[0;34m/\u001b[0m\u001b[0mexamples\u001b[0m\u001b[0;34m/\u001b[0m\u001b[0mcopydb\u001b[0m\u001b[0;34m>\u001b[0m\u001b[0;31m`\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0malternatives\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 639\u001b[0;31m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    640\u001b[0m            \u001b[0mThe\u001b[0m \u001b[0;34m:\u001b[0m\u001b[0mmeth\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;31m`\u001b[0m\u001b[0mMongoClient\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdisconnect\u001b[0m\u001b[0;31m`\u001b[0m \u001b[0mmethod\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0mremoved\u001b[0m\u001b[0;34m;\u001b[0m \u001b[0mit\u001b[0m \u001b[0mwas\u001b[0m \u001b[0ma\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    641\u001b[0m            \u001b[0msynonym\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0;34m:\u001b[0m\u001b[0mmeth\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;31m`\u001b[0m\u001b[0;34m~\u001b[0m\u001b[0mpymongo\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mMongoClient\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[0;31m`\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pymongo/uri_parser.py\u001b[0m in \u001b[0;36mparse_uri\u001b[0;34m(uri, default_port, validate, warn, normalize, connect_timeout)\u001b[0m\n\u001b[1;32m    498\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0mpath_part\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    499\u001b[0m         \u001b[0mdbase\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0m_\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mopts\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpath_part\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpartition\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"?\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 500\u001b[0;31m         \u001b[0;32mif\u001b[0m \u001b[0mdbase\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    501\u001b[0m             \u001b[0mdbase\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0munquote_plus\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdbase\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    502\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0;34m\".\"\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mdbase\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pymongo/srv_resolver.py\u001b[0m in \u001b[0;36mget_hosts\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    100\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_get_srv_response_and_hosts\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mencapsulate_errors\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    101\u001b[0m         \u001b[0mresults\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_resolve_uri\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mencapsulate_errors\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 102\u001b[0;31m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    103\u001b[0m         \u001b[0;31m# Construct address tuples\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    104\u001b[0m         nodes = [\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pymongo/srv_resolver.py\u001b[0m in \u001b[0;36m_get_srv_response_and_hosts\u001b[0;34m(self, encapsulate_errors)\u001b[0m\n\u001b[1;32m     81\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     82\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mConfigurationError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexc\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 83\u001b[0;31m         \u001b[0;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresults\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     84\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mConfigurationError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Only one TXT record is supported\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     85\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0;34mb\"&\"\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjoin\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34mb\"\"\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjoin\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mres\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstrings\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mres\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mresults\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdecode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"utf-8\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/pymongo/srv_resolver.py\u001b[0m in \u001b[0;36m_resolve_uri\u001b[0;34m(self, encapsulate_errors)\u001b[0m\n\u001b[1;32m     77\u001b[0m             \u001b[0mresults\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_resolve\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__fqdn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"TXT\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlifetime\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__connect_timeout\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     78\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mresolver\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mNoAnswer\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mresolver\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mNXDOMAIN\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 79\u001b[0;31m             \u001b[0;31m# No TXT records\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     80\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     81\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mConfigurationError\u001b[0m: The DNS operation timed out after 20.004527807235718 seconds"
     ]
    }
   ],
   "source": [
    "coll = initialize_mongo()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29931"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "coll.count_documents({})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Count of Stories Added in the Last Week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_weekly_data(coll):\n",
    "    \n",
    "    today = date.today()\n",
    "    start_delta = datetime.timedelta(days=7)\n",
    "    start_date = today-start_delta\n",
    "    \n",
    "    pipeline = [\n",
    "        {\"$project\":{\"date_accessed\":\"$date_accessed\", \"date_updated\":\"$date_updated\", 'postID': \"$postID\",'postURL': \"$postURL\",\n",
    "                     \"headline\": \"$headline\", \"docs\": \"$docs\", \"author\": \"$author\", \"domain\": \"$domain\",\n",
    "                     \"date\": {\"$dateFromString\": {\"dateString\": \"$date_updated\"}}}},\n",
    "        {\"$match\": {\"date\": {\"$gte\":datetime.datetime(start_date.year, start_date.month, start_date.day, 0, 0), \n",
    "                             \"$lt\": datetime.datetime(today.year, today.month, today.day, 0, 0)}}}\n",
    "    ]\n",
    "    \n",
    "    docs = coll.aggregate(pipeline)\n",
    "    return docs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "type object 'datetime.datetime' has no attribute 'timedelta'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-42-e474e020d8a6>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0mc\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0mdocs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mget_weekly_data\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcoll\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      4\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mdoc\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mdocs\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m     \u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdoc\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<ipython-input-41-bbc58949d2be>\u001b[0m in \u001b[0;36mget_weekly_data\u001b[0;34m(coll)\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m     \u001b[0mtoday\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdate\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtoday\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 4\u001b[0;31m     \u001b[0mstart_delta\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtimedelta\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdays\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m7\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      5\u001b[0m     \u001b[0mstart_date\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mtoday\u001b[0m\u001b[0;34m-\u001b[0m\u001b[0mstart_delta\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mAttributeError\u001b[0m: type object 'datetime.datetime' has no attribute 'timedelta'"
     ]
    }
   ],
   "source": [
    "c=0\n",
    "result=[]\n",
    "docs = get_weekly_data(coll)\n",
    "for doc in docs:\n",
    "    result.append(doc)\n",
    "    c+=1\n",
    "print(c)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Count of Stories per fact checking site"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_stories_by_domain(coll):\n",
    "    pipeline = [\n",
    "        {\"$project\":{\"date_accessed\":\"$date_accessed\", \"date_updated\":\"$date_updated\", 'postID': \"$postID\",'postURL': \"$postURL\",\n",
    "                     \"headline\": \"$headline\", \"docs\": \"$docs\", \"author\": \"$author\", \"domain\": \"$domain\",\n",
    "                     \"date\": {\"$dateFromString\": {\"dateString\": \"$date_updated\"}}}},\n",
    "    ]\n",
    "    \n",
    "    docs = coll.aggregate([{\"$group\" : {\"_id\": \"$domain\", \"count\":{\"$sum\":1}}}])\n",
    "    return docs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "docs = get_stories_by_domain(coll)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': 'newsmobile.in', 'count': 5441}\n",
      "{'_id': 'newsmeter.in', 'count': 1803}\n",
      "{'_id': 'altnews.in/hindi', 'count': 1943}\n",
      "{'_id': 'boomlive.in', 'count': 3207}\n",
      "{'_id': 'factcrescendo.com', 'count': 251}\n",
      "{'_id': 'vishvasnews.com/hindi', 'count': 946}\n",
      "{'_id': 'factchecker.in', 'count': 363}\n",
      "{'_id': 'bangla.boomlive.in', 'count': 2709}\n",
      "{'_id': 'vishvasnews.com/assamese', 'count': 56}\n",
      "{'_id': 'vishvasnews.com', 'count': 242}\n",
      "{'_id': 'factly.in', 'count': 3859}\n",
      "{'_id': 'vishvasnews.com/punjabi', 'count': 494}\n",
      "{'_id': 'indiatoday.in', 'count': 1678}\n",
      "{'_id': 'hindi.boomlive.in', 'count': 2203}\n",
      "{'_id': 'altnews.in', 'count': 2589}\n",
      "{'_id': 'vishvasnews.com/english', 'count': 398}\n",
      "{'_id': 'thequint.com', 'count': 1440}\n",
      "{'_id': 'digiteye.in', 'count': 309}\n",
      "18\n"
     ]
    }
   ],
   "source": [
    "c=0\n",
    "result=[]\n",
    "docs = get_stories_by_domain(coll)\n",
    "for doc in docs:\n",
    "    print(doc)\n",
    "    result.append(doc)\n",
    "    c+=1\n",
    "print(c)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write to Google doc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def upload_to_spreadsheet():\n",
    "    \n",
    "    service = gspread.service_account('cli_secret.json')\n",
    "    wb_name = 'FCDBStatus_Public'\n",
    "    sh = client.open(sheet_name)\n",
    "\n",
    "    worksheets_count = len(list(sh.worksheets()))\n",
    "    \n",
    "    \n",
    "    sh.share(EMAIL, perm_type='user', role='writer')\n",
    "    worksheet_name = \"worksheet\" + str(worksheets_count)\n",
    "    \n",
    "    rows = data.shape[0]\n",
    "    #cols = data.shape[1]\n",
    "    \n",
    "    worksheet = sh.add_worksheet(title=worksheet_name,rows=rows,cols=20)\n",
    "\n",
    "    spread = Spread(sheet_name)\n",
    "\n",
    "    spread.df_to_sheet(df=data,sheet=worksheet_name,index=False)\n",
    "    \n",
    "    validation_rule = DataValidationRule(\n",
    "    BooleanCondition('ONE_OF_LIST', ['0', '1']),showCustomUi=True)\n",
    "    \n",
    "    validation_range = 'C2:E' + str(rows)\n",
    "    set_data_validation_for_cell_range(worksheet,validation_range, validation_rule)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "service = gspread.service_account('cli_secret.json')\n",
    "wb_name = 'FCDBStatus_Public'\n",
    "sheet1_name = 'updated_count'\n",
    "sheet2_name = 'domain_count'\n",
    "sh = service.open(wb_name)\n"
   ]
  },
  {
   "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.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}