Chapter 4 - Managing Your Data in CAS.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Chapter 4 - Managing Your Data in CAS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Getting Started with CASLibs and CAS Tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a connection to CAS and list the CASLibs using the **caslibinfo** action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import swat\n",
    "\n",
    "conn = swat.CAS('server-name.mycompany.com', 5570, 'username', 'password')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; CASLibInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Name\">Name</th>\n",
       "      <th title=\"Type\">Type</th>\n",
       "      <th title=\"Description\">Description</th>\n",
       "      <th title=\"Path\">Path</th>\n",
       "      <th title=\"Definition\">Definition</th>\n",
       "      <th title=\"Subdirs\">Subdirs</th>\n",
       "      <th title=\"Local\">Local</th>\n",
       "      <th title=\"Active\">Active</th>\n",
       "      <th title=\"Personal\">Personal</th>\n",
       "      <th title=\"Hidden\">Hidden</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CASUSER(username)</td>\n",
       "      <td>PATH</td>\n",
       "      <td>Personal File System Caslib</td>\n",
       "      <td>/u/username/</td>\n",
       "      <td></td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000507s</span> &#183; <span class=\"cas-memory\">mem 0.0981MB</span></small></p>"
      ],
      "text/plain": [
       "[CASLibInfo]\n",
       "\n",
       "               Name  Type                  Description        Path Definition  \\\n",
       " 0  CASUSER(username)  PATH  Personal File System Caslib  /u/username/              \n",
       " \n",
       "    Subdirs  Local  Active  Personal  Hidden  \n",
       " 0      1.0    0.0     1.0       1.0     0.0  \n",
       "\n",
       "+ Elapsed: 0.000507s, mem: 0.0981mb"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.caslibinfo()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List the items at a path relative to the given CASLib using the **fileinfo** action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; FileInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Permission\">Permission</th>\n",
       "      <th title=\"Owner\">Owner</th>\n",
       "      <th title=\"Group\">Group</th>\n",
       "      <th title=\"Name\">Name</th>\n",
       "      <th title=\"Size of File in Bytes\">Size</th>\n",
       "      <th title=\"Encryption Method\">Encryption</th>\n",
       "      <th title=\"Time\">Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>iris.csv</td>\n",
       "      <td>3716</td>\n",
       "      <td></td>\n",
       "      <td>18Feb2016:17:25:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>cars.csv</td>\n",
       "      <td>42177</td>\n",
       "      <td></td>\n",
       "      <td>18Feb2016:17:25:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>sashelp_class.sashdat</td>\n",
       "      <td>82136</td>\n",
       "      <td>NONE</td>\n",
       "      <td>19Feb2016:17:02:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>foo.csv</td>\n",
       "      <td>3716</td>\n",
       "      <td></td>\n",
       "      <td>06Mar2016:16:59:11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>foo.sashdat</td>\n",
       "      <td>19472</td>\n",
       "      <td>NONE</td>\n",
       "      <td>06Mar2016:16:59:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>foo.xlsx.sashdat</td>\n",
       "      <td>19472</td>\n",
       "      <td>NONE</td>\n",
       "      <td>06Mar2016:17:00:32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>foo.xls.sashdat</td>\n",
       "      <td>19472</td>\n",
       "      <td>NONE</td>\n",
       "      <td>06Mar2016:17:00:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>hmeq_f.sas7bdat</td>\n",
       "      <td>786432</td>\n",
       "      <td></td>\n",
       "      <td>19Nov2015:15:49:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>class.csv</td>\n",
       "      <td>519</td>\n",
       "      <td></td>\n",
       "      <td>14Apr2016:10:39:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>stock_history_small_mv_blanks.csv</td>\n",
       "      <td>2865</td>\n",
       "      <td></td>\n",
       "      <td>15Apr2016:10:20:25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>gettingstarted.csv</td>\n",
       "      <td>5454</td>\n",
       "      <td></td>\n",
       "      <td>01Jul2016:12:00:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>attrition.csv</td>\n",
       "      <td>6231611</td>\n",
       "      <td></td>\n",
       "      <td>11Sep2016:22:39:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>bbq.csv</td>\n",
       "      <td>2875209</td>\n",
       "      <td></td>\n",
       "      <td>29Sep2016:17:27:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>bbq.sashdat</td>\n",
       "      <td>6708576</td>\n",
       "      <td>NONE</td>\n",
       "      <td>30Sep2016:10:35:25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>testout.csv</td>\n",
       "      <td>3716</td>\n",
       "      <td></td>\n",
       "      <td>11Oct2016:14:16:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>karate.csv</td>\n",
       "      <td>494</td>\n",
       "      <td></td>\n",
       "      <td>27Oct2016:11:21:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>bbq-final.csv</td>\n",
       "      <td>3360201</td>\n",
       "      <td></td>\n",
       "      <td>12Oct2016:10:36:55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.0727s</span> &#183; <span class=\"cas-user\">user 0.002s</span> &#183; <span class=\"cas-sys\">sys 0.002s</span> &#183; <span class=\"cas-memory\">mem 0.121MB</span></small></p>"
      ],
      "text/plain": [
       "[FileInfo]\n",
       "\n",
       "     Permission   Owner  Group                               Name     Size  \\\n",
       " 0   -rw-r--r--  username  users                           iris.csv     3716   \n",
       " 1   -rw-r--r--  username  users                           cars.csv    42177   \n",
       " 2   -rw-r--r--  username  users              sashelp_class.sashdat    82136   \n",
       " 3   -rwxr-xr-x  username  users                            foo.csv     3716   \n",
       " 4   -rwxr-xr-x  username  users                        foo.sashdat    19472   \n",
       " 5   -rwxr-xr-x  username  users                   foo.xlsx.sashdat    19472   \n",
       " 6   -rwxr-xr-x  username  users                    foo.xls.sashdat    19472   \n",
       " 7   -rwxr-xr-x  username  users                    hmeq_f.sas7bdat   786432   \n",
       " 8   -rw-r--r--  username  users                          class.csv      519   \n",
       " 9   -rw-r--r--  username  users  stock_history_small_mv_blanks.csv     2865   \n",
       " 10  -rw-r--r--  username  users                 gettingstarted.csv     5454   \n",
       " 11  -rw-r--r--  username  users                      attrition.csv  6231611   \n",
       " 12  -rw-r--r--  username  users                            bbq.csv  2875209   \n",
       " 13  -rwxr-xr-x  username  users                        bbq.sashdat  6708576   \n",
       " 14  -rwxr-xr-x  username  users                        testout.csv     3716   \n",
       " 15  -rw-r--r--  username  users                         karate.csv      494   \n",
       " 16  -rw-r--r--  username  users                      bbq-final.csv  3360201   \n",
       " \n",
       "    Encryption                Time  \n",
       " 0              18Feb2016:17:25:43  \n",
       " 1              18Feb2016:17:25:49  \n",
       " 2        NONE  19Feb2016:17:02:37  \n",
       " 3              06Mar2016:16:59:11  \n",
       " 4        NONE  06Mar2016:16:59:41  \n",
       " 5        NONE  06Mar2016:17:00:32  \n",
       " 6        NONE  06Mar2016:17:00:45  \n",
       " 7              19Nov2015:15:49:15  \n",
       " 8              14Apr2016:10:39:02  \n",
       " 9              15Apr2016:10:20:25  \n",
       " 10             01Jul2016:12:00:38  \n",
       " 11             11Sep2016:22:39:38  \n",
       " 12             29Sep2016:17:27:52  \n",
       " 13       NONE  30Sep2016:10:35:25  \n",
       " 14             11Oct2016:14:16:37  \n",
       " 15             27Oct2016:11:21:08  \n",
       " 16             12Oct2016:10:36:55  \n",
       "\n",
       "+ Elapsed: 0.0727s, user: 0.002s, sys: 0.002s, mem: 0.121mb"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.fileinfo('data', caslib='casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use the **fileinfo** action with the active CASLib (i.e., casuser)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; FileInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Permission\">Permission</th>\n",
       "      <th title=\"Owner\">Owner</th>\n",
       "      <th title=\"Group\">Group</th>\n",
       "      <th title=\"Name\">Name</th>\n",
       "      <th title=\"Size of File in Bytes\">Size</th>\n",
       "      <th title=\"Encryption Method\">Encryption</th>\n",
       "      <th title=\"Time\">Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>iris.csv</td>\n",
       "      <td>3716</td>\n",
       "      <td></td>\n",
       "      <td>18Feb2016:17:25:43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>cars.csv</td>\n",
       "      <td>42177</td>\n",
       "      <td></td>\n",
       "      <td>18Feb2016:17:25:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>sashelp_class.sashdat</td>\n",
       "      <td>82136</td>\n",
       "      <td>NONE</td>\n",
       "      <td>19Feb2016:17:02:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>foo.csv</td>\n",
       "      <td>3716</td>\n",
       "      <td></td>\n",
       "      <td>06Mar2016:16:59:11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>foo.sashdat</td>\n",
       "      <td>19472</td>\n",
       "      <td>NONE</td>\n",
       "      <td>06Mar2016:16:59:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>foo.xlsx.sashdat</td>\n",
       "      <td>19472</td>\n",
       "      <td>NONE</td>\n",
       "      <td>06Mar2016:17:00:32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>foo.xls.sashdat</td>\n",
       "      <td>19472</td>\n",
       "      <td>NONE</td>\n",
       "      <td>06Mar2016:17:00:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>hmeq_f.sas7bdat</td>\n",
       "      <td>786432</td>\n",
       "      <td></td>\n",
       "      <td>19Nov2015:15:49:15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>class.csv</td>\n",
       "      <td>519</td>\n",
       "      <td></td>\n",
       "      <td>14Apr2016:10:39:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>stock_history_small_mv_blanks.csv</td>\n",
       "      <td>2865</td>\n",
       "      <td></td>\n",
       "      <td>15Apr2016:10:20:25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>gettingstarted.csv</td>\n",
       "      <td>5454</td>\n",
       "      <td></td>\n",
       "      <td>01Jul2016:12:00:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>attrition.csv</td>\n",
       "      <td>6231611</td>\n",
       "      <td></td>\n",
       "      <td>11Sep2016:22:39:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>bbq.csv</td>\n",
       "      <td>2875209</td>\n",
       "      <td></td>\n",
       "      <td>29Sep2016:17:27:52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>bbq.sashdat</td>\n",
       "      <td>6708576</td>\n",
       "      <td>NONE</td>\n",
       "      <td>30Sep2016:10:35:25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>-rwxr-xr-x</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>testout.csv</td>\n",
       "      <td>3716</td>\n",
       "      <td></td>\n",
       "      <td>11Oct2016:14:16:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>karate.csv</td>\n",
       "      <td>494</td>\n",
       "      <td></td>\n",
       "      <td>27Oct2016:11:21:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>-rw-r--r--</td>\n",
       "      <td>username</td>\n",
       "      <td>users</td>\n",
       "      <td>bbq-final.csv</td>\n",
       "      <td>3360201</td>\n",
       "      <td></td>\n",
       "      <td>12Oct2016:10:36:55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.00871s</span> &#183; <span class=\"cas-user\">user 0.002s</span> &#183; <span class=\"cas-sys\">sys 0.001s</span> &#183; <span class=\"cas-memory\">mem 0.118MB</span></small></p>"
      ],
      "text/plain": [
       "[FileInfo]\n",
       "\n",
       "     Permission   Owner  Group                               Name     Size  \\\n",
       " 0   -rw-r--r--  username  users                           iris.csv     3716   \n",
       " 1   -rw-r--r--  username  users                           cars.csv    42177   \n",
       " 2   -rw-r--r--  username  users              sashelp_class.sashdat    82136   \n",
       " 3   -rwxr-xr-x  username  users                            foo.csv     3716   \n",
       " 4   -rwxr-xr-x  username  users                        foo.sashdat    19472   \n",
       " 5   -rwxr-xr-x  username  users                   foo.xlsx.sashdat    19472   \n",
       " 6   -rwxr-xr-x  username  users                    foo.xls.sashdat    19472   \n",
       " 7   -rwxr-xr-x  username  users                    hmeq_f.sas7bdat   786432   \n",
       " 8   -rw-r--r--  username  users                          class.csv      519   \n",
       " 9   -rw-r--r--  username  users  stock_history_small_mv_blanks.csv     2865   \n",
       " 10  -rw-r--r--  username  users                 gettingstarted.csv     5454   \n",
       " 11  -rw-r--r--  username  users                      attrition.csv  6231611   \n",
       " 12  -rw-r--r--  username  users                            bbq.csv  2875209   \n",
       " 13  -rwxr-xr-x  username  users                        bbq.sashdat  6708576   \n",
       " 14  -rwxr-xr-x  username  users                        testout.csv     3716   \n",
       " 15  -rw-r--r--  username  users                         karate.csv      494   \n",
       " 16  -rw-r--r--  username  users                      bbq-final.csv  3360201   \n",
       " \n",
       "    Encryption                Time  \n",
       " 0              18Feb2016:17:25:43  \n",
       " 1              18Feb2016:17:25:49  \n",
       " 2        NONE  19Feb2016:17:02:37  \n",
       " 3              06Mar2016:16:59:11  \n",
       " 4        NONE  06Mar2016:16:59:41  \n",
       " 5        NONE  06Mar2016:17:00:32  \n",
       " 6        NONE  06Mar2016:17:00:45  \n",
       " 7              19Nov2015:15:49:15  \n",
       " 8              14Apr2016:10:39:02  \n",
       " 9              15Apr2016:10:20:25  \n",
       " 10             01Jul2016:12:00:38  \n",
       " 11             11Sep2016:22:39:38  \n",
       " 12             29Sep2016:17:27:52  \n",
       " 13       NONE  30Sep2016:10:35:25  \n",
       " 14             11Oct2016:14:16:37  \n",
       " 15             27Oct2016:11:21:08  \n",
       " 16             12Oct2016:10:36:55  \n",
       "\n",
       "+ Elapsed: 0.00871s, user: 0.002s, sys: 0.001s, mem: 0.118mb"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.fileinfo('data')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading Data into a CAS Table\n",
    "\n",
    "Load data from the server-side using the **loadtable** action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ERROR: The table DATA.IRIS already exists in caslib CASUSER(username).\n",
      "ERROR: The action stopped due to errors.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; caslib</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASUSER(username)</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; tableName</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>DATA.IRIS</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; casTable</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASTable('DATA.IRIS', caslib='CASUSER(username)')</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000436s</span> &#183; <span class=\"cas-memory\">mem 0.123MB</span></small></p>"
      ],
      "text/plain": [
       "[caslib]\n",
       "\n",
       " 'CASUSER(username)'\n",
       "\n",
       "[tableName]\n",
       "\n",
       " 'DATA.IRIS'\n",
       "\n",
       "[casTable]\n",
       "\n",
       " CASTable('DATA.IRIS', caslib='CASUSER(username)')\n",
       "\n",
       "+ Elapsed: 0.000436s, mem: 0.123mb"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out = conn.loadtable('data/iris.csv', caslib='casuser')\n",
    "out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Specify an output table name explicitly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NOTE: Cloud Analytic Services made the file data/iris.csv available as table MYDATA in caslib CASUSER(username).\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; caslib</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASUSER(username)</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; tableName</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>MYDATA</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; casTable</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASTable('MYDATA', caslib='CASUSER(username)')</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.111s</span> &#183; <span class=\"cas-user\">user 0.061s</span> &#183; <span class=\"cas-sys\">sys 0.052s</span> &#183; <span class=\"cas-memory\">mem 64.7MB</span></small></p>"
      ],
      "text/plain": [
       "[caslib]\n",
       "\n",
       " 'CASUSER(username)'\n",
       "\n",
       "[tableName]\n",
       "\n",
       " 'MYDATA'\n",
       "\n",
       "[casTable]\n",
       "\n",
       " CASTable('MYDATA', caslib='CASUSER(username)')\n",
       "\n",
       "+ Elapsed: 0.111s, user: 0.061s, sys: 0.052s, mem: 64.7mb"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out = conn.loadtable('data/iris.csv', caslib='casuser',\n",
    "                     casout=dict(name='mydata', caslib='casuser'))\n",
    "out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get information about the table using the **tableinfo** action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; TableInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Name\">Name</th>\n",
       "      <th title=\"Rows\">Rows</th>\n",
       "      <th title=\"Columns\">Columns</th>\n",
       "      <th title=\"Encoding\">Encoding</th>\n",
       "      <th title=\"Created\">CreateTimeFormatted</th>\n",
       "      <th title=\"Last Modified\">ModTimeFormatted</th>\n",
       "      <th title=\"Character Set\">JavaCharSet</th>\n",
       "      <th title=\"CreateTime\">CreateTime</th>\n",
       "      <th title=\"ModTime\">ModTime</th>\n",
       "      <th title=\"Global\">Global</th>\n",
       "      <th title=\"Repeated\">Repeated</th>\n",
       "      <th title=\"View\">View</th>\n",
       "      <th title=\"Loaded Source\">SourceName</th>\n",
       "      <th title=\"Source Caslib\">SourceCaslib</th>\n",
       "      <th title=\"Compressed\">Compressed</th>\n",
       "      <th title=\"Table Creator\">Creator</th>\n",
       "      <th title=\"Last Table Modifier\">Modifier</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DATA.IRIS</td>\n",
       "      <td>150</td>\n",
       "      <td>5</td>\n",
       "      <td>utf-8</td>\n",
       "      <td>01Nov2016:14:42:17</td>\n",
       "      <td>01Nov2016:14:42:17</td>\n",
       "      <td>UTF8</td>\n",
       "      <td>1.793631e+09</td>\n",
       "      <td>1.793631e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>data/iris.csv</td>\n",
       "      <td>CASUSER(username)</td>\n",
       "      <td>0</td>\n",
       "      <td>username</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.00062s</span> &#183; <span class=\"cas-memory\">mem 0.103MB</span></small></p>"
      ],
      "text/plain": [
       "[TableInfo]\n",
       "\n",
       "         Name  Rows  Columns Encoding CreateTimeFormatted    ModTimeFormatted  \\\n",
       " 0  DATA.IRIS   150        5    utf-8  01Nov2016:14:42:17  01Nov2016:14:42:17   \n",
       " \n",
       "   JavaCharSet    CreateTime       ModTime  Global  Repeated  View  \\\n",
       " 0        UTF8  1.793631e+09  1.793631e+09       0         0     0   \n",
       " \n",
       "       SourceName     SourceCaslib  Compressed Creator Modifier  \n",
       " 0  data/iris.csv  CASUSER(username)           0  username           \n",
       "\n",
       "+ Elapsed: 0.00062s, mem: 0.103mb"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.tableinfo(name='data.iris', caslib='casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get information about the table columns using **columninfo**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; ColumnInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Column\">Column</th>\n",
       "      <th title=\"ID\">ID</th>\n",
       "      <th title=\"Type\">Type</th>\n",
       "      <th title=\"RawLength\">RawLength</th>\n",
       "      <th title=\"FormattedLength\">FormattedLength</th>\n",
       "      <th title=\"NFL\">NFL</th>\n",
       "      <th title=\"NFD\">NFD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sepal_length</td>\n",
       "      <td>1</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sepal_width</td>\n",
       "      <td>2</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>petal_length</td>\n",
       "      <td>3</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>petal_width</td>\n",
       "      <td>4</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>species</td>\n",
       "      <td>5</td>\n",
       "      <td>varchar</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000639s</span> &#183; <span class=\"cas-memory\">mem 0.172MB</span></small></p>"
      ],
      "text/plain": [
       "[ColumnInfo]\n",
       "\n",
       "          Column  ID     Type  RawLength  FormattedLength  NFL  NFD\n",
       " 0  sepal_length   1   double          8               12    0    0\n",
       " 1   sepal_width   2   double          8               12    0    0\n",
       " 2  petal_length   3   double          8               12    0    0\n",
       " 3   petal_width   4   double          8               12    0    0\n",
       " 4       species   5  varchar         10               10    0    0\n",
       "\n",
       "+ Elapsed: 0.000639s, mem: 0.172mb"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.columninfo(table=dict(name='data.iris', caslib='casuser'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Displaying Data in a CAS Table\n",
    "\n",
    "Use the **fetch** action to download rows of data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; Fetch</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\"><caption>Selected Rows from Table DATA.IRIS</caption>\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"sepal_length\">sepal_length</th>\n",
       "      <th title=\"sepal_width\">sepal_width</th>\n",
       "      <th title=\"petal_length\">petal_length</th>\n",
       "      <th title=\"petal_width\">petal_width</th>\n",
       "      <th title=\"species\">species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.7</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.6</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.6</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.00379s</span> &#183; <span class=\"cas-user\">user 0.003s</span> &#183; <span class=\"cas-memory\">mem 1.64MB</span></small></p>"
      ],
      "text/plain": [
       "[Fetch]\n",
       "\n",
       " Selected Rows from Table DATA.IRIS\n",
       " \n",
       "    sepal_length  sepal_width  petal_length  petal_width species\n",
       " 0           5.1          3.5           1.4          0.2  setosa\n",
       " 1           4.9          3.0           1.4          0.2  setosa\n",
       " 2           4.7          3.2           1.3          0.2  setosa\n",
       " 3           4.6          3.1           1.5          0.2  setosa\n",
       " 4           5.0          3.6           1.4          0.2  setosa\n",
       "\n",
       "+ Elapsed: 0.00379s, user: 0.003s, mem: 1.64mb"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.fetch(table=dict(name='data.iris', caslib='casuser'), to=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Specify sorting options to get a predictable set of data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; Fetch</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\"><caption>Selected Rows from Table DATA.IRIS</caption>\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"sepal_length\">sepal_length</th>\n",
       "      <th title=\"sepal_width\">sepal_width</th>\n",
       "      <th title=\"petal_length\">petal_length</th>\n",
       "      <th title=\"petal_width\">petal_width</th>\n",
       "      <th title=\"species\">species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4.3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.4</td>\n",
       "      <td>2.9</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.4</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.4</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.5</td>\n",
       "      <td>2.3</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.0172s</span> &#183; <span class=\"cas-user\">user 0.01s</span> &#183; <span class=\"cas-sys\">sys 0.007s</span> &#183; <span class=\"cas-memory\">mem 8.59MB</span></small></p>"
      ],
      "text/plain": [
       "[Fetch]\n",
       "\n",
       " Selected Rows from Table DATA.IRIS\n",
       " \n",
       "    sepal_length  sepal_width  petal_length  petal_width species\n",
       " 0           4.3          3.0           1.1          0.1  setosa\n",
       " 1           4.4          2.9           1.4          0.2  setosa\n",
       " 2           4.4          3.0           1.3          0.2  setosa\n",
       " 3           4.4          3.2           1.3          0.2  setosa\n",
       " 4           4.5          2.3           1.3          0.3  setosa\n",
       "\n",
       "+ Elapsed: 0.0172s, user: 0.01s, sys: 0.007s, mem: 8.59mb"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.fetch(table=dict(name='data.iris', caslib='casuser'), to=5,\n",
    "           sortby=['sepal_length', 'sepal_width'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Computing Simple Statistics\n",
    "\n",
    "Run the **summary** action on the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; Summary</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\"><caption>Descriptive Statistics for DATA.IRIS</caption>\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Analysis Variable\">Column</th>\n",
       "      <th title=\"Min\">Min</th>\n",
       "      <th title=\"Max\">Max</th>\n",
       "      <th title=\"N\">N</th>\n",
       "      <th title=\"Number Missing\">NMiss</th>\n",
       "      <th title=\"Mean\">Mean</th>\n",
       "      <th title=\"Sum\">Sum</th>\n",
       "      <th title=\"Std Dev.\">Std</th>\n",
       "      <th title=\"Std Error\">StdErr</th>\n",
       "      <th title=\"Variance\">Var</th>\n",
       "      <th title=\"Uncorrected Sum of Squares\">USS</th>\n",
       "      <th title=\"Corrected Sum of Squares\">CSS</th>\n",
       "      <th title=\"Coefficient of Variation\">CV</th>\n",
       "      <th title=\"t Value\">TValue</th>\n",
       "      <th title=\"Pr &gt; |t|\">ProbT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sepal_length</td>\n",
       "      <td>4.3</td>\n",
       "      <td>7.9</td>\n",
       "      <td>150.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.843333</td>\n",
       "      <td>876.5</td>\n",
       "      <td>0.828066</td>\n",
       "      <td>0.067611</td>\n",
       "      <td>0.685694</td>\n",
       "      <td>5223.85</td>\n",
       "      <td>102.168333</td>\n",
       "      <td>14.171126</td>\n",
       "      <td>86.425375</td>\n",
       "      <td>3.331256e-129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sepal_width</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.4</td>\n",
       "      <td>150.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.054000</td>\n",
       "      <td>458.1</td>\n",
       "      <td>0.433594</td>\n",
       "      <td>0.035403</td>\n",
       "      <td>0.188004</td>\n",
       "      <td>1427.05</td>\n",
       "      <td>28.012600</td>\n",
       "      <td>14.197587</td>\n",
       "      <td>86.264297</td>\n",
       "      <td>4.374977e-129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>petal_length</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.9</td>\n",
       "      <td>150.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.758667</td>\n",
       "      <td>563.8</td>\n",
       "      <td>1.764420</td>\n",
       "      <td>0.144064</td>\n",
       "      <td>3.113179</td>\n",
       "      <td>2583.00</td>\n",
       "      <td>463.863733</td>\n",
       "      <td>46.942721</td>\n",
       "      <td>26.090198</td>\n",
       "      <td>1.994305e-57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>petal_width</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.5</td>\n",
       "      <td>150.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.198667</td>\n",
       "      <td>179.8</td>\n",
       "      <td>0.763161</td>\n",
       "      <td>0.062312</td>\n",
       "      <td>0.582414</td>\n",
       "      <td>302.30</td>\n",
       "      <td>86.779733</td>\n",
       "      <td>63.667470</td>\n",
       "      <td>19.236588</td>\n",
       "      <td>3.209704e-42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.0252s</span> &#183; <span class=\"cas-user\">user 0.015s</span> &#183; <span class=\"cas-sys\">sys 0.014s</span> &#183; <span class=\"cas-memory\">mem 1.74MB</span></small></p>"
      ],
      "text/plain": [
       "[Summary]\n",
       "\n",
       " Descriptive Statistics for DATA.IRIS\n",
       " \n",
       "          Column  Min  Max      N  NMiss      Mean    Sum       Std    StdErr  \\\n",
       " 0  sepal_length  4.3  7.9  150.0    0.0  5.843333  876.5  0.828066  0.067611   \n",
       " 1   sepal_width  2.0  4.4  150.0    0.0  3.054000  458.1  0.433594  0.035403   \n",
       " 2  petal_length  1.0  6.9  150.0    0.0  3.758667  563.8  1.764420  0.144064   \n",
       " 3   petal_width  0.1  2.5  150.0    0.0  1.198667  179.8  0.763161  0.062312   \n",
       " \n",
       "         Var      USS         CSS         CV     TValue          ProbT  \n",
       " 0  0.685694  5223.85  102.168333  14.171126  86.425375  3.331256e-129  \n",
       " 1  0.188004  1427.05   28.012600  14.197587  86.264297  4.374977e-129  \n",
       " 2  3.113179  2583.00  463.863733  46.942721  26.090198   1.994305e-57  \n",
       " 3  0.582414   302.30   86.779733  63.667470  19.236588   3.209704e-42  \n",
       "\n",
       "+ Elapsed: 0.0252s, user: 0.015s, sys: 0.014s, mem: 1.74mb"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.summary(table=dict(name='data.iris', caslib='casuser'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dropping a CAS Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NOTE: Cloud Analytic Services dropped table data.iris from caslib CASUSER(username).\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000491s</span> &#183; <span class=\"cas-memory\">mem 0.0736MB</span></small></p>"
      ],
      "text/plain": [
       "+ Elapsed: 0.000491s, mem: 0.0736mb"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.droptable('data.iris', caslib='casuser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Active CASLib\n",
    "\n",
    "Use the **caslibinfo** action to display information about CASLibs.  The Active column indicates whether the CASLib is the active CASLib."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; CASLibInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Name\">Name</th>\n",
       "      <th title=\"Type\">Type</th>\n",
       "      <th title=\"Description\">Description</th>\n",
       "      <th title=\"Path\">Path</th>\n",
       "      <th title=\"Definition\">Definition</th>\n",
       "      <th title=\"Subdirs\">Subdirs</th>\n",
       "      <th title=\"Local\">Local</th>\n",
       "      <th title=\"Active\">Active</th>\n",
       "      <th title=\"Personal\">Personal</th>\n",
       "      <th title=\"Hidden\">Hidden</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CASUSER(username)</td>\n",
       "      <td>PATH</td>\n",
       "      <td>Personal File System Caslib</td>\n",
       "      <td>/u/username/</td>\n",
       "      <td></td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000472s</span> &#183; <span class=\"cas-memory\">mem 0.0947MB</span></small></p>"
      ],
      "text/plain": [
       "[CASLibInfo]\n",
       "\n",
       "               Name  Type                  Description        Path Definition  \\\n",
       " 0  CASUSER(username)  PATH  Personal File System Caslib  /u/username/              \n",
       " \n",
       "    Subdirs  Local  Active  Personal  Hidden  \n",
       " 0      1.0    0.0     1.0       1.0     0.0  \n",
       "\n",
       "+ Elapsed: 0.000472s, mem: 0.0947mb"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.caslibinfo()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can get the active CASLib setting using the **getsessopt** action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; caslib</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASUSER(username)</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000211s</span> &#183; <span class=\"cas-memory\">mem 0.0652MB</span></small></p>"
      ],
      "text/plain": [
       "[caslib]\n",
       "\n",
       " 'CASUSER(username)'\n",
       "\n",
       "+ Elapsed: 0.000211s, mem: 0.0652mb"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.getsessopt('caslib')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can set the active CASLib using the **setsessopt** action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ERROR: The value otherlib for session option CASLIB is invalid.\n",
      "ERROR: The action stopped due to errors.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000377s</span> &#183; <span class=\"cas-memory\">mem 0.0909MB</span></small></p>"
      ],
      "text/plain": [
       "+ Elapsed: 0.000377s, mem: 0.0909mb"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.setsessopt(caslib='otherlib')\n",
    "# NOTE: 'CASTestTmp' is now the active caslib.\n",
    "# Out[39]: + Elapsed: 0.000289s, mem: 0.0948mb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uploading Data Files to CAS Tables\n",
    "\n",
    "Use the **upload** method on **CAS** connection objects to upload data from client-side files.  This uploads the file to the server as-is.  It is then parsed on the server."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NOTE: Cloud Analytic Services made the uploaded file available as table IRIS in caslib CASUSER(username).\n",
      "NOTE: The table IRIS has been created in caslib CASUSER(username) from binary data uploaded to Cloud Analytic Services.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; caslib</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASUSER(username)</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; tableName</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>IRIS</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; casTable</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASTable('IRIS', caslib='CASUSER(username)')</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.0999s</span> &#183; <span class=\"cas-user\">user 0.056s</span> &#183; <span class=\"cas-sys\">sys 0.037s</span> &#183; <span class=\"cas-memory\">mem 64.7MB</span></small></p>"
      ],
      "text/plain": [
       "[caslib]\n",
       "\n",
       " 'CASUSER(username)'\n",
       "\n",
       "[tableName]\n",
       "\n",
       " 'IRIS'\n",
       "\n",
       "[casTable]\n",
       "\n",
       " CASTable('IRIS', caslib='CASUSER(username)')\n",
       "\n",
       "+ Elapsed: 0.0999s, user: 0.056s, sys: 0.037s, mem: 64.7mb"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.upload('/u/username/data/iris.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; ColumnInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Column\">Column</th>\n",
       "      <th title=\"ID\">ID</th>\n",
       "      <th title=\"Type\">Type</th>\n",
       "      <th title=\"RawLength\">RawLength</th>\n",
       "      <th title=\"FormattedLength\">FormattedLength</th>\n",
       "      <th title=\"NFL\">NFL</th>\n",
       "      <th title=\"NFD\">NFD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sepal_length</td>\n",
       "      <td>1</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sepal_width</td>\n",
       "      <td>2</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>petal_length</td>\n",
       "      <td>3</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>petal_width</td>\n",
       "      <td>4</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>species</td>\n",
       "      <td>5</td>\n",
       "      <td>varchar</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000685s</span> &#183; <span class=\"cas-memory\">mem 0.173MB</span></small></p>"
      ],
      "text/plain": [
       "[ColumnInfo]\n",
       "\n",
       "          Column  ID     Type  RawLength  FormattedLength  NFL  NFD\n",
       " 0  sepal_length   1   double          8               12    0    0\n",
       " 1   sepal_width   2   double          8               12    0    0\n",
       " 2  petal_length   3   double          8               12    0    0\n",
       " 3   petal_width   4   double          8               12    0    0\n",
       " 4       species   5  varchar         10               10    0    0\n",
       "\n",
       "+ Elapsed: 0.000685s, mem: 0.173mb"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.columninfo(table=dict(name='iris', caslib='casuser'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Specify an explicit table name on **upload**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NOTE: Cloud Analytic Services made the uploaded file available as table MYDATA2 in caslib CASUSER(username).\n",
      "NOTE: The table MYDATA2 has been created in caslib CASUSER(username) from binary data uploaded to Cloud Analytic Services.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; caslib</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASUSER(username)</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; tableName</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>MYDATA2</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; casTable</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASTable('MYDATA2', caslib='CASUSER(username)')</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.0972s</span> &#183; <span class=\"cas-user\">user 0.054s</span> &#183; <span class=\"cas-sys\">sys 0.041s</span> &#183; <span class=\"cas-memory\">mem 64.7MB</span></small></p>"
      ],
      "text/plain": [
       "[caslib]\n",
       "\n",
       " 'CASUSER(username)'\n",
       "\n",
       "[tableName]\n",
       "\n",
       " 'MYDATA2'\n",
       "\n",
       "[casTable]\n",
       "\n",
       " CASTable('MYDATA2', caslib='CASUSER(username)')\n",
       "\n",
       "+ Elapsed: 0.0972s, user: 0.054s, sys: 0.041s, mem: 64.7mb"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.upload('/u/username/data/iris.csv', casout=dict(name='iris2', caslib='casuser'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **upload** method will pass a given **importoptions=** parameter to the underlying **loadtable** action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NOTE: Cloud Analytic Services made the uploaded file available as table IRIS_TSV in caslib CASUSER(username).\n",
      "NOTE: The table IRIS_TSV has been created in caslib CASUSER(username) from binary data uploaded to Cloud Analytic Services.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; caslib</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASUSER(username)</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; tableName</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>IRIS_TSV</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; casTable</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASTable('IRIS_TSV', caslib='CASUSER(username)')</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.115s</span> &#183; <span class=\"cas-user\">user 0.078s</span> &#183; <span class=\"cas-sys\">sys 0.036s</span> &#183; <span class=\"cas-memory\">mem 64.7MB</span></small></p>"
      ],
      "text/plain": [
       "[caslib]\n",
       "\n",
       " 'CASUSER(username)'\n",
       "\n",
       "[tableName]\n",
       "\n",
       " 'IRIS_TSV'\n",
       "\n",
       "[casTable]\n",
       "\n",
       " CASTable('IRIS_TSV', caslib='CASUSER(username)')\n",
       "\n",
       "+ Elapsed: 0.115s, user: 0.078s, sys: 0.036s, mem: 64.7mb"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out = conn.upload('/u/username/data/iris.tsv',\n",
    "                  importoptions=dict(filetype='csv', delimiter='\\t'),\n",
    "                  casout=dict(name='iris_tsv', caslib='casuser'))\n",
    "out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uploading Data from URLs to CAS Tables\n",
    "\n",
    "Rather than specifying a filename, you can specify a URL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NOTE: Cloud Analytic Services made the uploaded file available as table CLASS in caslib CASUSER(username).\n",
      "NOTE: The table CLASS has been created in caslib CASUSER(username) from binary data uploaded to Cloud Analytic Services.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; caslib</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASUSER(username)</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; tableName</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CLASS</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; casTable</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASTable('CLASS', caslib='CASUSER(username)')</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.101s</span> &#183; <span class=\"cas-user\">user 0.057s</span> &#183; <span class=\"cas-sys\">sys 0.04s</span> &#183; <span class=\"cas-memory\">mem 64.7MB</span></small></p>"
      ],
      "text/plain": [
       "[caslib]\n",
       "\n",
       " 'CASUSER(username)'\n",
       "\n",
       "[tableName]\n",
       "\n",
       " 'CLASS'\n",
       "\n",
       "[casTable]\n",
       "\n",
       " CASTable('CLASS', caslib='CASUSER(username)')\n",
       "\n",
       "+ Elapsed: 0.101s, user: 0.057s, sys: 0.04s, mem: 64.7mb"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.upload('https://github.com/sassoftware/' \n",
    "            'sas-viya-programming/blob/master/data/class.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uploading Data from a Pandas DataFrame to a CAS Table\n",
    "\n",
    "In addition to files, you can upload Pandas **DataFrames**.  Note, however, that the **DataFrame** will be exported to a CSV file, then uploaded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sepal_length</th>\n",
       "      <th>sepal_width</th>\n",
       "      <th>petal_length</th>\n",
       "      <th>petal_width</th>\n",
       "      <th>species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.7</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.6</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.6</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sepal_length  sepal_width  petal_length  petal_width species\n",
       "0           5.1          3.5           1.4          0.2  setosa\n",
       "1           4.9          3.0           1.4          0.2  setosa\n",
       "2           4.7          3.2           1.3          0.2  setosa\n",
       "3           4.6          3.1           1.5          0.2  setosa\n",
       "4           5.0          3.6           1.4          0.2  setosa"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('/u/username/data/iris.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NOTE: Cloud Analytic Services made the uploaded file available as table DF_IRIS in caslib CASUSER(username).\n",
      "NOTE: The table DF_IRIS has been created in caslib CASUSER(username) from binary data uploaded to Cloud Analytic Services.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; caslib</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASUSER(username)</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; tableName</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>DF_IRIS</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; casTable</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASTable('DF_IRIS', caslib='CASUSER(username)')</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.0994s</span> &#183; <span class=\"cas-user\">user 0.054s</span> &#183; <span class=\"cas-sys\">sys 0.039s</span> &#183; <span class=\"cas-memory\">mem 64.7MB</span></small></p>"
      ],
      "text/plain": [
       "[caslib]\n",
       "\n",
       " 'CASUSER(username)'\n",
       "\n",
       "[tableName]\n",
       "\n",
       " 'DF_IRIS'\n",
       "\n",
       "[casTable]\n",
       "\n",
       " CASTable('DF_IRIS', caslib='CASUSER(username)')\n",
       "\n",
       "+ Elapsed: 0.0994s, user: 0.054s, sys: 0.039s, mem: 64.7mb"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.upload(df, casout=dict(name='iris_df', caslib='casuser'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; Fetch</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\"><caption>Selected Rows from Table DF_IRIS</caption>\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"sepal_length\">sepal_length</th>\n",
       "      <th title=\"sepal_width\">sepal_width</th>\n",
       "      <th title=\"petal_length\">petal_length</th>\n",
       "      <th title=\"petal_width\">petal_width</th>\n",
       "      <th title=\"species\">species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.7</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.6</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.6</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.00324s</span> &#183; <span class=\"cas-user\">user 0.001s</span> &#183; <span class=\"cas-sys\">sys 0.002s</span> &#183; <span class=\"cas-memory\">mem 1.64MB</span></small></p>"
      ],
      "text/plain": [
       "[Fetch]\n",
       "\n",
       " Selected Rows from Table DF_IRIS\n",
       " \n",
       "    sepal_length  sepal_width  petal_length  petal_width species\n",
       " 0           5.1          3.5           1.4          0.2  setosa\n",
       " 1           4.9          3.0           1.4          0.2  setosa\n",
       " 2           4.7          3.2           1.3          0.2  setosa\n",
       " 3           4.6          3.1           1.5          0.2  setosa\n",
       " 4           5.0          3.6           1.4          0.2  setosa\n",
       "\n",
       "+ Elapsed: 0.00324s, user: 0.001s, sys: 0.002s, mem: 1.64mb"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.fetch(table=dict(name='iris_df', caslib='casuser'), to=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using Data Message Handlers\n",
    "\n",
    "Data message handlers allow you to write custom data loaders."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from swat.cas import datamsghandlers as dmh"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Display all of the SWAT-supplied data message handler subclasses."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[swat.cas.datamsghandlers.PandasDataFrame, swat.cas.datamsghandlers.DBAPI]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dmh.CASDataMsgHandler.__subclasses__()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[swat.cas.datamsghandlers.JSON,\n",
       " swat.cas.datamsghandlers.CSV,\n",
       " swat.cas.datamsghandlers.Clipboard,\n",
       " swat.cas.datamsghandlers.Text,\n",
       " swat.cas.datamsghandlers.SQLQuery,\n",
       " swat.cas.datamsghandlers.SAS7BDAT,\n",
       " swat.cas.datamsghandlers.HTML,\n",
       " swat.cas.datamsghandlers.Excel,\n",
       " swat.cas.datamsghandlers.FWF,\n",
       " swat.cas.datamsghandlers.SQLTable]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dmh.PandasDataFrame.__subclasses__()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The HTML Data Message Handler"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "htmldmh = dmh.HTML('https://www.fdic.gov/bank/' +         \n",
    "                   'individual/failed/banklist.html', index=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Display the **addtable** parameters created by the HTML data message handler."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'datamsghandler': <swat.cas.datamsghandlers.HTML at 0x7f638ca7a630>,\n",
       " 'reclen': 104,\n",
       " 'vars': [{'length': 16,\n",
       "   'name': 'Bank Name',\n",
       "   'offset': 0,\n",
       "   'rtype': 'CHAR',\n",
       "   'type': 'VARCHAR'},\n",
       "  {'length': 16,\n",
       "   'name': 'City',\n",
       "   'offset': 16,\n",
       "   'rtype': 'CHAR',\n",
       "   'type': 'VARCHAR'},\n",
       "  {'length': 16,\n",
       "   'name': 'ST',\n",
       "   'offset': 32,\n",
       "   'rtype': 'CHAR',\n",
       "   'type': 'VARCHAR'},\n",
       "  {'length': 8,\n",
       "   'name': 'CERT',\n",
       "   'offset': 48,\n",
       "   'rtype': 'NUMERIC',\n",
       "   'type': 'INT64'},\n",
       "  {'length': 16,\n",
       "   'name': 'Acquiring Institution',\n",
       "   'offset': 56,\n",
       "   'rtype': 'CHAR',\n",
       "   'type': 'VARCHAR'},\n",
       "  {'length': 16,\n",
       "   'name': 'Closing Date',\n",
       "   'offset': 72,\n",
       "   'rtype': 'CHAR',\n",
       "   'type': 'VARCHAR'},\n",
       "  {'length': 16,\n",
       "   'name': 'Updated Date',\n",
       "   'offset': 88,\n",
       "   'rtype': 'CHAR',\n",
       "   'type': 'VARCHAR'}]}"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "htmldmh.args.addtable"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Call the **addtable** action using the generated parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "out = conn.addtable(table='banklist', caslib='casuser',\n",
    "                    **htmldmh.args.addtable)\n",
    "out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; ColumnInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Column\">Column</th>\n",
       "      <th title=\"ID\">ID</th>\n",
       "      <th title=\"Type\">Type</th>\n",
       "      <th title=\"RawLength\">RawLength</th>\n",
       "      <th title=\"FormattedLength\">FormattedLength</th>\n",
       "      <th title=\"NFL\">NFL</th>\n",
       "      <th title=\"NFD\">NFD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bank Name</td>\n",
       "      <td>1</td>\n",
       "      <td>varchar</td>\n",
       "      <td>90</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City</td>\n",
       "      <td>2</td>\n",
       "      <td>varchar</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ST</td>\n",
       "      <td>3</td>\n",
       "      <td>varchar</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CERT</td>\n",
       "      <td>4</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Acquiring Institution</td>\n",
       "      <td>5</td>\n",
       "      <td>varchar</td>\n",
       "      <td>65</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Closing Date</td>\n",
       "      <td>6</td>\n",
       "      <td>varchar</td>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Updated Date</td>\n",
       "      <td>7</td>\n",
       "      <td>varchar</td>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000717s</span> &#183; <span class=\"cas-memory\">mem 0.164MB</span></small></p>"
      ],
      "text/plain": [
       "[ColumnInfo]\n",
       "\n",
       "                   Column  ID     Type  RawLength  FormattedLength  NFL  NFD\n",
       " 0              Bank Name   1  varchar         90               90    0    0\n",
       " 1                   City   2  varchar         17               17    0    0\n",
       " 2                     ST   3  varchar          2                2    0    0\n",
       " 3                   CERT   4    int64          8               12    0    0\n",
       " 4  Acquiring Institution   5  varchar         65               65    0    0\n",
       " 5           Closing Date   6  varchar         18               18    0    0\n",
       " 6           Updated Date   7  varchar         18               18    0    0\n",
       "\n",
       "+ Elapsed: 0.000717s, mem: 0.164mb"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.columninfo(table=dict(name='banklist', caslib='casuser'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Parse the dates in columns 5 and 6.  Use the **replace=** option in the new **addtable** call to replace the existing table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "htmldmh = dmh.HTML('https://www.fdic.gov/bank/' +\n",
    "                   'individual/failed/banklist.html',\n",
    "                   index=0, parse_dates=[5, 6])\n",
    "\n",
    "out = conn.addtable(table='banklist', caslib='casuser',\n",
    "                            replace=True,\n",
    "                            **htmldmh.args.addtable)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; ColumnInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Column\">Column</th>\n",
       "      <th title=\"ID\">ID</th>\n",
       "      <th title=\"Type\">Type</th>\n",
       "      <th title=\"RawLength\">RawLength</th>\n",
       "      <th title=\"FormattedLength\">FormattedLength</th>\n",
       "      <th title=\"Format\">Format</th>\n",
       "      <th title=\"NFL\">NFL</th>\n",
       "      <th title=\"NFD\">NFD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bank Name</td>\n",
       "      <td>1</td>\n",
       "      <td>varchar</td>\n",
       "      <td>90</td>\n",
       "      <td>90</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>City</td>\n",
       "      <td>2</td>\n",
       "      <td>varchar</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ST</td>\n",
       "      <td>3</td>\n",
       "      <td>varchar</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CERT</td>\n",
       "      <td>4</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Acquiring Institution</td>\n",
       "      <td>5</td>\n",
       "      <td>varchar</td>\n",
       "      <td>65</td>\n",
       "      <td>65</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Closing Date</td>\n",
       "      <td>6</td>\n",
       "      <td>datetime</td>\n",
       "      <td>8</td>\n",
       "      <td>20</td>\n",
       "      <td>DATETIME</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Updated Date</td>\n",
       "      <td>7</td>\n",
       "      <td>datetime</td>\n",
       "      <td>8</td>\n",
       "      <td>20</td>\n",
       "      <td>DATETIME</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000652s</span> &#183; <span class=\"cas-memory\">mem 0.167MB</span></small></p>"
      ],
      "text/plain": [
       "[ColumnInfo]\n",
       "\n",
       "                   Column  ID      Type  RawLength  FormattedLength    Format  \\\n",
       " 0              Bank Name   1   varchar         90               90             \n",
       " 1                   City   2   varchar         17               17             \n",
       " 2                     ST   3   varchar          2                2             \n",
       " 3                   CERT   4     int64          8               12             \n",
       " 4  Acquiring Institution   5   varchar         65               65             \n",
       " 5           Closing Date   6  datetime          8               20  DATETIME   \n",
       " 6           Updated Date   7  datetime          8               20  DATETIME   \n",
       " \n",
       "    NFL  NFD  \n",
       " 0    0    0  \n",
       " 1    0    0  \n",
       " 2    0    0  \n",
       " 3    0    0  \n",
       " 4    0    0  \n",
       " 5    0    0  \n",
       " 6    0    0  \n",
       "\n",
       "+ Elapsed: 0.000652s, mem: 0.167mb"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.columninfo(table=dict(name='banklist', \n",
    "                           caslib='casuser'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fetch a few rows of the data using **sastypes=False** so that we get actual dates in the resulting **DataFrame**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; Fetch</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\"><caption>Selected Rows from Table BANKLIST</caption>\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Bank Name\">Bank Name</th>\n",
       "      <th title=\"City\">City</th>\n",
       "      <th title=\"ST\">ST</th>\n",
       "      <th title=\"CERT\">CERT</th>\n",
       "      <th title=\"Acquiring Institution\">Acquiring Institution</th>\n",
       "      <th title=\"Closing Date\">Closing Date</th>\n",
       "      <th title=\"Updated Date\">Updated Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Allied Bank</td>\n",
       "      <td>Mulberry</td>\n",
       "      <td>AR</td>\n",
       "      <td>91</td>\n",
       "      <td>Today's Bank</td>\n",
       "      <td>2016-09-23</td>\n",
       "      <td>2016-10-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Woodbury Banking Company</td>\n",
       "      <td>Woodbury</td>\n",
       "      <td>GA</td>\n",
       "      <td>11297</td>\n",
       "      <td>United Bank</td>\n",
       "      <td>2016-08-19</td>\n",
       "      <td>2016-10-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>First CornerStone Bank</td>\n",
       "      <td>King of Prussia</td>\n",
       "      <td>PA</td>\n",
       "      <td>35312</td>\n",
       "      <td>First-Citizens Bank &amp; Trust Company</td>\n",
       "      <td>2016-05-06</td>\n",
       "      <td>2016-09-06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.00392s</span> &#183; <span class=\"cas-user\">user 0.003s</span> &#183; <span class=\"cas-memory\">mem 1.69MB</span></small></p>"
      ],
      "text/plain": [
       "[Fetch]\n",
       "\n",
       " Selected Rows from Table BANKLIST\n",
       " \n",
       "                       Bank Name             City  ST   CERT  \\\n",
       " 0                   Allied Bank         Mulberry  AR     91   \n",
       " 1  The Woodbury Banking Company         Woodbury  GA  11297   \n",
       " 2        First CornerStone Bank  King of Prussia  PA  35312   \n",
       " \n",
       "                  Acquiring Institution Closing Date Updated Date  \n",
       " 0                         Today's Bank   2016-09-23   2016-10-17  \n",
       " 1                          United Bank   2016-08-19   2016-10-17  \n",
       " 2  First-Citizens Bank & Trust Company   2016-05-06   2016-09-06  \n",
       "\n",
       "+ Elapsed: 0.00392s, user: 0.003s, mem: 1.69mb"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.fetch(table=dict(name='banklist', caslib='casuser'), \n",
    "           sastypes=False, to=3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Excel Data Message Handler"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "exceldmh = dmh.Excel('http://www.fsa.usda.gov/Internet/' +  \n",
    "                     'FSA_File/disaster_cty_list_ytd_14.xls')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add the data to the server."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "out = conn.addtable(table='crops', caslib='casuser', \n",
    "                    **exceldmh.args.addtable)\n",
    "out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; ColumnInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Column\">Column</th>\n",
       "      <th title=\"ID\">ID</th>\n",
       "      <th title=\"Type\">Type</th>\n",
       "      <th title=\"RawLength\">RawLength</th>\n",
       "      <th title=\"FormattedLength\">FormattedLength</th>\n",
       "      <th title=\"Format\">Format</th>\n",
       "      <th title=\"NFL\">NFL</th>\n",
       "      <th title=\"NFD\">NFD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FIPS</td>\n",
       "      <td>1</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>County</td>\n",
       "      <td>2</td>\n",
       "      <td>varchar</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>State</td>\n",
       "      <td>3</td>\n",
       "      <td>varchar</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Designation Code</td>\n",
       "      <td>4</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Designation Number</td>\n",
       "      <td>5</td>\n",
       "      <td>varchar</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DROUGHT</td>\n",
       "      <td>6</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>FLOOD, Flash flooding</td>\n",
       "      <td>7</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Excessive rain, moisture, humidity</td>\n",
       "      <td>8</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Severe Storms, thunderstorms</td>\n",
       "      <td>9</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Ground Saturation\\nStanding Water</td>\n",
       "      <td>10</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Hail</td>\n",
       "      <td>11</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Wind, High Winds</td>\n",
       "      <td>12</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Fire, Wildfire</td>\n",
       "      <td>13</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Heat, Excessive heat\\nHigh temp. (incl. low hu...</td>\n",
       "      <td>14</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Winter Storms, Ice Storms, Snow, Blizzard</td>\n",
       "      <td>15</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Frost, FREEZE</td>\n",
       "      <td>16</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Hurricanes, Typhoons, Tropical Storms</td>\n",
       "      <td>17</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Tornadoes</td>\n",
       "      <td>18</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Volcano</td>\n",
       "      <td>19</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Mudslides, Debris Flows, Landslides</td>\n",
       "      <td>20</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Heavy Surf</td>\n",
       "      <td>21</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Ice Jams</td>\n",
       "      <td>22</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Insects</td>\n",
       "      <td>23</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Tidal Surges</td>\n",
       "      <td>24</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Cold, wet weather</td>\n",
       "      <td>25</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Cool/Cold, Below-normal Temperatures</td>\n",
       "      <td>26</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Lightning</td>\n",
       "      <td>27</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Disease</td>\n",
       "      <td>28</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Approval date</td>\n",
       "      <td>29</td>\n",
       "      <td>varchar</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Begin Date</td>\n",
       "      <td>30</td>\n",
       "      <td>datetime</td>\n",
       "      <td>8</td>\n",
       "      <td>20</td>\n",
       "      <td>DATETIME</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>End Date</td>\n",
       "      <td>31</td>\n",
       "      <td>varchar</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Description of disaster</td>\n",
       "      <td>32</td>\n",
       "      <td>varchar</td>\n",
       "      <td>251</td>\n",
       "      <td>251</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>CROP DISASTER YEAR</td>\n",
       "      <td>33</td>\n",
       "      <td>int64</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Unnamed: 33</td>\n",
       "      <td>34</td>\n",
       "      <td>varchar</td>\n",
       "      <td>101</td>\n",
       "      <td>101</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Unnamed: 34</td>\n",
       "      <td>35</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000753s</span> &#183; <span class=\"cas-memory\">mem 0.167MB</span></small></p>"
      ],
      "text/plain": [
       "[ColumnInfo]\n",
       "\n",
       "                                                Column  ID      Type  \\\n",
       " 0                                                FIPS   1     int64   \n",
       " 1                                              County   2   varchar   \n",
       " 2                                               State   3   varchar   \n",
       " 3                                    Designation Code   4     int64   \n",
       " 4                                  Designation Number   5   varchar   \n",
       " 5                                             DROUGHT   6     int64   \n",
       " 6                               FLOOD, Flash flooding   7     int64   \n",
       " 7                  Excessive rain, moisture, humidity   8     int64   \n",
       " 8                        Severe Storms, thunderstorms   9     int64   \n",
       " 9                   Ground Saturation\\nStanding Water  10     int64   \n",
       " 10                                               Hail  11     int64   \n",
       " 11                                   Wind, High Winds  12     int64   \n",
       " 12                                     Fire, Wildfire  13     int64   \n",
       " 13  Heat, Excessive heat\\nHigh temp. (incl. low hu...  14     int64   \n",
       " 14          Winter Storms, Ice Storms, Snow, Blizzard  15     int64   \n",
       " 15                                      Frost, FREEZE  16     int64   \n",
       " 16              Hurricanes, Typhoons, Tropical Storms  17     int64   \n",
       " 17                                          Tornadoes  18     int64   \n",
       " 18                                            Volcano  19     int64   \n",
       " 19                Mudslides, Debris Flows, Landslides  20     int64   \n",
       " 20                                         Heavy Surf  21     int64   \n",
       " 21                                           Ice Jams  22     int64   \n",
       " 22                                            Insects  23     int64   \n",
       " 23                                       Tidal Surges  24     int64   \n",
       " 24                                  Cold, wet weather  25     int64   \n",
       " 25               Cool/Cold, Below-normal Temperatures  26     int64   \n",
       " 26                                          Lightning  27     int64   \n",
       " 27                                            Disease  28     int64   \n",
       " 28                                      Approval date  29   varchar   \n",
       " 29                                         Begin Date  30  datetime   \n",
       " 30                                           End Date  31   varchar   \n",
       " 31                            Description of disaster  32   varchar   \n",
       " 32                                 CROP DISASTER YEAR  33     int64   \n",
       " 33                                        Unnamed: 33  34   varchar   \n",
       " 34                                        Unnamed: 34  35    double   \n",
       " \n",
       "     RawLength  FormattedLength    Format  NFL  NFD  \n",
       " 0           8               12              0    0  \n",
       " 1          17               17              0    0  \n",
       " 2          14               14              0    0  \n",
       " 3           8               12              0    0  \n",
       " 4           5                5              0    0  \n",
       " 5           8               12              0    0  \n",
       " 6           8               12              0    0  \n",
       " 7           8               12              0    0  \n",
       " 8           8               12              0    0  \n",
       " 9           8               12              0    0  \n",
       " 10          8               12              0    0  \n",
       " 11          8               12              0    0  \n",
       " 12          8               12              0    0  \n",
       " 13          8               12              0    0  \n",
       " 14          8               12              0    0  \n",
       " 15          8               12              0    0  \n",
       " 16          8               12              0    0  \n",
       " 17          8               12              0    0  \n",
       " 18          8               12              0    0  \n",
       " 19          8               12              0    0  \n",
       " 20          8               12              0    0  \n",
       " 21          8               12              0    0  \n",
       " 22          8               12              0    0  \n",
       " 23          8               12              0    0  \n",
       " 24          8               12              0    0  \n",
       " 25          8               12              0    0  \n",
       " 26          8               12              0    0  \n",
       " 27          8               12              0    0  \n",
       " 28          0                0              0    0  \n",
       " 29          8               20  DATETIME    0    0  \n",
       " 30         10               10              0    0  \n",
       " 31        251              251              0    0  \n",
       " 32          8               12              0    0  \n",
       " 33        101              101              0    0  \n",
       " 34          8               12              0    0  \n",
       "\n",
       "+ Elapsed: 0.000753s, mem: 0.167mb"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.columninfo(table=dict(name='crops', caslib='casuser'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The PandasDataFrame Data Message Handler"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read the Excel file into a Pandas **DataFrame**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "exceldf = pd.read_excel('http://www.fsa.usda.gov/Internet/' + \n",
    "                        'FSA_File/disaster_cty_list_ytd_14.xls')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a **PandasDataFrame** data message handler."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "exceldmh = dmh.PandasDataFrame(exceldf)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add the table to the server."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "out = conn.addtable(table='dfcrops', caslib='casuser', \n",
    "                    **exceldmh.args.addtable)\n",
    "out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using Data Message Handlers with Databases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import csv\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create an in-memory database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sqlc = sqlite3.connect('iris.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cur = sqlc.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f638ab94f10>"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('''CREATE TABLE iris (sepal_length REAL, \n",
    "                                           sepal_width REAL, \n",
    "                                           petal_length REAL, \n",
    "                                           petal_width REAL, \n",
    "                                           species CHAR(10));''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Parse the iris CSV file and format it as tuples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with open('/u/username/data/iris.csv', 'r') as iris:\n",
    "    data = csv.DictReader(iris)\n",
    "    rows = [(x['sepal_length'], \n",
    "             x['sepal_width'], \n",
    "             x['petal_length'], \n",
    "             x['petal_width'], \n",
    "             x['species']) for x in data]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the data into the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f638ab94f10>"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.executemany('''INSERT INTO iris (sepal_length, \n",
    "                                              sepal_width, \n",
    "                                              petal_length, \n",
    "                                              petal_width, \n",
    "                                              species) \n",
    "                            VALUES (?, ?, ?, ?, ?);''', rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sqlc.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Verify that the data looks correct."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f638ab94f10>"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT * from iris')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(5.1, 3.5, 1.4, 0.2, 'setosa'),\n",
       " (4.9, 3.0, 1.4, 0.2, 'setosa'),\n",
       " (4.7, 3.2, 1.3, 0.2, 'setosa'),\n",
       " (4.6, 3.1, 1.5, 0.2, 'setosa'),\n",
       " (5.0, 3.6, 1.4, 0.2, 'setosa')]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.fetchmany(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create an SQLAlchemy database engine."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "eng = dmh.SQLTable.create_engine('sqlite:///iris.db')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create the data message handler."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sqldmh = dmh.SQLTable('iris', eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the database into CAS."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "out = conn.addtable(table='iris_sql', caslib='casuser', \n",
    "                    **sqldmh.args.addtable)\n",
    "out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check the data in the server."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; ColumnInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Column\">Column</th>\n",
       "      <th title=\"ID\">ID</th>\n",
       "      <th title=\"Type\">Type</th>\n",
       "      <th title=\"RawLength\">RawLength</th>\n",
       "      <th title=\"FormattedLength\">FormattedLength</th>\n",
       "      <th title=\"NFL\">NFL</th>\n",
       "      <th title=\"NFD\">NFD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sepal_length</td>\n",
       "      <td>1</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sepal_width</td>\n",
       "      <td>2</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>petal_length</td>\n",
       "      <td>3</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>petal_width</td>\n",
       "      <td>4</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>species</td>\n",
       "      <td>5</td>\n",
       "      <td>varchar</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000683s</span> &#183; <span class=\"cas-user\">user 0.001s</span> &#183; <span class=\"cas-memory\">mem 0.173MB</span></small></p>"
      ],
      "text/plain": [
       "[ColumnInfo]\n",
       "\n",
       "          Column  ID     Type  RawLength  FormattedLength  NFL  NFD\n",
       " 0  sepal_length   1   double          8               12    0    0\n",
       " 1   sepal_width   2   double          8               12    0    0\n",
       " 2  petal_length   3   double          8               12    0    0\n",
       " 3   petal_width   4   double          8               12    0    0\n",
       " 4       species   5  varchar         10               10    0    0\n",
       "\n",
       "+ Elapsed: 0.000683s, user: 0.001s, mem: 0.173mb"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.columninfo(table=dict(name='iris_sql', caslib='casuser'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; Fetch</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\"><caption>Selected Rows from Table SQLIRIS</caption>\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"sepal_length\">sepal_length</th>\n",
       "      <th title=\"sepal_width\">sepal_width</th>\n",
       "      <th title=\"petal_length\">petal_length</th>\n",
       "      <th title=\"petal_width\">petal_width</th>\n",
       "      <th title=\"species\">species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.7</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.6</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.6</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.00354s</span> &#183; <span class=\"cas-user\">user 0.001s</span> &#183; <span class=\"cas-sys\">sys 0.002s</span> &#183; <span class=\"cas-memory\">mem 1.64MB</span></small></p>"
      ],
      "text/plain": [
       "[Fetch]\n",
       "\n",
       " Selected Rows from Table SQLIRIS\n",
       " \n",
       "    sepal_length  sepal_width  petal_length  petal_width species\n",
       " 0           5.1          3.5           1.4          0.2  setosa\n",
       " 1           4.9          3.0           1.4          0.2  setosa\n",
       " 2           4.7          3.2           1.3          0.2  setosa\n",
       " 3           4.6          3.1           1.5          0.2  setosa\n",
       " 4           5.0          3.6           1.4          0.2  setosa\n",
       "\n",
       "+ Elapsed: 0.00354s, user: 0.001s, sys: 0.002s, mem: 1.64mb"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.fetch(table=dict(name='iris_sql', caslib='casuser'), to=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set up a query to use with the data message handler."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sqldmh = dmh.SQLQuery('''SELECT * FROM iris \n",
    "                             WHERE species = \"versicolor\" \n",
    "                                   AND sepal_length > 6.6''', eng)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the result of the query into CAS."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "out = conn.addtable(table='iris_sql2', caslib='casuser', \n",
    "                    **sqldmh.args.addtable)\n",
    "out"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check the data on the server."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; Fetch</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\"><caption>Selected Rows from Table SQLIRIS2</caption>\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"sepal_length\">sepal_length</th>\n",
       "      <th title=\"sepal_width\">sepal_width</th>\n",
       "      <th title=\"petal_length\">petal_length</th>\n",
       "      <th title=\"petal_width\">petal_width</th>\n",
       "      <th title=\"species\">species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7.0</td>\n",
       "      <td>3.2</td>\n",
       "      <td>4.7</td>\n",
       "      <td>1.4</td>\n",
       "      <td>versicolor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6.9</td>\n",
       "      <td>3.1</td>\n",
       "      <td>4.9</td>\n",
       "      <td>1.5</td>\n",
       "      <td>versicolor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6.7</td>\n",
       "      <td>3.1</td>\n",
       "      <td>4.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>versicolor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6.8</td>\n",
       "      <td>2.8</td>\n",
       "      <td>4.8</td>\n",
       "      <td>1.4</td>\n",
       "      <td>versicolor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.7</td>\n",
       "      <td>versicolor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6.7</td>\n",
       "      <td>3.1</td>\n",
       "      <td>4.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>versicolor</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.00349s</span> &#183; <span class=\"cas-user\">user 0.004s</span> &#183; <span class=\"cas-memory\">mem 1.64MB</span></small></p>"
      ],
      "text/plain": [
       "[Fetch]\n",
       "\n",
       " Selected Rows from Table SQLIRIS2\n",
       " \n",
       "    sepal_length  sepal_width  petal_length  petal_width     species\n",
       " 0           7.0          3.2           4.7          1.4  versicolor\n",
       " 1           6.9          3.1           4.9          1.5  versicolor\n",
       " 2           6.7          3.1           4.4          1.4  versicolor\n",
       " 3           6.8          2.8           4.8          1.4  versicolor\n",
       " 4           6.7          3.0           5.0          1.7  versicolor\n",
       " 5           6.7          3.1           4.7          1.5  versicolor\n",
       "\n",
       "+ Elapsed: 0.00349s, user: 0.004s, mem: 1.64mb"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.fetch(table=dict(name='iris_sql2', caslib='casuser'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Streaming Data from a Database into a CAS Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sqlc = sqlite3.connect('iris.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "c = sqlc.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Execute a query on the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f6389f60420>"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.execute('SELECT * FROM iris')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a **DBAPI** data message handler to stream the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "dbdmh = dmh.DBAPI(sqlite3, c, nrecs=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run the **addtable** action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; caslib</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASUSER(username)</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; tableName</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>DBIRIS</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; casTable</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASTable('DBIRIS', caslib='CASUSER(username)')</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.0286s</span> &#183; <span class=\"cas-user\">user 0.002s</span> &#183; <span class=\"cas-memory\">mem 0.236MB</span></small></p>"
      ],
      "text/plain": [
       "[caslib]\n",
       "\n",
       " 'CASUSER(username)'\n",
       "\n",
       "[tableName]\n",
       "\n",
       " 'DBIRIS'\n",
       "\n",
       "[casTable]\n",
       "\n",
       " CASTable('DBIRIS', caslib='CASUSER(username)')\n",
       "\n",
       "+ Elapsed: 0.0286s, user: 0.002s, mem: 0.236mb"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.addtable(table='iris_db', caslib='casuser', \n",
    "              **dbdmh.args.addtable)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Verify the data on the server."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; ColumnInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Column\">Column</th>\n",
       "      <th title=\"ID\">ID</th>\n",
       "      <th title=\"Type\">Type</th>\n",
       "      <th title=\"RawLength\">RawLength</th>\n",
       "      <th title=\"FormattedLength\">FormattedLength</th>\n",
       "      <th title=\"NFL\">NFL</th>\n",
       "      <th title=\"NFD\">NFD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sepal_length</td>\n",
       "      <td>1</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sepal_width</td>\n",
       "      <td>2</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>petal_length</td>\n",
       "      <td>3</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>petal_width</td>\n",
       "      <td>4</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>species</td>\n",
       "      <td>5</td>\n",
       "      <td>varchar</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000629s</span> &#183; <span class=\"cas-memory\">mem 0.172MB</span></small></p>"
      ],
      "text/plain": [
       "[ColumnInfo]\n",
       "\n",
       "          Column  ID     Type  RawLength  FormattedLength  NFL  NFD\n",
       " 0  sepal_length   1   double          8               12    0    0\n",
       " 1   sepal_width   2   double          8               12    0    0\n",
       " 2  petal_length   3   double          8               12    0    0\n",
       " 3   petal_width   4   double          8               12    0    0\n",
       " 4       species   5  varchar         10               10    0    0\n",
       "\n",
       "+ Elapsed: 0.000629s, mem: 0.172mb"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.columninfo(table=dict(name='iris_db', caslib='casuser'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; Fetch</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\"><caption>Selected Rows from Table DBIRIS</caption>\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"sepal_length\">sepal_length</th>\n",
       "      <th title=\"sepal_width\">sepal_width</th>\n",
       "      <th title=\"petal_length\">petal_length</th>\n",
       "      <th title=\"petal_width\">petal_width</th>\n",
       "      <th title=\"species\">species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.7</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.6</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.6</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.00349s</span> &#183; <span class=\"cas-user\">user 0.002s</span> &#183; <span class=\"cas-sys\">sys 0.001s</span> &#183; <span class=\"cas-memory\">mem 1.64MB</span></small></p>"
      ],
      "text/plain": [
       "[Fetch]\n",
       "\n",
       " Selected Rows from Table DBIRIS\n",
       " \n",
       "    sepal_length  sepal_width  petal_length  petal_width species\n",
       " 0           5.1          3.5           1.4          0.2  setosa\n",
       " 1           4.9          3.0           1.4          0.2  setosa\n",
       " 2           4.7          3.2           1.3          0.2  setosa\n",
       " 3           4.6          3.1           1.5          0.2  setosa\n",
       " 4           5.0          3.6           1.4          0.2  setosa\n",
       "\n",
       "+ Elapsed: 0.00349s, user: 0.002s, sys: 0.001s, mem: 1.64mb"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.fetch(table=dict(name='iris_db', caslib='casuser'), to=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Writing Your Own Data Message Handlers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a data message handler that subclasses from **CASDataMsgHandler**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "class MyDMH(dmh.CASDataMsgHandler):\n",
    "\n",
    "    def __init__(self):\n",
    "        self.data = [\n",
    "            ('Alfred',  'M', 14, 69,   112.5),\n",
    "            ('Alice',   'F', 13, 56.5, 84),\n",
    "            ('Barbara', 'F', 13, 65.3, 98),\n",
    "            ('Carol',   'F', 14, 62.8, 102.5),\n",
    "            ('Henry',   'M', 14, 63.5, 102.5),           \n",
    "        ] \n",
    "\n",
    "        vars = [\n",
    "            dict(name='name', label='Name', type='varchar'),\n",
    "            dict(name='sex', label='Sex', type='varchar'),\n",
    "            dict(name='age', label='Age', type='int32'),\n",
    "            dict(name='height', label='Height', type='double'),\n",
    "            dict(name='weight', label='Weight', type='double'),\n",
    "        ]\n",
    "\n",
    "        super(MyDMH, self).__init__(vars)\n",
    "\n",
    "    def getrow(self, row):\n",
    "        try:\n",
    "            return self.data[row]\n",
    "        except IndexError:\n",
    "            return"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create an instance of the data message handler."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "mydmh = MyDMH()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Call the **addtable** action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; caslib</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASUSER(username)</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; tableName</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>MYCLASSDATA</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; casTable</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASTable('MYCLASSDATA', caslib='CASUSER(username)')</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.0168s</span> &#183; <span class=\"cas-user\">user 0.000999s</span> &#183; <span class=\"cas-sys\">sys 0.001s</span> &#183; <span class=\"cas-memory\">mem 0.228MB</span></small></p>"
      ],
      "text/plain": [
       "[caslib]\n",
       "\n",
       " 'CASUSER(username)'\n",
       "\n",
       "[tableName]\n",
       "\n",
       " 'MYCLASSDATA'\n",
       "\n",
       "[casTable]\n",
       "\n",
       " CASTable('MYCLASSDATA', caslib='CASUSER(username)')\n",
       "\n",
       "+ Elapsed: 0.0168s, user: 0.000999s, sys: 0.001s, mem: 0.228mb"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.addtable(table='myclass', caslib='casuser',\n",
    "              **mydmh.args.addtable)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Verify the data on the server."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; ColumnInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Column\">Column</th>\n",
       "      <th title=\"Label\">Label</th>\n",
       "      <th title=\"ID\">ID</th>\n",
       "      <th title=\"Type\">Type</th>\n",
       "      <th title=\"RawLength\">RawLength</th>\n",
       "      <th title=\"FormattedLength\">FormattedLength</th>\n",
       "      <th title=\"NFL\">NFL</th>\n",
       "      <th title=\"NFD\">NFD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>name</td>\n",
       "      <td>Name</td>\n",
       "      <td>1</td>\n",
       "      <td>varchar</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sex</td>\n",
       "      <td>Sex</td>\n",
       "      <td>2</td>\n",
       "      <td>varchar</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>age</td>\n",
       "      <td>Age</td>\n",
       "      <td>3</td>\n",
       "      <td>int32</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>height</td>\n",
       "      <td>Height</td>\n",
       "      <td>4</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>weight</td>\n",
       "      <td>Weight</td>\n",
       "      <td>5</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000629s</span> &#183; <span class=\"cas-memory\">mem 0.174MB</span></small></p>"
      ],
      "text/plain": [
       "[ColumnInfo]\n",
       "\n",
       "    Column   Label  ID     Type  RawLength  FormattedLength  NFL  NFD\n",
       " 0    name    Name   1  varchar          7                7    0    0\n",
       " 1     sex     Sex   2  varchar          1                1    0    0\n",
       " 2     age     Age   3    int32          4               12    0    0\n",
       " 3  height  Height   4   double          8               12    0    0\n",
       " 4  weight  Weight   5   double          8               12    0    0\n",
       "\n",
       "+ Elapsed: 0.000629s, mem: 0.174mb"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.columninfo(table=dict(name='myclass', caslib='casuser'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; Fetch</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\"><caption>Selected Rows from Table MYCLASSDATA</caption>\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Name\">name</th>\n",
       "      <th title=\"Sex\">sex</th>\n",
       "      <th title=\"Age\">age</th>\n",
       "      <th title=\"Height\">height</th>\n",
       "      <th title=\"Weight\">weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alfred</td>\n",
       "      <td>M</td>\n",
       "      <td>14.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>112.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alice</td>\n",
       "      <td>F</td>\n",
       "      <td>13.0</td>\n",
       "      <td>56.5</td>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Barbara</td>\n",
       "      <td>F</td>\n",
       "      <td>13.0</td>\n",
       "      <td>65.3</td>\n",
       "      <td>98.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Carol</td>\n",
       "      <td>F</td>\n",
       "      <td>14.0</td>\n",
       "      <td>62.8</td>\n",
       "      <td>102.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Henry</td>\n",
       "      <td>M</td>\n",
       "      <td>14.0</td>\n",
       "      <td>63.5</td>\n",
       "      <td>102.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.00332s</span> &#183; <span class=\"cas-user\">user 0.003s</span> &#183; <span class=\"cas-memory\">mem 1.64MB</span></small></p>"
      ],
      "text/plain": [
       "[Fetch]\n",
       "\n",
       " Selected Rows from Table MYCLASSDATA\n",
       " \n",
       "       name sex   age  height  weight\n",
       " 0   Alfred   M  14.0    69.0   112.5\n",
       " 1    Alice   F  13.0    56.5    84.0\n",
       " 2  Barbara   F  13.0    65.3    98.0\n",
       " 3    Carol   F  14.0    62.8   102.5\n",
       " 4    Henry   M  14.0    63.5   102.5\n",
       "\n",
       "+ Elapsed: 0.00332s, user: 0.003s, mem: 1.64mb"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.fetch(table=dict(name='myclass', caslib='casuser'), to=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding Data Transformers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add a date column to the data message handler."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "class MyDMH(dmh.CASDataMsgHandler):\n",
    "\n",
    "    def __init__(self):\n",
    "        self.data = [\n",
    "            ('Alfred',  'M', 14, 69,   112.5, '1987-03-01'),\n",
    "            ('Alice',   'F', 13, 56.5, 84,    '1988-06-12'),\n",
    "            ('Barbara', 'F', 13, 65.3, 98,    '1988-12-13'),\n",
    "            ('Carol',   'F', 14, 62.8, 102.5, '1987-04-17'),\n",
    "            ('Henry',   'M', 14, 63.5, 102.5, '1987-01-30'),           \n",
    "        ] \n",
    "\n",
    "        vars = [\n",
    "            dict(name='name', label='Name', type='varchar'),\n",
    "            dict(name='sex', label='Sex', type='varchar'),\n",
    "            dict(name='age', label='Age', type='int32'),\n",
    "            dict(name='height', label='Height', type='double'),\n",
    "            dict(name='weight', label='Weight', type='double'),\n",
    "            dict(name='birthdate', label='Birth Date',\n",
    "                 type='date', format='DATE', formattedlength=12),\n",
    "        ]\n",
    "\n",
    "        super(MyDMH, self).__init__(vars)\n",
    "\n",
    "    def getrow(self, row):\n",
    "        try:\n",
    "            return self.data[row]\n",
    "        except IndexError:\n",
    "            return"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Take that same data message handler and add a **transformers=** parameter with a function that converts the date strings to CAS dates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "class MyDMH(dmh.CASDataMsgHandler):\n",
    "\n",
    "    def __init__(self):\n",
    "        self.data = [\n",
    "            ('Alfred',  'M', 14, 69,   112.5, '1987-03-01'),\n",
    "            ('Alice',   'F', 13, 56.5, 84,    '1988-06-12'),\n",
    "            ('Barbara', 'F', 13, 65.3, 98,    '1988-12-13'),\n",
    "            ('Carol',   'F', 14, 62.8, 102.5, '1987-04-17'),\n",
    "            ('Henry',   'M', 14, 63.5, 102.5, '1987-01-30'),           \n",
    "        ] \n",
    "\n",
    "        vars = [\n",
    "            dict(name='name', label='Name', type='varchar'),\n",
    "            dict(name='sex', label='Sex', type='varchar'),\n",
    "            dict(name='age', label='Age', type='int32'),\n",
    "            dict(name='height', label='Height', type='double'),\n",
    "            dict(name='weight', label='Weight', type='double'),\n",
    "            dict(name='birthdate', label='Birth Date',\n",
    "                 type='date', format='DATE', formattedlength=12),\n",
    "        ]\n",
    "\n",
    "        transformers = {\n",
    "            'birthdate': dmh.str2cas_date,\n",
    "        }\n",
    "\n",
    "        super(MyDMH, self).__init__(vars, transformers=transformers)\n",
    "\n",
    "    def getrow(self, row):\n",
    "        try:\n",
    "            return self.data[row]\n",
    "        except IndexError:\n",
    "            return\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create an instance of the data message handler."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "mydmh = MyDMH()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run the **addtable** action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; caslib</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASUSER(username)</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; tableName</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>MYCLASSDATA</div>\n",
       "</div>\n",
       "<div class=\"cas-results-key\"><hr/><b>&#167; casTable</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>CASTable('MYCLASSDATA', caslib='CASUSER(username)')</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.0234s</span> &#183; <span class=\"cas-user\">user 0.019s</span> &#183; <span class=\"cas-sys\">sys 0.002s</span> &#183; <span class=\"cas-memory\">mem 0.489MB</span></small></p>"
      ],
      "text/plain": [
       "[caslib]\n",
       "\n",
       " 'CASUSER(username)'\n",
       "\n",
       "[tableName]\n",
       "\n",
       " 'MYCLASSDATA'\n",
       "\n",
       "[casTable]\n",
       "\n",
       " CASTable('MYCLASSDATA', caslib='CASUSER(username)')\n",
       "\n",
       "+ Elapsed: 0.0234s, user: 0.019s, sys: 0.002s, mem: 0.489mb"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.addtable(table='myclass', caslib='casuser', replace=True,\n",
    "              **mydmh.args.addtable)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Verify the data on the server."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; ColumnInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Column\">Column</th>\n",
       "      <th title=\"Label\">Label</th>\n",
       "      <th title=\"ID\">ID</th>\n",
       "      <th title=\"Type\">Type</th>\n",
       "      <th title=\"RawLength\">RawLength</th>\n",
       "      <th title=\"FormattedLength\">FormattedLength</th>\n",
       "      <th title=\"Format\">Format</th>\n",
       "      <th title=\"NFL\">NFL</th>\n",
       "      <th title=\"NFD\">NFD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>name</td>\n",
       "      <td>Name</td>\n",
       "      <td>1</td>\n",
       "      <td>varchar</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sex</td>\n",
       "      <td>Sex</td>\n",
       "      <td>2</td>\n",
       "      <td>varchar</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>age</td>\n",
       "      <td>Age</td>\n",
       "      <td>3</td>\n",
       "      <td>int32</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>height</td>\n",
       "      <td>Height</td>\n",
       "      <td>4</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>weight</td>\n",
       "      <td>Weight</td>\n",
       "      <td>5</td>\n",
       "      <td>double</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>birthdate</td>\n",
       "      <td>Birth Date</td>\n",
       "      <td>6</td>\n",
       "      <td>date</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "      <td>DATE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000644s</span> &#183; <span class=\"cas-memory\">mem 0.173MB</span></small></p>"
      ],
      "text/plain": [
       "[ColumnInfo]\n",
       "\n",
       "       Column       Label  ID     Type  RawLength  FormattedLength Format  NFL  \\\n",
       " 0       name        Name   1  varchar          7                7           0   \n",
       " 1        sex         Sex   2  varchar          1                1           0   \n",
       " 2        age         Age   3    int32          4               12           0   \n",
       " 3     height      Height   4   double          8               12           0   \n",
       " 4     weight      Weight   5   double          8               12           0   \n",
       " 5  birthdate  Birth Date   6     date          4               12   DATE    0   \n",
       " \n",
       "    NFD  \n",
       " 0    0  \n",
       " 1    0  \n",
       " 2    0  \n",
       " 3    0  \n",
       " 4    0  \n",
       " 5    0  \n",
       "\n",
       "+ Elapsed: 0.000644s, mem: 0.173mb"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.columninfo(table=dict(name='myclass', caslib='casuser'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; Fetch</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\"><caption>Selected Rows from Table MYCLASSDATA</caption>\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Name\">name</th>\n",
       "      <th title=\"Sex\">sex</th>\n",
       "      <th title=\"Age\">age</th>\n",
       "      <th title=\"Height\">height</th>\n",
       "      <th title=\"Weight\">weight</th>\n",
       "      <th title=\"Birth Date\">birthdate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alfred</td>\n",
       "      <td>M</td>\n",
       "      <td>14</td>\n",
       "      <td>69.0</td>\n",
       "      <td>112.5</td>\n",
       "      <td>1987-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alice</td>\n",
       "      <td>F</td>\n",
       "      <td>13</td>\n",
       "      <td>56.5</td>\n",
       "      <td>84.0</td>\n",
       "      <td>1988-06-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Barbara</td>\n",
       "      <td>F</td>\n",
       "      <td>13</td>\n",
       "      <td>65.3</td>\n",
       "      <td>98.0</td>\n",
       "      <td>1988-12-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Carol</td>\n",
       "      <td>F</td>\n",
       "      <td>14</td>\n",
       "      <td>62.8</td>\n",
       "      <td>102.5</td>\n",
       "      <td>1987-04-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Henry</td>\n",
       "      <td>M</td>\n",
       "      <td>14</td>\n",
       "      <td>63.5</td>\n",
       "      <td>102.5</td>\n",
       "      <td>1987-01-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.00369s</span> &#183; <span class=\"cas-user\">user 0.004s</span> &#183; <span class=\"cas-memory\">mem 1.64MB</span></small></p>"
      ],
      "text/plain": [
       "[Fetch]\n",
       "\n",
       " Selected Rows from Table MYCLASSDATA\n",
       " \n",
       "       name sex  age  height  weight   birthdate\n",
       " 0   Alfred   M   14    69.0   112.5  1987-03-01\n",
       " 1    Alice   F   13    56.5    84.0  1988-06-12\n",
       " 2  Barbara   F   13    65.3    98.0  1988-12-13\n",
       " 3    Carol   F   14    62.8   102.5  1987-04-17\n",
       " 4    Henry   M   14    63.5   102.5  1987-01-30\n",
       "\n",
       "+ Elapsed: 0.00369s, user: 0.004s, mem: 1.64mb"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.fetch(table=dict(name='myclass', caslib='casuser'), sastypes=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Managing CASLibs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a CASLib"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a new filesystem-based CASLib."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NOTE: Failed to resolve path /disk/research/data/ for caslib research.\n",
      "NOTE: Cloud Analytic Services added the caslib 'research'.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; CASLibInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Name\">Name</th>\n",
       "      <th title=\"Type\">Type</th>\n",
       "      <th title=\"Description\">Description</th>\n",
       "      <th title=\"Path\">Path</th>\n",
       "      <th title=\"Definition\">Definition</th>\n",
       "      <th title=\"Subdirs\">Subdirs</th>\n",
       "      <th title=\"Local\">Local</th>\n",
       "      <th title=\"Active\">Active</th>\n",
       "      <th title=\"Personal\">Personal</th>\n",
       "      <th title=\"Hidden\">Hidden</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>research</td>\n",
       "      <td>PATH</td>\n",
       "      <td>Research Data</td>\n",
       "      <td>/disk/research/data/</td>\n",
       "      <td></td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.00128s</span> &#183; <span class=\"cas-user\">user 0.001s</span> &#183; <span class=\"cas-memory\">mem 0.119MB</span></small></p>"
      ],
      "text/plain": [
       "[CASLibInfo]\n",
       "\n",
       "        Name  Type    Description                  Path Definition  Subdirs  \\\n",
       " 0  research  PATH  Research Data  /disk/research/data/                 0.0   \n",
       " \n",
       "    Local  Active  Personal  Hidden  \n",
       " 0    0.0     0.0       0.0     0.0  \n",
       "\n",
       "+ Elapsed: 0.00128s, user: 0.001s, mem: 0.119mb"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.addcaslib(path='/research/data', \n",
    "               caslib='research', \n",
    "               description='Research Data',  \n",
    "               subdirs=False, \n",
    "               session=False, \n",
    "               activeonadd=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setting an Active CASLib\n",
    "\n",
    "The active CASLib for a session can be set using the **setsessopt** action."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NOTE: 'research' is now the active caslib.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000258s</span> &#183; <span class=\"cas-memory\">mem 0.0928MB</span></small></p>"
      ],
      "text/plain": [
       "+ Elapsed: 0.000258s, mem: 0.0928mb"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.setsessopt(caslib='research')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"cas-results-key\"><b>&#167; CASLibInfo</b></div>\n",
       "<div class=\"cas-results-body\">\n",
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th title=\"\"></th>\n",
       "      <th title=\"Name\">Name</th>\n",
       "      <th title=\"Type\">Type</th>\n",
       "      <th title=\"Description\">Description</th>\n",
       "      <th title=\"Path\">Path</th>\n",
       "      <th title=\"Definition\">Definition</th>\n",
       "      <th title=\"Subdirs\">Subdirs</th>\n",
       "      <th title=\"Local\">Local</th>\n",
       "      <th title=\"Active\">Active</th>\n",
       "      <th title=\"Personal\">Personal</th>\n",
       "      <th title=\"Hidden\">Hidden</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>research</td>\n",
       "      <td>PATH</td>\n",
       "      <td>Research Data</td>\n",
       "      <td>/disk/research/data/</td>\n",
       "      <td></td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "</div>\n",
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000467s</span> &#183; <span class=\"cas-memory\">mem 0.0942MB</span></small></p>"
      ],
      "text/plain": [
       "[CASLibInfo]\n",
       "\n",
       "        Name  Type    Description                  Path Definition  Subdirs  \\\n",
       " 0  research  PATH  Research Data  /disk/research/data/                 0.0   \n",
       " \n",
       "    Local  Active  Personal  Hidden  \n",
       " 0    0.0     1.0       0.0     0.0  \n",
       "\n",
       "+ Elapsed: 0.000467s, mem: 0.0942mb"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.caslibinfo(caslib='research')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dropping a CASLib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NOTE: 'CASUSER(username)' is now the active caslib.\n",
      "NOTE: Cloud Analytic Services removed the caslib 'research'.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div class=\"cas-output-area\"></div>\n",
       "<p class=\"cas-results-performance\"><small><span class=\"cas-elapsed\">elapsed 0.000757s</span> &#183; <span class=\"cas-user\">user 0.001s</span> &#183; <span class=\"cas-memory\">mem 0.0655MB</span></small></p>"
      ],
      "text/plain": [
       "+ Elapsed: 0.000757s, user: 0.001s, mem: 0.0655mb"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn.dropcaslib('research')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.4.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}