API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Sep 21st, 2016
61
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.58 KB | None | 0 0
raw download clone embed print report
  1. {
  2. "cells": [
  3. {
  4. "cell_type": "code",
  5. "execution_count": 13,
  6. "metadata": {
  7. "ExecuteTime": {
  8. "end_time": "2016-09-21T11:36:25.037811",
  9. "start_time": "2016-09-21T11:36:25.012904"
  10. },
  11. "collapsed": false
  12. },
  13. "outputs": [],
  14. "source": [
  15. "from IPython.core.magic import (register_line_magic, register_cell_magic,\n",
  16. " register_line_cell_magic)\n",
  17. "from IPython.display import display\n",
  18. "from IPython.display import HTML\n",
  19. "import pandas as pd\n",
  20. "import pandas.io.sql as psql\n",
  21. "import psycopg2\n",
  22. "\n",
  23. "import credentials"
  24. ]
  25. },
  26. {
  27. "cell_type": "markdown",
  28. "metadata": {},
  29. "source": [
  30. "## Connect to the database using credentials"
  31. ]
  32. },
  33. {
  34. "cell_type": "code",
  35. "execution_count": 14,
  36. "metadata": {
  37. "ExecuteTime": {
  38. "end_time": "2016-09-21T11:36:25.816961",
  39. "start_time": "2016-09-21T11:36:25.787434"
  40. },
  41. "collapsed": false
  42. },
  43. "outputs": [],
  44. "source": [
  45. "conn = psycopg2.connect(database=credentials.database,\n",
  46. " host=credentials.host,\n",
  47. " port=credentials.port,\n",
  48. " user=credentials.username,\n",
  49. " password=credentials.password)\n",
  50. "\n",
  51. "conn.autocommit = True"
  52. ]
  53. },
  54. {
  55. "cell_type": "markdown",
  56. "metadata": {},
  57. "source": [
  58. "## Execute SQL command and query data from the database"
  59. ]
  60. },
  61. {
  62. "cell_type": "markdown",
  63. "metadata": {},
  64. "source": [
  65. "### Create new table"
  66. ]
  67. },
  68. {
  69. "cell_type": "code",
  70. "execution_count": 19,
  71. "metadata": {
  72. "ExecuteTime": {
  73. "end_time": "2016-09-21T11:39:33.977764",
  74. "start_time": "2016-09-21T11:39:33.838421"
  75. },
  76. "collapsed": false
  77. },
  78. "outputs": [
  79. {
  80. "data": {
  81. "text/plain": [
  82. "<cursor object at 0x115801430; closed: 0>"
  83. ]
  84. },
  85. "execution_count": 19,
  86. "metadata": {},
  87. "output_type": "execute_result"
  88. }
  89. ],
  90. "source": [
  91. "sql = '''\n",
  92. "DROP TABLE IF EXISTS example_data_table;\n",
  93. "CREATE TABLE example_data_table\n",
  94. "AS \n",
  95. "SELECT 1 col1, 2 col2, 3 col3;\n",
  96. "'''\n",
  97. "psql.execute(sql, conn)"
  98. ]
  99. },
  100. {
  101. "cell_type": "markdown",
  102. "metadata": {},
  103. "source": [
  104. "### Query results into a pandas dataframe"
  105. ]
  106. },
  107. {
  108. "cell_type": "code",
  109. "execution_count": 23,
  110. "metadata": {
  111. "ExecuteTime": {
  112. "end_time": "2016-09-21T11:41:02.538333",
  113. "start_time": "2016-09-21T11:41:02.459945"
  114. },
  115. "collapsed": false
  116. },
  117. "outputs": [],
  118. "source": [
  119. "sql = '''\n",
  120. "SELECT *\n",
  121. "FROM example_data_table\n",
  122. "'''\n",
  123. "df = psql.read_sql(sql, conn)"
  124. ]
  125. },
  126. {
  127. "cell_type": "code",
  128. "execution_count": 24,
  129. "metadata": {
  130. "ExecuteTime": {
  131. "end_time": "2016-09-21T11:41:02.716222",
  132. "start_time": "2016-09-21T11:41:02.676607"
  133. },
  134. "collapsed": false
  135. },
  136. "outputs": [
  137. {
  138. "data": {
  139. "text/html": [
  140. "<div>\n",
  141. "<table border=\"1\" class=\"dataframe\">\n",
  142. " <thead>\n",
  143. " <tr style=\"text-align: right;\">\n",
  144. " <th></th>\n",
  145. " <th>col1</th>\n",
  146. " <th>col2</th>\n",
  147. " <th>col3</th>\n",
  148. " </tr>\n",
  149. " </thead>\n",
  150. " <tbody>\n",
  151. " <tr>\n",
  152. " <th>0</th>\n",
  153. " <td>1</td>\n",
  154. " <td>2</td>\n",
  155. " <td>3</td>\n",
  156. " </tr>\n",
  157. " </tbody>\n",
  158. "</table>\n",
  159. "</div>"
  160. ],
  161. "text/plain": [
  162. " col1 col2 col3\n",
  163. "0 1 2 3"
  164. ]
  165. },
  166. "execution_count": 24,
  167. "metadata": {},
  168. "output_type": "execute_result"
  169. }
  170. ],
  171. "source": [
  172. "df"
  173. ]
  174. },
  175. {
  176. "cell_type": "markdown",
  177. "metadata": {},
  178. "source": [
  179. "## Utilize Jupyter Magic\n",
  180. "### These magic commands are helpful for interacting with the database"
  181. ]
  182. },
  183. {
  184. "cell_type": "code",
  185. "execution_count": 25,
  186. "metadata": {
  187. "ExecuteTime": {
  188. "end_time": "2016-09-21T11:41:36.853951",
  189. "start_time": "2016-09-21T11:41:36.826985"
  190. },
  191. "collapsed": false
  192. },
  193. "outputs": [],
  194. "source": [
  195. "_df = None\n",
  196. "@register_cell_magic\n",
  197. "def showsql(line, cell):\n",
  198. " \"\"\"\n",
  199. " Extract the code in the specific cell (should be valid SQL), and execute\n",
  200. " it using the connection object to the backend database. \n",
  201. " The resulting Pandas dataframe\n",
  202. " is rendered inline below the cell using IPython.display.\n",
  203. " You'd use this for SELECT\n",
  204. " \"\"\"\n",
  205. " #Use the global connection object defined above.\n",
  206. " global conn\n",
  207. " global _df\n",
  208. " _df = psql.read_sql(cell, conn)\n",
  209. " conn.commit()\n",
  210. " display(_df)\n",
  211. " return\n",
  212. " \n",
  213. "@register_cell_magic\n",
  214. "def execsql(line, cell):\n",
  215. " \"\"\"\n",
  216. " Extract the code in the specific cell (should be valid SQL), and execute\n",
  217. " it using the connection object to the backend database. \n",
  218. " You'd use this for CREATE/UPDATE/DELETE\n",
  219. " \"\"\"\n",
  220. " #Use the global connection object defined above.\n",
  221. " global conn\n",
  222. " global _df\n",
  223. " _df = psql.execute(cell, conn)\n",
  224. " conn.commit()\n",
  225. " return\n",
  226. "\n",
  227. "# We delete these to avoid name conflicts for automagic to work\n",
  228. "del execsql, showsql"
  229. ]
  230. },
  231. {
  232. "cell_type": "code",
  233. "execution_count": 26,
  234. "metadata": {
  235. "ExecuteTime": {
  236. "end_time": "2016-09-21T11:41:38.033078",
  237. "start_time": "2016-09-21T11:41:37.874356"
  238. },
  239. "collapsed": false
  240. },
  241. "outputs": [],
  242. "source": [
  243. "%%execsql\n",
  244. "DROP TABLE IF EXISTS example_data_table;\n",
  245. "CREATE TABLE example_data_table\n",
  246. "AS \n",
  247. "SELECT 1 col1, 2 col2, 3 col3;"
  248. ]
  249. },
  250. {
  251. "cell_type": "code",
  252. "execution_count": 27,
  253. "metadata": {
  254. "ExecuteTime": {
  255. "end_time": "2016-09-21T11:41:38.656976",
  256. "start_time": "2016-09-21T11:41:38.624547"
  257. },
  258. "collapsed": false
  259. },
  260. "outputs": [
  261. {
  262. "data": {
  263. "text/html": [
  264. "<div>\n",
  265. "<table border=\"1\" class=\"dataframe\">\n",
  266. " <thead>\n",
  267. " <tr style=\"text-align: right;\">\n",
  268. " <th></th>\n",
  269. " <th>col1</th>\n",
  270. " <th>col2</th>\n",
  271. " <th>col3</th>\n",
  272. " </tr>\n",
  273. " </thead>\n",
  274. " <tbody>\n",
  275. " <tr>\n",
  276. " <th>0</th>\n",
  277. " <td>1</td>\n",
  278. " <td>2</td>\n",
  279. " <td>3</td>\n",
  280. " </tr>\n",
  281. " </tbody>\n",
  282. "</table>\n",
  283. "</div>"
  284. ],
  285. "text/plain": [
  286. " col1 col2 col3\n",
  287. "0 1 2 3"
  288. ]
  289. },
  290. "metadata": {},
  291. "output_type": "display_data"
  292. }
  293. ],
  294. "source": [
  295. "%%showsql\n",
  296. "SELECT *\n",
  297. "FROM example_data_table"
  298. ]
  299. },
  300. {
  301. "cell_type": "code",
  302. "execution_count": 29,
  303. "metadata": {
  304. "ExecuteTime": {
  305. "end_time": "2016-09-21T11:42:31.964638",
  306. "start_time": "2016-09-21T11:42:31.938667"
  307. },
  308. "collapsed": false
  309. },
  310. "outputs": [
  311. {
  312. "data": {
  313. "text/html": [
  314. "<div>\n",
  315. "<table border=\"1\" class=\"dataframe\">\n",
  316. " <thead>\n",
  317. " <tr style=\"text-align: right;\">\n",
  318. " <th></th>\n",
  319. " <th>col1</th>\n",
  320. " <th>col2</th>\n",
  321. " <th>col3</th>\n",
  322. " </tr>\n",
  323. " </thead>\n",
  324. " <tbody>\n",
  325. " <tr>\n",
  326. " <th>0</th>\n",
  327. " <td>1</td>\n",
  328. " <td>2</td>\n",
  329. " <td>3</td>\n",
  330. " </tr>\n",
  331. " </tbody>\n",
  332. "</table>\n",
  333. "</div>"
  334. ],
  335. "text/plain": [
  336. " col1 col2 col3\n",
  337. "0 1 2 3"
  338. ]
  339. },
  340. "execution_count": 29,
  341. "metadata": {},
  342. "output_type": "execute_result"
  343. }
  344. ],
  345. "source": [
  346. "# global variable: result of query in pandas dataframe \n",
  347. "_df "
  348. ]
  349. }
  350. ],
  351. "metadata": {
  352. "anaconda-cloud": {},
  353. "kernelspec": {
  354. "display_name": "Python 2",
  355. "language": "python",
  356. "name": "python2"
  357. },
  358. "language_info": {
  359. "codemirror_mode": {
  360. "name": "ipython",
  361. "version": 2
  362. },
  363. "file_extension": ".py",
  364. "mimetype": "text/x-python",
  365. "name": "python",
  366. "nbconvert_exporter": "python",
  367. "pygments_lexer": "ipython2",
  368. "version": "2.7.12"
  369. }
  370. },
  371. "nbformat": 4,
  372. "nbformat_minor": 0
  373. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!