def run_test_197(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test")
except:
pass
rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(conn, "CREATE UNIQUE INDEX index1 ON index_test (id)")
print "Test first index table:"
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn,None,config.user,"index_test",True)
else:
result = ibm_db.statistics(conn,None,None,"INDEX_TEST",True)
row = ibm_db.fetch_tuple(result)
## skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print row[2] # TABLE_NAME
print row[3] # NON_UNIQUE
print row[5] # INDEX_NAME
print row[8] # COLUMN_NAME
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test2")
except:
pass
rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(conn, "CREATE INDEX index2 ON index_test2 (data)")
print "Test second index table:"
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn,None,config.user,"index_test2",True)
else:
result = ibm_db.statistics(conn,None,None,"INDEX_TEST2",True)
row = ibm_db.fetch_tuple(result)
### skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print row[2] # TABLE_NAME
print row[3] # NON_UNIQUE
print row[5] # INDEX_NAME
print row[8] # COLUMN_NAME
print "Test non-existent table:"
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn,None,config.user,"non_existent_table",True)
else:
result = ibm_db.statistics(conn,None,None,"NON_EXISTENT_TABLE",True)
row = ibm_db.fetch_tuple(result)
if row:
print "Non-Empty"
else:
print "Empty"
else:
print 'no connection: ' + ibm_db.conn_errormsg()
def run_test_042(self):
conn = ibm_db.connect(config.database, config.user, config.password)
#if ({}['EMPNO'] != None):
# result = ibm_db.exec_immediate(conn, "select photo_format, picture, length(picture) from emp_photo where photo_format='jpg' and empno='" + {}['EMPNO'] + "'")
# row = ibm_db.fetch_array(result)
# if row:
# # We'll be outputting a
# header('Content-type: image/' + row[0])
# header('Content-Length: ' + row[2])
# print row[1]
# else:
# print ibm_db.error()
# continue
#else:
result = ibm_db.exec_immediate(
conn,
"select EMPNO, PHOTO_FORMAT from emp_photo where photo_format='jpg'"
)
row = ibm_db.fetch_tuple(result)
while (row):
print(
"<a href='test_042.php?EMPNO=%s' target=_blank>%s (%s)</a><br>"
% (row[0], row[0], row[1]))
row = ibm_db.fetch_tuple(result)
def check_audit_col(src_db, schema, view):
# check if there are audit columns in PDA and BMSIW views
# src_db - either PDA or DB2
aud_col_list = []
if src_db == 'DB2':
sql = f"SELECT COLNAME, COLNO FROM IDAA.BMSIW_COLUMNS WHERE SCHEMA = '{schema}' AND "\
f"VIEWNAME = '{view}' ORDER BY COLNO DESC FETCH FIRST 3 ROWS ONLY;"
elif src_db == 'PDA':
sql = f"SELECT COLNAME, COLNO FROM IDAA.PDA_REL_COLUMNS WHERE CREATOR = '{schema}' AND "\
f"NAME = '{view}' ORDER BY COLNO DESC FETCH FIRST 3 ROWS ONLY;"
else:
#print('ERROR: Data not found!')
return aud_col_list
stmt = ibm_db.exec_immediate(conn, sql)
res = ibm_db.fetch_tuple(stmt)
while res:
aud_col_list.append(res[0])
res = ibm_db.fetch_tuple(stmt)
aud_col_list.reverse()
return aud_col_list
def select_sql(self,SQL):
stmt = ibm_db.exec_immediate(self.connection,SQL)
row = ibm_db.fetch_tuple(stmt)
log.info("execute sql {} {}".format(self.host,self.database))
while ( row ):
yield row
row = ibm_db.fetch_tuple(stmt)
def _read_routines_(self):
# SQL bodied routines
stmt = ibm_db.prepare(self._conn_, DBQueries.read_routines)
ibm_db.execute(stmt, ())
tpl = ibm_db.fetch_tuple(stmt)
while tpl:
specificname, routineschema, routinename, text, rtype, remarks = tpl
text = os.linesep.join([s for s in text.splitlines() if s])
r = DBRoutine(specificname, routineschema, routinename, text,
rtype, remarks)
self._routines_[(tpl[0], tpl[1], tpl[2])] = r
tpl = ibm_db.fetch_tuple(stmt)
# external routines
stmt = ibm_db.prepare(self._conn_, DBQueries.read_external_routines)
ibm_db.execute(stmt, ())
tpl = ibm_db.fetch_tuple(stmt)
while tpl:
specificname, routineschema, routinename, routinetype, typename, length, scale,\
language, parameter_style, deterministic, external_action, fenced, threadsafe,\
implementation, remarks, codepage, sql_data_access = tpl
r = DBRoutineExternal(specificname, routineschema, routinename,
routinetype, typename, length, scale,
language, parameter_style, deterministic,
external_action, fenced, threadsafe,
implementation, remarks, codepage,
sql_data_access)
self._routines_[(tpl[0], tpl[1], tpl[2])] = r
tpl = ibm_db.fetch_tuple(stmt)
self._read_external_routine_params_()
def getBatRes(conn, sql, commitnum=500):
''' Call fetch_tuple: '''
num = 0
tbname = 'TEST'
stmt = db2.exec_immediate(conn, sql)
res = db2.fetch_tuple(stmt)
while res != False:
insql = "insert into %s values('%s','%s','%s','%s','%s','%s',\
'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',\
'%s','%s','%s','%s','%s',%s,%s,%s,%s,%s,'%s',\
%s, %s, %s, %s)" \
%(tbname, res[0],res[1],res[2],res[3],res[4],res[5],\
res[6],res[7],res[8],res[9],res[10],res[11],\
res[12],res[13],res[14],\
res[15],res[16],res[17],res[18],res[19],res[20],\
res[21] or 'Null',\
res[22] or 'Null',res[23] or 'Null',\
res[24] or 'Null',res[25] or 'Null',res[26] or 'Null', \
res[27] or 'Null',res[28] or 'Null',res[29] or 'Null',\
res[30] or 'Null')
ret = insertRec(conn, insql)
if ret == True:
num += 1
if num % commitnum == 0:
print 'current records:%d, transcation commit.' %num
db2.commit(conn)
res = db2.fetch_tuple(stmt)
db2.commit(conn)
return num
def run_test_6561(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
stmt = ibm_db.exec_immediate(
conn,
"INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)"
)
statement = "SELECT count(id) FROM animals"
result = ibm_db.exec_immediate(conn, statement)
if ((not result) and ibm_db.stmt_error()):
print("ERROR: %s" % (ibm_db.stmt_errormsg(), ))
row = ibm_db.fetch_tuple(result)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(result)
ibm_db.rollback(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def fetch_all(stmt): t_list = [] t_row = ibm_db.fetch_tuple(stmt) while t_row: t_list.append(t_row) t_row = ibm_db.fetch_tuple(stmt) return t_list
def get_station_data(conn, station):
result = ibm_db.exec_immediate(conn, "SELECT DATE_,ENTRIES,EXITS FROM RIDES_PER_STATION WHERE STATION = \'"
+ station + "\'")
# Creating empty lists for dataframe
dates = []
entries = []
exits = []
# Parsing info from db2 into pandas dataframe
row = ibm_db.fetch_tuple(result)
while row:
dates.append(row[0])
entries.append(row[1])
exits.append(row[2])
row = ibm_db.fetch_tuple(result)
ibm_db.close(conn)
data = pd.DataFrame(data={'DATE': dates, 'ENTRIES': entries, 'EXITS': exits})
pandemic_date = date(2020, 3, 13)
for i in data.index:
current = data.loc[i, 'DATE']
data.loc[i, 'WEEKDAY'] = 'WEEKDAY' if current.isoweekday() < 6 else 'WEEKEND'
data.loc[i, 'PANDEMIC'] = 'PRE' if current <= pandemic_date else "POST"
data.sort_values(by='DATE', inplace=True)
return data
def call(self, itool):
"""Call xmlservice with accumulated input XML.
Args:
itool - iToolkit object
Returns:
xml
"""
if isinstance(self.uid, str):
conn = ibm_db.connect(self.db2, self.uid, self.pwd)
else:
conn = self.uid
# sql = "call " + self.lib + ".iPLUG512K(?,?,?,?)"
sql = "call " + self.lib + ".iPLUGR512K(?,?,?)"
stmt = ibm_db.prepare(conn, sql)
ipc = self.ipc
ctl = self.ctl
xml_in = itool.xml_in()
xml_out = ""
ibm_db.bind_param(stmt, 1, ipc, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 2, ctl, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 3, xml_in, ibm_db.SQL_PARAM_INPUT)
# ibm_db.bind_param(stmt, 4, xml_out, ibm_db.SQL_PARAM_OUTPUT)
result = ibm_db.execute(stmt)
if (result):
row = ibm_db.fetch_tuple(stmt)
while (row):
for i in row:
xml_out += i
row = ibm_db.fetch_tuple(stmt)
ibm_db.close(conn)
return xml_out
def run_test_142(self):
sql = "SELECT id, breed, name, weight FROM animals WHERE weight < ? AND weight > ?"
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.prepare(conn, sql)
weight = 200.05
mass = 2.0
ibm_db.bind_param(stmt, 1, weight, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 2, mass, ibm_db.SQL_PARAM_INPUT)
result = ibm_db.execute(stmt)
if ( result ):
row = ibm_db.fetch_tuple(stmt)
while ( row ):
#row.each { |child| print child }
for i in row:
print i
row = ibm_db.fetch_tuple(stmt)
ibm_db.close(conn)
else:
print "Connection failed."
def qtask6():
if request.method == 'POST':
latitude_one = float(request.form.get('latitudeValueOne'))
longitude_one = float(request.form.get('longitudeValueOne'))
latitude_two = float(request.form.get('latitudeValueTwo'))
longitude_two = float(request.form.get('longitudeValueTwo'))
depth_from = float(request.form.get('depthFrom'))
depth_to = float(request.form.get('depthTo'))
if latitude_one > latitude_two:
latitude_one, latitude_two = latitude_two, latitude_one
print('one')
if longitude_one > longitude_two:
longitude_one, longitude_two = longitude_two, longitude_one
print('two')
list_of_data = []
sql = "select l.place, q.time, q.id, q.depth, q.mag, q.nst, q.error FROM Q q INNER JOIN L l on l.id = q.id where (l.latitude between '" + str(
latitude_one) + "' and '" + str(
latitude_two) + "') and (l.longitude between '" + str(
longitude_one) + "' and '" + str(
longitude_two) + "' ) and q.depth between '" + str(
depth_from) + "' and '" + str(depth_to) + "'"
print(sql)
stmt = ibm_db.exec_immediate(conn, sql)
result = ibm_db.fetch_tuple(stmt)
# print(result)
while result:
list_of_data.append(result)
result = ibm_db.fetch_tuple(stmt)
print(list_of_data)
return render_template('qtask1.html', data=list_of_data)
def task1():
if request.method == 'POST':
latitude = request.form.get('latitudeValue')
longitude = request.form.get('longitudeValue')
list_of_data = []
sql = "SELECT *,\
(\
(\
(\
acos(\
sin((" + latitude + " * 0.01745329251))\
*\
sin((latitude * 0.01745329251)) +\
cos((" + latitude + " * 0.01745329251))\
*\
cos((latitude * 0.01745329251)) \
* \
cos(((" + longitude + " - longitude) * 0.01745329251))) \
) * 57.2957795131\
) * 60 * 1.1515 \
)\
as distance FROM eqi where mag > 6 ORDER BY Distance asc LIMIT 1"
# print(sql)
stmt = ibm_db.exec_immediate(conn, sql)
result = ibm_db.fetch_tuple(stmt)
# print(result)
while result:
list_of_data.append(result)
result = ibm_db.fetch_tuple(stmt)
# print(list_of_data)
return render_template('eqmagabove6.html',
data=list_of_data,
lat=latitude,
long=longitude)
def task5():
if request.method == 'POST':
latitude_one = float(request.form.get('latitudeValueOne'))
longitude_one = float(request.form.get('longitudeValueOne'))
latitude_two = float(request.form.get('latitudeValueTwo'))
longitude_two = float(request.form.get('longitudeValueTwo'))
if latitude_one > latitude_two:
latitude_one, latitude_two = latitude_two, latitude_one
print('one')
if longitude_one > longitude_two:
longitude_one, longitude_two = longitude_two, longitude_one
print('two')
list_of_data = []
sql = "(SELECT *\
FROM latlong where (latitude between '" + str(
latitude_one) + "' and '" + str(
latitude_two) + "') and (longitude between " + str(
longitude_one) + " and " + str(longitude_two) + " )) "
print(sql)
stmt = ibm_db.exec_immediate(conn, sql)
result = ibm_db.fetch_tuple(stmt)
# print(result)
while result:
list_of_data.append(result)
result = ibm_db.fetch_tuple(stmt)
# print(list_of_data)
return render_template('task5.html',
data=list_of_data,
latitude_one=latitude_one,
longitude_one=longitude_one,
latitude_two=latitude_two,
longitude_two=longitude_two)
def run_test_158(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50")
output = ''
row = ibm_db.fetch_assoc(result)
while ( row ):
output += str(row['ID']) + ', ' + row['NAME'] + ', ' + str(row['DEPT']) + ', ' + row['JOB'] + ', ' + str(row['YEARS']) + ', ' + str(row['SALARY']) + ', ' + str(row['COMM'])
row = ibm_db.fetch_assoc(result)
result2 = ibm_db.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')")
row2 = ibm_db.fetch_assoc(result2)
while ( row2 ):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
if (row2['LOCATION'] == None):
row2['LOCATION'] = ''
output += str(row2['DEPTNO']) + ', ' + row2['DEPTNAME'] + ', ' + str(row2['MGRNO']) + ', ' + row2['ADMRDEPT'] + ', ' + row2['LOCATION']
row2 = ibm_db.fetch_assoc(result2)
result3 = ibm_db.exec_immediate(conn,"SELECT * FROM employee WHERE lastname IN ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT')")
row3 = ibm_db.fetch_tuple(result3)
while ( row3 ):
output += row3[0] + ', ' + row3[3] + ', ' + row3[5]
row3=ibm_db.fetch_tuple(result3)
print(output)
def run_test_022(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.autocommit(conn, 0)
ac = ibm_db.autocommit(conn)
if ac != 0:
print("Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test")
#continue
ibm_db.exec_immediate(
conn,
"INSERT INTO animals values (7,'bug','Brain Bug',10000.1)")
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.rollback(conn)
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_020(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
ac = ibm_db.autocommit(conn)
if ac != 0:
print "Cannot set ibm_db.SQL_AUTOCOMMIT_OFF\nCannot run test"
#continue
ibm_db.exec_immediate(conn, "DELETE FROM animals")
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
ibm_db.rollback(conn)
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
ibm_db.close(conn)
else:
print "Connection failed."
def populateTableDump(inputConn, tableName):
sql = 'SELECT * FROM E0015DB.' + tableName + ' WHERE COMPANY_CODE = \'MLF\' FETCH FIRST ' + str(
determineRowCount(conn, tableName)) + ' ROWS ONLY'
stmt = ibm_db.exec_immediate(inputConn, sql)
dictionary = ibm_db.fetch_tuple(stmt)
tableList = []
while dictionary != False:
currentRow = list(dictionary)
for i in range(len(currentRow)):
try:
currentRow[i] = currentRow[i].strip()
except:
pass
try:
if i == 3 or i == 6:
currentRow[i] = currentRow[i].replace('\x9f', '*')
if i == 4 or i == 5:
currentRow[i] = currentRow[i].strftime('%Y-%m-%d')
except:
pass
tableList.append(currentRow)
dictionary = ibm_db.fetch_tuple(stmt)
return tableList
def run_test_020(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
ac = ibm_db.autocommit(conn)
if ac != 0:
print("Cannot set ibm_db.SQL_AUTOCOMMIT_OFF\nCannot run test")
#continue
ibm_db.exec_immediate(conn, "DELETE FROM animals")
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.rollback(conn)
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.close(conn)
else:
print("Connection failed.")
def get_legacy_view_name(pda_schema, pda_view):
sql = f"SELECT OLD_SCHEMA, OLD_VIEW_NAME FROM IDAA.PDA_VIEW_MAP WHERE NEW_SCHEMA = '{pda_schema}' AND NEW_VIEW_NAME = '{pda_view}' FETCH FIRST 1 ROW ONLY;"
stmt = ibm_db.exec_immediate(conn, sql)
tuple = ibm_db.fetch_tuple(stmt)
while tuple:
return tuple[0], tuple[1]
tuple = ibm_db.fetch_tuple(stmt)
def report_changes_for_contexts(schema, csvFile, outputcsv):
writer = print_legend(csvFile, outputcsv)
# Execute query to find all suffixes being replicated
sql = (
"select eid, dn_trunc "
"from {}.ldap_entry "
"where eid in "
"(select peid "
"from {}.ldap_entry as l, {}.OBJECTCLASS as o "
"where l.eid=o.eid "
"and o.OBJECTCLASS='IBM-REPLICAGROUP')"
).format(schema, schema, schema)
logger.debug("Executing SQL: {}".format(sql))
stmt = ibm_db.exec_immediate(conn, sql)
result = ibm_db.fetch_tuple(stmt)
while (result):
eid, context = result[0], result[1]
logger.debug("eid: {} context: {}".format(eid, format))
tablename = "REPLCHG{}".format(eid)
ret_data1 = get_changes(conn, schema, tablename, 0) # Successful changes
if not ret_data1:
logger.info("No replication data found for successful changes, perhaps no replication setup for {}?"
.format(context))
write_or_print_data(writer, context, None, None, None, outputcsv)
else:
maxChangeID = get_latest_update(conn, schema, tablename)
ret_data2 = get_changes(conn, schema, tablename, 1) # Oldest Pending changes
write_or_print_data(writer, context, ret_data1, maxChangeID, ret_data2, outputcsv)
result = ibm_db.fetch_tuple(stmt)
def run_test_158(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50")
output = ''
row = ibm_db.fetch_assoc(result)
while ( row ):
output += str(row['ID']) + ', ' + row['NAME'] + ', ' + str(row['DEPT']) + ', ' + row['JOB'] + ', ' + str(row['YEARS']) + ', ' + str(row['SALARY']) + ', ' + str(row['COMM'])
row = ibm_db.fetch_assoc(result)
result2 = ibm_db.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')")
row2 = ibm_db.fetch_assoc(result2)
while ( row2 ):
if (row2['MGRNO'] == None):
row2['MGRNO'] = ''
if (row2['LOCATION'] == None):
row2['LOCATION'] = ''
output += str(row2['DEPTNO']) + ', ' + row2['DEPTNAME'] + ', ' + str(row2['MGRNO']) + ', ' + row2['ADMRDEPT'] + ', ' + row2['LOCATION']
row2 = ibm_db.fetch_assoc(result2)
result3 = ibm_db.exec_immediate(conn,"SELECT * FROM employee WHERE lastname IN ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT')")
row3 = ibm_db.fetch_tuple(result3)
while ( row3 ):
output += row3[0] + ', ' + row3[3] + ', ' + row3[5]
row3=ibm_db.fetch_tuple(result3)
print(output)
def query_db(self, query, args=()):
"""Submits database query.
Examples:
for user in query_db('select * from users'):
print user['username'], 'has the id', user['user_id']
for user in query_db('select * from users where username = ?', [the_username]):
print user['username'], 'has the id', user['user_id']
Returns list
list = list of rows, where each row is represented using tuple
"""
rows = []
if self._conn:
log.debug("Running query\n" + query)
log.debug("Query params: " + pprint.pformat(args))
stmt = ibm_db.prepare(self._conn, query)
for i, param in enumerate(args):
ibm_db.bind_param(stmt, i, param)
ibm_db.execute(stmt)
if re.search('create|insert|update|delete', query, re.I):
return rows
row = ibm_db.fetch_tuple(stmt)
while (row):
rows.append(row)
row = ibm_db.fetch_tuple(stmt)
return rows
def run_test_146(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn:
name = "Peaches"
second_name = "Rickety Ride"
weight = 0
print("Values of bound parameters _before_ CALL:")
print(" 1: %s 2: %s 3: %d\n" % (name, second_name, weight))
stmt, name, second_name, weight = ibm_db.callproc(
conn, 'match_animal', (name, second_name, weight))
if stmt is not None:
print("Values of bound parameters _after_ CALL:")
print(" 1: %s 2: %s 3: %d\n" % (name, second_name, weight))
if (server.DBMS_NAME[0:3] != 'IDS'):
print("Results:")
row = ibm_db.fetch_tuple(stmt)
while (row):
print(" %s, %s, %s" %
(row[0].strip(), row[1].strip(), row[2]))
row = ibm_db.fetch_tuple(stmt)
def run_test_022(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.autocommit(conn, 0)
ac = ibm_db.autocommit(conn)
if ac != 0:
print("Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test")
#continue
ibm_db.exec_immediate(conn, "INSERT INTO animals values (7,'bug','Brain Bug',10000.1)")
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.rollback(conn)
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.close(conn)
else:
print("Connection failed.")
def getModel(self, spcItem):
spcId = spcItem["SPCID"]
sites = spcItem["SITE"]
groupingKeys = spcItem["GROUPING_KEYS"]
sql = self.getQuerySQL(spcItem)
if self._debug:
print sql
rets = {}
for site in sites:
conn = self.getDBConnection(site)
stmt = ibm_db.exec_immediate(conn, sql)
mtypeTuple = ibm_db.fetch_tuple(stmt)
mtypes = Set([])
while mtypeTuple != False:
mtypes.add(mtypeTuple[0])
mtypeTuple = ibm_db.fetch_tuple(stmt)
rets[site] = mtypes
if self._debug:
print site, mtypes
# union all the results
uRet = Set([])
for ret in rets.itervalues():
uRet = uRet.union(ret)
return list(uRet)
def run_test_143(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert1 = "INSERT INTO animals (id, breed, name, weight) VALUES (NULL, 'ghost', NULL, ?)"
select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL'
if conn:
stmt = ibm_db.prepare(conn, insert1)
animal = None
ibm_db.bind_param(stmt, 1, animal)
if ibm_db.execute(stmt):
stmt = ibm_db.exec_immediate(conn, select)
row = ibm_db.fetch_tuple(stmt)
while (row):
#row.each { |child| print child }
for i in row:
print(i)
row = ibm_db.fetch_tuple(stmt)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def get_db_list(admin_db, user):
global global_db_list
db_list = []
tLock.acquire()
print("db_list has acquired a lock")
# Get password
str = open('C:/Users/sxk11/PycharmProjects/Safari_1/test.txt', 'r').read()
conn = ibm_db.connect('ADM1P', 'tu01945', str)
sql = 'SELECT * FROM DB2ADM1P.UDB_DB_NAMES ' \
'where ' \
'db_all_chk_ind = \'Y\' ' \
'and db_alias in (\'LSS1D\', \'EDT5D\', \'EDT6D\', \'DBI1D\') '
# 'db_status = \'D\' '\
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
tuple = ibm_db.fetch_tuple(stmt)
while tuple != False:
print("The ID is : ", tuple[0])
print("The alias name is : ", tuple[3])
db_name = tuple[2]
db_list.append(db_name)
tuple = ibm_db.fetch_tuple(stmt)
print(db_list)
tLock.release()
print("db_list has completed lock released")
global_db_list = db_list
def comment(id):
conn = connect.connection()
if "mail" in session:
prevProjects = set()
sql_stmt = "select * from projekt where kennung = %s" % id
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
if prevPorjectsResult is not None:
row = ibm_db.fetch_tuple(prevPorjectsResult)
prevProjects.add(row)
while row:
row = ibm_db.fetch_tuple(prevPorjectsResult)
if row:
prevProjects.add(row)
if request.method == "POST":
comment = request.form["comment"]
anonymousid = request.form["anonymousid"]
mail = session["mail"]
if anonymousid == 'yes':
sql_stmt1 = "insert into komment (text,benutzer,projekt,sichtbarkeit) values ('" + comment + "' , '" + mail + "', '" + id + "','privat')"
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt1)
else:
sql_stmt2 = "insert into komment (text,benutzer,projekt,sichtbarkeit) values ('" + comment + "' , '" + mail + "', '" + id + "','oeffentlich')"
prevPorjectsResult = ibm_db.exec_immediate(conn, sql_stmt2)
return redirect(url_for("projectdetails", id=id))
return render_template('comment.html', prevProjects=prevProjects)
else:
return redirect(url_for("login"))
def project():
if "mail" in session:
conn = connect.connection()
openProjects = set()
sql_stmt = "select kennung,titel,finanzierungslimit,name,kategorie,status,ersteller from projekt inner join benutzer on projekt.ersteller=benutzer.email where ersteller = '%s' and status='offen'" % \
session["mail"]
openProjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
if openProjectsResult is not None:
row = ibm_db.fetch_tuple(openProjectsResult)
openProjects.add(row)
while row:
row = ibm_db.fetch_tuple(openProjectsResult)
if row:
openProjects.add(row)
closeProjects = set()
sql_stmt = "select kennung,titel,finanzierungslimit,name,kategorie,status,ersteller from projekt inner join benutzer on projekt.ersteller=benutzer.email where ersteller = '%s' and status='geschlossen'" % \
session["mail"]
closeProjectsResult = ibm_db.exec_immediate(conn, sql_stmt)
if closeProjectsResult is not None:
row = ibm_db.fetch_tuple(closeProjectsResult)
closeProjects.add(row)
while row:
row = ibm_db.fetch_tuple(closeProjectsResult)
if row:
closeProjects.add(row)
return render_template('projects.html', openProjects=openProjects, closeProjects=closeProjects,
ses_email=session["mail"])
else:
return redirect(url_for("login"))
def run_test_143(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
insert1 = "INSERT INTO animals (id, breed, name, weight) VALUES (NULL, 'ghost', NULL, ?)"
select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL'
if conn:
stmt = ibm_db.prepare(conn, insert1)
animal = None
ibm_db.bind_param(stmt, 1, animal)
if ibm_db.execute(stmt):
stmt = ibm_db.exec_immediate(conn, select)
row = ibm_db.fetch_tuple(stmt)
while ( row ):
#row.each { |child| print child }
for i in row:
print(i)
row = ibm_db.fetch_tuple(stmt)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_197(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test")
except:
pass
rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(conn, "CREATE UNIQUE INDEX index1 ON index_test (id)")
print("Test first index table:")
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn,None,config.user,"index_test",True)
else:
result = ibm_db.statistics(conn,None,None,"INDEX_TEST",True)
row = ibm_db.fetch_tuple(result)
## skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print(row[2]) # TABLE_NAME
print(row[3]) # NON_UNIQUE
print(row[5]) # INDEX_NAME
print(row[8]) # COLUMN_NAME
try:
rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test2")
except:
pass
rc = ibm_db.exec_immediate(conn, "CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))")
rc = ibm_db.exec_immediate(conn, "CREATE INDEX index2 ON index_test2 (data)")
print("Test second index table:")
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn,None,config.user,"index_test2",True)
else:
result = ibm_db.statistics(conn,None,None,"INDEX_TEST2",True)
row = ibm_db.fetch_tuple(result)
### skipping table info row. statistics returns informtation about table itself for informix ###
if (server.DBMS_NAME[0:3] == 'IDS'):
row = ibm_db.fetch_tuple(result)
print(row[2]) # TABLE_NAME
print(row[3]) # NON_UNIQUE
print(row[5]) # INDEX_NAME
print(row[8]) # COLUMN_NAME
print("Test non-existent table:")
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.statistics(conn,None,config.user,"non_existent_table",True)
else:
result = ibm_db.statistics(conn,None,None,"NON_EXISTENT_TABLE",True)
row = ibm_db.fetch_tuple(result)
if row:
print("Non-Empty")
else:
print("Empty")
else:
print('no connection: ' + ibm_db.conn_errormsg())
def run_test_064(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, 't')
else:
result = ibm_db.tables(conn, None, 'T')
for i in range(0, ibm_db.num_fields(result)):
print "%s, " % ibm_db.field_name(result, i)
print
print
i = 0
row = ibm_db.fetch_tuple(result)
while (row):
ibm_db.num_fields(result)
if (i < 4):
print ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , \n"
i = i + 1
row = ibm_db.fetch_tuple(result)
ibm_db.free_result(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
def run_test_064(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, 't')
else:
result = ibm_db.tables(conn, None, 'T')
for i in range(0, ibm_db.num_fields(result)):
print("%s, " % ibm_db.field_name(result, i))
print()
print()
i = 0
row = ibm_db.fetch_tuple(result)
while ( row ):
ibm_db.num_fields(result)
if (i < 4):
print(", " + row[1] + ", " + row[2] + ", " + row[3] + ", , \n")
i = i + 1
row = ibm_db.fetch_tuple(result)
ibm_db.free_result(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
def execute(self,sql):
self._stmt = ibm_db.exec_immediate(self._conn,sql)
self._result = ibm_db.fetch_tuple(self._stmt)
self.result_list = []
while(self._result):
self.result_list.append(self._result)
self._result = ibm_db.fetch_tuple(self._stmt)
return self.result_list
def queryTuples( self, _sql ):
stmt = ibm_db.exec_immediate( self.connection, _sql )
tuples = []
tup = ibm_db.fetch_tuple( stmt )
while tup != False:
tuples.append( tup )
tup = ibm_db.fetch_tuple( stmt )
return tuples
def connect_db2():
for (dbtype, customer, dbname, dbuser, dbpass, dbalias, sid, hostname,
dbport, usertunn, hostunn, portunn, umbralert, ip) in data_sql_db2:
print(dbtype, customer, dbalias, ip)
if customer[:1] != '#':
try:
# Create connection to remote DB2
con = ibm_db.connect(
"DATABASE=" + sid + ";HOSTNAME=" + hostname +
";PROTOCOL=TCPIP;UID=" + dbuser + ";PWD=" + dbpass +
";PORT=" + dbport, "", "")
except:
# Connection error handling
data_error = traceback.format_exc().splitlines()
list_connection_fail.append({
"CLIENTE": customer,
"ALIAS": dbalias,
"SID": sid,
"ERROR": str(data_error[-1])
})
# TODO make error handling for connection
else:
try:
# Execute query to get space
stmt = ibm_db.exec_immediate(
con,
"select DB_STORAGE_PATH,FS_TOTAL_SIZE,STO_PATH_FREE_SIZE,FS_USED_SIZE from sysibmadm.SNAPSTORAGE_PATHS"
)
except:
# Query error handling
data_error = traceback.format_exc().splitlines()
list_connection_fail.append({
"CLIENTE": customer,
"ALIAS": dbalias,
"SID": sid,
"ERROR": str(data_error[-1])
})
# TODO make error handling for query
else:
# Fetch first result
result = ibm_db.fetch_tuple(stmt)
inserts = []
while (result):
# TODO whatever needed with the result
inserts.append("('" + customer + "','" + dbtype +
"','" + time_i + "','" + sid + "','" +
dbname + "','" + str(result[0]) + "'," +
str(float(result[1]) / 1000.0) + "," +
str(float(result[2]) / 1000.0) + "," +
str(float(result[3]) / 1000.0) +
",0,0,0,0,0)")
result = ibm_db.fetch_tuple(stmt)
stm = "INSERT INTO espacios_stats values " + ','.join(
inserts) + ";"
cur_sql_db2.execute(stm)
cnx_1.commit()
return
def run_test_065(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, '%', "t3")
else:
result = ibm_db.tables(conn, None, '%', "T3")
columns = ibm_db.num_fields(result)
for i in range(0, columns):
print "%s, " % ibm_db.field_name(result, i)
print "\n\n"
row = ibm_db.fetch_tuple(result)
while ( row ):
final = ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , ";
row = ibm_db.fetch_tuple(result)
print final
ibm_db.free_result(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
def run_test_065(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
create = 'CREATE SCHEMA AUTHORIZATION t'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, '%', "t3")
else:
result = ibm_db.tables(conn, None, '%', "T3")
columns = ibm_db.num_fields(result)
for i in range(0, columns):
print("%s, " % ibm_db.field_name(result, i))
print("\n\n")
row = ibm_db.fetch_tuple(result)
while (row):
final = ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , "
row = ibm_db.fetch_tuple(result)
print(final)
ibm_db.free_result(result)
ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
def get_pda_schema_list():
sql = f"SELECT DISTINCT TRIM(NEW_SCHEMA) AS PDA_SCHEMA FROM IDAA.PDA_VIEW_MAP ORDER BY 1"
stmt = ibm_db.exec_immediate(conn, sql)
tuple = ibm_db.fetch_tuple(stmt)
pda_schemas = []
while tuple:
pda_schemas.append(tuple[0])
tuple = ibm_db.fetch_tuple(stmt)
return pda_schemas
def determineRowCount(inputConn, tableName):
sqlCount = 'SELECT COUNT(*) FROM E0015DB.' + tableName + ' WHERE COMPANY_CODE = \'MLF\''
stmt = ibm_db.exec_immediate(inputConn, sqlCount)
dictionary = ibm_db.fetch_tuple(stmt)
rowCount = 0
while dictionary != False:
rowCount = int(dictionary[0])
dictionary = ibm_db.fetch_tuple(stmt)
return rowCount
def get_pda_view_list(schema):
sql = f"SELECT DISTINCT TRIM(NEW_VIEW_NAME) AS PDA_VIEW FROM IDAA.PDA_VIEW_MAP WHERE NEW_SCHEMA = '{schema}'ORDER BY 1"
stmt = ibm_db.exec_immediate(conn, sql)
tuple = ibm_db.fetch_tuple(stmt)
pda_views = []
while tuple:
pda_views.append(tuple[0])
tuple = ibm_db.fetch_tuple(stmt)
return pda_views
def main():
conn = None
stmt = None
if not conf.has_option('passwd'):
conf.conf.set(conf.section, 'passwd', unicode(getpass.getpass('Пароль: '), 'utf-8'))
try:
conn = ibm_db.connect('DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=%s;UID=%s;PWD=%s;' % (conf.get('database'), conf.get('hostname'), conf.getint('port'),
conf.get('protocol'), conf.get('user'), conf.get('passwd')), '', '')
stmt = ibm_db.exec_immediate(conn, unicode(args.request[0], 'utf-8'))
try:
result = ibm_db.fetch_tuple(stmt)
except:
rows = ibm_db.num_rows(stmt)
if rows != -1:
print u'Обработано строк %d' % rows
else:
print u'Команда выполнена'
return
if result:
column_conv = []
head = u''
underline=u''
for i in xrange(len(result)):
if i != 0:
head += u'|'
underline += u'+'
name = ibm_db.field_name(stmt, i)
size = ibm_db.field_display_size(stmt, i)
if len(name) > size:
size = len(name)
if ibm_db.field_nullable(stmt, i) and len(u'NULL') > size:
size = len(u'NULL')
type_field = ibm_db.field_type(stmt, i)
if type_field == 'float' or type_field == 'real' or type_field == 'decimal':
column_conv.append({'size': size, 'format': u'{0:%d.%df}' % (size, (size - ibm_db.field_precision(stmt, i))), 'fn': convert_to_float})
elif type_field == 'int' or type_field == 'bigint':
column_conv.append({'size': size, 'format': u'{0:%dd}' % size, 'fn': convert_to_int})
else:
column_conv.append({'size': size, 'format': u'{0:%ds}' % size, 'fn': without_convert})
head += name.center(size)
underline += u'-' * size
print head
print underline
while( result ):
print conv(result, column_conv)
result = ibm_db.fetch_tuple(stmt)
else:
print u'Результата не возвращено'
except Exception as e:
print >> sys.stderr, e
sys.exit(-1)
finally:
if stmt:
ibm_db.free_result(stmt)
if conn:
ibm_db.close(conn)
def run_test_046(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, photo_format FROM emp_photo")
else:
result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, length(picture) FROM emp_photo")
row = ibm_db.fetch_tuple(result)
while ( row ):
if row[1] != 'xwd':
print("<a href='test_046.php?EMPNO=%s&FORMAT=%s' target=_blank>%s - %s - %s bytes</a><br>" % (row[0], row[1], row[0], row[1], row[2]))
row = ibm_db.fetch_tuple(result)
def connectBD2():
# 连接数据库
# conn = ibm_db.connect('DATABASE=uibs;HOSTNAME=10.125.192.32;PORT=60000;PROTOCOL=TCPIP;UID=db2inst1;PWD=db2inst1;', '', '')
conn = ibm_db.connect(conn_str, '', '')
# 获取数据库服务器信息
server = ibm_db.server_info(conn)
# 查询数据库sql
sql ="select * from emp"
if conn:
result = ibm_db.exec_immediate(conn, sql)
onerow = ibm_db.fetch_tuple(result)
while ( onerow ):
print(onerow)
onerow = ibm_db.fetch_tuple(result)
def run_test_130(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.prepare(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
if ibm_db.execute(stmt):
row = ibm_db.fetch_tuple(stmt)
while ( row ):
for i in row:
print i
row = ibm_db.fetch_tuple(stmt)
else:
print "Connection failed."
def get_source_data(self, querysql, queue):
""" Call fetch_row """
num = 0
conn = self.get_conn()
try:
stmt = ibm_db.exec_immediate(conn, querysql)
result = ibm_db.fetch_tuple(stmt)
while result:
queue.put(result)
num += 1
result = ibm_db.fetch_tuple(stmt)
except Exception as e:
print e.args
return num
def run_test_131(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.prepare( conn, "SELECT id, breed, name, weight FROM animals WHERE id = ?" )
if ibm_db.execute(stmt, (0,)):
row = ibm_db.fetch_tuple(stmt)
while ( row ):
#row.each { |child| print child }
for i in row:
print(i)
row = ibm_db.fetch_tuple(stmt)
else:
print("Connection failed.")
def executeSelect(statement):
connection = ibm_db.pconnect(url, '', '')
statement = ibm_db.prepare(connection, statement)
ibm_db.execute(statement)
data = ibm_db.fetch_tuple(statement)
result = []
while (data):
result.append(data)
data = ibm_db.fetch_tuple(statement)
ibm_db.free_stmt(statement)
ibm_db.close(connection)
return result
def run_test_047(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, photo_format from emp_photo")
else:
result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, length(PICTURE) from emp_photo")
row = ibm_db.fetch_tuple(result)
while ( row ):
if (row[1] == 'gif'):
print "<img src='test_047.php?EMPNO=%s&FORMAT=%s'><br>\n" % (row[0], row[1])
if (row[1] != 'xwd'):
print "<a href='test_047.php?EMPNO=%s&FORMAT=%s' target=_blank>%s - %s - %s bytes</a>\n<br>" % (row[0], row[1], row[0], row[1], row[2])
row = ibm_db.fetch_tuple(result)
def run_test_021(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
ibm_db.autocommit(conn, 0)
ac = ibm_db.autocommit(conn)
if ac != 0:
print "Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test"
#continue
ibm_db.exec_immediate(conn, "DELETE FROM animals")
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
ibm_db.commit(conn)
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
# Populate the animal table
animals = (
(0, 'cat', 'Pook', 3.2),
(1, 'dog', 'Peaches', 12.3),
(2, 'horse', 'Smarty', 350.0),
(3, 'gold fish', 'Bubbles', 0.1),
(4, 'budgerigar', 'Gizmo', 0.2),
(5, 'goat', 'Rickety Ride', 9.7),
(6, 'llama', 'Sweater', 150)
)
insert = 'INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for animal in animals:
result = ibm_db.execute(stmt, animal)
ibm_db.commit(conn)
ibm_db.close(conn)
else:
print "Connection failed."
def summation(q, txtBefore, txtAfter):
sum_str = q[2]
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None:
raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = " + ISOL_LEVEL)
# Prepare statement
sum_stmt = ibm_db.prepare(conn, sum_str)
if sum_stmt == False:
raise Usage("Failed to prepare sum query")
# Execute statement
if ibm_db.execute(sum_stmt) == False:
raise Usage("Failed to execute the sum query")
sum = ibm_db.fetch_tuple(sum_stmt)
ibm_db.commit(conn)
# Print result set to output file
try:
f = open(OUTPUT_FILE_PATH + "/output.txt", "a")
f.write(txtBefore + str(sum) + txtAfter)
f.close()
except IOError, e:
raise Usage("Failed to manipulate output.txt.\n")
def run_test_260(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
row = ibm_db.fetch_tuple(stmt)
while ( row ):
for i in row:
print(i)
row = ibm_db.fetch_tuple(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
def experiment(query_str,g):
# generate nb of parameters for query
matchList = re.findall('\?', query_str)
nbParams = len(matchList)
if (len(ATTLIST) != nbParams): raise Usage("Attribute missing (add appropriate -a option)")
# Connect to DB
conn = ibm_db.pconnect('DRIVER={IBM DB2 ODBC DRIVER};DATABASE='+DATABASE+';HOSTNAME='+HOSTNAME+';PORT='+str(PORT)+'; PROTOCOL=TCPIP;UID='+USERNAME+';PWD='+PASSWORD+';','','')
if conn is None: raise Usage(ibm_db.conn_errormsg())
# Prepare statement
query_stmt = ibm_db.prepare(conn, query_str)
if (query_stmt == False): raise Usage("Failed to prepare query")
# Execute statement
for i in range(NBQUERIES):
if (nbParams == 0):
if ibm_db.execute(query_stmt) == False:
raise Usage("Failed to execute the query")
else:
t = g.getWrite(i)
l = list(t)
u = [l[j] for j in range(len(l)) if j in ATTLIST]
if ibm_db.execute(query_stmt, tuple(u)) == False:
raise Usage("Failed to execute the query")
nbtuples = 0
while (ibm_db.fetch_tuple(query_stmt) != False):
nbtuples += 1
print "Query"+str(i)+": "+str(nbtuples)+" fetched."
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
def summation(q):
sum_str = q[2]
# Connect to DB
start=time.time()
#sys.stderr.write("Start sum %s\n"%(start))
for n in range(int(round(NBSWAPS/NBSWAPTHREADS))):
#sys.stderr.write("Sum %d"%n)
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None: raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
# Prepare statement
sum_stmt = ibm_db.prepare(conn, sum_str)
if (sum_stmt == False): raise Usage("Failed to prepare sum query")
# Execute statement
if ibm_db.execute(sum_stmt) == False: raise Usage("Failed to execute the sum query")
sum= ibm_db.fetch_tuple(sum_stmt)
ibm_db.commit(conn)
# Print result set to output file
try:
f = open(OUTPUT_FILE_PATH, 'a')
f.write(str(sum)+'\n')
# f.close
except IOError, e:
raise Usage("Failed to manipulate sum.sql.\n")
finally:
def run_test_040(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
# Drop the test table, in case it exists
drop = 'DROP TABLE animals'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the test table
create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))'
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(conn, "select * from animals")
onerow = ibm_db.fetch_tuple(stmt)
for element in onerow:
print element
ibm_db.rollback(conn)
def run_test_044(self):
conn = ibm_db.connect(config.database, config.user, config.password)
result = ibm_db.exec_immediate(conn, "select * from sales")
row = ibm_db.fetch_tuple(result)
while ( row ):
#printf("%-10s ",row[0])
#printf("%-15s ",row[1])
#printf("%-15s ",row[2])
#printf("%4s",row[3])
#print
if (row[3] == None):
row = row[0:3] + (' ',)
print("%-10s %-15s %-15s %4s" % (row[0], row[1], row[2], row[3]))
row = ibm_db.fetch_tuple(result)
def get_xml_by_id(conn, articleid):
if conn:
sql = "SELECT FULLTEXT FROM ARTICLE WHERE ID = " + str(articleid) + ";"
print("get_xml_by_id_sql", articleid)
stmt = ibm_db.exec_immediate(conn, sql)
result = ibm_db.fetch_tuple(stmt)
return etree.fromstring(result[0].encode("utf-8").replace("encoding=\"UTF-16\"", "encoding=\"UTF-8\""))
def queryDict( self, _sql ):
stmt = ibm_db.exec_immediate( self.connection, _sql )
dictionary = []
item = ibm_db.fetch_assoc( stmt )
while item != False:
dictionary.append( item )
item = ibm_db.fetch_tuple( stmt )
return dictionary