Using IPython SQL magic in a Jupyter notebook to create new database tables using the PERSIST command

sql2pandas2sql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'Connected: postgres@nyc'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql postgresql://postgres:Admin@localhost/nyc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>neighborhoodname</th>\n",
       "        <th>crimecount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bedford-Stuyvesant</td>\n",
       "        <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South Bronx</td>\n",
       "        <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brownsville</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>East Brooklyn</td>\n",
       "        <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harlem</td>\n",
       "        <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jamaica</td>\n",
       "        <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tremont</td>\n",
       "        <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wakefield-Williamsbridge</td>\n",
       "        <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mott Haven</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fort Green</td>\n",
       "        <td>25</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Bedford-Stuyvesant', 113L),\n",
       " (u'South Bronx', 66L),\n",
       " (u'Brownsville', 50L),\n",
       " (u'East Brooklyn', 43L),\n",
       " (u'Harlem', 41L),\n",
       " (u'Jamaica', 37L),\n",
       " (u'Tremont', 30L),\n",
       " (u'Wakefield-Williamsbridge', 29L),\n",
       " (u'Mott Haven', 27L),\n",
       " (u'Fort Green', 25L)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "    Polys.Name AS NeighborhoodName\n",
    "    , Count(*) AS CrimeCount\n",
    "FROM\n",
    "    nyc_homicides AS Points\n",
    "JOIN\n",
    "    nyc_Neighborhoods AS Polys\n",
    "ON\n",
    "    ST_Contains(Polys.geom, Points.geom)\n",
    "AND\n",
    "    Points.YEAR in (2008, 2009, 2010)\n",
    "GROUP BY\n",
    "    Polys.Name\n",
    "ORDER BY\n",
    "    CrimeCount DESC\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = _.DataFrame()\n",
    "df['Readable'] = df['crimecount'].apply(lambda x: str(x) + ' crimes')\n",
    "df.set_index('neighborhoodname', drop=True, inplace=True)\n",
    "CrimeCountStats = df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'Persisted crimecountstats'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql PERSIST CrimeCountStats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>neighborhoodname</th>\n",
       "        <th>crimecount</th>\n",
       "        <th>Readable</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bedford-Stuyvesant</td>\n",
       "        <td>113</td>\n",
       "        <td>113 crimes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South Bronx</td>\n",
       "        <td>66</td>\n",
       "        <td>66 crimes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brownsville</td>\n",
       "        <td>50</td>\n",
       "        <td>50 crimes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>East Brooklyn</td>\n",
       "        <td>43</td>\n",
       "        <td>43 crimes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harlem</td>\n",
       "        <td>41</td>\n",
       "        <td>41 crimes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jamaica</td>\n",
       "        <td>37</td>\n",
       "        <td>37 crimes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tremont</td>\n",
       "        <td>30</td>\n",
       "        <td>30 crimes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wakefield-Williamsbridge</td>\n",
       "        <td>29</td>\n",
       "        <td>29 crimes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mott Haven</td>\n",
       "        <td>27</td>\n",
       "        <td>27 crimes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fort Green</td>\n",
       "        <td>25</td>\n",
       "        <td>25 crimes</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Bedford-Stuyvesant', 113L, u'113 crimes'),\n",
       " (u'South Bronx', 66L, u'66 crimes'),\n",
       " (u'Brownsville', 50L, u'50 crimes'),\n",
       " (u'East Brooklyn', 43L, u'43 crimes'),\n",
       " (u'Harlem', 41L, u'41 crimes'),\n",
       " (u'Jamaica', 37L, u'37 crimes'),\n",
       " (u'Tremont', 30L, u'30 crimes'),\n",
       " (u'Wakefield-Williamsbridge', 29L, u'29 crimes'),\n",
       " (u'Mott Haven', 27L, u'27 crimes'),\n",
       " (u'Fort Green', 25L, u'25 crimes')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from CrimeCountStats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql DROP TABLE CrimeCountStats"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}