Skip to content

Instantly share code, notes, and snippets.

@FranckPachot

FranckPachot/Blog20190428-postgresql-and-jupyter-notebook-on-windows.ipynb

Created April 28, 2019 17:57
Show Gist options
  • Star 4 You must be signed in to star a gist
  • Fork 4 You must be signed in to fork a gist
  • Save FranckPachot/31fd2450506a60ae8698b066e0876de9 to your computer and use it in GitHub Desktop.
Save FranckPachot/31fd2450506a60ae8698b066e0876de9 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
Blog20190428-postgresql-and-jupyter-notebook-on-windows.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Example notebook for [PostgreSQL and Jupyter notebook on Windows](https://medium.com/@FranckPachot/postgresql-and-jupyter-notebook-on-windows-e7b68cb6427d)\n",
"\n",
"## (Blog by Franck Pachot)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## I restart the PostgreSQL server and re-create the DEMO database\n",
"Because I use this for repeatable demos, I restart and re-create the database"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"waiting for server to start....2019-04-28 19:52:33.255 CEST [104] LOG: listening on IPv6 address \"::1\", port 5432\r\n",
"2019-04-28 19:52:33.255 CEST [104] LOG: listening on IPv4 address \"127.0.0.1\", port 5432\r\n",
"2019-04-28 19:52:33.301 CEST [4656] LOG: database system was shut down at 2019-04-28 19:52:27 CEST\n",
"2019-04-28 19:52:33.354 CEST [104] LOG: database system is ready to accept connections\r\n",
" done\r\n",
"server started\r\n",
"DROP DATABASE\r\n",
"CREATE DATABASE\r\n"
]
},
{
"name": "stderr",
"output_type": "stream",
"text": [
"+ pg_ctl.exe restart '--pgdata=C:\\Anaconda\\pgdata'\n",
"pg_ctl: PID file \"C:/Anaconda/pgdata/postmaster.pid\" does not exist\r\n",
"Is server running?\r\n",
"trying to start server anyway\r\n",
"+ psql.exe -c 'drop database if exists DEMO;' postgres\n",
"+ psql.exe -c 'create database DEMO;' postgres\n"
]
}
],
"source": [
"%%bash -x\n",
"pg_ctl.exe restart --pgdata=\"C:\\Anaconda\\pgdata\"\n",
"psql.exe -c \"drop database if exists DEMO;\" postgres\n",
"psql.exe -c \"create database DEMO;\" postgres"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## I load the library and connect to DEMO\n",
"I use `%reload_ext` to avoid the \"The sql extension is already loaded.\" error with `%load_ext`"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'Connected: @demo'"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%reload_ext sql\n",
"%sql postgresql://localhost/demo "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"I can see that I run the latest PostgreSQL release:"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://localhost/demo\n",
"1 rows affected.\n",
"+------------------------------------------------------------+\n",
"| version |\n",
"+------------------------------------------------------------+\n",
"| PostgreSQL 11.2, compiled by Visual C++ build 1915, 64-bit |\n",
"+------------------------------------------------------------+\n"
]
}
],
"source": [
"pg_version=%sql select version()\n",
"print(pg_version)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## I create my demo table\n",
"Yes, I like the SCOTT/TIGER schema from Oracle...\n",
"I can run many statements but all output is displayed at the end:"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://localhost/demo\n",
"Done.\n",
"Done.\n",
"Done.\n",
"Done.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n",
"1 rows affected.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"drop table if exists EMP;\n",
"drop table if exists DEPT;\n",
"CREATE TABLE DEPT\n",
" (DEPTNO integer CONSTRAINT PK_DEPT PRIMARY KEY,\n",
" DNAME text ,\n",
" LOC text ) ;\n",
"CREATE TABLE EMP\n",
" (EMPNO integer CONSTRAINT PK_EMP PRIMARY KEY,\n",
" ENAME text,\n",
" JOB text,\n",
" MGR integer,\n",
" HIREDATE date,\n",
" SAL integer,\n",
" COMM integer,\n",
" DEPTNO integer CONSTRAINT FK_DEPTNO REFERENCES DEPT);\n",
"INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');\n",
"INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');\n",
"INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');\n",
"INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');\n",
"INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);\n",
"INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);\n",
"INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);\n",
"INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);\n",
"INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);\n",
"INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);\n",
"INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);\n",
"INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-7-1987','dd-mm-yyyy')-85,3000,NULL,20);\n",
"INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);\n",
"INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);\n",
"INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-7-1987', 'dd-mm-yyyy')-51,1100,NULL,20);\n",
"INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);\n",
"INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);\n",
"INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## PSQL special commands\n",
"Thanks to the PGspecial plug-in I can use the 'backslash' commands"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://localhost/demo\n",
"2 rows affected.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>Schema</th>\n",
" <th>Name</th>\n",
" <th>Type</th>\n",
" <th>Owner</th>\n",
" </tr>\n",
" <tr>\n",
" <td>public</td>\n",
" <td>dept</td>\n",
" <td>table</td>\n",
" <td>Franck</td>\n",
" </tr>\n",
" <tr>\n",
" <td>public</td>\n",
" <td>emp</td>\n",
" <td>table</td>\n",
" <td>Franck</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[('public', 'dept', 'table', 'Franck'), ('public', 'emp', 'table', 'Franck')]"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql \\dt"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## I query some rows with SQL\n",
"It is easy to stay in the best practices: use bind variables!"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://localhost/demo\n",
"3 rows affected.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>deptno</th>\n",
" <th>dname</th>\n",
" <th>loc</th>\n",
" <th>empno</th>\n",
" <th>ename</th>\n",
" <th>job</th>\n",
" <th>mgr</th>\n",
" <th>hiredate</th>\n",
" <th>sal</th>\n",
" <th>comm</th>\n",
" </tr>\n",
" <tr>\n",
" <td>10</td>\n",
" <td>ACCOUNTING</td>\n",
" <td>NEW YORK</td>\n",
" <td>7782</td>\n",
" <td>CLARK</td>\n",
" <td>MANAGER</td>\n",
" <td>7839</td>\n",
" <td>1981-06-09</td>\n",
" <td>2450</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <td>10</td>\n",
" <td>ACCOUNTING</td>\n",
" <td>NEW YORK</td>\n",
" <td>7839</td>\n",
" <td>KING</td>\n",
" <td>PRESIDENT</td>\n",
" <td>None</td>\n",
" <td>1981-11-17</td>\n",
" <td>5000</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <td>10</td>\n",
" <td>ACCOUNTING</td>\n",
" <td>NEW YORK</td>\n",
" <td>7934</td>\n",
" <td>MILLER</td>\n",
" <td>CLERK</td>\n",
" <td>7782</td>\n",
" <td>1982-01-23</td>\n",
" <td>1300</td>\n",
" <td>None</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(10, 'ACCOUNTING', 'NEW YORK', 7782, 'CLARK', 'MANAGER', 7839, datetime.date(1981, 6, 9), 2450, None),\n",
" (10, 'ACCOUNTING', 'NEW YORK', 7839, 'KING', 'PRESIDENT', None, datetime.date(1981, 11, 17), 5000, None),\n",
" (10, 'ACCOUNTING', 'NEW YORK', 7934, 'MILLER', 'CLERK', 7782, datetime.date(1982, 1, 23), 1300, None)]"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"deptno=10\n",
"%sql select * from DEPT join EMP using (DEPTNO) where deptno=:deptno"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"By default, the output is nicely formated in an HTML table\n",
"\n",
"The previous result is accessible with `_` and can be displayed as an ASCII table"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"+--------+------------+----------+-------+--------+-----------+------+------------+------+------+\n",
"| deptno | dname | loc | empno | ename | job | mgr | hiredate | sal | comm |\n",
"+--------+------------+----------+-------+--------+-----------+------+------------+------+------+\n",
"| 10 | ACCOUNTING | NEW YORK | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | None |\n",
"| 10 | ACCOUNTING | NEW YORK | 7839 | KING | PRESIDENT | None | 1981-11-17 | 5000 | None |\n",
"| 10 | ACCOUNTING | NEW YORK | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | None |\n",
"+--------+------------+----------+-------+--------+-----------+------+------------+------+------+\n"
]
}
],
"source": [
"print(_)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"I can access to the first row:"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"(10, 'ACCOUNTING', 'NEW YORK', 7782, 'CLARK', 'MANAGER', 7839, datetime.date(1981, 6, 9), 2450, None)\n"
]
}
],
"source": [
"print(_[0])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"I can display the column names:"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"['deptno', 'dname', 'loc', 'empno', 'ename', 'job', 'mgr', 'hiredate', 'sal', 'comm']\n"
]
}
],
"source": [
"print(_.keys)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"I can access to one column by index or by name:"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"CLARK\n",
"1981-06-09\n"
]
}
],
"source": [
"print(_[0][4])\n",
"print(_[0]['hiredate'])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## I display some values graphically\n",
"We can use mathplotlib .plot(), .pie(), and .bar()"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://localhost/demo\n",
"3 rows affected.\n"
]
},
{
"data": {
"text/plain": [
"<BarContainer object of 3 artists>"
]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
},
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAY4AAAE3CAYAAAC9wabBAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAHw5JREFUeJzt3XmYpFV99vHvLSCKkogyEAR0VEYjXirqgKhxCW6IMUhcAIkQJUENuLyaGFwSXF4UcYsLYkCIYBQkbmBEAQkuvBFhQIICAiMQGSEyiisKyPB7/zintYRh6Ae6urp7vp/r6qurTp2qPj093Xc9Z01VIUnSdN1p0g2QJM0vBockaRCDQ5I0iMEhSRrE4JAkDWJwSJIGMTgkSYMYHJKkQQwOSdIgBockaZB1x/XCSe4CfA1Yv3+dT1XVAUnuBxwL3BM4B3hhVd2QZH3gaOBRwI+BXavq8v5arwP2BlYBr6iqk9b0tTfeeONavHjxWL4vSVqozj777B9V1aLbqje24ACuB3aoql8mWQ84PckXgVcD762qY5N8mBYIh/bPP6mqrZLsBrwD2DXJ1sBuwEOAewNfTvLAqlp1a1948eLFLFu2bIzfmiQtPEn+Zzr1xtZVVc0v+931+kcBOwCf6uVHAc/ut3fu9+mPPzlJevmxVXV9VV0GLAe2G1e7JUlrNtYxjiTrJDkXuBo4Bfge8NOqurFXWQFs3m9vDlwB0B//GXCv0fLVPEeSNMvGGhxVtaqqtgG2oF0lPHh11frn3Mpjt1b+e5Lsk2RZkmUrV668vU2WJN2GWZlVVVU/Bb4CbA/cI8nU2MoWwJX99gpgS4D++B8C14yWr+Y5o1/jsKpaWlVLFy26zbEdSdLtNLbgSLIoyT367bsCTwEuBE4Dntur7QUc32+f0O/TH//PaqdMnQDslmT9PiNrCXDmuNotSVqzcc6q2gw4Ksk6tIA6rqr+I8kFwLFJ/i/wLeCIXv8I4GNJltOuNHYDqKrzkxwHXADcCOy7phlVkqTxykI8Onbp0qXldFxJGibJ2VW19LbquXJckjTIOLuq5q3F+39h0k1YsC4/6JmTboKkO8grDknSIAaHJGkQg0OSNIjBIUkaxOCQJA1icEiSBjE4JEmDGBySpEEMDknSIAaHJGkQg0OSNIjBIUkaxOCQJA1icEiSBjE4JEmDGBySpEEMDknSIAaHJGkQg0OSNIjBIUkaxOCQJA1icEiSBjE4JEmDGBySpEEMDknSIGMLjiRbJjktyYVJzk/yyl7+piQ/SHJu/9hp5DmvS7I8yUVJnj5SvmMvW55k/3G1WZJ029Yd42vfCLymqs5JsiFwdpJT+mPvrap3jVZOsjWwG/AQ4N7Al5M8sD98CPBUYAVwVpITquqCMbZdknQrxhYcVXUVcFW//YskFwKbr+EpOwPHVtX1wGVJlgPb9ceWV9WlAEmO7XUNDkmagFkZ40iyGHgE8M1etF+S85IcmWSjXrY5cMXI01b0slsrlyRNwNiDI8ndgU8Dr6qqnwOHAg8AtqFdkbx7qupqnl5rKL/519knybIky1auXDkjbZck3dJYgyPJerTQ+HhVfQagqn5YVauq6ibgcH7XHbUC2HLk6VsAV66h/PdU1WFVtbSqli5atGjmvxlJEjDeWVUBjgAurKr3jJRvNlJtF+A7/fYJwG5J1k9yP2AJcCZwFrAkyf2S3Jk2gH7CuNotSVqzcc6qehzwQuDbSc7tZa8Hdk+yDa276XLgJQBVdX6S42iD3jcC+1bVKoAk+wEnAesAR1bV+WNstyRpDcY5q+p0Vj8+ceIannMgcOBqyk9c0/MkSbPHleOSpEEMDknSIAaHJGkQg0OSNIjBIUkaxOCQJA1icEiSBjE4JEmDGBySpEEMDknSIAaHJGkQg0OSNIjBIUkaxOCQJA1icEiSBjE4JEmDGBySpEEMDknSIOM8c1ySVmvx/l+YdBMWrMsPeubYv4ZXHJKkQQwOSdIgBockaRCDQ5I0iMEhSRrE4JAkDWJwSJIGGVtwJNkyyWlJLkxyfpJX9vJ7JjklySX980a9PEnen2R5kvOSPHLktfbq9S9Jste42ixJum3jvOK4EXhNVT0Y2B7YN8nWwP7AqVW1BDi13wd4BrCkf+wDHAotaIADgEcD2wEHTIWNJGn2jS04quqqqjqn3/4FcCGwObAzcFSvdhTw7H57Z+Doas4A7pFkM+DpwClVdU1V/QQ4BdhxXO2WJK3ZrIxxJFkMPAL4JrBpVV0FLVyATXq1zYErRp62opfdWrkkaQLGHhxJ7g58GnhVVf18TVVXU1ZrKL/519knybIky1auXHn7GitJuk1jDY4k69FC4+NV9Zle/MPeBUX/fHUvXwFsOfL0LYAr11D+e6rqsKpaWlVLFy1aNLPfiCTpt8Y5qyrAEcCFVfWekYdOAKZmRu0FHD9SvmefXbU98LPelXUS8LQkG/VB8af1MknSBIxzW/XHAS8Evp3k3F72euAg4LgkewPfB57XHzsR2AlYDvwKeBFAVV2T5K3AWb3eW6rqmjG2W5K0BmMLjqo6ndWPTwA8eTX1C9j3Vl7rSODImWudJOn2cuW4JGkQg0OSNIjBIUkaxOCQJA1icEiSBjE4JEmDGBySpEEMDknSIAaHJGkQg0OSNIjBIUkaxOCQJA1icEiSBjE4JEmDGBySpEEMDknSIAaHJGkQg0OSNIjBIUkaxOCQJA1icEiSBjE4JEmDrDudSklevZrinwFnV9W5M9skSdJcNt0rjqXAS4HN+8c+wJOAw5O8djxNkyTNRdO64gDuBTyyqn4JkOQA4FPAE4CzgYPH0zxJ0lwz3SuO+wA3jNz/DXDfqvo1cP2Mt0qSNGdN94rjE8AZSY7v958FHJPkbsAFY2mZNE2L9//CpJuwYF1+0DMn3QTNQdO64qiqtwJ/A/yUNij+0qp6S1VdW1V7rO45SY5McnWS74yUvSnJD5Kc2z92GnnsdUmWJ7koydNHynfsZcuT7H97v1FJ0syY7qyq9wGfrKr3DXjtjwIfBI6+Wfl7q+pdN3v9rYHdgIcA9wa+nOSB/eFDgKcCK4CzkpxQVV7lSNKETHeM4xzgjf1d/zuTLL2tJ1TV14Brpvn6OwPHVtX1VXUZsBzYrn8sr6pLq+oG4NheV5I0IdPtqjqqqnai/SG/GHhHkktu59fcL8l5vStro162OXDFSJ0V/G7q7+rKJUkTMnTl+FbAHwOLge/ejq93KPAAYBvgKuDdvTyrqVtrKL+FJPskWZZk2cqVK29H0yRJ0zGt4EgydYXxFuA7wKOq6llDv1hV/bCqVlXVTcDhtCsYaFcSW45U3QK4cg3lq3vtw6pqaVUtXbRo0dCmSZKmabrTcS8DHgvcH1gfeFiSqXGMaUuyWVVd1e/uQgshgBOATyR5D21wfAlwJu2KY0mS+wE/oA2gv2DI15QkzazpBscq4D9p7/jPBbYHvgHscGtPSHIMbVuSjZOsAA4AnpRkG1p30+XASwCq6vwkx9HWhNwI7FtVq/rr7AecBKwDHFlV5w/7FiVJM2m6wfEKYFvgjKr60yR/DLx5TU+oqt1XU3zEGuofCBy4mvITgROn2U5J0phNd3D8uqq6DiDJ+lX1XeBB42uWJGmumu4Vx4ok9wA+B5yS5CfcyiC1JGlhm1ZwVNUu/eabkpwG/CHwpbG1SpI0Z033iuO3quqr42iIJGl+8OhYSdIgBockaRCDQ5I0iMEhSRrE4JAkDWJwSJIGMTgkSYMYHJKkQQwOSdIgBockaRCDQ5I0iMEhSRrE4JAkDWJwSJIGMTgkSYMYHJKkQQwOSdIgBockaRCDQ5I0iMEhSRrE4JAkDWJwSJIGGVtwJDkyydVJvjNSds8kpyS5pH/eqJcnyfuTLE9yXpJHjjxnr17/kiR7jau9kqTpGecVx0eBHW9Wtj9walUtAU7t9wGeASzpH/sAh0ILGuAA4NHAdsABU2EjSZqMsQVHVX0NuOZmxTsDR/XbRwHPHik/upozgHsk2Qx4OnBKVV1TVT8BTuGWYSRJmkWzPcaxaVVdBdA/b9LLNweuGKm3opfdWrkkaULmyuB4VlNWayi/5Qsk+yRZlmTZypUrZ7RxkqTfme3g+GHvgqJ/vrqXrwC2HKm3BXDlGspvoaoOq6qlVbV00aJFM95wSVIz28FxAjA1M2ov4PiR8j377KrtgZ/1rqyTgKcl2agPij+tl0mSJmTdcb1wkmOAJwEbJ1lBmx11EHBckr2B7wPP69VPBHYClgO/Al4EUFXXJHkrcFav95aquvmAuyRpFo0tOKpq91t56MmrqVvAvrfyOkcCR85g0yRJd8BcGRyXJM0TBockaRCDQ5I0iMEhSRrE4JAkDWJwSJIGMTgkSYMYHJKkQQwOSdIgBockaRCDQ5I0iMEhSRrE4JAkDWJwSJIGMTgkSYMYHJKkQQwOSdIgBockaRCDQ5I0iMEhSRrE4JAkDWJwSJIGMTgkSYMYHJKkQQwOSdIgBockaRCDQ5I0yESCI8nlSb6d5Nwky3rZPZOckuSS/nmjXp4k70+yPMl5SR45iTZLkppJXnH8aVVtU1VL+/39gVOraglwar8P8AxgSf/YBzh01lsqSfqtudRVtTNwVL99FPDskfKjqzkDuEeSzSbRQEnS5IKjgJOTnJ1kn162aVVdBdA/b9LLNweuGHnuil4mSZqAdSf0dR9XVVcm2QQ4Jcl311A3qymrW1RqAbQPwH3uc5+ZaaUk6RYmcsVRVVf2z1cDnwW2A3441QXVP1/dq68Athx5+hbAlat5zcOqamlVLV20aNE4my9Ja7VZD44kd0uy4dRt4GnAd4ATgL16tb2A4/vtE4A9++yq7YGfTXVpSZJm3yS6qjYFPptk6ut/oqq+lOQs4LgkewPfB57X658I7AQsB34FvGj2myxJmjLrwVFVlwIPX035j4Enr6a8gH1noWmSpGmYS9NxJUnzgMEhSRrE4JAkDWJwSJIGMTgkSYMYHJKkQQwOSdIgBockaRCDQ5I0iMEhSRrE4JAkDWJwSJIGMTgkSYMYHJKkQQwOSdIgBockaRCDQ5I0iMEhSRrE4JAkDWJwSJIGMTgkSYMYHJKkQQwOSdIgBockaRCDQ5I0iMEhSRpk3gRHkh2TXJRkeZL9J90eSVpbzYvgSLIOcAjwDGBrYPckW0+2VZK0dpoXwQFsByyvqkur6gbgWGDnCbdJktZK8yU4NgeuGLm/opdJkmbZupNuwDRlNWX1exWSfYB9+t1fJrlo7K2aGzYGfjTpRkxX3jHpFswJ8+Zn5s/rt9aWn9l9p1NpvgTHCmDLkftbAFeOVqiqw4DDZrNRc0GSZVW1dNLt0PT5M5t//Jn9vvnSVXUWsCTJ/ZLcGdgNOGHCbZKktdK8uOKoqhuT7AecBKwDHFlV50+4WZK0VpoXwQFQVScCJ066HXPQWtc9twD4M5t//JmNSFXddi1Jkrr5MsYhSZojDA5J0iAGhwBIsmmSu066HWuzJP4+LjBJ7jLpNoyD/1FFkqcB/wrsmmTL26qvmZfkGcBrk2w46bZoZiS5P3B6kq0m3ZaZZnCs5ZI8E3gX8B7glKq64maPr27VvmZQ/xkcDFwK/GrCzdEMqapLaUsIjk5yv0m3ZyY5q2otlmQz2kLKv6+qr4yUv5j2B+yTVVVJUv5HGYv+bvQTwEur6pyR7qq7ANdV1U3++89PSdapqlVJ3gksBf6qqv5n0u2aCV5xrN02AH56s9D4APB64LH0vb/8ozVW1wNXAZck2QB4FW290jG0rqv1/fefP5Js288OumsPjc1pv0vXAp/q3VfznsGxFkryR/3mFcD1SR4y8vDXgW2BrwBbJbnbLDdvrZBkhyRPBq6jdVF9APg28DDgy8C/Aw8HHjyxRur2eBDwZuChvXvqWOCYqvoz4HPAx5IsmWQDZ8K8WTmumdG7p96V5EtV9bEkP6YNir+tqq6rquN6vY1oG0s6xjHD+mSEQ4E9q2plko/SAuKbwLFV9ZNebxfarqya45I8lnbUw8dpO3e/g/azO6KqPtirHQTcDTg0yY5VdeNEGjsDDI61z03AqcBTk1wF/D3tnRBJ/gM4G9gTeAnwwqr65aQauhD1gfC3AntU1Rn96u9nVXXszeo9F1gMXDz7rdQQfUbcP9OC4RFV9fEkNwCvBv5raqyjqlYBr0+y8XwODXBwfK2R5D7AlX3DyA2A59KO4j2c1kXyFtofqgAb0gZrvz2h5i5ISRYBn6KdZrl3v/914OCqOrLX2QLYhRbcu1XVdybWYN2mJNvSJje8qKpOv9ljfwPsSpu1eGpV/WYCTRwLrzjWAkm2A74EnJnkk8BFtD9gNwB7AMdV1cv6jJ5NgWur6ucTa/DC9XPgg8DSJG8GngK8ayo0urvSBlKfU1Vry2Fk89kDgROq6vQkd+qz4KauMA5P8nPgQOA3tCv9BcHgWDssp4XFQ4HPAEcAnwUWAecCf5vkzlX1edoMH82wPqX2+iRfAG4E9gN+XFUfGanzHNosq48tpHenC9yvaOMW0CYb3QTc1Nc/7QAcRxvzWD6Z5o2Hs6oWsD41cL+qugZ4JvAD4A+AJwHH06bj7gg8ixYezqCaYVMr8UfWw/yKNt32A8DlSV7e6/0F7Z3pJYbG3JZk9A33T4BnJXlo7wZerzrgUcBOVXXcQlm/McUrjgUqyQNoYxZ79vVjhyR5FnAysFFVvQHYo8+e+hPgoqq6dnItXlj6O84Nga8meV9VvW8kPK5PcgrtjdsTkhwP3BvYxe6puS3JTsDO/ffmpVX1lSQfBL6Y5BlT44JJ/pI2yeTPJ9jcsTE4FqA+y+O9wHbAZcDBvf/1A30q6BeTrFtV/9Cnfn5+ku1diPo7zp8n2R34aJIbqurQHh7r9pD+TN9YcjPgxVV14UQbrTXqv1cHAgfQQuGUJNtX1duT3AT8R5LTad1XTwD+om87suA4q2qBSfJ02mllf1FVZ/eyx9D+w3+2h8cmtDUDH62qN0+utQtTHy+6YeT+trSV4O+uqkNHyp9NWzB2qJMR5ra+NczngX+pqn/uZf8KfLyqvtzvP5K2dmMD4FsLrXtqlGMcC0iSHYFDgJW0P0gAVNU3gDcAuyTZt6qupl2NHD2Rhi5gPbj/LcmeU2VVdRZt9tprkvxtr/fXtJ/V8YbGvHADbV+3DZM8vpdtAuyd5ORedmNVnVxVn1vIoQFecSwY/apiap+pHwH/CHy5qg4ZqbM98CHgw1XlGcpj0Lum3gf8L3AhbdrzN6pqRb/yOBq4hLZS/LlV9d8Ta6wG6VuF/CVtrdODgD+i/Z49H7g7bbuYJ9Kms980qXbOBoNjAeh74mwF/KiqvpXk7sDjgJfStkr/0EjdbYGrF/o7otnWF1VeR3sX+o/A+2k/g01p/eGvpk1MuA8tPPatqvMm01pNR99G5MFVdcTITrcPBl5AWzz7qqlFf32m1QZry9WjwTHPJVkHOAr4MW3bg//pi5DuCjweeBlwUlV9eILNXND6TJun0RbzrUjybuBBVfVn/Y/PV2n94xvRxp+OdcfbuS3tQK0TaFcQ/wT8Gji8qn7ed7x9Sa/6X1X1pQk1c2Ic45jn+v43L6ctQnol7cqDqvo1bTuLDwHPS7L3xBq5gCX5M+DttN2ErwSoqtcAlyZ5O/BvtDU0uwJvBM4yNOauPo2aqvoF7arxPOCXtCvFLyTZmbYK/EDaIPi2WQuPXPaKY55K8kTgXsAFVfXd/p/3PbSVx4cCF/epnxsAj6YtLFsxuRYvPH2DwmOA11bVWUnuDNyZ1l31N8DzaHsYfX1qO4oJNlfTkGTL6qdg9quO19G6e09LciTtynIF8DHgTOCyqvrRxBo8Ia7jmIeS3IV21Oi2wMVp23L/lDa//H3Ac2jnOVzSVyqfNqGmLnTX0959Xtd/JvvTugd/Tfvd2gBYD8DQmPv6Oo139rVO/1tVv+gXIH+ZdvzAY2njhlcCb6OdkLnWhQZ4xTHvJFlSVZckeSBt/OKhtEvq3WnbH2xF23jtHOAVXmWMT+/WeDXtXehDaAcwnU6bTfVc2iyb/wccWFXXT6qdum19KvubgddX1alJ7l79SIEk/wU8krZb8dQRBOutzVvDGBzzSO+OOhk4s6pe02d4vIJ2nsP+fdDuibRZHw8HHldV359cixe+PoPtobRDr46fCogkh9Lm/h/Y181ojurT1I+hvdH6fJL70nZeeF1VXdTHBx9RVfv1yShF2xxgrf3jaXDMM/1K41Da2oA39vtvpK3d+Ls+o2oDgN5NpVmW5PnAa4HnVdVlk26P1izJrrQp06+n7Vz8YdpRAx/ojy+hXUnuU1XHT6yhc4jBMQ+sZguLrWhbo59eVW9I8iDgNbT+9Bevze+EJintWN5daQPju5aHMM1pSZYCN1XVOWmHLj2JtqPtkVV18Ei9TYBtaAPhl0yksXOM03HnuCQPB96ddjIcAFW1HNgbeEySv+87qh4C/Iw2o0eT8VPaqvCdDY25rY9pHAtskmT9qjoc+CLtqN6z+4wqkuxFu9o429D4Ha845rj+H/jjwKXAO6vqByOPPYrWJfJq2hYX6zoIK61ZH9M4irYt+mk3e2wP2qrwj9C2FdmT1kV1/qw3dA5zOu4c1Qdd16+qH/c+8w/TDrp/G+3s8KKd6pdebxWwanItluaN+wOf6msz7kVb5/RE4BfAu2nTqf+JthfV8wyNWzI45qC0A5deDtwjyVnAfwMvBg4H/gH4F+B82pqBzWj7/0tagyR/TOtOvAx4e5Lv08akrgLuAlwD/HvfKibAOU5uWD27quaYkUOY9qHtP3V/YF/gbNrmee+kbZy3PnBfYO/qp45JWr2+n9g/AS/s66B2o01iOJPWbXUx7SyNd9J+p26cWGPnAYNjDunvct4NnFZVnx8pfwBty/TjaFsdPJC23cjlLvCT1qyfkfIm4E1VdVKSu1XVtX1Q/PqRen9Fm3Ty59VOxtStcFbVHNLHLe7NyCFMvfx7tK24H15Vq6rqwqo63dCQ1izJQ2mzpd7QQ2Mr4PgkDxtZrLlxklfRNgl9iaFx2wyOOSDJo/qmhQAnAesnWb8/ll5+MfCwtXEnTmmokd+by4HPAs9Pspg2PnjS1Fkovd6WtDPCX1hVF8x6Y+chg2PC+rbcHwUenWQRbSB8Z9rGancZWcz3UNr2zm6WJ922O8Nvt0ffg3ZC3/eAz1XVO5NM/e17NO0Arhe49mb6HOOYoCRPok2z3bOqzhwp34F2xsPJtH1xfkjb0PAFnhonrVnf3fZltDdh51XVZ5Lcjfa7tk5VvaDXexHw18DzR9dH6bYZHBOU5GXAb6rqI0nWo+2Ts05V3dhXjC8BngyspO2d4zsiaQ1Gdrk9mraLwr2Bg/tMqg1pB5sV7U3ZS2mLAP29GsjgmKAkBwOLqupFI2XpBzDdzznk0vQluSdts8+d+y63W9BO6ju0qs7ode4MfJK2Ff62jmncPgbHLEvyJ7T1FyfRrih2Bz5UVd/tj9+p73B7MPDZqvrG5ForzS9Jnkk75Owx1c4HP5F2oNa5wBXAkfxut4WrJtfS+c3B8VnUB8I/TBuoW5+2gvVewK5JtoZ2UlzfYmQH2hGVkqapqr4A/B1to8IP0AbJD6EdrrUbbYHfjYbGHeMVxyxJ8mjaCtW9quqbI+UPAt4AXEtbufot2kFMu7siXLp9kjyFNo6xWVX9sJfdCbjn2nrc60wyOGZJ33/qIVV10NT5GknW7QPhm9IG8p5Fu5z+ZlVdPNEGS/Nc377nXcAOU+GhmeEmh7PnAcBTgYNGDmWa2s32nsCKqnrbRFomLUBV9cU+GP7FJEuryjVQM8QxjjHqVxJTjgZWJHn2yOKjqc+70sY0JM2gftTrEwyNmWVwjEnfwvmqJO9J8tdVdQ3t/IztgecAVNWqft7xzrSxDUkzrKp+Oek2LDR2VY3PtcA3aKu+d02yDbAM+APa9iKvAb4C/DntfOpLJ9VQSRrCwfExSvJe2srVPWjdUTsCD6Yd9fpk4HPAT13oJ2k+satqDEZ25vwH2vYGGwM/oJ3YdzbtYKZNgQsMDUnzjV1VY9C3DAlthepy4D3AI4FXVdXnkiwBfjx6iIwkzRd2VY1ZX+D3deADVfXWSbdHku4ou6rGrKouonVZrZNkg0m3R5LuKINjdnwDeNSkGyFJM8GuqlmSZIOq+tWk2yFJd5TBIUkaxK4qSdIgBockaRCDQ5I0iMEhSRrE4JDugCRvSvJ3k26HNJsMDknSIAaHNFCSNyS5KMmXgQf1sq8keUeSM5NcnOTxvXxxkq8nOad/PLaXPynJV5Mc1+sflGSP/vxvJ3lAr7coyaeTnNU/Hjexb1zq3ORQGiDJo4DdgEfQfn/Ooe14DLBuVW2XZCfgAOApwNXAU6vqur655THA0l7/4bRt9q8BLgU+0p//SuDlwKuA9wHvrarTk9wHOKk/R5oYg0Ma5vHAZ6d2AUhywshjn+mfzwYW99vrAR/sB3mtAh44Uv+sqrqqv873gJN7+beBP+23nwJs/bud+vmDJBtW1S9m7DuSBjI4pOFubbuFqW3yV/G7363/QzsF8uG0ruHrVlMf4KaR+zeNPP9OwGOq6td3sM3SjHGMQxrma8AuSe6aZEPgWbdR/w+Bq6rqJuCFwDoDv97JwH5Td/qVizRRBoc0QFWdA3wSOBf4NO2slTX5ELBXkjNo3VTXDvySrwCWJjkvyQXASwc+X5pxbnIoSRrEKw5J0iAGhyRpEINDkjSIwSFJGsTgkCQNYnBIkgYxOCRJgxgckqRB/j8RuTlhjQwUywAAAABJRU5ErkJggg==\n",
"text/plain": [
"<Figure size 432x288 with 1 Axes>"
]
},
"metadata": {
"needs_background": "light"
},
"output_type": "display_data"
}
],
"source": [
"r=%sql select dname,avg(sal) from DEPT join EMP using (DEPTNO) group by dname\n",
"%matplotlib inline\n",
"r.bar()"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.7.3"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment