<commit_before>{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table\n",
    "from sqlalchemy.orm import mapper, sessionmaker\n",
    "import requests\n",
    "import json\n",
    "from elasticsearch import Elasticsearch\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017-11-05 00:51:37,819 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1\n",
      "2017-11-05 00:51:37,821 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 00:51:37,824 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1\n",
      "2017-11-05 00:51:37,825 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 00:51:37,827 INFO sqlalchemy.engine.base.Engine PRAGMA table_info(\"stories\")\n",
      "2017-11-05 00:51:37,829 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 00:51:37,831 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'stories' AND type = 'table'\n",
      "2017-11-05 00:51:37,833 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 00:51:37,835 INFO sqlalchemy.engine.base.Engine PRAGMA foreign_key_list(\"stories\")\n",
      "2017-11-05 00:51:37,836 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 00:51:37,838 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'stories' AND type = 'table'\n",
      "2017-11-05 00:51:37,839 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 00:51:37,843 INFO sqlalchemy.engine.base.Engine PRAGMA index_list(\"stories\")\n",
      "2017-11-05 00:51:37,849 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 00:51:37,850 INFO sqlalchemy.engine.base.Engine PRAGMA index_list(\"stories\")\n",
      "2017-11-05 00:51:37,851 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 00:51:37,853 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'stories' AND type = 'table'\n",
      "2017-11-05 00:51:37,854 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "class Story(object):\n",
    "    pass\n",
    " \n",
    "def loadSession():\n",
    "    dbPath = 'stories.sqlite'\n",
    "    engine = create_engine('sqlite:///%s' % dbPath, echo=True)\n",
    " \n",
    "    bookmarks = Table('stories', MetaData(engine), autoload=True)\n",
    "    mapper(Story, bookmarks)\n",
    " \n",
    "    Session = sessionmaker(bind=engine)\n",
    "    session = Session()\n",
    "    return session\n",
    "\n",
    "session = loadSession()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017-11-05 00:51:39,156 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2017-11-05 00:51:39,174 INFO sqlalchemy.engine.base.Engine SELECT stories.id AS stories_id, stories.title AS stories_title, stories.published AS stories_published, stories.tags AS stories_tags, stories.text AS stories_text, stories.likes AS stories_likes, stories.hrefs AS stories_hrefs, stories.url AS stories_url \n",
      "FROM stories\n",
      "2017-11-05 00:51:39,178 INFO sqlalchemy.engine.base.Engine ()\n",
      "23558\n",
      "['__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', '_sa_class_manager', '_sa_instance_state', 'hrefs', 'id', 'likes', 'published', 'tags', 'text', 'title', 'url']\n"
     ]
    }
   ],
   "source": [
    "stories = session.query(Story).all()\n",
    "print(len(stories))\n",
    "print(dir(stories[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b'{\\n  \"name\" : \"-L9u61j\",\\n  \"cluster_name\" : \"elasticsearch\",\\n  \"cluster_uuid\" : \"7yPgKxlWRwmSpPqu9E9w2A\",\\n  \"version\" : {\\n    \"number\" : \"5.6.3\",\\n    \"build_hash\" : \"1a2f265\",\\n    \"build_date\" : \"2017-10-06T20:33:39.012Z\",\\n    \"build_snapshot\" : false,\\n    \"lucene_version\" : \"6.6.1\"\\n  },\\n  \"tagline\" : \"You Know, for Search\"\\n}\\n'\n"
     ]
    }
   ],
   "source": [
    "res = requests.get('http://localhost:9200')\n",
    "print(res.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "es = Elasticsearch([{'host': 'localhost', 'port': 9200}])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b'{\"name\":\"<NAME>\",\"height\":\"172\",\"mass\":\"77\",\"hair_color\":\"blond\",\"skin_color\":\"fair\",\"eye_color\":\"blue\",\"birth_year\":\"19BBY\",\"gender\":\"male\",\"homeworld\":\"https://swapi.co/api/planets/1/\",\"films\":[\"https://swapi.co/api/films/2/\",\"https://swapi.co/api/films/6/\",\"https://swapi.co/api/films/3/\",\"https://swapi.co/api/films/1/\",\"https://swapi.co/api/films/7/\"],\"species\":[\"https://swapi.co/api/species/1/\"],\"vehicles\":[\"https://swapi.co/api/vehicles/14/\",\"https://swapi.co/api/vehicles/30/\"],\"starships\":[\"https://swapi.co/api/starships/12/\",\"https://swapi.co/api/starships/22/\"],\"created\":\"2014-12-09T13:50:51.644000Z\",\"edited\":\"2014-12-20T21:17:56.891000Z\",\"url\":\"https://swapi.co/api/people/1/\"}'\n",
      "2\n"
     ]
    }
   ],
   "source": [
    "r = requests.get('http://localhost:9200') \n",
    "i = 1\n",
    "while r.status_code == 200:\n",
    "    r = requests.get('http://swapi.co/api/people/'+ str(i))\n",
    "   # es.index(index='sw', doc_type='people', id=i, body=json.loads(r.content))\n",
    "    print(r.content)\n",
    "    i=i+1\n",
    "    break\n",
    " \n",
    "print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': '5',\n",
       " '_index': 'zadolbali',\n",
       " '_source': {'id': 5,\n",
       "  'likes': 0,\n",
       "  'tags': 'insurers mail',\n",
       "  'text': 'Работаю в страховой компании. Любимые клиенты пишут:«На крышу мерседеса упала с балкона кошка. Cуществуют риски \"падение предмета\" и \"атака животного\". Что писать в страховом акте?»Претензионщики мрачно шутят, что если бы она упала и померла в полете — то было бы проще. Записали бы предметом.',\n",
       "  'title': 'Атака одушевленного обьекта',\n",
       "  'url': 'http://zadolba.li/story/5'},\n",
       " '_type': 'stories',\n",
       " '_version': 3,\n",
       " 'found': True}"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es.get(index='zadolbali', id=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 'hrefs', 'id', 'likes', 'published', 'tags', 'text', 'title', 'url'\n",
    "for story in stories:\n",
    "    body = {\n",
    "        \"id\": story.id,\n",
    "        \"title\": story.title,\n",
    "        \"text\": story.text,\n",
    "        \"likes\": story.likes,\n",
    "        \"tags\": story.tags,\n",
    "        \"url\": story.url\n",
    "    }\n",
    "    es.index(index='zadolbali', doc_type='stories', id=story.id, body=body)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': '5',\n",
       " '_index': 'zadolbali',\n",
       " '_source': {'id': 5,\n",
       "  'likes': 0,\n",
       "  'tags': 'insurers mail',\n",
       "  'text': 'Работаю в страховой компании. Любимые клиенты пишут:«На крышу мерседеса упала с балкона кошка. Cуществуют риски \"падение предмета\" и \"атака животного\". Что писать в страховом акте?»Претензионщики мрачно шутят, что если бы она упала и померла в полете — то было бы проще. Записали бы предметом.',\n",
       "  'title': 'Атака одушевленного обьекта',\n",
       "  'url': 'http://zadolba.li/story/5'},\n",
       " '_type': 'stories',\n",
       " '_version': 1,\n",
       " 'found': True}"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es.get(index='zadolbali', id=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true<commit_msg>update on index filling notebook<commit_after>{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table\n",
    "from sqlalchemy.orm import mapper, sessionmaker\n",
    "import requests\n",
    "import json\n",
    "from elasticsearch import Elasticsearch\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017-11-05 02:23:53,679 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1\n",
      "2017-11-05 02:23:53,685 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 02:23:53,687 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1\n",
      "2017-11-05 02:23:53,689 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 02:23:53,690 INFO sqlalchemy.engine.base.Engine PRAGMA table_info(\"stories\")\n",
      "2017-11-05 02:23:53,691 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 02:23:53,694 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'stories' AND type = 'table'\n",
      "2017-11-05 02:23:53,695 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 02:23:53,696 INFO sqlalchemy.engine.base.Engine PRAGMA foreign_key_list(\"stories\")\n",
      "2017-11-05 02:23:53,697 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 02:23:53,698 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'stories' AND type = 'table'\n",
      "2017-11-05 02:23:53,699 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 02:23:53,701 INFO sqlalchemy.engine.base.Engine PRAGMA index_list(\"stories\")\n",
      "2017-11-05 02:23:53,702 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 02:23:53,703 INFO sqlalchemy.engine.base.Engine PRAGMA index_list(\"stories\")\n",
      "2017-11-05 02:23:53,704 INFO sqlalchemy.engine.base.Engine ()\n",
      "2017-11-05 02:23:53,706 INFO sqlalchemy.engine.base.Engine SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE name = 'stories' AND type = 'table'\n",
      "2017-11-05 02:23:53,707 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    }
   ],
   "source": [
    "class Story(object):\n",
    "    pass\n",
    " \n",
    "def loadSession():\n",
    "    dbPath = 'stories.sqlite'\n",
    "    engine = create_engine('sqlite:///%s' % dbPath, echo=True)\n",
    " \n",
    "    bookmarks = Table('stories', MetaData(engine), autoload=True)\n",
    "    mapper(Story, bookmarks)\n",
    " \n",
    "    Session = sessionmaker(bind=engine)\n",
    "    session = Session()\n",
    "    return session\n",
    "\n",
    "session = loadSession()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017-11-05 02:23:54,149 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2017-11-05 02:23:54,153 INFO sqlalchemy.engine.base.Engine SELECT stories.id AS stories_id, stories.title AS stories_title, stories.published AS stories_published, stories.tags AS stories_tags, stories.text AS stories_text, stories.likes AS stories_likes, stories.hrefs AS stories_hrefs, stories.url AS stories_url \n",
      "FROM stories\n",
      "2017-11-05 02:23:54,155 INFO sqlalchemy.engine.base.Engine ()\n",
      "23558\n",
      "['__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', '_sa_class_manager', '_sa_instance_state', 'hrefs', 'id', 'likes', 'published', 'tags', 'text', 'title', 'url']\n"
     ]
    }
   ],
   "source": [
    "stories = session.query(Story).all()\n",
    "print(len(stories))\n",
    "print(dir(stories[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b'{\\n  \"name\" : \"-L9u61j\",\\n  \"cluster_name\" : \"elasticsearch\",\\n  \"cluster_uuid\" : \"7yPgKxlWRwmSpPqu9E9w2A\",\\n  \"version\" : {\\n    \"number\" : \"5.6.3\",\\n    \"build_hash\" : \"1a2f265\",\\n    \"build_date\" : \"2017-10-06T20:33:39.012Z\",\\n    \"build_snapshot\" : false,\\n    \"lucene_version\" : \"6.6.1\"\\n  },\\n  \"tagline\" : \"You Know, for Search\"\\n}\\n'\n"
     ]
    }
   ],
   "source": [
    "res = requests.get('http://localhost:9200')\n",
    "print(res.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "es = Elasticsearch([{'host': 'localhost', 'port': 9200}])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b'{\"name\":\"<NAME>\",\"height\":\"172\",\"mass\":\"77\",\"hair_color\":\"blond\",\"skin_color\":\"fair\",\"eye_color\":\"blue\",\"birth_year\":\"19BBY\",\"gender\":\"male\",\"homeworld\":\"https://swapi.co/api/planets/1/\",\"films\":[\"https://swapi.co/api/films/2/\",\"https://swapi.co/api/films/6/\",\"https://swapi.co/api/films/3/\",\"https://swapi.co/api/films/1/\",\"https://swapi.co/api/films/7/\"],\"species\":[\"https://swapi.co/api/species/1/\"],\"vehicles\":[\"https://swapi.co/api/vehicles/14/\",\"https://swapi.co/api/vehicles/30/\"],\"starships\":[\"https://swapi.co/api/starships/12/\",\"https://swapi.co/api/starships/22/\"],\"created\":\"2014-12-09T13:50:51.644000Z\",\"edited\":\"2014-12-20T21:17:56.891000Z\",\"url\":\"https://swapi.co/api/people/1/\"}'\n",
      "2\n"
     ]
    }
   ],
   "source": [
    "r = requests.get('http://localhost:9200') \n",
    "i = 1\n",
    "while r.status_code == 200:\n",
    "    r = requests.get('http://swapi.co/api/people/'+ str(i))\n",
    "   # es.index(index='sw', doc_type='people', id=i, body=json.loads(r.content))\n",
    "    print(r.content)\n",
    "    i=i+1\n",
    "    break\n",
    " \n",
    "print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': '5',\n",
       " '_index': 'zadolbali',\n",
       " '_source': {'id': 5,\n",
       "  'likes': 0,\n",
       "  'published': 20090915,\n",
       "  'tags': 'insurers mail',\n",
       "  'text': 'Работаю в страховой компании. Любимые клиенты пишут:«На крышу мерседеса упала с балкона кошка. Cуществуют риски \"падение предмета\" и \"атака животного\". Что писать в страховом акте?»Претензионщики мрачно шутят, что если бы она упала и померла в полете — то было бы проще. Записали бы предметом.',\n",
       "  'title': 'Атака одушевленного обьекта',\n",
       "  'url': 'http://zadolba.li/story/5'},\n",
       " '_type': 'stories',\n",
       " '_version': 1,\n",
       " 'found': True}"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es.get(index='zadolbali', id=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 'hrefs', 'id', 'likes', 'published', 'tags', 'text', 'title', 'url'\n",
    "for story in stories:\n",
    "    body = {\n",
    "        \"id\": story.id,\n",
    "        \"title\": story.title,\n",
    "        \"text\": story.text,\n",
    "        \"published\": story.published,\n",
    "        \"likes\": story.likes,\n",
    "        \"tags\": story.tags,\n",
    "        \"url\": story.url\n",
    "    }\n",
    "    es.index(index='zadolbali', doc_type='stories', id=story.id, body=body)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'_id': '5',\n",
       " '_index': 'zadolbali',\n",
       " '_source': {'id': 5,\n",
       "  'likes': 0,\n",
       "  'published': 20090915,\n",
       "  'tags': 'insurers mail',\n",
       "  'text': 'Работаю в страховой компании. Любимые клиенты пишут:«На крышу мерседеса упала с балкона кошка. Cуществуют риски \"падение предмета\" и \"атака животного\". Что писать в страховом акте?»Претензионщики мрачно шутят, что если бы она упала и померла в полете — то было бы проще. Записали бы предметом.',\n",
       "  'title': 'Атака одушевленного обьекта',\n",
       "  'url': 'http://zadolba.li/story/5'},\n",
       " '_type': 'stories',\n",
       " '_version': 1,\n",
       " 'found': True}"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es.get(index='zadolbali', id=5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
{'fraction_non_alphanumeric': 0.22277201097187113, 'fraction_numerical': 0.055881245630075835, 'mean_word_length': 2.801676548763034, 'pattern_counts': {'":': 194, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 24, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1959893', 'n_tokens_mistral': 8001, 'n_tokens_neox': 7315, 'n_words': 1279}