Skip to content

Instantly share code, notes, and snippets.

@AlexArcPy

AlexArcPy/sqlmagic.ipynb

Created February 8, 2018 09:46
Show Gist options
  • Save AlexArcPy/f67d18a9b47a810f2855f36c973c3193 to your computer and use it in GitHub Desktop.
Save AlexArcPy/f67d18a9b47a810f2855f36c973c3193 to your computer and use it in GitHub Desktop.
Download ZIP
Adding IPython SQL magic to Jupyter notebook
Display the source blob
Display the rendered blob
Raw
sqlmagic.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If you do not use the `%%sql` magic in your Jupyter notebook, the output of your SQL queries will be just a plain list of tuples which you can see printed below."
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/plain": [
"[(1,\n",
" 33034629,\n",
" u'2002-02-06 00:00:00.0000000',\n",
" 2,\n",
" None,\n",
" None,\n",
" None,\n",
" 458,\n",
" 45800,\n",
" ' i\\x00\\x00\\x01\\r\\xd0\\x00\\xde\\x02<\\x81\\x13A\\x04\\xa3\\x92\\x02\\x81\\x9fOA\\x00\\x00\\x00\\x00\\x00\\x00\\x00\\x00')]"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import pymssql\n",
"conn = pymssql.connect('localhost', '', '', \"Zion\")\n",
"cur = conn.cursor()\n",
"res = cur.execute('SELECT TOP 1 * FROM dbo.SmallSprings')\n",
"cur.fetchall()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"A better way to work with the result sets returned is to draw them as a table with the headers. This is where the [IPython SQL magic](https://github.com/catherinedevlin/ipython-sql) gets very handy. You can install it using `pip install ipython-sql`. Refer to its GitHub repository for details of the implementation.\n",
"\n",
"You have to connect to a database and then all your subsequent SQL queries will be aware of this connection and the result sets are also drawn nicely in a table. In this particular case, I am connecting to a Microsoft SQL Server database."
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"u'Connected: None@non_spat'"
]
},
"execution_count": 1,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%load_ext sql\n",
"%sql mssql+pymssql://localhost/non_spat?charset=utf8"
]
},
{
"cell_type": "code",
"execution_count": 50,
"metadata": {
"scrolled": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>BORONAME</th>\n",
" <th>POPN_TOTAL</th>\n",
" <th>POPN_WHITE</th>\n",
" <th>POPN_BLACK</th>\n",
" <th>POPN_NATIV</th>\n",
" <th>POPN_ASIAN</th>\n",
" </tr>\n",
" <tr>\n",
" <td>Queens</td>\n",
" <td>4563.00000000</td>\n",
" <td>436.00000000</td>\n",
" <td>293.00000000</td>\n",
" <td>24.00000000</td>\n",
" <td>3511.00000000</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Queens</td>\n",
" <td>2693.00000000</td>\n",
" <td>100.00000000</td>\n",
" <td>2260.00000000</td>\n",
" <td>20.00000000</td>\n",
" <td>5.00000000</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Queens</td>\n",
" <td>2539.00000000</td>\n",
" <td>551.00000000</td>\n",
" <td>1447.00000000</td>\n",
" <td>19.00000000</td>\n",
" <td>46.00000000</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Queens</td>\n",
" <td>2529.00000000</td>\n",
" <td>849.00000000</td>\n",
" <td>1090.00000000</td>\n",
" <td>18.00000000</td>\n",
" <td>72.00000000</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Queens</td>\n",
" <td>2526.00000000</td>\n",
" <td>672.00000000</td>\n",
" <td>1256.00000000</td>\n",
" <td>12.00000000</td>\n",
" <td>58.00000000</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(u'Queens', Decimal('4563.00000000'), Decimal('436.00000000'), Decimal('293.00000000'), Decimal('24.00000000'), Decimal('3511.00000000')),\n",
" (u'Queens', Decimal('2693.00000000'), Decimal('100.00000000'), Decimal('2260.00000000'), Decimal('20.00000000'), Decimal('5.00000000')),\n",
" (u'Queens', Decimal('2539.00000000'), Decimal('551.00000000'), Decimal('1447.00000000'), Decimal('19.00000000'), Decimal('46.00000000')),\n",
" (u'Queens', Decimal('2529.00000000'), Decimal('849.00000000'), Decimal('1090.00000000'), Decimal('18.00000000'), Decimal('72.00000000')),\n",
" (u'Queens', Decimal('2526.00000000'), Decimal('672.00000000'), Decimal('1256.00000000'), Decimal('12.00000000'), Decimal('58.00000000'))]"
]
},
"execution_count": 50,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql \n",
"SELECT TOP 5 \n",
" BORONAME, POPN_TOTAL, POPN_WHITE, POPN_BLACK, POPN_NATIV, POPN_ASIAN\n",
"FROM \n",
" dbo.census_blocks \n",
"WHERE \n",
" BORONAME = 'Queens'\n",
"ORDER BY \n",
" POPN_TOTAL DESC"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Another neat feature of the `%%sql` magic is that you will be able to get the result of your SQL query as a `pandas` data frame object if you would like to proceed working with the result set using Python. The result object of SQL query execution can be accessed from a variable `_`. This is because IPython's output caching system defines several global variables; `_` (a single underscore) stores previous output just like the IPython interpreter."
]
},
{
"cell_type": "code",
"execution_count": 51,
"metadata": {
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style>\n",
" .dataframe thead tr:only-child th {\n",
" text-align: right;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>BORONAME</th>\n",
" <th>POPN_TOTAL</th>\n",
" <th>POPN_WHITE</th>\n",
" <th>POPN_BLACK</th>\n",
" <th>POPN_NATIV</th>\n",
" <th>POPN_ASIAN</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>count</th>\n",
" <td>5</td>\n",
" <td>5</td>\n",
" <td>5</td>\n",
" <td>5</td>\n",
" <td>5</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>unique</th>\n",
" <td>1</td>\n",
" <td>5</td>\n",
" <td>5</td>\n",
" <td>5</td>\n",
" <td>5</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>top</th>\n",
" <td>Queens</td>\n",
" <td>4563.00000000</td>\n",
" <td>551.00000000</td>\n",
" <td>1447.00000000</td>\n",
" <td>12.00000000</td>\n",
" <td>3511.00000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>freq</th>\n",
" <td>5</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" BORONAME POPN_TOTAL POPN_WHITE POPN_BLACK POPN_NATIV \\\n",
"count 5 5 5 5 5 \n",
"unique 1 5 5 5 5 \n",
"top Queens 4563.00000000 551.00000000 1447.00000000 12.00000000 \n",
"freq 5 1 1 1 1 \n",
"\n",
" POPN_ASIAN \n",
"count 5 \n",
"unique 5 \n",
"top 3511.00000000 \n",
"freq 1 "
]
},
"execution_count": 51,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = _.DataFrame()\n",
"df.describe()"
]
}
],
"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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment