This repository has been archived by the owner on Jul 18, 2024. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 24
/
ibm_db-fetch_tuple.py
125 lines (108 loc) · 6.62 KB
/
ibm_db-fetch_tuple.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
#! /usr/bin/python3
#-------------------------------------------------------------------------------------------------#
# NAME: ibm_db-fetch_tuple.py #
# #
# PURPOSE: This program is designed to illustrate how to use the ibm_db.fetch_tuple() API. #
# #
# Additional APIs used: #
# ibm_db.exec_immediate() #
# #
# USAGE: Log in as a Db2 database instance user (for example, db2inst1) and issue the #
# following command from a terminal window: #
# #
# ./ibm_db-fetch_tuple.py #
# #
#-------------------------------------------------------------------------------------------------#
# DISCLAIMER OF WARRANTIES AND LIMITATION OF LIABILITY #
# #
# (C) COPYRIGHT International Business Machines Corp. 2018, 2019 All Rights Reserved #
# Licensed Materials - Property of IBM #
# #
# US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP #
# Schedule Contract with IBM Corp. #
# #
# The following source code ("Sample") is owned by International Business Machines Corporation #
# or one of its subsidiaries ("IBM") and is copyrighted and licensed, not sold. You may use, #
# copy, modify, and distribute the Sample in any form without payment to IBM, for the purpose #
# of assisting you in the creation of Python applications using the ibm_db library. #
# #
# The Sample code is provided to you on an "AS IS" basis, without warranty of any kind. IBM #
# HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT #
# LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. #
# Some jurisdictions do not allow for the exclusion or limitation of implied warranties, so the #
# above limitations or exclusions may not apply to you. IBM shall not be liable for any damages #
# you suffer as a result of using, copying, modifying or distributing the Sample, even if IBM #
# has been advised of the possibility of such damages. #
#-------------------------------------------------------------------------------------------------#
# Load The Appropriate Python Modules
import sys # Provides Information About Python Interpreter Constants, Functions, & Methods
import ibm_db # Contains The APIs Needed To Work With Db2 Databases
#-------------------------------------------------------------------------------------------------#
# Import The Db2ConnectionMgr Class Definition, Attributes, And Methods That Have Been Defined #
# In The File Named "ibm_db_tools.py"; This Class Contains The Programming Logic Needed To #
# Establish And Terminate A Connection To A Db2 Server Or Database #
#-------------------------------------------------------------------------------------------------#
from ibm_db_tools import Db2ConnectionMgr
#-------------------------------------------------------------------------------------------------#
# Import The ipynb_exit Class Definition, Attributes, And Methods That Have Been Defined In The #
# File Named "ipynb_exit.py"; This Class Contains The Programming Logic Needed To Allow "exit()" #
# Functionality To Work Without Raising An Error Or Stopping The Kernel If The Application Is #
# Invoked In A Jupyter Notebook #
#-------------------------------------------------------------------------------------------------#
from ipynb_exit import exit
# Define And Initialize The Appropriate Variables
dbName = "SAMPLE"
userID = "db2inst1"
passWord = "Passw0rd"
dbConnection = None
resultSet = False
dataRecord = False
# Create An Instance Of The Db2ConnectionMgr Class And Use It To Connect To A Db2 Database
conn = Db2ConnectionMgr('DB', dbName, '', '', userID, passWord)
conn.openConnection()
if conn.returnCode is True:
dbConnection = conn.connectionID
else:
conn.closeConnection()
exit(-1)
# Define The SQL Statement That Is To Be Executed
sqlStatement = "SELECT deptname FROM department WHERE admrdept = 'A00'"
# Execute The SQL Statement Just Defined
print("Executing the SQL statement \"" + sqlStatement + "\" ... ", end="")
try:
resultSet = ibm_db.exec_immediate(dbConnection, sqlStatement)
except Exception:
pass
# If The SQL Statement Could Not Be Executed, Display An Error Message And Exit
if resultSet is False:
print("\nERROR: Unable to execute the SQL statement specified.\n")
conn.closeConnection()
exit(-1)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Display A Report Header
print("Query results:\n")
print("DEPTNAME")
print("____________________________")
# As Long As There Are Records In The Result Set Produced, ...
noData = False
while noData is False:
# Retrieve A Record And Store It In A Python Tuple
try:
dataRecord = ibm_db.fetch_tuple(resultSet)
except:
pass
# If The Data Could Not Be Retrieved Or There Was No Data To Retrieve, Set The
# "No Data" Flag And Exit The Loop
if dataRecord is False:
noData = True
# Otherwise, Display The Data Retrieved
else:
print(dataRecord[0])
# Add A Blank Line To The End Of The Report
print()
# Close The Database Connection That Was Opened Earlier
conn.closeConnection()
# Return Control To The Operating System
exit()