{"cells":[{"cell_type":"markdown","id":"be61f466-82b5-48e6-bcb4-d9369cf7e899","metadata":{},"outputs":[],"source":["\n","<center>\n","    <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/assets/logos/SN_web_lightmode.png\" width=\"300\" alt=\"cognitiveclass.ai logo\">\n","</center>\n","\n","\n","# Access DB2 on Cloud using Python\n","\n","\n","Estimated time needed: **15** minutes\n","    \n","\n","## Objectives\n","\n","After completing this lab you will be able to:\n","\n","* Create a table\n","* Insert data into the table\n","* Query data from the table\n","* Retrieve the result set into a pandas dataframe\n","* Close the database connection\n"]},{"cell_type":"markdown","id":"c0c3a23c-56f0-4e44-8fad-7d725a9e244b","metadata":{},"outputs":[],"source":["\n","__Notice:__ Please follow the instructions given in the first Lab of this course to Create a database service instance of Db2 on Cloud.\n","\n","## Task 1: Import the `ibm_db` Python library\n","\n","The `ibm_db` [API ](https://pypi.python.org/pypi/ibm_db/) provides a variety of useful Python functions for accessing and manipulating data in an IBM® data server database, including functions for connecting to a database, preparing and issuing SQL statements, fetching rows from result sets, calling stored procedures, committing and rolling back transactions, handling errors, and retrieving metadata.\n","\n","\n","We import the ibm_db library into our Python Application\n","\n","The following required modules are pre-installed in the Skills Network Labs environment. However if you run this notebook commands in a different Jupyter environment (e.g. Watson Studio or Ananconda) you may need to install these libraries by removing the `#` sign before `!pip` in the code cell below.\n"]},{"cell_type":"code","id":"8f938dcf-fd8f-47ad-82a9-f31565cf69ec","metadata":{},"outputs":[],"source":["# These libraries are pre-installed in SN Labs. If running in another environment please uncomment lines below to install them:\n# !pip install --force-reinstall ibm_db==3.1.0 ibm_db_sa==0.3.3\n# Ensure we don't load_ext with sqlalchemy>=1.4 (incompadible)\n# !pip uninstall sqlalchemy==1.4 -y && pip install sqlalchemy==1.3.24\n# !pip install ipython-sql"]},{"cell_type":"code","id":"0f4a7491-2590-40c3-bcb6-42f7d3a0462a","metadata":{},"outputs":[],"source":["!pip install --force-reinstall ibm_db ibm_db_sa\nimport ibm_db"]},{"cell_type":"markdown","id":"ed266b06-ad8b-47a6-bd80-8f9c3196089a","metadata":{},"outputs":[],"source":["When the command above completes, the `ibm_db` library is loaded in your notebook. \n","\n","\n","## Task 2: Identify the database connection credentials\n","\n","Connecting to dashDB or DB2 database requires the following information:\n","* Driver Name\n","* Database name \n","* Host DNS name or IP address \n","* Host port\n","* Connection protocol\n","* User ID\n","* User Password\n","\n","\n","\n","__Notice:__ To obtain credentials please refer to the instructions given in the first Lab of this course\n","\n","Now enter your database credentials below\n","\n","Replace the placeholder values in angular brackets <> below with your actual database credentials \n","\n","e.g. replace \"database\" with \"BLUDB\"\n","\n"]},{"cell_type":"code","id":"804e4525-437b-4e9a-add1-8f29e234b076","metadata":{},"outputs":[],"source":["#Replace the placeholder values with the actuals for your Db2 Service Credentials\ndsn_driver = \"{IBM DB2 ODBC DRIVER}\"\ndsn_database = \"database\"            # e.g. \"BLUDB\"\ndsn_hostname = \"hostname\"            # e.g.: \"dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net\"\ndsn_port = \"port\"                    # e.g. \"50000\" \ndsn_protocol = \"protocol\"            # i.e. \"TCPIP\"\ndsn_uid = \"username\"                 # e.g. \"abc12345\"\ndsn_pwd = \"password\"                 # e.g. \"7dBZ3wWt9XN6$o0J\"\ndsn_security = \"SSL\"              #i.e. \"SSL\""]},{"cell_type":"markdown","id":"949976d1-67ce-4cb7-aeb6-e900768cf761","metadata":{},"outputs":[],"source":["## Task 3: Create the database connection\n","\n","Ibm_db API uses the IBM Data Server Driver for ODBC and CLI APIs to connect to IBM DB2 and Informix.\n","\n","\n","Create the database connection\n"]},{"cell_type":"code","id":"260d9235-03d0-43e1-a12a-3bf33bf24a6c","metadata":{},"outputs":[],"source":["#Create database connection\n#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter\ndsn = (\n    \"DRIVER={0};\"\n    \"DATABASE={1};\"\n    \"HOSTNAME={2};\"\n    \"PORT={3};\"\n    \"PROTOCOL={4};\"\n    \"UID={5};\"\n    \"PWD={6};\"\n    \"SECURITY={7};\").format(dsn_driver, dsn_database, dsn_hostname, dsn_port, dsn_protocol, dsn_uid, dsn_pwd,dsn_security)\n\ntry:\n    conn = ibm_db.connect(dsn, \"\", \"\")\n    print (\"Connected to database: \", dsn_database, \"as user: \", dsn_uid, \"on host: \", dsn_hostname)\n\nexcept:\n    print (\"Unable to connect: \", ibm_db.conn_errormsg() )\n"]},{"cell_type":"markdown","id":"8018ca8e-c6ad-41dc-8a37-73081e749ff9","metadata":{},"outputs":[],"source":["## Task 4: Create a table in the database\n","\n","In this step we will create a table in the database with following details:\n","\n","<img src=\"https://ibm.box.com/shared/static/ztd2cn4xkdoj5erlk4hhng39kbp63s1h.jpg\" align=\"center\">\n"]},{"cell_type":"code","id":"1553ed4e-7a98-496c-9514-2a84cc66985f","metadata":{},"outputs":[],"source":["#Lets first drop the table INSTRUCTOR in case it exists from a previous attempt\ndropQuery = \"drop table INSTRUCTOR\"\n\n#Now execute the drop statment\ndropStmt = ibm_db.exec_immediate(conn, dropQuery)"]},{"cell_type":"markdown","id":"11c156b9-0114-455b-91c7-7bd38b914381","metadata":{},"outputs":[],"source":["## Dont worry if you get this error:\n","If you see an exception/error similar to the following, indicating that INSTRUCTOR is an undefined name, that's okay. It just implies that the INSTRUCTOR table does not exist in the table - which would be the case if you had not created it previously.\n","\n","Exception: [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  \"ABC12345.INSTRUCTOR\" is an undefined name.  SQLSTATE=42704 SQLCODE=-204\n"]},{"cell_type":"code","id":"e02afac0-3d27-4c87-85b0-70e2cede5f26","metadata":{},"outputs":[],"source":["#Construct the Create Table DDL statement - replace the ... with rest of the statement\ncreateQuery = \"create table INSTRUCTOR(id INTEGER PRIMARY KEY NOT NULL, fname ...)\"\n\n#Now fill in the name of the method and execute the statement\ncreateStmt = ibm_db.replace_with_name_of_execution_method(conn, createQuery)"]},{"cell_type":"markdown","id":"174f118e-e852-4c2d-b6d8-39df747a6a7b","metadata":{},"outputs":[],"source":["\n","<details><summary>Click here for the solution</summary>\n","\n","```python\n","createQuery = \"create table INSTRUCTOR(ID INTEGER PRIMARY KEY NOT NULL, FNAME VARCHAR(20), LNAME VARCHAR(20), CITY VARCHAR(20), CCODE CHAR(2))\"\n","\n","createStmt = ibm_db.exec_immediate(conn,createQuery)\n","```\n","\n","</details>\n"]},{"cell_type":"markdown","id":"22ef6736-1567-46a9-8917-3893836831ca","metadata":{},"outputs":[],"source":["## Task 5: Insert data into the table\n","\n","In this step we will insert some rows of data into the table. \n","\n","The INSTRUCTOR table we created in the previous step contains 3 rows of data:\n","\n","<img src=\"https://ibm.box.com/shared/static/j5yjassxefrjknivfpekj7698dqe4d8i.jpg\" align=\"center\">\n","\n","We will start by inserting just the first row of data, i.e. for instructor Rav Ahuja \n"]},{"cell_type":"code","id":"d3d770c6-5b7a-4570-b61d-55f0b63253d0","metadata":{},"outputs":[],"source":["#Construct the query - replace ... with the insert statement\ninsertQuery = \"...\"\n\n#execute the insert statement\ninsertStmt = ibm_db.exec_immediate(conn, insertQuery)"]},{"cell_type":"markdown","id":"8b800dc3-e912-414d-b766-e23ca1202efa","metadata":{},"outputs":[],"source":["<details><summary>Click here for the solution</summary>\n","\n","```python\n","insertQuery = \"insert into INSTRUCTOR values (1, 'Rav', 'Ahuja', 'TORONTO', 'CA')\"\n","\n","insertStmt = ibm_db.exec_immediate(conn, insertQuery)\n","```\n","\n","</details>\n"]},{"cell_type":"markdown","id":"da841819-711f-4a9a-bb3a-b673ecfb316f","metadata":{},"outputs":[],"source":["Now use a single query to insert the remaining two rows of data\n"]},{"cell_type":"code","id":"1147def5-7283-4b65-8c9a-b174d47b0446","metadata":{},"outputs":[],"source":["#replace ... with the insert statement that inerts the remaining two rows of data\ninsertQuery2 = \"...\"\n\n#execute the statement\ninsertStmt2 = ibm_db.exec_immediate(conn, insertQuery2)"]},{"cell_type":"markdown","id":"95c3988d-7275-4da0-96cc-52a44fee086f","metadata":{},"outputs":[],"source":["<details><summary>Click here for the solution</summary>\n","\n","```python\n","insertQuery2 = \"insert into INSTRUCTOR values (2, 'Raul', 'Chong', 'Markham', 'CA'), (3, 'Hima', 'Vasudevan', 'Chicago', 'US')\"\n","\n","insertStmt2 = ibm_db.exec_immediate(conn, insertQuery2)\n","\n","```\n","\n","</details>\n"]},{"cell_type":"markdown","id":"162c7bd1-60b8-4c2b-be05-8e477b283958","metadata":{},"outputs":[],"source":["## Task 6: Query data in the table\n","\n","In this step we will retrieve data we inserted into the INSTRUCTOR table. \n"]},{"cell_type":"code","id":"330f9a56-fa25-419f-baa1-729c5574d10f","metadata":{},"outputs":[],"source":["#Construct the query that retrieves all rows from the INSTRUCTOR table\nselectQuery = \"select * from INSTRUCTOR\"\n\n#Execute the statement\nselectStmt = ibm_db.exec_immediate(conn, selectQuery)\n\n#Fetch the Dictionary (for the first row only) - replace ... with your code\n..."]},{"cell_type":"markdown","id":"df552fc2-c021-43b7-a5cc-72ab58d68da3","metadata":{},"outputs":[],"source":["<details><summary>Click here for the solution</summary>\n","\n","```python\n","#Construct the query that retrieves all rows from the INSTRUCTOR table\n","selectQuery = \"select * from INSTRUCTOR\"\n","\n","#Execute the statement\n","selectStmt = ibm_db.exec_immediate(conn, selectQuery)\n","\n","#Fetch the Dictionary (for the first row only)\n","ibm_db.fetch_both(selectStmt)\n","\n","```\n","\n","</details>\n"]},{"cell_type":"code","id":"97bc6e27-9f11-46ab-b182-58d1d83b25ed","metadata":{},"outputs":[],"source":["#Fetch the rest of the rows and print the ID and FNAME for those rows\nwhile ibm_db.fetch_row(selectStmt) != False:\n   print (\" ID:\",  ibm_db.result(selectStmt, 0), \" FNAME:\",  ibm_db.result(selectStmt, \"FNAME\"))"]},{"cell_type":"markdown","id":"099d9f02-e97e-4147-a73b-46acf8db89f6","metadata":{},"outputs":[],"source":["<details><summary>Click here for the solution</summary>\n","\n","```python\n","#Fetch the rest of the rows and print the ID and FNAME for those rows\n","while ibm_db.fetch_row(selectStmt) != False:\n","    print (\" ID:\",  ibm_db.result(selectStmt, 0), \" FNAME:\",  ibm_db.result(selectStmt, \"FNAME\"))\n","\n","```\n","\n","</details>\n"]},{"cell_type":"markdown","id":"b1a685a4-4bdc-4fde-ace3-1952fcadb230","metadata":{},"outputs":[],"source":["Bonus: now write and execute an update statement that changes the Rav's CITY to MOOSETOWN \n"]},{"cell_type":"code","id":"d5ddaeb6-ff69-444a-ac82-c157c17c6699","metadata":{},"outputs":[],"source":["#Enter your code below\n"]},{"cell_type":"markdown","id":"7096de38-8e67-437a-83da-73815408387e","metadata":{},"outputs":[],"source":["<details><summary>Click here for the solution</summary>\n","\n","```python\n","updateQuery = \"update INSTRUCTOR set CITY='MOOSETOWN' where FNAME='Rav'\"\n","updateStmt = ibm_db.exec_immediate(conn, updateQuery)\n","\n","```\n","\n","</details>\n"]},{"cell_type":"markdown","id":"14b9a91d-fafe-4d68-ba84-45f06e0562fd","metadata":{},"outputs":[],"source":["## Task 7: Retrieve data into Pandas \n","\n","In this step we will retrieve the contents of the INSTRUCTOR table into a Pandas dataframe\n"]},{"cell_type":"code","id":"8e0b3cfe-4ecf-4bfb-a616-330d1d938c76","metadata":{},"outputs":[],"source":["!pip install pandas\nimport pandas\nimport ibm_db_dbi"]},{"cell_type":"code","id":"607b33bc-f547-4624-b10d-665353ac96e9","metadata":{},"outputs":[],"source":["#connection for pandas\npconn = ibm_db_dbi.Connection(conn)"]},{"cell_type":"code","id":"31b9a7d7-a365-42bb-ae52-1c30b1e4c444","metadata":{},"outputs":[],"source":["#query statement to retrieve all rows in INSTRUCTOR table\nselectQuery = \"select * from INSTRUCTOR\"\n\n#retrieve the query results into a pandas dataframe\npdf = pandas.read_sql(selectQuery, pconn)\n\n#print just the LNAME for first row in the pandas data frame\npdf.LNAME[0]"]},{"cell_type":"code","id":"4969bcc7-e870-447c-a1d0-a7936df0c052","metadata":{},"outputs":[],"source":["#print the entire data frame\npdf"]},{"cell_type":"markdown","id":"a7263f3b-054a-4e59-bbb9-631c9ccf0679","metadata":{},"outputs":[],"source":["Once the data is in a Pandas dataframe, you can do the typical pandas operations on it. \n","\n","For example you can use the shape method to see how many rows and columns are in the dataframe\n"]},{"cell_type":"code","id":"19a67d5d-ff1b-4273-a171-8e61f92a4778","metadata":{},"outputs":[],"source":["pdf.shape"]},{"cell_type":"markdown","id":"e99aed6d-83a1-4dbc-8e34-1c8efb4faf4a","metadata":{},"outputs":[],"source":["## Task 8: Close the Connection\n","We free all resources by closing the connection. Remember that it is always important to close connections so that we can avoid unused connections taking up resources.\n"]},{"cell_type":"code","id":"40816d48-bdf4-42e3-a2f2-c3b14032a947","metadata":{},"outputs":[],"source":["ibm_db.close(conn)"]},{"cell_type":"markdown","id":"2b32013b-b52c-4919-9a32-c5a4641d54a9","metadata":{},"outputs":[],"source":["## Summary\n","\n","In this tutorial you established a connection to a database instance of DB2 Warehouse on Cloud from a Python notebook using ibm_db API. Then created a table and insert a few rows of data into it. Then queried the data. You also retrieved the data into a pandas dataframe.\n"]},{"cell_type":"markdown","id":"33fec3df-af70-4ef8-acdd-8d78d8e57f9c","metadata":{},"outputs":[],"source":["## Author\n","\n","<a href=\"https://www.linkedin.com/in/ravahuja/\" target=\"_blank\">Rav Ahuja</a>\n","\n","## <h3 align=\"center\"> © IBM Corporation 2020. All rights reserved. <h3/>\n","\n","```{toggle}## Change Log\n","```\n","```{toggle}|  Date (YYYY-MM-DD) |  Version | Changed By  |  Change Description |\n","```\n","```{toggle}|---|---|---|---|\n","```\n","```{toggle}| 2021-11-17  | 2.2  | Lakshmi  | Updated library  |\n","```\n","```{toggle}| 2021-07-09  | 2.1  | Malika   | Updated connection string   |\n","```\n","```{toggle}| 2020-08-28  | 2.0  | Lavanya  |  Moved lab to course repo in GitLab |\n","```\n"]}],"metadata":{"kernelspec":{"name":"python3","display_name":"Python 3 (ipykernel)","language":"python"},"language_info":{"name":"python","version":"3.11.9","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"prev_pub_hash":"004cf28a13ff3679b3ac2ca54c26923d68fb1b47b1ae0fa9484bbe1f8ee1f735"},"nbformat":4,"nbformat_minor":4}