API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Aug 20th, 2019
68
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.83 KB | None | 0 0
raw download clone embed print report
  1. {
  2. "cells": [
  3. {
  4. "cell_type": "code",
  5. "execution_count": 34,
  6. "metadata": {},
  7. "outputs": [],
  8. "source": [
  9. "import ibm_db\n",
  10. "import ibm_db_dbi\n",
  11. "import pandas"
  12. ]
  13. },
  14. {
  15. "cell_type": "code",
  16. "execution_count": 15,
  17. "metadata": {},
  18. "outputs": [],
  19. "source": [
  20. "dsn_hostname = \"dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net\" \n",
  21. "dsn_uid = \"rvq37092\" \n",
  22. "dsn_pwd = \"mtbl2z7w@fjr4tdm\" \n",
  23. "\n",
  24. "dsn_driver = \"{IBM DB2 ODBC DRIVER}\"\n",
  25. "dsn_database = \"BLUDB\" \n",
  26. "dsn_port = \"50000\" \n",
  27. "dsn_protocol = \"TCPIP\" "
  28. ]
  29. },
  30. {
  31. "cell_type": "code",
  32. "execution_count": 16,
  33. "metadata": {},
  34. "outputs": [
  35. {
  36. "name": "stdout",
  37. "output_type": "stream",
  38. "text": [
  39. "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=rvq37092;PWD=mtbl2z7w@fjr4tdm;\n"
  40. ]
  41. }
  42. ],
  43. "source": [
  44. "#используется как атрибут при создании объекта подключения в методе ibm_db.connect()\n",
  45. "dsn = (\n",
  46. " f\"DRIVER={dsn_driver};\"\n",
  47. " f\"DATABASE={dsn_database};\"\n",
  48. " f\"HOSTNAME={dsn_hostname};\"\n",
  49. " f\"PORT={dsn_port};\"\n",
  50. " f\"PROTOCOL={dsn_protocol};\"\n",
  51. " f\"UID={dsn_uid};\"\n",
  52. " f\"PWD={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": 17,
  61. "metadata": {},
  62. "outputs": [
  63. {
  64. "name": "stdout",
  65. "output_type": "stream",
  66. "text": [
  67. "Connected to database: BLUDB as user: rvq37092 on host: dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net\n"
  68. ]
  69. }
  70. ],
  71. "source": [
  72. "#создание объекта подключения к БД\n",
  73. "try:\n",
  74. " conn = ibm_db.connect(dsn, \"\", \"\")\n",
  75. " print (\"Connected to database: \", dsn_database, \"as user: \", dsn_uid, \"on host: \", dsn_hostname)\n",
  76. "\n",
  77. "except:\n",
  78. " print (\"Unable to connect: \", ibm_db.conn_errormsg() )"
  79. ]
  80. },
  81. {
  82. "cell_type": "code",
  83. "execution_count": 18,
  84. "metadata": {},
  85. "outputs": [
  86. {
  87. "name": "stdout",
  88. "output_type": "stream",
  89. "text": [
  90. "DBMS_NAME: DB2/LINUXX8664\n",
  91. "DBMS_VER: 11.01.0303\n",
  92. "DB_NAME: BLUDB\n"
  93. ]
  94. }
  95. ],
  96. "source": [
  97. "server = ibm_db.server_info(conn)\n",
  98. "\n",
  99. "print (\"DBMS_NAME: \", server.DBMS_NAME)\n",
  100. "print (\"DBMS_VER: \", server.DBMS_VER)\n",
  101. "print (\"DB_NAME: \", server.DB_NAME)"
  102. ]
  103. },
  104. {
  105. "cell_type": "code",
  106. "execution_count": 19,
  107. "metadata": {},
  108. "outputs": [
  109. {
  110. "name": "stdout",
  111. "output_type": "stream",
  112. "text": [
  113. "DRIVER_NAME: libdb2.a\n",
  114. "DRIVER_VER: 11.01.0404\n",
  115. "DATA_SOURCE_NAME: BLUDB\n",
  116. "DRIVER_ODBC_VER: 03.51\n",
  117. "ODBC_VER: 03.01.0000\n",
  118. "ODBC_SQL_CONFORMANCE: EXTENDED\n",
  119. "APPL_CODEPAGE: 1208\n",
  120. "CONN_CODEPAGE: 1208\n"
  121. ]
  122. }
  123. ],
  124. "source": [
  125. "client = ibm_db.client_info(conn)\n",
  126. "\n",
  127. "print (\"DRIVER_NAME: \", client.DRIVER_NAME) \n",
  128. "print (\"DRIVER_VER: \", client.DRIVER_VER)\n",
  129. "print (\"DATA_SOURCE_NAME: \", client.DATA_SOURCE_NAME)\n",
  130. "print (\"DRIVER_ODBC_VER: \", client.DRIVER_ODBC_VER)\n",
  131. "print (\"ODBC_VER: \", client.ODBC_VER)\n",
  132. "print (\"ODBC_SQL_CONFORMANCE: \", client.ODBC_SQL_CONFORMANCE)\n",
  133. "print (\"APPL_CODEPAGE: \", client.APPL_CODEPAGE)\n",
  134. "print (\"CONN_CODEPAGE: \", client.CONN_CODEPAGE)"
  135. ]
  136. },
  137. {
  138. "cell_type": "code",
  139. "execution_count": 20,
  140. "metadata": {},
  141. "outputs": [],
  142. "source": [
  143. "#ibm_db.close(conn)"
  144. ]
  145. },
  146. {
  147. "cell_type": "code",
  148. "execution_count": 44,
  149. "metadata": {},
  150. "outputs": [],
  151. "source": [
  152. "# Создание SQL запроса на стирание таблицы\n",
  153. "dropQuery = \"drop table INSTRUCTOR\"\n",
  154. "\n",
  155. "#Отправка SQL запроса\n",
  156. "dropStmt = ibm_db.exec_immediate(conn, dropQuery)"
  157. ]
  158. },
  159. {
  160. "cell_type": "code",
  161. "execution_count": 45,
  162. "metadata": {},
  163. "outputs": [],
  164. "source": [
  165. "# создание SQL запроса на создание таблицы\n",
  166. "createQuery = \"create table INSTRUCTOR(ID INTEGER PRIMARY KEY NOT NULL, FNAME VARCHAR(20), LNAME VARCHAR(20), CITY VARCHAR(20), CCODE CHAR(2))\"\n",
  167. "# отправка\n",
  168. "createStmt = ibm_db.exec_immediate(conn,createQuery)"
  169. ]
  170. },
  171. {
  172. "cell_type": "code",
  173. "execution_count": 46,
  174. "metadata": {},
  175. "outputs": [],
  176. "source": [
  177. "# заполнение таблицы тесовой информацией\n",
  178. "insertQuery = \"insert into INSTRUCTOR values (1, 'Rav', 'Ahuja', 'TORONTO', 'CA'), (2, 'Raul', 'Chong', 'Markham', 'CA'), (3, 'Hima', 'Vasudevan', 'Chicago', 'US')\"\n",
  179. "\n",
  180. "insertStmt = ibm_db.exec_immediate(conn, insertQuery)"
  181. ]
  182. },
  183. {
  184. "cell_type": "code",
  185. "execution_count": 50,
  186. "metadata": {},
  187. "outputs": [
  188. {
  189. "data": {
  190. "text/plain": [
  191. "{'ID': 1,\n",
  192. " 0: 1,\n",
  193. " 'FNAME': 'Rav',\n",
  194. " 1: 'Rav',\n",
  195. " 'LNAME': 'Ahuja',\n",
  196. " 2: 'Ahuja',\n",
  197. " 'CITY': 'Moscow',\n",
  198. " 3: 'Moscow',\n",
  199. " 'CCODE': 'CA',\n",
  200. " 4: 'CA'}"
  201. ]
  202. },
  203. "execution_count": 50,
  204. "metadata": {},
  205. "output_type": "execute_result"
  206. }
  207. ],
  208. "source": [
  209. "# создание SQL запроса \"выбрать все строки из таблицы\"\n",
  210. "selectQuery = \"select * from INSTRUCTOR\"\n",
  211. "\n",
  212. "# отправка таблицы\n",
  213. "selectStmt = ibm_db.exec_immediate(conn, selectQuery)\n",
  214. "\n",
  215. "# получение JSON\n",
  216. "ibm_db.fetch_both(selectStmt)"
  217. ]
  218. },
  219. {
  220. "cell_type": "code",
  221. "execution_count": 49,
  222. "metadata": {},
  223. "outputs": [],
  224. "source": [
  225. "# создание SQL запроса \"заменить город у Rav\"\n",
  226. "updateQuery = \"update INSTRUCTOR SET CITY='Moscow' where FNAME='Rav'\"\n",
  227. "\n",
  228. "# отправка таблицы\n",
  229. "updateStmt = ibm_db.exec_immediate(conn, updateQuery)"
  230. ]
  231. },
  232. {
  233. "cell_type": "code",
  234. "execution_count": 51,
  235. "metadata": {},
  236. "outputs": [],
  237. "source": [
  238. "# соединение для работы модуля pandas\n",
  239. "pconn = ibm_db_dbi.Connection(conn)"
  240. ]
  241. },
  242. {
  243. "cell_type": "code",
  244. "execution_count": 59,
  245. "metadata": {},
  246. "outputs": [
  247. {
  248. "data": {
  249. "text/html": [
  250. "<div>\n",
  251. "<style scoped>\n",
  252. " .dataframe tbody tr th:only-of-type {\n",
  253. " vertical-align: middle;\n",
  254. " }\n",
  255. "\n",
  256. " .dataframe tbody tr th {\n",
  257. " vertical-align: top;\n",
  258. " }\n",
  259. "\n",
  260. " .dataframe thead th {\n",
  261. " text-align: right;\n",
  262. " }\n",
  263. "</style>\n",
  264. "<table border=\"1\" class=\"dataframe\">\n",
  265. " <thead>\n",
  266. " <tr style=\"text-align: right;\">\n",
  267. " <th></th>\n",
  268. " <th>ID</th>\n",
  269. " <th>FNAME</th>\n",
  270. " <th>LNAME</th>\n",
  271. " <th>CITY</th>\n",
  272. " <th>CCODE</th>\n",
  273. " </tr>\n",
  274. " </thead>\n",
  275. " <tbody>\n",
  276. " <tr>\n",
  277. " <th>0</th>\n",
  278. " <td>1</td>\n",
  279. " <td>Rav</td>\n",
  280. " <td>Ahuja</td>\n",
  281. " <td>Moscow</td>\n",
  282. " <td>CA</td>\n",
  283. " </tr>\n",
  284. " <tr>\n",
  285. " <th>1</th>\n",
  286. " <td>2</td>\n",
  287. " <td>Raul</td>\n",
  288. " <td>Chong</td>\n",
  289. " <td>Markham</td>\n",
  290. " <td>CA</td>\n",
  291. " </tr>\n",
  292. " <tr>\n",
  293. " <th>2</th>\n",
  294. " <td>3</td>\n",
  295. " <td>Hima</td>\n",
  296. " <td>Vasudevan</td>\n",
  297. " <td>Chicago</td>\n",
  298. " <td>US</td>\n",
  299. " </tr>\n",
  300. " </tbody>\n",
  301. "</table>\n",
  302. "</div>"
  303. ],
  304. "text/plain": [
  305. " ID FNAME LNAME CITY CCODE\n",
  306. "0 1 Rav Ahuja Moscow CA\n",
  307. "1 2 Raul Chong Markham CA\n",
  308. "2 3 Hima Vasudevan Chicago US"
  309. ]
  310. },
  311. "execution_count": 59,
  312. "metadata": {},
  313. "output_type": "execute_result"
  314. }
  315. ],
  316. "source": [
  317. "selectQuery = \"select * from INSTRUCTOR\"\n",
  318. "\n",
  319. "pdf = pandas.read_sql(selectQuery, pconn)\n",
  320. "\n",
  321. "#pdf.CITY LNAME, FNAME и т.д.\n",
  322. "pdf"
  323. ]
  324. },
  325. {
  326. "cell_type": "code",
  327. "execution_count": 60,
  328. "metadata": {},
  329. "outputs": [
  330. {
  331. "data": {
  332. "text/plain": [
  333. "(3, 5)"
  334. ]
  335. },
  336. "execution_count": 60,
  337. "metadata": {},
  338. "output_type": "execute_result"
  339. }
  340. ],
  341. "source": [
  342. "pdf.shape"
  343. ]
  344. },
  345. {
  346. "cell_type": "code",
  347. "execution_count": 61,
  348. "metadata": {},
  349. "outputs": [
  350. {
  351. "data": {
  352. "text/plain": [
  353. "True"
  354. ]
  355. },
  356. "execution_count": 61,
  357. "metadata": {},
  358. "output_type": "execute_result"
  359. }
  360. ],
  361. "source": [
  362. "ibm_db.close(conn)"
  363. ]
  364. }
  365. ],
  366. "metadata": {
  367. "kernelspec": {
  368. "display_name": "Python",
  369. "language": "python",
  370. "name": "conda-env-python-py"
  371. },
  372. "language_info": {
  373. "codemirror_mode": {
  374. "name": "ipython",
  375. "version": 3
  376. },
  377. "file_extension": ".py",
  378. "mimetype": "text/x-python",
  379. "name": "python",
  380. "nbconvert_exporter": "python",
  381. "pygments_lexer": "ipython3",
  382. "version": "3.6.7"
  383. }
  384. },
  385. "nbformat": 4,
  386. "nbformat_minor": 4
  387. }
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!