db.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from lxml import etree\n",
    "from pathlib import Path\n",
    "from pymongo import MongoClient"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "db=MongoClient()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "stations=db.remaining"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "stations.create_collection(\"logs\")\n",
    "logs_doc=stations.logs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Collection(Database(MongoClient(host=['localhost:27017'], document_class=dict, tz_aware=False, connect=True), 'remaining'), 'empties')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stations.create_collection(\"empties\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'name': 'empties',\n",
       "  'type': 'collection',\n",
       "  'options': {},\n",
       "  'info': {'readOnly': False,\n",
       "   'uuid': UUID('2fe57771-3815-4043-b8f8-80f3f7ebba95')},\n",
       "  'idIndex': {'v': 2, 'key': {'_id': 1}, 'name': '_id_'}},\n",
       " {'name': 'logs',\n",
       "  'type': 'collection',\n",
       "  'options': {},\n",
       "  'info': {'readOnly': False,\n",
       "   'uuid': UUID('1b8a8885-aad2-4533-9b57-499c8b8a7095')},\n",
       "  'idIndex': {'v': 2, 'key': {'_id': 1}, 'name': '_id_'}}]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted(list(stations.list_collections()),key=lambda station:station['name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_safe_first(values):\n",
    "    if values:\n",
    "        return values[0]\n",
    "    else:\n",
    "        return \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = Path(\"./remaining\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "files=[file for file in path.iterdir() if not file.is_dir()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'scrapper' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-10-60f1fe18b2e4>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mscrapper\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mlist_collection_names\u001b[0m \u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m: name 'scrapper' is not defined"
     ]
    }
   ],
   "source": [
    "scrapper.list_collection_names ()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "collection 2020_ALPHA created\n"
     ]
    }
   ],
   "source": [
    "for file in files:\n",
    "    if file.stem not in stations.list_collection_names():\n",
    "        stations.create_collection(file.stem)\n",
    "        print(f\"collection {file.stem} created\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "parser = etree.XMLParser(recover=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_show(show,station,extras={}):\n",
    "    params={\n",
    "            'show_id':show.xpath(\"./sendung//stammdaten//metadaten/id/text()\")[0],\n",
    "            'title':show.xpath(\"./sendung//stammdaten//programm/titel/text()\")[0],\n",
    "    }\n",
    "    values={**params,**extras}\n",
    "    values['subtitle']=get_safe_first(show.xpath(\"./sendung//stammdaten//programm/nebentitel/text()\")) \n",
    "    values['originaltitel']=get_safe_first(show.xpath(\"./sendung//stammdaten//programm/originaltitel/text()\")) \n",
    "    values['sender_genre']=get_safe_first(show.xpath(\"./sendung//stammdaten//programm/sender_genre/text()\"))\n",
    "    values['dmb_haupt_genre']=get_safe_first(show.xpath(\"./sendung//stammdaten//programm/dmb_genre/dmb_haupt_genre/text()\"))\n",
    "    values['dmb_neben_genre']=get_safe_first(show.xpath(\"./sendung//stammdaten//programm/dmb_genre/dmb_neben_genre/text()\"))\n",
    "    values['produktion_land']=get_safe_first(show.xpath(\"./sendung//stammdaten//programm/produktion/land/text()\"))\n",
    "    values['produktion_jahr']=get_safe_first(show.xpath(\"./sendung//stammdaten//programm/produktion/jahr/text()\"))\n",
    "    values['hinweissender']=get_safe_first(show.xpath(\"./sendung//stammdaten//programm/hinweissender/text()\"))\n",
    "    values['titel_ergaenzung']=get_safe_first(show.xpath(\"./sendung//stammdaten//programm/titel_ergaenzung/text()\"))\n",
    "    values['hinweissender']=get_safe_first(show.xpath(\"./sendung//stammdaten//programm/hinweissender/text()\"))\n",
    "    values['ausstrahlung_start']=get_safe_first(show.xpath(\"./sendung//stammdaten//ausstrahlung/start/text()\"))\n",
    "    values['ausstrahlung_start']=get_safe_first(show.xpath(\"./sendung//stammdaten//ausstrahlung/wh-text/text()\"))\n",
    "    values['ausstrahlung_sender_start']=get_safe_first(show.xpath(\"./sendung//stammdaten//ausstrahlung/sender_start/text()\"))\n",
    "    values['ausstrahlung_stop']=get_safe_first(show.xpath(\"./sendung//stammdaten//ausstrahlung/stop/text()\"))\n",
    "    values['ausstrahlung_dauer']=get_safe_first(show.xpath(\"./sendung//stammdaten//ausstrahlung/dauer/text()\"))\n",
    "    values['nettolaenge']=get_safe_first(show.xpath(\"./sendung//stammdaten//ausstrahlung/nettolaenge/text()\"))\n",
    "    values['ausstrahlung_sender']=get_safe_first(show.xpath(\"./sendung//stammdaten//ausstrahlung/sender/text()\"))\n",
    "    values['produzierender_sender']=get_safe_first(show.xpath(\"./sendung//stammdaten//ausstrahlung/produzierender_sender/text()\"))\n",
    "    values['dmbInfoset_ProduktionsLaender_ProduktionsLand']=get_safe_first(show.xpath(\"./sendung//stammdaten//dmbInfoset/ProduktionsLaender/ProduktionsLand/text()\"))\n",
    "    values['dmbFolgetitel']=get_safe_first(show.xpath(\"./sendung//stammdaten//dmbInfoset/dmbFolgetitel/text()\"))\n",
    "    values['dmbStaffel']=get_safe_first(show.xpath(\"./sendung//stammdaten//dmbInfoset/dmbStaffel/text()\"))\n",
    "    values['dmbFolge']=get_safe_first(show.xpath(\"./sendung//stammdaten//dmbInfoset/dmbFolge/text()\"))\n",
    "    values['dmbProduktionsJahr']=get_safe_first(show.xpath(\"./sendung//stammdaten//dmbInfoset/dmbProduktionsJahr/text()\"))\n",
    "    values['dmbOriginalFolgetitel']=get_safe_first(show.xpath(\"./sendung//stammdaten//dmbInfoset/dmbOriginalFolgetitel/text()\"))\n",
    "    values['staff']=show.xpath(\"./sendung//stammdaten//dmbInfoset/StaffList/Staff/Person/Fullname/text()\")\n",
    "    produktionsLand_nodes=show.xpath(\"./sendung//stammdaten//dmbInfoset/ProduktionsLaender/ProduktionsLand\")\n",
    "    produktionsLand=[{\"land\":node.text,'kfz':node.attrib.get(\"dmbstd:kfz\"),'alpha3':node.attrib.get(\"dmbstd:alpha3\") } for node in produktionsLand_nodes]\n",
    "    values['produktionsLand']=produktionsLand\n",
    "    staff_nodes=show.xpath(\"./sendung//stammdaten//dmbInfoset/StaffList/Staff\")\n",
    "    staff=[{\"staff_id\":node.xpath(\"./Person\")[0].attrib.get(\"dmbstd:id\"),\"staff_function\":node.attrib.get(\"dmbstd:Function\"),\"fullname\":node.xpath(\"./Person/Fullname\")[0].text} for node in staff_nodes]\n",
    "    values['staff']=staff\n",
    "    values['crew_united_url']=\"\"\n",
    "    values['crew_united_productions']=[]\n",
    "    values['crew_united_koproduzent_productions']=[]\n",
    "    values['cu_first_showing_country']=\"\"\n",
    "    values['cu_first_showing_station']=\"\"\n",
    "    values['cu_first_showing_date_time']=\"\"\n",
    "    values['imdb_id']=\"\"\n",
    "    values['imdb_link']=\"\"\n",
    "    values['imdb_distributors']=[]\n",
    "    values['imdb_productions']=[]\n",
    "    values['imdb_flag']=\"\"\n",
    "    values['cu_flag']=\"\"\n",
    "    \n",
    "    \n",
    "    \n",
    "    station.replace_one(params,values,upsert=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "station=stations[\"2019_3SAT\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = Path(\"./Archivdaten_2019/2019_3SAT.xml\")\n",
    "tree = etree.parse(str(path),parser=parser)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "ge_shows=tree.xpath(\".//daten/einfuegen/sendung//stammdaten//produktion/land[contains(text(),'Deutschland')]/ancestor-or-self::einfuegen\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_ge_shows=tree.xpath(\".//daten/einfuegen/sendung//stammdaten//dmbInfoset//dmbProduktionsLand[contains(text(),'Deutschland')]/ancestor-or-self::einfuegen\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "all2_ge_shows=tree.xpath(\".//daten/einfuegen/sendung//stammdaten//dmbInfoset//ProduktionsLaender/ProduktionsLand[contains(text(),'Deutschland')]/ancestor-or-self::einfuegen\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_shows=[\n",
    "    *ge_shows,\n",
    "    *all_ge_shows,\n",
    "    *all2_ge_shows\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10590"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(final_shows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_shows=[]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "seen=[]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "for show in final_shows:\n",
    "    show_id=show.xpath(\".//metadaten/id\")[0].text\n",
    "    if not show_id in seen:\n",
    "        seen.append(show_id)\n",
    "        unique_shows.append(show)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6053"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(unique_shows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index,show in enumerate(unique_shows):\n",
    "    save_show(show,station)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "unexpected EOF while parsing (<ipython-input-16-7cf999461286>, line 17)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-16-7cf999461286>\"\u001b[1;36m, line \u001b[1;32m17\u001b[0m\n\u001b[1;33m    #     collection=scrapper[file.stem]\u001b[0m\n\u001b[1;37m                                        ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m unexpected EOF while parsing\n"
     ]
    }
   ],
   "source": [
    "for file in files:\n",
    "    station=stations[file.stem]\n",
    "    tree = etree.parse(str(file),parser=parser)\n",
    "    shows=tree.xpath(\".//daten/einfuegen/sendung//stammdaten\")\n",
    "    ge_shows=tree.xpath(\".//daten/einfuegen/sendung//stammdaten//produktion/land[contains(text(),'Deutschland')]/ancestor-or-self::einfuegen\")\n",
    "    print(f\"total shows in {file.name} are {len(shows)} and german shows are {len(ge_shows)}\")\n",
    "    logs_doc.insert_one({\n",
    "        \"station\":file.stem,\n",
    "        \"total_shows\":len(shows),\n",
    "        \"german_shows\":len(ge_shows),\n",
    "        \"imdb_found\":0,\n",
    "        \"crew_united_found\":0\n",
    "    })\n",
    "    for index,show in enumerate(ge_shows):\n",
    "        \n",
    "#     break\n",
    "#     collection=scrapper[file.stem]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for c in stations.list_collections():\n",
    "#     print(c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "l=[]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "station=stations[\"empties\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "empty_shows=[]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "shows=tree.xpath(\".//daten/einfuegen\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "for show in shows:\n",
    "    production_land=show.xpath(\".//stammdaten//produktion/land/text()\")\n",
    "    production_land_1=show.xpath(\".//stammdaten//dmbInfoset//dmbProduktionsLand/text()\")\n",
    "    production_land_2=show.xpath(\".//stammdaten//dmbInfoset//ProduktionsLaender/ProduktionsLand/text()\")\n",
    "    if all([not production_land,not production_land_1,not production_land_2]) :\n",
    "        empty_shows.append(show)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "for show in empty_shows:\n",
    "    save_show(show,station,{\"station\":\"2019_3SAT\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "empties=stations[\"empties\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = Path(\"./shows\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "files=[file for file in path.iterdir() if not file.is_dir()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[WindowsPath('remaining/2020_ALPHA.xml')]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020_ALPHA total shows 16189 found 8769 empty 6498\n"
     ]
    }
   ],
   "source": [
    "for file in files:\n",
    "    if file.stem not in stations.list_collection_names():\n",
    "        stations.create_collection(file.stem)\n",
    "    station=stations[file.stem]\n",
    "    tree = etree.parse(str(file),parser=parser)\n",
    "    ge_shows=tree.xpath(\".//daten/einfuegen/sendung//stammdaten//produktion/land[contains(text(),'Deutschland')]/ancestor-or-self::einfuegen\")\n",
    "    all_ge_shows=tree.xpath(\".//daten/einfuegen/sendung//stammdaten//dmbInfoset//dmbProduktionsLand[contains(text(),'Deutschland')]/ancestor-or-self::einfuegen\")\n",
    "    all2_ge_shows=tree.xpath(\".//daten/einfuegen/sendung//stammdaten//dmbInfoset//ProduktionsLaender/ProduktionsLand[contains(text(),'Deutschland')]/ancestor-or-self::einfuegen\")\n",
    "    final_shows=[\n",
    "        *ge_shows,\n",
    "        *all_ge_shows,\n",
    "        *all2_ge_shows\n",
    "    ]\n",
    "    unique_shows=[]\n",
    "    seen=[]\n",
    "    for show in final_shows:\n",
    "        show_id=show.xpath(\".//metadaten/id\")[0].text\n",
    "        if not show_id in seen:\n",
    "            seen.append(show_id)\n",
    "            unique_shows.append(show)\n",
    "    for index,show in enumerate(unique_shows):\n",
    "        save_show(show,station)\n",
    "    empty_shows=[]\n",
    "    shows=tree.xpath(\".//daten/einfuegen\")\n",
    "    for show in shows:\n",
    "        production_land=show.xpath(\".//stammdaten//produktion/land/text()\")\n",
    "        production_land_1=show.xpath(\".//stammdaten//dmbInfoset//dmbProduktionsLand/text()\")\n",
    "        production_land_2=show.xpath(\".//stammdaten//dmbInfoset//ProduktionsLaender/ProduktionsLand/text()\")\n",
    "        if all([not production_land,not production_land_1,not production_land_2]) :\n",
    "            empty_shows.append(show)\n",
    "    for show in empty_shows:\n",
    "        save_show(show,empties,{\"station\":file.stem})\n",
    "    print(f\"{file.stem} total shows {len(shows)} found {len(unique_shows)} empty {len(empty_shows)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "movie-scrapper",
   "language": "python",
   "name": "movie-scrapper"
  },
  "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}