API tools faq
paste
Guest User

Untitled

a guest
Dec 13th, 2018
70
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.53 KB | None | 0 0
raw download clone embed print report
  1. {
  2. "cells": [
  3. {
  4. "cell_type": "code",
  5. "execution_count": 1,
  6. "metadata": {},
  7. "outputs": [],
  8. "source": [
  9. "import ibm_db"
  10. ]
  11. },
  12. {
  13. "cell_type": "code",
  14. "execution_count": 2,
  15. "metadata": {},
  16. "outputs": [],
  17. "source": [
  18. "dsn_hostname = \"dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net\"\n",
  19. "dsn_uid = \"kld43050\"\n",
  20. "dsn_pwd = \"jxn1fnf5njg75+w2\"\n",
  21. "dsn_protocol= \"TCPIP\"\n",
  22. "dsn_driver = \"{IBM DB2 ODBC DRIVER\"\n",
  23. "dsn_database= \"BLUDB\"\n",
  24. "dsn_port = \"50000\"\n"
  25. ]
  26. },
  27. {
  28. "cell_type": "code",
  29. "execution_count": 3,
  30. "metadata": {},
  31. "outputs": [],
  32. "source": [
  33. "dsn = (\n",
  34. " \"DRIVER={0};\"\n",
  35. " \"DATABASE={1};\"\n",
  36. " \"HOSTNAME={2};\"\n",
  37. " \"PORT={3};\"\n",
  38. " \"PROTOCOL={4};\"\n",
  39. " \"UID={5};\"\n",
  40. " \"PWD={6};\").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd)"
  41. ]
  42. },
  43. {
  44. "cell_type": "code",
  45. "execution_count": 4,
  46. "metadata": {},
  47. "outputs": [
  48. {
  49. "name": "stdout",
  50. "output_type": "stream",
  51. "text": [
  52. "DRIVER={IBM DB2 ODBC DRIVER;DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=kld43050;PWD=jxn1fnf5njg75+w2;\n"
  53. ]
  54. }
  55. ],
  56. "source": [
  57. "print(dsn)"
  58. ]
  59. },
  60. {
  61. "cell_type": "code",
  62. "execution_count": 5,
  63. "metadata": {},
  64. "outputs": [
  65. {
  66. "name": "stdout",
  67. "output_type": "stream",
  68. "text": [
  69. "Connected!\n"
  70. ]
  71. }
  72. ],
  73. "source": [
  74. "dsn = (\n",
  75. " \"DRIVER={0};\"\n",
  76. " \"DATABASE={1};\"\n",
  77. " \"HOSTNAME={2};\"\n",
  78. " \"PORT={3};\"\n",
  79. " \"PROTOCOL={4};\"\n",
  80. " \"UID={5};\"\n",
  81. " \"PWD={6};\").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd)\n",
  82. "\n",
  83. "try:\n",
  84. " conn = ibm_db.connect(dsn, \"\", \"\")\n",
  85. " print (\"Connected!\")\n",
  86. "\n",
  87. "except:\n",
  88. " print (\"Unable to connect to database\")"
  89. ]
  90. },
  91. {
  92. "cell_type": "code",
  93. "execution_count": 6,
  94. "metadata": {},
  95. "outputs": [],
  96. "source": [
  97. "dropQuery = \"drop table INSTRUCTOR\"\n",
  98. "\n",
  99. "#Now execute the drop statment\n",
  100. "dropStmt = ibm_db.exec_immediate(conn, dropQuery)"
  101. ]
  102. },
  103. {
  104. "cell_type": "code",
  105. "execution_count": 7,
  106. "metadata": {},
  107. "outputs": [],
  108. "source": [
  109. "#then Create Table DDL statement - replace the ... with rest of the statement\n",
  110. "createQuery = \"create table INSTRUCTOR(ID INTEGER PRIMARY KEY NOT NULL, FNAME VARCHAR(20), LNAME VARCHAR(20), CITY VARCHAR(20), CCODE CHAR(2))\"\n",
  111. "\n",
  112. "#Now fill in the name of the method and execute the statement\n",
  113. "createStmt = ibm_db.exec_immediate(conn, createQuery)"
  114. ]
  115. },
  116. {
  117. "cell_type": "code",
  118. "execution_count": 8,
  119. "metadata": {},
  120. "outputs": [],
  121. "source": [
  122. "#Construct the query - replace ... with the insert statement\n",
  123. "insertQuery = \"insert into INSTRUCTOR values (1, 'Rav', 'Ahuja', 'TORONTO', 'CA')\"\n",
  124. "\n",
  125. "#execute the insert statement\n",
  126. "insertStmt = ibm_db.exec_immediate(conn, insertQuery)"
  127. ]
  128. },
  129. {
  130. "cell_type": "code",
  131. "execution_count": 9,
  132. "metadata": {},
  133. "outputs": [],
  134. "source": [
  135. "insertQuery2 = \"insert into INSTRUCTOR values (2, 'Raul', 'Chong', 'Markham', 'CA'), (3, 'Hima', 'Vasudevan', 'Chicago', 'US')\"\n",
  136. "#execute the statement\n",
  137. "insertStmt2 = ibm_db.exec_immediate(conn, insertQuery2)"
  138. ]
  139. },
  140. {
  141. "cell_type": "code",
  142. "execution_count": 10,
  143. "metadata": {},
  144. "outputs": [
  145. {
  146. "data": {
  147. "text/plain": [
  148. "{'ID': 1,\n",
  149. " 0: 1,\n",
  150. " 'FNAME': 'Rav',\n",
  151. " 1: 'Rav',\n",
  152. " 'LNAME': 'Ahuja',\n",
  153. " 2: 'Ahuja',\n",
  154. " 'CITY': 'TORONTO',\n",
  155. " 3: 'TORONTO',\n",
  156. " 'CCODE': 'CA',\n",
  157. " 4: 'CA'}"
  158. ]
  159. },
  160. "execution_count": 10,
  161. "metadata": {},
  162. "output_type": "execute_result"
  163. }
  164. ],
  165. "source": [
  166. "#Construct the query that retrieves all rows from the INSTRUCTOR table\n",
  167. "selectQuery = \"select * from INSTRUCTOR\"\n",
  168. "\n",
  169. "#Execute the statement\n",
  170. "selectStmt = ibm_db.exec_immediate(conn, selectQuery)\n",
  171. "\n",
  172. "#Fetch the Dictionary (for the first row only)\n",
  173. "ibm_db.fetch_both(selectStmt)"
  174. ]
  175. },
  176. {
  177. "cell_type": "code",
  178. "execution_count": null,
  179. "metadata": {},
  180. "outputs": [],
  181. "source": [
  182. "#Fetch the rest of the rows and print the ID and FNAME for those rows\n",
  183. "while ibm_db.fetch_row(selectStmt) != False:\n",
  184. " print (\" ID:\", ibm_db.result(selectStmt, 0), \" FNAME:\", ibm_db.result(selectStmt, \"FNAME\"))"
  185. ]
  186. },
  187. {
  188. "cell_type": "code",
  189. "execution_count": 15,
  190. "metadata": {},
  191. "outputs": [],
  192. "source": [
  193. "updateQuery = \"update INSTRUCTOR set CITY='MOOSETOWN' where FNAME='Rav'\"\n",
  194. "updateStmt = ibm_db.exec_immediate(conn, updateQuery)"
  195. ]
  196. },
  197. {
  198. "cell_type": "code",
  199. "execution_count": 16,
  200. "metadata": {},
  201. "outputs": [],
  202. "source": [
  203. "import pandas\n",
  204. "import ibm_db_dbi"
  205. ]
  206. },
  207. {
  208. "cell_type": "code",
  209. "execution_count": 17,
  210. "metadata": {},
  211. "outputs": [],
  212. "source": [
  213. "#connection for pandas\n",
  214. "pconn = ibm_db_dbi.Connection(conn)"
  215. ]
  216. },
  217. {
  218. "cell_type": "code",
  219. "execution_count": 18,
  220. "metadata": {},
  221. "outputs": [
  222. {
  223. "data": {
  224. "text/plain": [
  225. "'Ahuja'"
  226. ]
  227. },
  228. "execution_count": 18,
  229. "metadata": {},
  230. "output_type": "execute_result"
  231. }
  232. ],
  233. "source": [
  234. "#query statement to retrieve all rows in INSTRUCTOR table\n",
  235. "selectQuery = \"select * from INSTRUCTOR\"\n",
  236. "\n",
  237. "#retrieve the query results into a pandas dataframe\n",
  238. "pdf = pandas.read_sql(selectQuery, pconn)\n",
  239. "\n",
  240. "#print just the LNAME for first row in the pandas data frame\n",
  241. "pdf.LNAME[0]"
  242. ]
  243. },
  244. {
  245. "cell_type": "code",
  246. "execution_count": 19,
  247. "metadata": {},
  248. "outputs": [
  249. {
  250. "data": {
  251. "text/html": [
  252. "<div>\n",
  253. "<style scoped>\n",
  254. " .dataframe tbody tr th:only-of-type {\n",
  255. " vertical-align: middle;\n",
  256. " }\n",
  257. "\n",
  258. " .dataframe tbody tr th {\n",
  259. " vertical-align: top;\n",
  260. " }\n",
  261. "\n",
  262. " .dataframe thead th {\n",
  263. " text-align: right;\n",
  264. " }\n",
  265. "</style>\n",
  266. "<table border=\"1\" class=\"dataframe\">\n",
  267. " <thead>\n",
  268. " <tr style=\"text-align: right;\">\n",
  269. " <th></th>\n",
  270. " <th>ID</th>\n",
  271. " <th>FNAME</th>\n",
  272. " <th>LNAME</th>\n",
  273. " <th>CITY</th>\n",
  274. " <th>CCODE</th>\n",
  275. " </tr>\n",
  276. " </thead>\n",
  277. " <tbody>\n",
  278. " <tr>\n",
  279. " <th>0</th>\n",
  280. " <td>1</td>\n",
  281. " <td>Rav</td>\n",
  282. " <td>Ahuja</td>\n",
  283. " <td>MOOSETOWN</td>\n",
  284. " <td>CA</td>\n",
  285. " </tr>\n",
  286. " <tr>\n",
  287. " <th>1</th>\n",
  288. " <td>2</td>\n",
  289. " <td>Raul</td>\n",
  290. " <td>Chong</td>\n",
  291. " <td>Markham</td>\n",
  292. " <td>CA</td>\n",
  293. " </tr>\n",
  294. " <tr>\n",
  295. " <th>2</th>\n",
  296. " <td>3</td>\n",
  297. " <td>Hima</td>\n",
  298. " <td>Vasudevan</td>\n",
  299. " <td>Chicago</td>\n",
  300. " <td>US</td>\n",
  301. " </tr>\n",
  302. " </tbody>\n",
  303. "</table>\n",
  304. "</div>"
  305. ],
  306. "text/plain": [
  307. " ID FNAME LNAME CITY CCODE\n",
  308. "0 1 Rav Ahuja MOOSETOWN CA\n",
  309. "1 2 Raul Chong Markham CA\n",
  310. "2 3 Hima Vasudevan Chicago US"
  311. ]
  312. },
  313. "execution_count": 19,
  314. "metadata": {},
  315. "output_type": "execute_result"
  316. }
  317. ],
  318. "source": [
  319. "#print the entire data frame\n",
  320. "pdf"
  321. ]
  322. },
  323. {
  324. "cell_type": "code",
  325. "execution_count": null,
  326. "metadata": {},
  327. "outputs": [],
  328. "source": []
  329. }
  330. ],
  331. "metadata": {
  332. "kernelspec": {
  333. "display_name": "Python 3",
  334. "language": "python",
  335. "name": "python3"
  336. },
  337. "language_info": {
  338. "codemirror_mode": {
  339. "name": "ipython",
  340. "version": 3
  341. },
  342. "file_extension": ".py",
  343. "mimetype": "text/x-python",
  344. "name": "python",
  345. "nbconvert_exporter": "python",
  346. "pygments_lexer": "ipython3",
  347. "version": "3.6.6"
  348. }
  349. },
  350. "nbformat": 4,
  351. "nbformat_minor": 2
  352. }
Add Comment
Please, Sign In to add comment
Public Pastes
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!