sql_local_queries_examples.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a9016e87-dfc1-4fbe-8a66-cc798dc6974e",
   "metadata": {},
   "source": [
    "# SQL Local Queries\n",
    "### "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b748f4f4-a64b-45db-b48d-16348f3bceae",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import wget\n",
    "import psycopg2\n",
    "import pymongo\n",
    "import sqlite3\n",
    "from sqlalchemy import create_engine\n",
    "from bson.json_util import dumps, loads\n",
    "import requests\n",
    "import json\n",
    "import os\n",
    "import sys\n",
    "import dotenv\n",
    "\n",
    "# sql passwords file\n",
    "os.chdir(\"C:\\\\Users\\\\dwagn\\\\git\\\\ds-6001\\\\mod7\") \n",
    "dotenv.load_dotenv() \n",
    "sys.tracebacklimit = 0 # error tracebacks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4ae8fbdc-e21e-4e96-916c-b073209da58b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 4 separate tables in db\n",
    "works = pd.read_csv(\"https://github.com/jkropko/DS-6001/raw/master/localdata/Works.csv\")\n",
    "characters = pd.read_csv(\"https://github.com/jkropko/DS-6001/raw/master/localdata/Characters.csv\")\n",
    "chapters = pd.read_csv(\"https://github.com/jkropko/DS-6001/raw/master/localdata/Chapters.csv\")\n",
    "paragraphs = pd.read_csv(\"https://github.com/jkropko/DS-6001/raw/master/localdata/Paragraphs.csv\")\n",
    "\n",
    "works.columns = works.columns.str.lower()\n",
    "characters.columns = characters.columns.str.lower()\n",
    "chapters.columns = chapters.columns.str.lower()\n",
    "paragraphs.columns = paragraphs.columns.str.lower()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16b18eb4-4849-4e64-88a1-f54dcea3811e",
   "metadata": {},
   "source": [
    "## PostgreSQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f3904fa5-79a7-484b-9369-f0314c5fcff7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# PostgreSQL\n",
    "\n",
    "dotenv.load_dotenv()\n",
    "pgpassword = os.getenv(\"pgpass\")\n",
    "\n",
    "dbserver = psycopg2.connect(\n",
    "    user='postgres', \n",
    "    password=pgpassword, \n",
    "    host=\"localhost\"\n",
    ")\n",
    "dbserver.autocommit = True\n",
    "cursor = dbserver.cursor()\n",
    "\n",
    "try:\n",
    "    cursor.execute(\"CREATE DATABASE shake\")\n",
    "except:\n",
    "    cursor.execute(\"DROP DATABASE shake\")\n",
    "    cursor.execute(\"CREATE DATABASE shake\")\n",
    "    \n",
    "engine = create_engine(\"postgresql+psycopg2://{user}:{pw}@localhost/{db}\"\n",
    "                       .format(user='postgres', \n",
    "                               pw=pgpassword, \n",
    "                               db=\"teams\"))\n",
    "\n",
    "works.to_sql('works', con = engine, index=False, chunksize=1000, if_exists = 'replace')\n",
    "characters.to_sql('characters', con = engine, index=False, chunksize=1000, if_exists = 'replace')\n",
    "chapters.to_sql('chapters', con = engine, index=False, chunksize=1000, if_exists = 'replace')\n",
    "paragraphs.to_sql('paragraphs', con = engine, index=False, chunksize=1000, if_exists = 'replace')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fc4f8cb-8043-4b45-a963-4522dfd47955",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3f59b9e4-a60c-484c-8ee6-c821026ca7f1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "      <th>totalwords</th>\n",
       "      <th>era</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>King Lear</td>\n",
       "      <td>1605</td>\n",
       "      <td>26119</td>\n",
       "      <td>middle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Troilus and Cressida</td>\n",
       "      <td>1601</td>\n",
       "      <td>26089</td>\n",
       "      <td>middle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Henry IV, Part II</td>\n",
       "      <td>1597</td>\n",
       "      <td>25692</td>\n",
       "      <td>early</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Henry VI, Part II</td>\n",
       "      <td>1590</td>\n",
       "      <td>25411</td>\n",
       "      <td>early</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Winter's Tale</td>\n",
       "      <td>1610</td>\n",
       "      <td>24914</td>\n",
       "      <td>late</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  title  year  totalwords     era\n",
       "0             King Lear  1605       26119  middle\n",
       "1  Troilus and Cressida  1601       26089  middle\n",
       "2     Henry IV, Part II  1597       25692   early\n",
       "3     Henry VI, Part II  1590       25411   early\n",
       "4     The Winter's Tale  1610       24914    late"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Takes title, date, and totalwords from works table, sorts by totalwords in descending order, \n",
    "creates new column (era) which classifies time periods from 1607, \n",
    "then finally displays rows 7 through 11:\n",
    "'''\n",
    "\n",
    "myquery = \"\"\"\n",
    "SELECT title, date as year, totalwords, \n",
    "CASE WHEN date < 1600 THEN 'early' \n",
    "WHEN date BETWEEN 1600 AND 1607 THEN 'middle' \n",
    "WHEN date > 1607 THEN 'late' ELSE NULL END AS era FROM works ORDER BY totalwords DESC LIMIT 5 OFFSET 6;\n",
    "\"\"\"\n",
    "pd.read_sql_query(myquery, con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "aa262a84-092a-4395-a7be-59647cf0dd7c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>genretype</th>\n",
       "      <th>avg_words</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>h</td>\n",
       "      <td>24236.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>t</td>\n",
       "      <td>23817.363636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c</td>\n",
       "      <td>20212.071429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>s</td>\n",
       "      <td>17515.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>p</td>\n",
       "      <td>6181.800000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  genretype     avg_words\n",
       "0         h  24236.000000\n",
       "1         t  23817.363636\n",
       "2         c  20212.071429\n",
       "3         s  17515.000000\n",
       "4         p   6181.800000"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Reports average number of words in works by genre type, then displays genre type and \n",
    "average wordcount within genre, then sorts by average in descending order\n",
    "'''\n",
    "\n",
    "myquery = \"\"\"\n",
    "SELECT genretype, AVG(totalwords) as avg_words\n",
    "FROM works\n",
    "GROUP BY genretype\n",
    "ORDER BY avg_words DESC;\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql_query(myquery, con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0e36585b-d6a9-45c8-b8a2-9a299f6442ca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>plaintext</th>\n",
       "      <th>charid</th>\n",
       "      <th>wordcount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ay, so, God b' wi' ye!                        ...</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>466.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           plaintext  charid  wordcount\n",
       "0  Ay, so, God b' wi' ye!                        ...  hamlet      466.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Generates table containig text of the longest speech in Hamlet\n",
    "'''\n",
    "\n",
    "myquery = \"\"\"\n",
    "SELECT plaintext, charid, wordcount\n",
    "FROM paragraphs\n",
    "WHERE charid = (SELECT charid \n",
    "                FROM characters \n",
    "                WHERE charname = 'Hamlet')\n",
    "    AND wordcount IS NOT null\n",
    "ORDER BY wordcount DESC\n",
    "LIMIT 1;\n",
    "\"\"\"\n",
    "pd.read_sql_query(myquery, con=engine)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "36683f93-0b12-4e84-8479-d36ed7a172e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>workid</th>\n",
       "      <th>act</th>\n",
       "      <th>scene</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Twelfth Night</td>\n",
       "      <td>12night</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>DUKE ORSINO's palace.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>All's Well That Ends Well</td>\n",
       "      <td>allswell</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>DUKE ORSINO's palace.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Antony and Cleopatra</td>\n",
       "      <td>antonycleo</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>DUKE ORSINO's palace.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>As You Like It</td>\n",
       "      <td>asyoulikeit</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>DUKE ORSINO's palace.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Comedy of Errors</td>\n",
       "      <td>comedyerrors</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>DUKE ORSINO's palace.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7993</th>\n",
       "      <td>Titus Andronicus</td>\n",
       "      <td>titus</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Before LEONTES' palace.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7994</th>\n",
       "      <td>Troilus and Cressida</td>\n",
       "      <td>troilus</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Before LEONTES' palace.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7995</th>\n",
       "      <td>Two Gentlemen of Verona</td>\n",
       "      <td>twogents</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Before LEONTES' palace.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7996</th>\n",
       "      <td>Venus and Adonis</td>\n",
       "      <td>venusadonis</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Before LEONTES' palace.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7997</th>\n",
       "      <td>The Winter's Tale</td>\n",
       "      <td>winterstale</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Before LEONTES' palace.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7998 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          title        workid  act  scene  \\\n",
       "0                 Twelfth Night       12night  1.0    1.0   \n",
       "1     All's Well That Ends Well      allswell  1.0    1.0   \n",
       "2          Antony and Cleopatra    antonycleo  1.0    1.0   \n",
       "3                As You Like It   asyoulikeit  1.0    1.0   \n",
       "4              Comedy of Errors  comedyerrors  1.0    1.0   \n",
       "...                         ...           ...  ...    ...   \n",
       "7993           Titus Andronicus         titus  5.0    2.0   \n",
       "7994       Troilus and Cressida       troilus  5.0    2.0   \n",
       "7995    Two Gentlemen of Verona      twogents  5.0    2.0   \n",
       "7996           Venus and Adonis   venusadonis  5.0    2.0   \n",
       "7997          The Winter's Tale   winterstale  5.0    2.0   \n",
       "\n",
       "                  description  \n",
       "0       DUKE ORSINO's palace.  \n",
       "1       DUKE ORSINO's palace.  \n",
       "2       DUKE ORSINO's palace.  \n",
       "3       DUKE ORSINO's palace.  \n",
       "4       DUKE ORSINO's palace.  \n",
       "...                       ...  \n",
       "7993  Before LEONTES' palace.  \n",
       "7994  Before LEONTES' palace.  \n",
       "7995  Before LEONTES' palace.  \n",
       "7996  Before LEONTES' palace.  \n",
       "7997  Before LEONTES' palace.  \n",
       "\n",
       "[7998 rows x 5 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Lists all chapters that are set in a palace or castle\n",
    "'''\n",
    "\n",
    "myquery = \"\"\"\n",
    "SELECT works.title, works.workid, chapters.section AS act, chapters.chapter AS scene, chapters.description\n",
    "FROM works, chapters\n",
    "WHERE lower(chapters.description) LIKE '%%palace%%'\n",
    "    OR lower(chapters.description) LIKE '%%castle%%'\n",
    "\"\"\"\n",
    "pd.read_sql_query(myquery, con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6c03a19f-5bde-4d35-8e21-c2250fcd89a1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>character</th>\n",
       "      <th>description</th>\n",
       "      <th>work_title</th>\n",
       "      <th>speech_count</th>\n",
       "      <th>avg_speech_length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(stage directions)</td>\n",
       "      <td>None</td>\n",
       "      <td>As You Like It</td>\n",
       "      <td>126.0</td>\n",
       "      <td>4.309517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alice</td>\n",
       "      <td>a lady attending on Princess Katherine</td>\n",
       "      <td>Henry V</td>\n",
       "      <td>22.0</td>\n",
       "      <td>7.454545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lucius</td>\n",
       "      <td>servant to Brutus</td>\n",
       "      <td>Julius Caesar</td>\n",
       "      <td>24.0</td>\n",
       "      <td>8.541667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Curtis</td>\n",
       "      <td>None</td>\n",
       "      <td>Taming of the Shrew</td>\n",
       "      <td>20.0</td>\n",
       "      <td>8.550000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>First Murderer</td>\n",
       "      <td>None</td>\n",
       "      <td>Macbeth</td>\n",
       "      <td>21.0</td>\n",
       "      <td>8.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>337</th>\n",
       "      <td>Oberon</td>\n",
       "      <td>king of the fairies</td>\n",
       "      <td>Midsummer Night's Dream</td>\n",
       "      <td>29.0</td>\n",
       "      <td>55.655172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>338</th>\n",
       "      <td>Duke of Buckingham</td>\n",
       "      <td>None</td>\n",
       "      <td>Henry VIII</td>\n",
       "      <td>26.0</td>\n",
       "      <td>57.307692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>339</th>\n",
       "      <td>Constance</td>\n",
       "      <td>mother to Arthur</td>\n",
       "      <td>King John</td>\n",
       "      <td>36.0</td>\n",
       "      <td>59.222222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340</th>\n",
       "      <td>Queen Katharine</td>\n",
       "      <td>wife to King Henry, afterwards divorced</td>\n",
       "      <td>Henry VIII</td>\n",
       "      <td>50.0</td>\n",
       "      <td>59.360000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>341</th>\n",
       "      <td>King Richard II</td>\n",
       "      <td>king of England</td>\n",
       "      <td>Richard II</td>\n",
       "      <td>98.0</td>\n",
       "      <td>61.765306</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>342 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              character                              description  \\\n",
       "0    (stage directions)                                     None   \n",
       "1                 Alice   a lady attending on Princess Katherine   \n",
       "2                Lucius                        servant to Brutus   \n",
       "3                Curtis                                     None   \n",
       "4        First Murderer                                     None   \n",
       "..                  ...                                      ...   \n",
       "337              Oberon                      king of the fairies   \n",
       "338  Duke of Buckingham                                     None   \n",
       "339           Constance                         mother to Arthur   \n",
       "340     Queen Katharine  wife to King Henry, afterwards divorced   \n",
       "341     King Richard II                          king of England   \n",
       "\n",
       "                  work_title  speech_count  avg_speech_length  \n",
       "0             As You Like It         126.0           4.309517  \n",
       "1                    Henry V          22.0           7.454545  \n",
       "2              Julius Caesar          24.0           8.541667  \n",
       "3        Taming of the Shrew          20.0           8.550000  \n",
       "4                    Macbeth          21.0           8.666667  \n",
       "..                       ...           ...                ...  \n",
       "337  Midsummer Night's Dream          29.0          55.655172  \n",
       "338               Henry VIII          26.0          57.307692  \n",
       "339                King John          36.0          59.222222  \n",
       "340               Henry VIII          50.0          59.360000  \n",
       "341               Richard II          98.0          61.765306  \n",
       "\n",
       "[342 rows x 5 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Example of joins to list columns from different tables, sorting by speech length and \n",
    "restricting to those who have given at least 20 speeches\n",
    "'''\n",
    "\n",
    "myquery = \"\"\"\n",
    "SELECT\n",
    "    c.charname as character,\n",
    "    c.description as description,\n",
    "    w.title as work_title,\n",
    "    c.speechcount as speech_count,\n",
    "    AVG(p.wordcount) as avg_speech_length\n",
    "FROM characters c\n",
    "LEFT JOIN paragraphs p ON c.charid = p.charid\n",
    "INNER JOIN works w ON c.works = w.workid\n",
    "WHERE c.speechcount >= 20\n",
    "GROUP BY c.charname, c.description, work_title, c.speechcount\n",
    "ORDER BY avg_speech_length\n",
    "\"\"\"\n",
    "pd.read_sql_query(myquery, con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3603e7d5-8708-48f5-a369-8c04ec9de6ca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>title</th>\n",
       "      <th>genretype</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Henry V</td>\n",
       "      <td>h</td>\n",
       "      <td>1598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Merry Wives of Windsor</td>\n",
       "      <td>c</td>\n",
       "      <td>1600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Coriolanus</td>\n",
       "      <td>t</td>\n",
       "      <td>1607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Much Ado about Nothing</td>\n",
       "      <td>c</td>\n",
       "      <td>1598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>The Winter's Tale</td>\n",
       "      <td>c</td>\n",
       "      <td>1610</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    title genretype  date\n",
       "0                 Henry V         h  1598\n",
       "1  Merry Wives of Windsor         c  1600\n",
       "2              Coriolanus         t  1607\n",
       "3  Much Ado about Nothing         c  1598\n",
       "4       The Winter's Tale         c  1610"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Works that have no scenes in a castle or palace\n",
    "'''\n",
    "\n",
    "myquery = \"\"\"\n",
    "SELECT\n",
    "    DISTINCT w.title,\n",
    "    w.genretype,\n",
    "    w.date\n",
    "FROM works w\n",
    "INNER JOIN chapters c ON c.workid = w.workid\n",
    "WHERE lower(c.description) NOT LIKE '%%palace%%'\n",
    "AND lower(c.description) NOT LIKE '%%castle%%'\n",
    "\"\"\"\n",
    "pd.read_sql_query(myquery, con=engine).head() # cutting off at 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3a2a8c9-2c12-4442-a711-964e773d600f",
   "metadata": {},
   "source": [
    "## MongoDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7d673a22-9b41-4c0b-a5d2-e41f0d5a09fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>text</th>\n",
       "      <th>country</th>\n",
       "      <th>adopted</th>\n",
       "      <th>revised</th>\n",
       "      <th>reinstated</th>\n",
       "      <th>democracy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>'Afghanistan 2004     Preamble    \\n﻿In the na...</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2004</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.372201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>'Albania 1998 (rev. 2012)     Preamble    \\nWe...</td>\n",
       "      <td>Albania</td>\n",
       "      <td>1998</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.535111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>'Andorra 1993     Preamble    \\nThe Andorran P...</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>1993</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>'Angola 2010     Preamble    \\nWe, the people ...</td>\n",
       "      <td>Angola</td>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.315043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>'Antigua and Barbuda 1981     Preamble    \\nWH...</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>1981</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                text              country  \\\n",
       "0  'Afghanistan 2004     Preamble    \\n﻿In the na...          Afghanistan   \n",
       "1  'Albania 1998 (rev. 2012)     Preamble    \\nWe...              Albania   \n",
       "2  'Andorra 1993     Preamble    \\nThe Andorran P...              Andorra   \n",
       "3  'Angola 2010     Preamble    \\nWe, the people ...               Angola   \n",
       "4  'Antigua and Barbuda 1981     Preamble    \\nWH...  Antigua and Barbuda   \n",
       "\n",
       "   adopted  revised  reinstated  democracy  \n",
       "0     2004      NaN         NaN   0.372201  \n",
       "1     1998   2012.0         NaN   0.535111  \n",
       "2     1993      NaN         NaN        NaN  \n",
       "3     2010      NaN         NaN   0.315043  \n",
       "4     1981      NaN         NaN        NaN  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "const = requests.get(\"https://github.com/jkropko/DS-6001/raw/master/localdata/const.json\")\n",
    "const_json = json.loads(const.text)\n",
    "pd.DataFrame.from_records(const_json).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "06a5452e-53bd-452f-b893-5733a5cae79b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.DeleteResult at 0x21954e7b640>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "myclient = pymongo.MongoClient(\"mongodb://localhost/\")\n",
    "constdb = myclient[\"const\"]\n",
    "\n",
    "collist = constdb.list_collection_names()\n",
    "if \"constcollection\" in collist:\n",
    "    constdb.constcollection.drop()\n",
    "constcollection = constdb['constcollection']\n",
    "\n",
    "constcollection.delete_many({}) # remove existing data from collection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "495d0eb7-c63f-4ba4-afdc-26d95564c301",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "145"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allconst = constcollection.insert_many(const_json)\n",
    "constcollection.count_documents({})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a1b14025-c040-407d-b137-c6d0ba8c91cb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>adopted</th>\n",
       "      <th>democracy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2004</td>\n",
       "      <td>0.372201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Angola</td>\n",
       "      <td>2010</td>\n",
       "      <td>0.315043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>1995</td>\n",
       "      <td>0.393278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Belarus</td>\n",
       "      <td>1994</td>\n",
       "      <td>0.289968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bosnia and Herzegovina</td>\n",
       "      <td>1995</td>\n",
       "      <td>0.338267</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  country  adopted  democracy\n",
       "0             Afghanistan     2004   0.372201\n",
       "1                  Angola     2010   0.315043\n",
       "2                 Armenia     1995   0.393278\n",
       "3                 Belarus     1994   0.289968\n",
       "4  Bosnia and Herzegovina     1995   0.338267"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Adopted after 1990 or less than 50% democracy score\n",
    "'''\n",
    "\n",
    "myquery = constcollection.find({'$or' : \n",
    "                                [{'adopted' : {'$gt':1990},\n",
    "                                'democracy' : {'$lt':0.5}}]\n",
    "                               },\n",
    "                               {'country':1,\n",
    "                                'adopted':1,\n",
    "                                'democracy':1,\n",
    "                                '_id':0})\n",
    "const_text = dumps(myquery)\n",
    "const_records = loads(const_text)\n",
    "const_df = pd.DataFrame.from_records(const_records)\n",
    "const_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7b6bcda4-0fdb-49af-9db3-51f4424412fe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>text</th>\n",
       "      <th>country</th>\n",
       "      <th>adopted</th>\n",
       "      <th>revised</th>\n",
       "      <th>reinstated</th>\n",
       "      <th>democracy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>61e4aece6860754c7caac1fd</td>\n",
       "      <td>'Hungary 2011 (rev. 2013)     Preamble    \\nGo...</td>\n",
       "      <td>Hungary</td>\n",
       "      <td>2011</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>None</td>\n",
       "      <td>0.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id  \\\n",
       "0  61e4aece6860754c7caac1fd   \n",
       "\n",
       "                                                text  country  adopted  \\\n",
       "0  'Hungary 2011 (rev. 2013)     Preamble    \\nGo...  Hungary     2011   \n",
       "\n",
       "   revised reinstated  democracy  \n",
       "0   2013.0       None        0.4  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Updating record's democracy score\n",
    "'''\n",
    "\n",
    "constcollection.update_one({'country' : 'Hungary'},\n",
    "                           {'$set' : {'democracy': 0.4}})\n",
    "myquery = constcollection.find({'country':'Hungary'})\n",
    "const_text = dumps(myquery)\n",
    "const_records = loads(const_text)\n",
    "const_df = pd.DataFrame.from_records(const_records)\n",
    "const_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9b69754f-a1f4-4ee4-b5bb-18f2d4326b90",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>adopted</th>\n",
       "      <th>democracy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Slovenia</td>\n",
       "      <td>1991</td>\n",
       "      <td>0.861380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Poland</td>\n",
       "      <td>1997</td>\n",
       "      <td>0.682208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Eritrea</td>\n",
       "      <td>1997</td>\n",
       "      <td>0.075621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Croatia</td>\n",
       "      <td>1991</td>\n",
       "      <td>0.710922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Macedonia (The former Yugoslav Republic of)</td>\n",
       "      <td>1991</td>\n",
       "      <td>0.510983</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       country  adopted  democracy\n",
       "0                                     Slovenia     1991   0.861380\n",
       "1                                       Poland     1997   0.682208\n",
       "2                                      Eritrea     1997   0.075621\n",
       "3                                      Croatia     1991   0.710922\n",
       "4  Macedonia (The former Yugoslav Republic of)     1991   0.510983"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Searching for texts that contain specific phrase\n",
    "'''\n",
    "\n",
    "def read_mongo_query(col, query):\n",
    "    query_text = dumps(col.find(query))\n",
    "    query_rec = loads(query_text)\n",
    "    query_df = pd.DataFrame.from_records(query_rec)\n",
    "    return query_df\n",
    "\n",
    "constcollection.create_index([('text', 'text')])\n",
    "df = read_mongo_query(constcollection, \n",
    "                      {'$text': {'$search':'\\\"freedom of speech\\\"',\n",
    "                                 '$caseSensitive': False}}) \\\n",
    "                     [['country', 'adopted', 'democracy']]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "60eef6c3-e8a7-4220-ad1b-4c2888e69eac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>text</th>\n",
       "      <th>country</th>\n",
       "      <th>adopted</th>\n",
       "      <th>revised</th>\n",
       "      <th>reinstated</th>\n",
       "      <th>democracy</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>61e4aece6860754c7caac23c</td>\n",
       "      <td>'Serbia 2006     Preamble    \\nConsidering the...</td>\n",
       "      <td>Serbia</td>\n",
       "      <td>2006</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.474443</td>\n",
       "      <td>5.030999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>61e4aece6860754c7caac1f4</td>\n",
       "      <td>'Finland 1999 (rev. 2011)     Chapter 1. Funda...</td>\n",
       "      <td>Finland</td>\n",
       "      <td>1999</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.856265</td>\n",
       "      <td>5.029000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>61e4aece6860754c7caac1f1</td>\n",
       "      <td>'Estonia 1992 (rev. 2011)     Preamble    \\nWi...</td>\n",
       "      <td>Estonia</td>\n",
       "      <td>1992</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.909233</td>\n",
       "      <td>5.024473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>61e4aece6860754c7caac1d0</td>\n",
       "      <td>'Armenia 1995 (rev. 2005)     Preamble    \\nTh...</td>\n",
       "      <td>Armenia</td>\n",
       "      <td>1995</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.393278</td>\n",
       "      <td>5.023651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>61e4aece6860754c7caac1cc</td>\n",
       "      <td>'Albania 1998 (rev. 2012)     Preamble    \\nWe...</td>\n",
       "      <td>Albania</td>\n",
       "      <td>1998</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.535111</td>\n",
       "      <td>5.023087</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id  \\\n",
       "0  61e4aece6860754c7caac23c   \n",
       "1  61e4aece6860754c7caac1f4   \n",
       "2  61e4aece6860754c7caac1f1   \n",
       "3  61e4aece6860754c7caac1d0   \n",
       "4  61e4aece6860754c7caac1cc   \n",
       "\n",
       "                                                text  country  adopted  \\\n",
       "0  'Serbia 2006     Preamble    \\nConsidering the...   Serbia     2006   \n",
       "1  'Finland 1999 (rev. 2011)     Chapter 1. Funda...  Finland     1999   \n",
       "2  'Estonia 1992 (rev. 2011)     Preamble    \\nWi...  Estonia     1992   \n",
       "3  'Armenia 1995 (rev. 2005)     Preamble    \\nTh...  Armenia     1995   \n",
       "4  'Albania 1998 (rev. 2012)     Preamble    \\nWe...  Albania     1998   \n",
       "\n",
       "   revised  reinstated  democracy     score  \n",
       "0      NaN         NaN   0.474443  5.030999  \n",
       "1   2011.0         NaN   0.856265  5.029000  \n",
       "2   2011.0         NaN   0.909233  5.024473  \n",
       "3   2005.0         NaN   0.393278  5.023651  \n",
       "4   2012.0         NaN   0.535111  5.023087  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Searching for any of multiple terms\n",
    "'''\n",
    "\n",
    "cursor = constcollection.find(\n",
    "            {'$text': {'$search': 'freedom liberty legal justice rights',\n",
    "                                   '$caseSensitive': False}},\n",
    "            {'score': {'$meta': 'textScore'}})\n",
    "cursor.sort([('score', {'$meta': 'textScore'})])\n",
    "\n",
    "qtext = dumps(cursor)\n",
    "qrec = loads(qtext)\n",
    "df = pd.DataFrame.from_records(qrec)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "1a5bbef0-f98d-410b-b436-ebad96dc605f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# end connections\n",
    "dbserver.close()\n",
    "myclient.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9371632e-aed3-4739-84e6-c10085ee2747",
   "metadata": {},
   "source": [
    "# SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "baaea361-da14-49cf-8606-be3f1a06ce05",
   "metadata": {},
   "outputs": [],
   "source": [
    "# read in files\n",
    "\n",
    "url = \"https://github.com/nolanbconaway/pitchfork-data/raw/master/pitchfork.db\"\n",
    "pfork = wget.download(url)\n",
    "pitchfork = sqlite3.connect(pfork)\n",
    "for t in ['artists','content','genres','labels','reviews','years']:\n",
    "    datatable = pd.read_sql_query(\"SELECT * FROM {tab}\".format(tab=t), pitchfork)\n",
    "    datatable.to_csv(\"{tab}.csv\".format(tab=t))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "a4cb20aa-e4a1-45a6-ad47-158938ad95ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "reviews = pd.read_csv(\"reviews.csv\")\n",
    "artists = pd.read_csv(\"artists.csv\")\n",
    "content = pd.read_csv(\"content.csv\")\n",
    "genres = pd.read_csv(\"genres.csv\")\n",
    "labels = pd.read_csv(\"labels.csv\")\n",
    "years = pd.read_csv(\"years.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "b7a3b232-f42c-4982-8b7d-779b476858b1",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\dwagn\\AppData\\Local\\Programs\\Python\\Python39\\lib\\site-packages\\pandas\\core\\generic.py:2779: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n",
      "  sql.to_sql(\n"
     ]
    }
   ],
   "source": [
    "music_db = sqlite3.connect(\"music.db\") \n",
    "\n",
    "reviews.to_sql('reviews', music_db, index=False, chunksize=1000, if_exists='replace')\n",
    "artists.to_sql('artists', music_db, index=False, chunksize=1000, if_exists='replace')\n",
    "content.to_sql('content', music_db, index=False, chunksize=1000, if_exists='replace')\n",
    "genres.to_sql('genres', music_db, index=False, chunksize=1000, if_exists='replace')\n",
    "labels.to_sql('labels', music_db, index=False, chunksize=1000, if_exists='replace')\n",
    "years.to_sql('years', music_db, index=False, chunksize=1000, if_exists='replace')\n",
    "\n",
    "music_cursor = music_db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "4bbff552-269d-44dc-8b5d-b519393b783e",
   "metadata": {},
   "outputs": [],
   "source": [
    "music_cursor.execute('SELECT * FROM reviews')\n",
    "\n",
    "col_names = [x[0] for x in music_cursor.description]\n",
    "music_df = pd.DataFrame(music_cursor.fetchall(), \n",
    "                        columns = col_names) \\\n",
    "                        .drop('Unnamed: 0', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "a551af91-ebbd-4adb-ada8-06617e82f839",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('artists',),\n",
       " ('content',),\n",
       " ('genres',),\n",
       " ('labels',),\n",
       " ('reviews',),\n",
       " ('years',)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# example of using sqlite meta table to find entity names in the db\n",
    "music_cursor.execute(\"SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;\")                    \n",
    "music_cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "a0505b86-6bd1-4972-b3d2-293d8fa42549",
   "metadata": {},
   "outputs": [],
   "source": [
    "music_db.commit()\n",
    "music_db.close()"
   ]
  }
 ],
 "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.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}