doc/recurrent/keras_boston-SimpleRNN.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Using TensorFlow backend.\n"
     ]
    }
   ],
   "source": [
    "import os, numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "os.environ[\"KERAS_BACKEND\"] = \"tensorflow\"\n",
    "\n",
    "import keras \n",
    "from sklearn import datasets\n",
    "\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "from keras.utils import np_utils\n",
    "\n",
    "keras.backend.set_floatx('float64')\n",
    "\n",
    "def set_reproducible():\n",
    "    import tensorflow as tf\n",
    "    import random as rn\n",
    "    import os\n",
    "    os.environ['PYTHONHASHSEED'] = '1960'\n",
    "    rn.seed(1960)\n",
    "    np.random.seed(1960)\n",
    "    session_conf = tf.ConfigProto(intra_op_parallelism_threads=1, inter_op_parallelism_threads=1)\n",
    "\n",
    "    from keras import backend as K\n",
    "    tf.set_random_seed(1960)\n",
    "\n",
    "    sess = tf.Session(graph=tf.get_default_graph(), config=session_conf)\n",
    "    K.set_session(sess)\n",
    "\n",
    "set_reproducible()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Build a Keras Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "NC = 13\n",
    "\n",
    "def create_model():\n",
    "    from keras.models import Sequential\n",
    "    from keras.layers import Dense, Activation, SimpleRNN\n",
    "    from keras.constraints import max_norm\n",
    "\n",
    "    model = Sequential()\n",
    "    model.add(SimpleRNN(4, input_shape=(1, NC), batch_size=1 , \n",
    "                   activation='relu',\n",
    "                   stateful=True,\n",
    "                   kernel_constraint=max_norm(2.),\n",
    "                   recurrent_constraint=max_norm(2.),\n",
    "                   kernel_initializer = keras.initializers.Orthogonal(gain=0.1),\n",
    "                   recurrent_initializer = keras.initializers.Zeros()))\n",
    "    model.add(Dense(1, activation=None))\n",
    "    adam_optimizer = keras.optimizers.Adam(lr=0.01, clipnorm=1.)\n",
    "    model.compile(loss='mse', optimizer=adam_optimizer)\n",
    "    return model\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "boston  = datasets.load_boston()\n",
    "boston.data = (boston.data - boston.data.mean(axis=0)) / boston.data.std(axis=0)\n",
    "boston.target = (boston.target - boston.target.mean(axis=0)) / boston.target.std(axis=0)\n",
    "\n",
    "boston.data = boston.data[:,0:NC]\n",
    "\n",
    "train_X, test_X, train_y, test_y = train_test_split(boston.data, boston.target, train_size=0.6, test_size=0.4, random_state=1960)\n",
    "train_X = train_X.reshape(train_X.shape[0] , 1, train_X.shape[1])\n",
    "test_X = test_X.reshape(test_X.shape[0] , 1, test_X.shape[1])\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#boston.data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#boston.target"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(303, 1, 13) (303,)\n",
      "Epoch 1/6\n",
      " - 1s - loss: 0.3736\n",
      "Epoch 2/6\n",
      " - 0s - loss: 0.2822\n",
      "Epoch 3/6\n",
      " - 0s - loss: 0.2880\n",
      "Epoch 4/6\n",
      " - 0s - loss: 0.2765\n",
      "Epoch 5/6\n",
      " - 0s - loss: 0.2956\n",
      "Epoch 6/6\n",
      " - 0s - loss: 0.3257\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<keras.callbacks.History at 0x7f25fd1e3a90>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "from keras.wrappers.scikit_learn import KerasRegressor\n",
    "\n",
    "clf = KerasRegressor(build_fn=create_model, batch_size=1, epochs=6, verbose=2)\n",
    "\n",
    "print(train_X.shape , train_y.shape)\n",
    "clf.fit(train_X, train_y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(203, 1, 13)\n",
      "[-0.15400091 -0.31443807 -0.20076562 -0.15400091  0.43109361 -0.42913229\n",
      " -0.38996782 -0.15400091 -0.91000277 -0.45662413 -0.15400091 -0.51796945\n",
      " -0.06204796 -0.87303262  1.01182806 -0.60236015  1.05630171 -0.15400091\n",
      " -1.27771481  0.49412818 -0.31432003  3.23656087 -1.06049006  0.19974604\n",
      "  1.30235407 -0.31813756 -0.15400091 -1.06477565 -0.15400091 -0.9142552\n",
      "  0.24939548 -0.75862913 -1.27374863  0.98656451 -0.15400091  0.10889413\n",
      "  0.35331937 -0.81250808 -0.26648952  0.56739454 -0.56415009 -0.15400091\n",
      " -0.36127076 -0.75369759 -0.15400091 -0.1317169   0.40734553 -0.10111318\n",
      " -1.01631935 -0.15400091 -0.15400091 -0.5953882  -0.71249294  1.98814505\n",
      " -0.72417342 -0.33617534  1.26391165 -0.24938593 -0.15400091 -0.21132226\n",
      " -0.24943465  1.12295919 -0.15400091  0.0682532  -1.22693992  1.10429868\n",
      " -0.15400091 -0.43157739 -0.63814887  0.10879875  0.01592168  3.45731391\n",
      " -0.46116141 -0.15400091  0.3804442  -0.84175394  1.7419177  -0.15400091\n",
      " -0.74250253  0.5319715  -0.47755424  0.3769674   0.06475891 -0.65828719\n",
      " -0.21319798  1.00037487 -0.71745368  1.12086243 -0.42480979  0.61509516\n",
      " -1.35793669  0.47561489 -0.20520806 -0.81235582 -0.08144864 -0.04881948\n",
      "  1.98428075  0.04245488  0.03914278 -0.16947468  0.28102679 -0.59266376\n",
      "  0.01365268 -0.15400091 -0.23840173 -0.15400091 -0.37413251 -0.48305778\n",
      "  2.13216481 -1.89587944  0.52327439 -0.15400091  0.02470494  1.70453848\n",
      " -0.83523226 -0.02590673 -0.81714741 -0.64798313 -0.67396904  0.21802168\n",
      " -0.18344852 -0.87903728 -0.58388379 -0.46142356 -0.22327162  2.10580654\n",
      " -0.92669145 -0.03036716 -0.62743147 -0.78151838 -0.15400091 -0.15400091\n",
      "  1.3462546  -0.15400091 -0.15400091 -0.79202631  0.72527606 -0.52860166\n",
      " -0.19937854 -1.00631982 -0.38175071  2.69217191 -0.26912786 -0.71613906\n",
      "  0.26212725  0.31104032 -0.15400091  0.11723163  1.38962365 -0.28107649\n",
      " -0.75886886  0.21590234  1.63316886 -0.15400091 -0.15400091 -0.77501068\n",
      "  2.26541706 -0.15400091 -0.25662152 -0.43879699  0.39652076 -0.70662139\n",
      " -0.15400091 -0.66523804  0.59604676 -0.15400091 -0.21507338  4.67374394\n",
      " -0.24209801  0.63960804 -0.23517254 -0.15400091 -0.15400091 -0.82941477\n",
      " -0.15400091 -0.10776256 -0.1489276  -0.15400091 -0.0710039   0.08227609\n",
      " -0.65195061  3.14749308 -0.15400091 -0.26674629  1.113698   -0.15400091\n",
      "  0.61532859  1.37209025 -0.77864481  0.54228938  1.30303425  1.47408189\n",
      " -0.7233656  -0.15400091  0.63294979  2.17002073 -0.6355826   0.59314411\n",
      "  1.04525814 -0.61696574 -0.15400091  0.57271393 -0.64490206]\n"
     ]
    }
   ],
   "source": [
    "print(test_X.shape)\n",
    "preds = clf.predict(test_X)\n",
    "print(preds)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate SQL Code from the Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json, requests, base64, dill as pickle, sys\n",
    "\n",
    "\n",
    "\n",
    "sys.setrecursionlimit(200000)\n",
    "pickle.settings['recurse'] = False\n",
    "\n",
    "\n",
    "def test_ws_sql_gen(pickle_data):\n",
    "    WS_URL=\"https://sklearn2sql.herokuapp.com/model\"\n",
    "    b64_data = base64.b64encode(pickle_data).decode('utf-8')\n",
    "    data={\"Name\":\"model1\", \"PickleData\":b64_data , \"SQLDialect\":\"postgresql\"}\n",
    "    r = requests.post(WS_URL, json=data)\n",
    "    # print(r.__dict__)\n",
    "    content = r.json()\n",
    "    # print(content)\n",
    "    lSQL = content[\"model\"][\"SQLGenrationResult\"][0][\"SQL\"]\n",
    "    return lSQL;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle_data = pickle.dumps(clf)\n",
    "lSQL = test_ws_sql_gen(pickle_data)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WITH RECURSIVE keras_input AS \n",
      "(SELECT \"ADS\".\"KEY\" AS \"KEY\", \"ADS\".\"Feature_0\" AS \"Feature_0\", \"ADS\".\"Feature_1\" AS \"Feature_1\", \"ADS\".\"Feature_2\" AS \"Feature_2\", \"ADS\".\"Feature_3\" AS \"Feature_3\", \"ADS\".\"Feature_4\" AS \"Feature_4\", \"ADS\".\"Feature_5\" AS \"Feature_5\", \"ADS\".\"Feature_6\" AS \"Feature_6\", \"ADS\".\"Feature_7\" AS \"Feature_7\", \"ADS\".\"Feature_8\" AS \"Feature_8\", \"ADS\".\"Feature_9\" AS \"Feature_9\", \"ADS\".\"Feature_10\" AS \"Feature_10\", \"ADS\".\"Feature_11\" AS \"Feature_11\", \"ADS\".\"Feature_12\" AS \"Feature_12\" \n",
      "FROM \"INPUT_DATA\" AS \"ADS\"), \n",
      "keras_input_1 AS \n",
      "(SELECT keras_input.\"KEY\" AS \"KEY\", keras_input.\"Feature_0\" AS \"Feature_0\", keras_input.\"Feature_1\" AS \"Feature_1\", keras_input.\"Feature_2\" AS \"Feature_2\", keras_input.\"Feature_3\" AS \"Feature_3\", keras_input.\"Feature_4\" AS \"Feature_4\", keras_input.\"Feature_5\" AS \"Feature_5\", keras_input.\"Feature_6\" AS \"Feature_6\", keras_input.\"Feature_7\" AS \"Feature_7\", keras_input.\"Feature_8\" AS \"Feature_8\", keras_input.\"Feature_9\" AS \"Feature_9\", keras_input.\"Feature_10\" AS \"Feature_10\", keras_input.\"Feature_11\" AS \"Feature_11\", keras_input.\"Feature_12\" AS \"Feature_12\" \n",
      "FROM keras_input), \n",
      "keras_input_1_rn AS \n",
      "(SELECT row_number() OVER (ORDER BY keras_input_1.\"KEY\" ASC) AS rn, keras_input_1.\"KEY\" AS \"KEY\", keras_input_1.\"Feature_0\" AS \"Feature_0\", keras_input_1.\"Feature_1\" AS \"Feature_1\", keras_input_1.\"Feature_2\" AS \"Feature_2\", keras_input_1.\"Feature_3\" AS \"Feature_3\", keras_input_1.\"Feature_4\" AS \"Feature_4\", keras_input_1.\"Feature_5\" AS \"Feature_5\", keras_input_1.\"Feature_6\" AS \"Feature_6\", keras_input_1.\"Feature_7\" AS \"Feature_7\", keras_input_1.\"Feature_8\" AS \"Feature_8\", keras_input_1.\"Feature_9\" AS \"Feature_9\", keras_input_1.\"Feature_10\" AS \"Feature_10\", keras_input_1.\"Feature_11\" AS \"Feature_11\", keras_input_1.\"Feature_12\" AS \"Feature_12\" \n",
      "FROM keras_input_1), \n",
      "input_kernel_bias AS \n",
      "(SELECT keras_input_1_rn.rn AS rn, keras_input_1_rn.\"KEY\" AS \"KEY\", keras_input_1_rn.\"Feature_0\" AS \"Feature_0\", keras_input_1_rn.\"Feature_1\" AS \"Feature_1\", keras_input_1_rn.\"Feature_2\" AS \"Feature_2\", keras_input_1_rn.\"Feature_3\" AS \"Feature_3\", keras_input_1_rn.\"Feature_4\" AS \"Feature_4\", keras_input_1_rn.\"Feature_5\" AS \"Feature_5\", keras_input_1_rn.\"Feature_6\" AS \"Feature_6\", keras_input_1_rn.\"Feature_7\" AS \"Feature_7\", keras_input_1_rn.\"Feature_8\" AS \"Feature_8\", keras_input_1_rn.\"Feature_9\" AS \"Feature_9\", keras_input_1_rn.\"Feature_10\" AS \"Feature_10\", keras_input_1_rn.\"Feature_11\" AS \"Feature_11\", keras_input_1_rn.\"Feature_12\" AS \"Feature_12\", -0.4722128963602821 + -0.14889145718538813 * keras_input_1_rn.\"Feature_0\" + -0.04805655092328096 * keras_input_1_rn.\"Feature_1\" + 0.17711011133571045 * keras_input_1_rn.\"Feature_2\" + -0.03691037378867321 * keras_input_1_rn.\"Feature_3\" + -0.02358416908976392 * keras_input_1_rn.\"Feature_4\" + 0.8060694831822887 * keras_input_1_rn.\"Feature_5\" + -0.024318553619865475 * keras_input_1_rn.\"Feature_6\" + -0.304988523749501 * keras_input_1_rn.\"Feature_7\" + -0.021366451246095895 * keras_input_1_rn.\"Feature_8\" + -0.21495450639507413 * keras_input_1_rn.\"Feature_9\" + -0.19119596824009805 * keras_input_1_rn.\"Feature_10\" + 0.06333714526924963 * keras_input_1_rn.\"Feature_11\" + -0.36208973527219623 * keras_input_1_rn.\"Feature_12\" AS dot_prod_1, -0.07908742295254845 + -0.13549708659902082 * keras_input_1_rn.\"Feature_0\" + 0.10199206580614223 * keras_input_1_rn.\"Feature_1\" + -0.23520174962398416 * keras_input_1_rn.\"Feature_2\" + -0.002124630430066588 * keras_input_1_rn.\"Feature_3\" + -0.09966096883147368 * keras_input_1_rn.\"Feature_4\" + 1.549626732816033 * keras_input_1_rn.\"Feature_5\" + -0.3066411378328723 * keras_input_1_rn.\"Feature_6\" + -0.3858360074000528 * keras_input_1_rn.\"Feature_7\" + -0.17863432690670805 * keras_input_1_rn.\"Feature_8\" + -0.5487969960218945 * keras_input_1_rn.\"Feature_9\" + -0.24599709929555252 * keras_input_1_rn.\"Feature_10\" + -0.09871608119270774 * keras_input_1_rn.\"Feature_11\" + -0.5071071353483482 * keras_input_1_rn.\"Feature_12\" AS dot_prod_2, 0.12052409041440879 + 0.45714119451695345 * keras_input_1_rn.\"Feature_0\" + -0.08838197391535994 * keras_input_1_rn.\"Feature_1\" + -0.037927474003869345 * keras_input_1_rn.\"Feature_2\" + -0.2112466779663222 * keras_input_1_rn.\"Feature_3\" + 0.5722180742605968 * keras_input_1_rn.\"Feature_4\" + 0.015105315902231749 * keras_input_1_rn.\"Feature_5\" + 0.8098363007464855 * keras_input_1_rn.\"Feature_6\" + 0.07622140793074113 * keras_input_1_rn.\"Feature_7\" + -0.17517727972230587 * keras_input_1_rn.\"Feature_8\" + 0.16898135784692309 * keras_input_1_rn.\"Feature_9\" + 0.5475983202422362 * keras_input_1_rn.\"Feature_10\" + -0.45301445031596344 * keras_input_1_rn.\"Feature_11\" + 1.0666969378735383 * keras_input_1_rn.\"Feature_12\" AS dot_prod_3, -0.379385670890883 + 0.12009765894095732 * keras_input_1_rn.\"Feature_0\" + 0.12368500719187678 * keras_input_1_rn.\"Feature_1\" + 0.3364163792935352 * keras_input_1_rn.\"Feature_2\" + 0.2762828761629398 * keras_input_1_rn.\"Feature_3\" + -0.29164489266389865 * keras_input_1_rn.\"Feature_4\" + 0.9327124328138109 * keras_input_1_rn.\"Feature_5\" + -0.2178451185350861 * keras_input_1_rn.\"Feature_6\" + -0.312787245382257 * keras_input_1_rn.\"Feature_7\" + 0.14310946003522243 * keras_input_1_rn.\"Feature_8\" + 0.016379163928927604 * keras_input_1_rn.\"Feature_9\" + 0.023535714319527123 * keras_input_1_rn.\"Feature_10\" + 0.2180239461356201 * keras_input_1_rn.\"Feature_11\" + -0.6654082098347297 * keras_input_1_rn.\"Feature_12\" AS dot_prod_4 \n",
      "FROM keras_input_1_rn), \n",
      "rnn_cte_simple_rnn_1(rn_1, \"KEY\", \"PreviousState_1\", \"PreviousState_2\", \"PreviousState_3\", \"PreviousState_4\", output_1, output_2, output_3, output_4, \"State_1\", \"State_2\", \"State_3\", \"State_4\") AS \n",
      "(SELECT input_kernel_bias.rn AS rn_1, input_kernel_bias.\"KEY\" AS \"KEY\", CAST(0.0 AS FLOAT) AS \"PreviousState_1\", CAST(0.0 AS FLOAT) AS \"PreviousState_2\", CAST(0.0 AS FLOAT) AS \"PreviousState_3\", CAST(0.0 AS FLOAT) AS \"PreviousState_4\", greatest(input_kernel_bias.dot_prod_1 + 0.0, 0) AS output_1, greatest(input_kernel_bias.dot_prod_2 + 0.0, 0) AS output_2, greatest(input_kernel_bias.dot_prod_3 + 0.0, 0) AS output_3, greatest(input_kernel_bias.dot_prod_4 + 0.0, 0) AS output_4, greatest(input_kernel_bias.dot_prod_1 + 0.0, 0) AS \"State_1\", greatest(input_kernel_bias.dot_prod_2 + 0.0, 0) AS \"State_2\", greatest(input_kernel_bias.dot_prod_3 + 0.0, 0) AS \"State_3\", greatest(input_kernel_bias.dot_prod_4 + 0.0, 0) AS \"State_4\" \n",
      "FROM input_kernel_bias \n",
      "WHERE input_kernel_bias.rn = 1 UNION ALL SELECT input_kernel_bias.rn AS rn, input_kernel_bias.\"KEY\" AS \"KEY\", rnn_cte_simple_rnn_1.\"State_1\" AS \"PreviousState_1\", rnn_cte_simple_rnn_1.\"State_2\" AS \"PreviousState_2\", rnn_cte_simple_rnn_1.\"State_3\" AS \"PreviousState_3\", rnn_cte_simple_rnn_1.\"State_4\" AS \"PreviousState_4\", greatest(input_kernel_bias.dot_prod_1 + -0.5955097868664003 * rnn_cte_simple_rnn_1.\"State_1\" + -0.295499643958178 * rnn_cte_simple_rnn_1.\"State_2\" + -0.7344893069386743 * rnn_cte_simple_rnn_1.\"State_3\" + -0.4143473334030094 * rnn_cte_simple_rnn_1.\"State_4\", 0) AS output_1, greatest(input_kernel_bias.dot_prod_2 + -0.37081281122899523 * rnn_cte_simple_rnn_1.\"State_1\" + -0.5586817497535401 * rnn_cte_simple_rnn_1.\"State_2\" + -0.10895080786984104 * rnn_cte_simple_rnn_1.\"State_3\" + -0.307255100643153 * rnn_cte_simple_rnn_1.\"State_4\", 0) AS output_2, greatest(input_kernel_bias.dot_prod_3 + 0.5276891346090428 * rnn_cte_simple_rnn_1.\"State_1\" + -0.017016700077914372 * rnn_cte_simple_rnn_1.\"State_2\" + -0.651326784438873 * rnn_cte_simple_rnn_1.\"State_3\" + 0.4680504437626115 * rnn_cte_simple_rnn_1.\"State_4\", 0) AS output_3, greatest(input_kernel_bias.dot_prod_4 + -0.5495353267495472 * rnn_cte_simple_rnn_1.\"State_1\" + -0.42290177905434495 * rnn_cte_simple_rnn_1.\"State_2\" + -0.4382004861909682 * rnn_cte_simple_rnn_1.\"State_3\" + -0.46753244017704676 * rnn_cte_simple_rnn_1.\"State_4\", 0) AS output_4, greatest(input_kernel_bias.dot_prod_1 + -0.5955097868664003 * rnn_cte_simple_rnn_1.\"State_1\" + -0.295499643958178 * rnn_cte_simple_rnn_1.\"State_2\" + -0.7344893069386743 * rnn_cte_simple_rnn_1.\"State_3\" + -0.4143473334030094 * rnn_cte_simple_rnn_1.\"State_4\", 0) AS \"State_1\", greatest(input_kernel_bias.dot_prod_2 + -0.37081281122899523 * rnn_cte_simple_rnn_1.\"State_1\" + -0.5586817497535401 * rnn_cte_simple_rnn_1.\"State_2\" + -0.10895080786984104 * rnn_cte_simple_rnn_1.\"State_3\" + -0.307255100643153 * rnn_cte_simple_rnn_1.\"State_4\", 0) AS \"State_2\", greatest(input_kernel_bias.dot_prod_3 + 0.5276891346090428 * rnn_cte_simple_rnn_1.\"State_1\" + -0.017016700077914372 * rnn_cte_simple_rnn_1.\"State_2\" + -0.651326784438873 * rnn_cte_simple_rnn_1.\"State_3\" + 0.4680504437626115 * rnn_cte_simple_rnn_1.\"State_4\", 0) AS \"State_3\", greatest(input_kernel_bias.dot_prod_4 + -0.5495353267495472 * rnn_cte_simple_rnn_1.\"State_1\" + -0.42290177905434495 * rnn_cte_simple_rnn_1.\"State_2\" + -0.4382004861909682 * rnn_cte_simple_rnn_1.\"State_3\" + -0.46753244017704676 * rnn_cte_simple_rnn_1.\"State_4\", 0) AS \"State_4\" \n",
      "FROM input_kernel_bias, rnn_cte_simple_rnn_1 \n",
      "WHERE input_kernel_bias.rn = rnn_cte_simple_rnn_1.rn_1 + 1), \n",
      "simple_rnn_1 AS \n",
      "(SELECT rnn_cte_simple_rnn_1.\"KEY\" AS \"KEY\", CAST(rnn_cte_simple_rnn_1.output_1 AS FLOAT) AS output_1, CAST(rnn_cte_simple_rnn_1.output_2 AS FLOAT) AS output_2, CAST(rnn_cte_simple_rnn_1.output_3 AS FLOAT) AS output_3, CAST(rnn_cte_simple_rnn_1.output_4 AS FLOAT) AS output_4 \n",
      "FROM rnn_cte_simple_rnn_1), \n",
      "simple_rnn_1_1 AS \n",
      "(SELECT simple_rnn_1.\"KEY\" AS \"KEY\", simple_rnn_1.output_1 AS output_1, simple_rnn_1.output_2 AS output_2, simple_rnn_1.output_3 AS output_3, simple_rnn_1.output_4 AS output_4 \n",
      "FROM simple_rnn_1), \n",
      "layer_dense_1 AS \n",
      "(SELECT simple_rnn_1_1.\"KEY\" AS \"KEY\", -0.1540009061444654 + 0.41590206943057423 * simple_rnn_1_1.output_1 + 0.38003403397704494 * simple_rnn_1_1.output_2 + -0.17532092646199582 * simple_rnn_1_1.output_3 + 0.24629243906713383 * simple_rnn_1_1.output_4 AS output_1 \n",
      "FROM simple_rnn_1_1)\n",
      " SELECT layer_dense_1.\"KEY\" AS \"KEY\", layer_dense_1.output_1 AS \"Estimator\" \n",
      "FROM layer_dense_1\n"
     ]
    }
   ],
   "source": [
    "print(lSQL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Execute the SQL Code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/antoine/.local/lib/python3.7/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.\n",
      "  \"\"\")\n"
     ]
    }
   ],
   "source": [
    "# save the dataset in a database table\n",
    "\n",
    "import sqlalchemy as sa\n",
    "\n",
    "#engine = sa.create_engine('sqlite://' , echo=False)\n",
    "engine = sa.create_engine(\"postgresql://db:db@localhost/db?port=5432\", echo=False)\n",
    "conn = engine.connect()\n",
    "\n",
    "lTable = pd.DataFrame(boston.data);\n",
    "lTable.columns = ['Feature_' + str(x) for x in range(boston.data.shape[1]) ]\n",
    "lTable['TGT'] = boston.target\n",
    "lTable['KEY'] = range(boston.data.shape[0])\n",
    "lTable.to_sql(\"INPUT_DATA\" , conn,   if_exists='replace', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_output = pd.read_sql(lSQL , conn);\n",
    "sql_output = sql_output.sort_values(by='KEY').reset_index(drop=True)\n",
    "conn.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>KEY</th>\n",
       "      <th>Estimator</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>230</td>\n",
       "      <td>-0.146201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>112</td>\n",
       "      <td>-0.375191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>125</td>\n",
       "      <td>-0.282834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>-0.154001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>213</td>\n",
       "      <td>0.431094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>109</td>\n",
       "      <td>-0.302007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>127</td>\n",
       "      <td>-0.275022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>244</td>\n",
       "      <td>-0.154001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406</th>\n",
       "      <td>406</td>\n",
       "      <td>-0.472338</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>490</th>\n",
       "      <td>490</td>\n",
       "      <td>-0.710604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>245</td>\n",
       "      <td>-0.224030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>133</td>\n",
       "      <td>-0.422183</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     KEY  Estimator\n",
       "230  230  -0.146201\n",
       "112  112  -0.375191\n",
       "125  125  -0.282834\n",
       "9      9  -0.154001\n",
       "213  213   0.431094\n",
       "109  109  -0.302007\n",
       "127  127  -0.275022\n",
       "244  244  -0.154001\n",
       "406  406  -0.472338\n",
       "490  490  -0.710604\n",
       "245  245  -0.224030\n",
       "133  133  -0.422183"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_output.sample(12, random_state=1960)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Keras Prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(506,)\n"
     ]
    },
    {
     "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>KEY</th>\n",
       "      <th>Estimator</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>230</td>\n",
       "      <td>-0.146201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>112</td>\n",
       "      <td>-0.375191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>125</td>\n",
       "      <td>-0.282834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>-0.154001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>213</td>\n",
       "      <td>0.431094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>109</td>\n",
       "      <td>-0.302007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>127</td>\n",
       "      <td>-0.275022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>244</td>\n",
       "      <td>-0.154001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>406</th>\n",
       "      <td>406</td>\n",
       "      <td>-0.472338</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>490</th>\n",
       "      <td>490</td>\n",
       "      <td>-0.710604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>245</th>\n",
       "      <td>245</td>\n",
       "      <td>-0.224030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>133</td>\n",
       "      <td>-0.422183</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     KEY  Estimator\n",
       "230  230  -0.146201\n",
       "112  112  -0.375191\n",
       "125  125  -0.282834\n",
       "9      9  -0.154001\n",
       "213  213   0.431094\n",
       "109  109  -0.302007\n",
       "127  127  -0.275022\n",
       "244  244  -0.154001\n",
       "406  406  -0.472338\n",
       "490  490  -0.710604\n",
       "245  245  -0.224030\n",
       "133  133  -0.422183"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "skl_output = pd.DataFrame()\n",
    "skl_output_key = pd.DataFrame(list(range(boston.data.shape[0])), columns=['KEY']);\n",
    "data = boston.data.reshape(boston.data.shape[0] , 1, boston.data.shape[1])\n",
    "skl_out = clf.predict(data)\n",
    "print(skl_out.shape)\n",
    "skl_output_estimator = pd.DataFrame(skl_out, columns=['Estimator'])\n",
    "skl_output['KEY'] = skl_output_key['KEY']\n",
    "skl_output['Estimator'] = skl_output_estimator\n",
    "skl_output.sample(12, random_state=1960)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "format": "column"
   },
   "source": [
    "# Comparing the SQL and Keras Predictions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_skl_join = skl_output.join(sql_output , how='left', on='KEY', lsuffix='_skl', rsuffix='_sql')\n",
    "sql_skl_join['Error'] = sql_skl_join.Estimator_sql - sql_skl_join.Estimator_skl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>KEY_skl</th>\n",
       "      <th>Estimator_skl</th>\n",
       "      <th>KEY_sql</th>\n",
       "      <th>Estimator_sql</th>\n",
       "      <th>Error</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0.623391</td>\n",
       "      <td>0</td>\n",
       "      <td>0.986760</td>\n",
       "      <td>3.633691e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>-0.154001</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.154001</td>\n",
       "      <td>4.163336e-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1.911148</td>\n",
       "      <td>2</td>\n",
       "      <td>1.911148</td>\n",
       "      <td>1.332268e-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>-0.001304</td>\n",
       "      <td>3</td>\n",
       "      <td>-0.001304</td>\n",
       "      <td>2.248635e-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1.366995</td>\n",
       "      <td>4</td>\n",
       "      <td>1.366995</td>\n",
       "      <td>-2.886580e-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>-0.154001</td>\n",
       "      <td>5</td>\n",
       "      <td>-0.154001</td>\n",
       "      <td>4.163336e-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>-0.154001</td>\n",
       "      <td>6</td>\n",
       "      <td>-0.154001</td>\n",
       "      <td>4.163336e-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>-0.270652</td>\n",
       "      <td>7</td>\n",
       "      <td>-0.270652</td>\n",
       "      <td>1.665335e-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>-0.505317</td>\n",
       "      <td>8</td>\n",
       "      <td>-0.505317</td>\n",
       "      <td>2.220446e-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>-0.154001</td>\n",
       "      <td>9</td>\n",
       "      <td>-0.154001</td>\n",
       "      <td>4.163336e-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>-0.303496</td>\n",
       "      <td>10</td>\n",
       "      <td>-0.303496</td>\n",
       "      <td>-2.220446e-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>-0.154001</td>\n",
       "      <td>11</td>\n",
       "      <td>-0.154001</td>\n",
       "      <td>4.163336e-16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    KEY_skl  Estimator_skl  KEY_sql  Estimator_sql         Error\n",
       "0         0       0.623391        0       0.986760  3.633691e-01\n",
       "1         1      -0.154001        1      -0.154001  4.163336e-16\n",
       "2         2       1.911148        2       1.911148  1.332268e-15\n",
       "3         3      -0.001304        3      -0.001304  2.248635e-16\n",
       "4         4       1.366995        4       1.366995 -2.886580e-15\n",
       "5         5      -0.154001        5      -0.154001  4.163336e-16\n",
       "6         6      -0.154001        6      -0.154001  4.163336e-16\n",
       "7         7      -0.270652        7      -0.270652  1.665335e-16\n",
       "8         8      -0.505317        8      -0.505317  2.220446e-16\n",
       "9         9      -0.154001        9      -0.154001  4.163336e-16\n",
       "10       10      -0.303496       10      -0.303496 -2.220446e-16\n",
       "11       11      -0.154001       11      -0.154001  4.163336e-16"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_skl_join.head(12)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>KEY_skl</th>\n",
       "      <th>Estimator_skl</th>\n",
       "      <th>KEY_sql</th>\n",
       "      <th>Estimator_sql</th>\n",
       "      <th>Error</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>506.000000</td>\n",
       "      <td>506.000000</td>\n",
       "      <td>506.000000</td>\n",
       "      <td>506.000000</td>\n",
       "      <td>5.060000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>252.500000</td>\n",
       "      <td>0.045914</td>\n",
       "      <td>252.500000</td>\n",
       "      <td>0.046632</td>\n",
       "      <td>7.181207e-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>146.213884</td>\n",
       "      <td>0.789350</td>\n",
       "      <td>146.213884</td>\n",
       "      <td>0.790041</td>\n",
       "      <td>1.615372e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.663316</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.663316</td>\n",
       "      <td>-5.329071e-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>126.250000</td>\n",
       "      <td>-0.357708</td>\n",
       "      <td>126.250000</td>\n",
       "      <td>-0.357708</td>\n",
       "      <td>-2.220446e-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>252.500000</td>\n",
       "      <td>-0.154001</td>\n",
       "      <td>252.500000</td>\n",
       "      <td>-0.154001</td>\n",
       "      <td>6.938894e-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>378.750000</td>\n",
       "      <td>0.234974</td>\n",
       "      <td>378.750000</td>\n",
       "      <td>0.234974</td>\n",
       "      <td>4.163336e-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>505.000000</td>\n",
       "      <td>3.979998</td>\n",
       "      <td>505.000000</td>\n",
       "      <td>3.979998</td>\n",
       "      <td>3.633691e-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          KEY_skl  Estimator_skl     KEY_sql  Estimator_sql         Error\n",
       "count  506.000000     506.000000  506.000000     506.000000  5.060000e+02\n",
       "mean   252.500000       0.045914  252.500000       0.046632  7.181207e-04\n",
       "std    146.213884       0.789350  146.213884       0.790041  1.615372e-02\n",
       "min      0.000000      -1.663316    0.000000      -1.663316 -5.329071e-15\n",
       "25%    126.250000      -0.357708  126.250000      -0.357708 -2.220446e-16\n",
       "50%    252.500000      -0.154001  252.500000      -0.154001  6.938894e-17\n",
       "75%    378.750000       0.234974  378.750000       0.234974  4.163336e-16\n",
       "max    505.000000       3.979998  505.000000       3.979998  3.633691e-01"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_skl_join.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}