Psycopg2 - DB JSON Parser.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime \n",
    "from flatten_json import flatten\n",
    "from http.server import BaseHTTPRequestHandler, HTTPServer\n",
    "import logging, json, re\n",
    "import sqlalchemy as db\n",
    "\n",
    "engine = db.create_engine('clickhouse://foobar:foobar@foobar/foobar')\n",
    "\n",
    "connection = engine.connect()\n",
    "metadata = db.MetaData()\n",
    "\n",
    "gonenc = db.Table('analysis',metadata, autoload = True, autoload_with = engine)\n",
    "\n",
    "false = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "127.0.0.1 - - [20/Jan/2020 14:12:08] \"POST / HTTP/1.1\" 200 -\n"
     ]
    }
   ],
   "source": [
    "class S(BaseHTTPRequestHandler):\n",
    "\n",
    "    def _set_response(self):\n",
    "        self.send_response(200)\n",
    "        self.send_header('Content-type', 'text/html')\n",
    "        self.end_headers()\n",
    "\n",
    "    def do_GET(self):\n",
    "        logging.info(\"GET request,\\nPath: %s\\nHeaders:\\n%s\\n\", str(self.path), str(self.headers))\n",
    "        self._set_response()\n",
    "        self.wfile.write(\"GET request for {}\".format(self.path).encode('utf-8'))\n",
    "\n",
    "    def do_POST(self):\n",
    "        \n",
    "        content_length = int(self.headers['Content-Length']) \n",
    "        post_data = self.rfile.read(content_length)\n",
    "        s = json.dumps(post_data.decode())\n",
    "        parsed = json.loads(s)\n",
    "        parsed_dict = eval(post_data)\n",
    "        flattened = flatten(parsed_dict)\n",
    "        \n",
    "        \n",
    "        bids_not_stripped = re.findall('\\\"price\\\":.+',parsed)\n",
    "        bids_pre = [x.strip('\"price\":,') for x in bids_not_stripped]\n",
    "        bids_a = [x.replace('\"','') for x in bids_pre]\n",
    "\n",
    "\n",
    "        bid_ids_not_stripped = re.findall('\\\"id\\\": \\\"\\S+\\\"',parsed)\n",
    "        bidders_pre = [x.strip('\"id\":\"') for x in bid_ids_not_stripped[2:]]\n",
    "        bidders_a =[x.replace('\"','') for x in bidders_pre]\n",
    "\n",
    "        aid = str(flattened['Auction_id'])\n",
    "        \n",
    "        pubs = str(flattened['Auction_publisher'])\n",
    "        \n",
    "        pg = str(flattened['Auction_site_page'])\n",
    "        \n",
    "        impress =  str(flattened['Auction_impressions_0_id'])\n",
    "        \n",
    "        height = str(flattened['Auction_impressions_0_acceptedSizes_0_h'])\n",
    "        \n",
    "        width = str(flattened['Auction_impressions_0_acceptedSizes_0_w'])\n",
    "             \n",
    "        \n",
    "        land = str(flattened['Auction_device_geo_country'])\n",
    "        \n",
    "    \n",
    "        timestamp = flattened['Statistics_lastAuctionEndTimestamp']\n",
    "        \n",
    "\n",
    "        query = db.insert(analysis).values(auction_id =aid,\n",
    "                                         publisher = pubs,\n",
    "                                         page = pg,\n",
    "                                         impid = impress,\n",
    "                                         h = height,\n",
    "                                         w = width,\n",
    "                                         bidders = bidders_a,\n",
    "                                         bids = bids_a, \n",
    "                                         country = land, \n",
    "                                         time = timestamp)\n",
    "        \n",
    "        ResultProxy = connection.execute(query)\n",
    "        self.send_response(200)\n",
    "        \n",
    "def run(server_class=HTTPServer, handler_class=S, port=8080):\n",
    "    logging.basicConfig(level=logging.INFO)\n",
    "    server_address = ('', port)\n",
    "    httpd = server_class(server_address, handler_class)\n",
    "    \n",
    "    try:\n",
    "        httpd.serve_forever()\n",
    "    except KeyboardInterrupt:\n",
    "        pass\n",
    "    httpd.server_close()\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    from sys import argv\n",
    "\n",
    "    if len(argv) == 2:\n",
    "        run(port=int(argv[1]))\n",
    "    else:\n",
    "        run()\n",
    "        \n",
    "        \n"
   ]
  }
 ],
 "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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}