API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 14th, 2019
513
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.07 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": 3,
  15. "metadata": {},
  16. "outputs": [],
  17. "source": [
  18. "#Replace the placeholder values with your actual Db2 hostname, username, and password:\n",
  19. "dsn_hostname = \"dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net\" # e.g.: \"dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net\"\n",
  20. "dsn_uid = \"bkl21830\" # e.g. \"abc12345\"\n",
  21. "dsn_pwd = \"1nhzc^3dv4cn9pn9\" # e.g. \"7dBZ3wWt9XN6$o0J\"\n",
  22. "\n",
  23. "dsn_driver = \"{IBM DB2 ODBC DRIVER}\"\n",
  24. "dsn_database = \"BLUDB\" # e.g. \"BLUDB\"\n",
  25. "dsn_port = \"50000\" # e.g. \"50000\" \n",
  26. "dsn_protocol = \"TCPIP\" # i.e. \"TCPIP\""
  27. ]
  28. },
  29. {
  30. "cell_type": "code",
  31. "execution_count": 4,
  32. "metadata": {},
  33. "outputs": [
  34. {
  35. "name": "stdout",
  36. "output_type": "stream",
  37. "text": [
  38. "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=bkl21830;PWD=1nhzc^3dv4cn9pn9;\n"
  39. ]
  40. }
  41. ],
  42. "source": [
  43. "#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter\n",
  44. "#Create the dsn connection string\n",
  45. "dsn = (\n",
  46. " \"DRIVER={0};\"\n",
  47. " \"DATABASE={1};\"\n",
  48. " \"HOSTNAME={2};\"\n",
  49. " \"PORT={3};\"\n",
  50. " \"PROTOCOL={4};\"\n",
  51. " \"UID={5};\"\n",
  52. " \"PWD={6};\").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd)\n",
  53. "\n",
  54. "#print the connection string to check correct values are specified\n",
  55. "print(dsn)"
  56. ]
  57. },
  58. {
  59. "cell_type": "code",
  60. "execution_count": 6,
  61. "metadata": {},
  62. "outputs": [
  63. {
  64. "name": "stdout",
  65. "output_type": "stream",
  66. "text": [
  67. "Connected to database: BLUDB as user: bkl21830 on host: dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net\n"
  68. ]
  69. }
  70. ],
  71. "source": [
  72. "#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter\n",
  73. "#Create database connection\n",
  74. "\n",
  75. "try:\n",
  76. " conn = ibm_db.connect(dsn, \"\", \"\")\n",
  77. " print (\"Connected to database: \", dsn_database, \"as user: \", dsn_uid, \"on host: \", dsn_hostname)\n",
  78. "\n",
  79. "except:\n",
  80. " print (\"Unable to connect: \", ibm_db.conn_errormsg() )\n"
  81. ]
  82. },
  83. {
  84. "cell_type": "code",
  85. "execution_count": 7,
  86. "metadata": {},
  87. "outputs": [],
  88. "source": [
  89. "dropQuery = \"drop table INSTRUCTOR\"\n",
  90. "dropStmt = ibm_db.exec_immediate(conn,dropQuery)"
  91. ]
  92. },
  93. {
  94. "cell_type": "code",
  95. "execution_count": 9,
  96. "metadata": {},
  97. "outputs": [
  98. {
  99. "ename": "AttributeError",
  100. "evalue": "module 'ibm_db' has no attribute 'replace_with_name_of_execution_method'",
  101. "output_type": "error",
  102. "traceback": [
  103. "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
  104. "\u001b[0;31mAttributeError\u001b[0m Traceback (most recent call last)",
  105. "\u001b[0;32m<ipython-input-9-c75c773fd9a3>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[0mcreateQuery\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"create table INSTRUCTOR(id INTEGER PRIMAR KEY NOT NULL, fname VARCHAR(10),lname VARCHAR(10),city VARCHAR(10),ccode CHAR(2))\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mcreatestmt\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mibm_db\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreplace_with_name_of_execution_method\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcreateQuery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
  106. "\u001b[0;31mAttributeError\u001b[0m: module 'ibm_db' has no attribute 'replace_with_name_of_execution_method'"
  107. ]
  108. }
  109. ],
  110. "source": [
  111. "createQuery = \"create table INSTRUCTOR(id INTEGER PRIMAR KEY NOT NULL, fname VARCHAR(10),lname VARCHAR(10),city VARCHAR(10),ccode CHAR(2))\"\n",
  112. "createstmt = ibm_db.replace_with_name_of_execution_method(conn, createQuery)"
  113. ]
  114. },
  115. {
  116. "cell_type": "code",
  117. "execution_count": 10,
  118. "metadata": {},
  119. "outputs": [
  120. {
  121. "ename": "AttributeError",
  122. "evalue": "module 'ibm_db' has no attribute 'replace_with_name_of_execution_method'",
  123. "output_type": "error",
  124. "traceback": [
  125. "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
  126. "\u001b[0;31mAttributeError\u001b[0m Traceback (most recent call last)",
  127. "\u001b[0;32m<ipython-input-10-c75c773fd9a3>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[0mcreateQuery\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"create table INSTRUCTOR(id INTEGER PRIMAR KEY NOT NULL, fname VARCHAR(10),lname VARCHAR(10),city VARCHAR(10),ccode CHAR(2))\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mcreatestmt\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mibm_db\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreplace_with_name_of_execution_method\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcreateQuery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
  128. "\u001b[0;31mAttributeError\u001b[0m: module 'ibm_db' has no attribute 'replace_with_name_of_execution_method'"
  129. ]
  130. }
  131. ],
  132. "source": [
  133. "createQuery = \"create table INSTRUCTOR(id INTEGER PRIMAR KEY NOT NULL, fname VARCHAR(10),lname VARCHAR(10),city VARCHAR(10),ccode CHAR(2))\"\n",
  134. "createstmt = ibm_db.replace_with_name_of_execution_method(conn, createQuery)"
  135. ]
  136. },
  137. {
  138. "cell_type": "code",
  139. "execution_count": 11,
  140. "metadata": {},
  141. "outputs": [
  142. {
  143. "ename": "Exception",
  144. "evalue": "[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token \"KEY\" was found following \"OR(id INTEGER PRIMAR\". Expected tokens may include: \"<references_spec>\". SQLSTATE=42601 SQLCODE=-104",
  145. "output_type": "error",
  146. "traceback": [
  147. "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
  148. "\u001b[0;31mException\u001b[0m Traceback (most recent call last)",
  149. "\u001b[0;32m<ipython-input-11-7a276474dc18>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[0mcreateQuery\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"create table INSTRUCTOR(id INTEGER PRIMAR KEY NOT NULL, fname VARCHAR(10),lname VARCHAR(10),city VARCHAR(10),ccode CHAR(2))\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mcreatestmt\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mibm_db\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexec_immediate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcreateQuery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
  150. "\u001b[0;31mException\u001b[0m: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token \"KEY\" was found following \"OR(id INTEGER PRIMAR\". Expected tokens may include: \"<references_spec>\". SQLSTATE=42601 SQLCODE=-104"
  151. ]
  152. }
  153. ],
  154. "source": [
  155. "createQuery = \"create table INSTRUCTOR(id INTEGER PRIMARY KEY NOT NULL, fname VARCHAR(10),lname VARCHAR(10),city VARCHAR(10),ccode CHAR(2))\"\n",
  156. "createstmt = ibm_db.exec_immediate(conn, createQuery)"
  157. ]
  158. },
  159. {
  160. "cell_type": "code",
  161. "execution_count": 12,
  162. "metadata": {},
  163. "outputs": [],
  164. "source": [
  165. "createQuery = \"create table INSTRUCTOR(id INTEGER PRIMARY KEY NOT NULL, fname VARCHAR(10),lname VARCHAR(10),city VARCHAR(10),ccode CHAR(2))\"\n",
  166. "createstmt = ibm_db.exec_immediate(conn, createQuery)\n"
  167. ]
  168. },
  169. {
  170. "cell_type": "code",
  171. "execution_count": 15,
  172. "metadata": {},
  173. "outputs": [],
  174. "source": [
  175. "insertQuery = \"INSERT INTO INSTRUCTOR(id,fname,lname,city,ccode) values (1,'Rav','Ahuja','Toronto','CA'),(2,'Raul','Chong','Markham','CA'),(3,'Hima','Vasudevan','Chicago','US')\"\n",
  176. "insertStmt = ibm_db.exec_immediate(conn,insertQuery)"
  177. ]
  178. },
  179. {
  180. "cell_type": "code",
  181. "execution_count": 16,
  182. "metadata": {},
  183. "outputs": [],
  184. "source": [
  185. "selectQuery = \"select * from INSTRUCTOR\"\n",
  186. "selectstmt = ibm_db.exec_immediate(conn,selectQuery)"
  187. ]
  188. },
  189. {
  190. "cell_type": "code",
  191. "execution_count": 17,
  192. "metadata": {},
  193. "outputs": [
  194. {
  195. "data": {
  196. "text/plain": [
  197. "{'ID': 1,\n",
  198. " 0: 1,\n",
  199. " 'FNAME': 'Rav',\n",
  200. " 1: 'Rav',\n",
  201. " 'LNAME': 'Ahuja',\n",
  202. " 2: 'Ahuja',\n",
  203. " 'CITY': 'Toronto',\n",
  204. " 3: 'Toronto',\n",
  205. " 'CCODE': 'CA',\n",
  206. " 4: 'CA'}"
  207. ]
  208. },
  209. "execution_count": 17,
  210. "metadata": {},
  211. "output_type": "execute_result"
  212. }
  213. ],
  214. "source": [
  215. "ibm_db.fetch_both(selectstmt)"
  216. ]
  217. },
  218. {
  219. "cell_type": "code",
  220. "execution_count": 18,
  221. "metadata": {},
  222. "outputs": [],
  223. "source": [
  224. "import pandas"
  225. ]
  226. },
  227. {
  228. "cell_type": "code",
  229. "execution_count": 19,
  230. "metadata": {},
  231. "outputs": [
  232. {
  233. "data": {
  234. "text/plain": [
  235. "{'ID': 2,\n",
  236. " 0: 2,\n",
  237. " 'FNAME': 'Raul',\n",
  238. " 1: 'Raul',\n",
  239. " 'LNAME': 'Chong',\n",
  240. " 2: 'Chong',\n",
  241. " 'CITY': 'Markham',\n",
  242. " 3: 'Markham',\n",
  243. " 'CCODE': 'CA',\n",
  244. " 4: 'CA'}"
  245. ]
  246. },
  247. "execution_count": 19,
  248. "metadata": {},
  249. "output_type": "execute_result"
  250. }
  251. ],
  252. "source": [
  253. "ibm_db.fetch_both(selectstmt)"
  254. ]
  255. },
  256. {
  257. "cell_type": "code",
  258. "execution_count": 22,
  259. "metadata": {},
  260. "outputs": [],
  261. "source": [
  262. "while ibm_db.fetch_row(selectstmt) != False:\n",
  263. " print(\" ID:\", ibm_db.result(selectstmt,0), \"Fname: \", ibm_db.result(selectstmt,\"fname\"))"
  264. ]
  265. },
  266. {
  267. "cell_type": "code",
  268. "execution_count": 26,
  269. "metadata": {},
  270. "outputs": [],
  271. "source": [
  272. "updateQuery = \"update INSTRUCTOR set CITY='MOOSETOWN' WHERE FNAME ='Rav'\"\n",
  273. "updatestmt = ibm_db.exec_immediate(conn,updateQuery)"
  274. ]
  275. },
  276. {
  277. "cell_type": "code",
  278. "execution_count": 27,
  279. "metadata": {},
  280. "outputs": [],
  281. "source": [
  282. "import ibm_db_dbi"
  283. ]
  284. },
  285. {
  286. "cell_type": "code",
  287. "execution_count": 28,
  288. "metadata": {},
  289. "outputs": [],
  290. "source": [
  291. "pconn = ibm_db_dbi.Connection(conn)"
  292. ]
  293. },
  294. {
  295. "cell_type": "code",
  296. "execution_count": 29,
  297. "metadata": {},
  298. "outputs": [],
  299. "source": [
  300. "selectQuery = \"select *from INSTRUCTOR\""
  301. ]
  302. },
  303. {
  304. "cell_type": "code",
  305. "execution_count": 30,
  306. "metadata": {},
  307. "outputs": [],
  308. "source": [
  309. "pdf = pandas.read_sql(selectQuery,pconn\n",
  310. " )"
  311. ]
  312. },
  313. {
  314. "cell_type": "code",
  315. "execution_count": 31,
  316. "metadata": {},
  317. "outputs": [
  318. {
  319. "data": {
  320. "text/plain": [
  321. "'Ahuja'"
  322. ]
  323. },
  324. "execution_count": 31,
  325. "metadata": {},
  326. "output_type": "execute_result"
  327. }
  328. ],
  329. "source": [
  330. "pdf.LNAME[0]"
  331. ]
  332. },
  333. {
  334. "cell_type": "code",
  335. "execution_count": 32,
  336. "metadata": {},
  337. "outputs": [
  338. {
  339. "data": {
  340. "text/html": [
  341. "<div>\n",
  342. "<style scoped>\n",
  343. " .dataframe tbody tr th:only-of-type {\n",
  344. " vertical-align: middle;\n",
  345. " }\n",
  346. "\n",
  347. " .dataframe tbody tr th {\n",
  348. " vertical-align: top;\n",
  349. " }\n",
  350. "\n",
  351. " .dataframe thead th {\n",
  352. " text-align: right;\n",
  353. " }\n",
  354. "</style>\n",
  355. "<table border=\"1\" class=\"dataframe\">\n",
  356. " <thead>\n",
  357. " <tr style=\"text-align: right;\">\n",
  358. " <th></th>\n",
  359. " <th>ID</th>\n",
  360. " <th>FNAME</th>\n",
  361. " <th>LNAME</th>\n",
  362. " <th>CITY</th>\n",
  363. " <th>CCODE</th>\n",
  364. " </tr>\n",
  365. " </thead>\n",
  366. " <tbody>\n",
  367. " <tr>\n",
  368. " <th>0</th>\n",
  369. " <td>1</td>\n",
  370. " <td>Rav</td>\n",
  371. " <td>Ahuja</td>\n",
  372. " <td>MOOSETOWN</td>\n",
  373. " <td>CA</td>\n",
  374. " </tr>\n",
  375. " <tr>\n",
  376. " <th>1</th>\n",
  377. " <td>2</td>\n",
  378. " <td>Raul</td>\n",
  379. " <td>Chong</td>\n",
  380. " <td>Markham</td>\n",
  381. " <td>CA</td>\n",
  382. " </tr>\n",
  383. " <tr>\n",
  384. " <th>2</th>\n",
  385. " <td>3</td>\n",
  386. " <td>Hima</td>\n",
  387. " <td>Vasudevan</td>\n",
  388. " <td>Chicago</td>\n",
  389. " <td>US</td>\n",
  390. " </tr>\n",
  391. " </tbody>\n",
  392. "</table>\n",
  393. "</div>"
  394. ],
  395. "text/plain": [
  396. " ID FNAME LNAME CITY CCODE\n",
  397. "0 1 Rav Ahuja MOOSETOWN CA\n",
  398. "1 2 Raul Chong Markham CA\n",
  399. "2 3 Hima Vasudevan Chicago US"
  400. ]
  401. },
  402. "execution_count": 32,
  403. "metadata": {},
  404. "output_type": "execute_result"
  405. }
  406. ],
  407. "source": [
  408. "pdf"
  409. ]
  410. },
  411. {
  412. "cell_type": "code",
  413. "execution_count": 33,
  414. "metadata": {},
  415. "outputs": [
  416. {
  417. "data": {
  418. "text/plain": [
  419. "(3, 5)"
  420. ]
  421. },
  422. "execution_count": 33,
  423. "metadata": {},
  424. "output_type": "execute_result"
  425. }
  426. ],
  427. "source": [
  428. "pdf.shape\n",
  429. "\n"
  430. ]
  431. },
  432. {
  433. "cell_type": "code",
  434. "execution_count": null,
  435. "metadata": {},
  436. "outputs": [],
  437. "source": []
  438. }
  439. ],
  440. "metadata": {
  441. "kernelspec": {
  442. "display_name": "Python 3",
  443. "language": "python",
  444. "name": "python3"
  445. },
  446. "language_info": {
  447. "codemirror_mode": {
  448. "name": "ipython",
  449. "version": 3
  450. },
  451. "file_extension": ".py",
  452. "mimetype": "text/x-python",
  453. "name": "python",
  454. "nbconvert_exporter": "python",
  455. "pygments_lexer": "ipython3",
  456. "version": "3.6.8"
  457. }
  458. },
  459. "nbformat": 4,
  460. "nbformat_minor": 2
  461. }
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!