def run_test_116(self):
conn = None
is_alive = ibm_db.active(conn)
if is_alive:
print("Is active")
else:
print("Is not active")
conn = ibm_db.connect(config.database, config.user, config.password)
is_alive = ibm_db.active(conn)
if is_alive:
print("Is active")
else:
print("Is not active")
ibm_db.close(conn)
is_alive = ibm_db.active(conn)
if is_alive:
print("Is active")
else:
print("Is not active")
# Executing active method multiple times to reproduce a customer reported defect
print(ibm_db.active(conn))
print(ibm_db.active(conn))
print(ibm_db.active(conn))
conn = ibm_db.connect(config.database, config.user, config.password)
print(ibm_db.active(conn))
print(ibm_db.active(conn))
print(ibm_db.active(conn))
def __init__(self, vcapServices):
db = "SQLDB"
hostname="75.126.155.153"
port = "50000"
username="******"
password="******"
if vcapServices is not None:
db2info = json.loads(os.environ['VCAP_SERVICES'])['sqldb'][0]
db2cred = db2info["credentials"]
self.sqlConn = ibm_db.connect("DATABASE="+db2cred['db']+";HOSTNAME="+db2cred['hostname']+";PORT="+str(db2cred['port'])+";UID="+db2cred['username']+";PWD="+db2cred['password']+";","","")
else:
self.sqlConn = ibm_db.connect("DATABASE="+db+";HOSTNAME="+hostname+";PORT="+port+";UID="+username+";PWD="+password+";","","")
def run_test_312(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
query = "INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)"
if conn:
stmt = ibm_db.prepare(conn, query)
params = ['STG', 'Systems & Technology', '123456', 'RSF', 'Fiji']
print("Binding parameters")
for i,p in enumerate(params, 1):
ibm_db.bind_param(stmt, i, Wrapper(p))
if ibm_db.execute(stmt):
print("Executing statement")
ibm_db.execute(stmt)
# force the cache to be unbound
for i,p in enumerate(params, 1):
ibm_db.bind_param(stmt, i, p)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_091(self):
try:
conn = ibm_db.connect(config.database, "y", config.password)
print "??? No way."
except:
err = ibm_db.conn_errormsg()
print err
def run_test_124(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn, "select * from staff, employee, org where employee.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') order by org.location,employee.lastname,staff.id")
cols = ibm_db.num_fields(result)
j = 0
row = ibm_db.fetch_both(result)
while ( row ):
for i in range(0, cols):
field = ibm_db.field_name(result, i)
value = row[ibm_db.field_name(result, i)]
if (value == None):
value = ''
print("%s:%s" % (field, value))
print("---------")
j += 1
if (j == 10):
break
row = ibm_db.fetch_both(result)
ibm_db.close(conn)
print("done")
else:
print(ibm_db.conn_errormsg())
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_300(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if server:
print "DBMS_NAME: string(%d) \"%s\"" % (len(server.DBMS_NAME), server.DBMS_NAME)
print "DBMS_VER: string(%d) \"%s\"" % (len(server.DBMS_VER), server.DBMS_VER)
print "DB_CODEPAGE: int(%d)" % server.DB_CODEPAGE
print "DB_NAME: string(%d) \"%s\"" % (len(server.DB_NAME), server.DB_NAME)
print "INST_NAME: string(%d) \"%s\"" % (len(server.INST_NAME), server.INST_NAME)
print "SPECIAL_CHARS: string(%d) \"%s\"" % (len(server.SPECIAL_CHARS), server.SPECIAL_CHARS)
print "KEYWORDS: int(%d)" % len(server.KEYWORDS)
print "DFT_ISOLATION: string(%d) \"%s\"" % (len(server.DFT_ISOLATION), server.DFT_ISOLATION)
il = ''
for opt in server.ISOLATION_OPTION:
il += opt + " "
print "ISOLATION_OPTION: string(%d) \"%s\"" % (len(il), il)
print "SQL_CONFORMANCE: string(%d) \"%s\"" % (len(server.SQL_CONFORMANCE), server.SQL_CONFORMANCE)
print "PROCEDURES:", server.PROCEDURES
print "IDENTIFIER_QUOTE_CHAR: string(%d) \"%s\"" % (len(server.IDENTIFIER_QUOTE_CHAR), server.IDENTIFIER_QUOTE_CHAR)
print "LIKE_ESCAPE_CLAUSE:", server.LIKE_ESCAPE_CLAUSE
print "MAX_COL_NAME_LEN: int(%d)" % server.MAX_COL_NAME_LEN
print "MAX_ROW_SIZE: int(%d)" % server.MAX_ROW_SIZE
print "MAX_IDENTIFIER_LEN: int(%d)" % server.MAX_IDENTIFIER_LEN
print "MAX_INDEX_SIZE: int(%d)" % server.MAX_INDEX_SIZE
print "MAX_PROC_NAME_LEN: int(%d)" % server.MAX_PROC_NAME_LEN
print "MAX_SCHEMA_NAME_LEN: int(%d)" % server.MAX_SCHEMA_NAME_LEN
print "MAX_STATEMENT_LEN: int(%d)" % server.MAX_STATEMENT_LEN
print "MAX_TABLE_NAME_LEN: int(%d)" % server.MAX_TABLE_NAME_LEN
print "NON_NULLABLE_COLUMNS:", server.NON_NULLABLE_COLUMNS
ibm_db.close(conn)
else:
print "Error."
def run_test_144(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
# Drop the test table, in case it exists
drop = 'DROP TABLE pictures'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the test table
create = 'CREATE TABLE pictures (id INTEGER, picture BLOB)'
result = ibm_db.exec_immediate(conn, create)
stmt = ibm_db.prepare(conn, "INSERT INTO pictures VALUES (0, ?)")
picture = os.path.dirname(os.path.abspath(__file__)) + "/pic1.jpg"
rc = ibm_db.bind_param(stmt, 1, picture, ibm_db.SQL_PARAM_INPUT, ibm_db.SQL_BINARY)
rc = ibm_db.execute(stmt)
num = ibm_db.num_rows(stmt)
print(num)
else:
print("Connection failed.")
def run_test_082(self):
try:
conn = ibm_db.connect(config.database, config.user, "z")
print("??? No way.")
except:
err = ibm_db.conn_error()
print(err)
def run_test_152(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 project")
row = ibm_db.fetch_assoc(result)
while row:
# printf("%6s ",row['PROJNO'])
# printf("%-24s ",row['PROJNAME'])
# printf("%3s ",row['DEPTNO'])
# printf("%6s",row['RESPEMP'])
# printf("%7s ",row['PRSTAFF'])
# printf("%10s ",row['PRSTDATE'])
# printf("%10s ",row['PRENDATE'])
# printf("%6s",row['MAJPROJ'])
# puts ""
if row["MAJPROJ"] == None:
row["MAJPROJ"] = ""
print "%6s %-24s %3s %6s%7s %10s %10s %6s" % (
row["PROJNO"],
row["PROJNAME"],
row["DEPTNO"],
row["RESPEMP"],
row["PRSTAFF"],
row["PRSTDATE"],
row["PRENDATE"],
row["MAJPROJ"],
)
row = ibm_db.fetch_assoc(result)
def run_test_155(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info( conn )
result = 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')")
i=0
row = ibm_db.fetch_assoc(result)
while ( row ):
i += 1
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
if (row['midinit'] == None):
row['midinit'] = ''
print("%6s %12s %s %-15s%3s %4s %10s %-8s%4d %s%10s %12s %12s %12s" % \
(row['empno'], row['firstnme'], row['midinit'], row['lastname'], row['workdept'], \
row['phoneno'], row['hiredate'], row['job'], row['edlevel'], row['sex'], \
row['birthdate'], row['salary'], row['bonus'], row['comm']))
row = ibm_db.fetch_assoc(result)
else:
if (row['MIDINIT'] == None):
row['MIDINIT'] = ''
print("%6s %12s %s %-15s%3s %4s %10s %-8s%4d %s%10s %12s %12s %12s" % \
(row['EMPNO'], row['FIRSTNME'], row['MIDINIT'], row['LASTNAME'], row['WORKDEPT'], \
row['PHONENO'], row['HIREDATE'], row['JOB'], row['EDLEVEL'], row['SEX'], \
row['BIRTHDATE'], row['SALARY'], row['BONUS'], row['COMM']))
row = ibm_db.fetch_assoc(result)
print("%d record(s) selected." % i)
def assert_expectf(self, testFuncName):
callstack = inspect.stack(0)
try:
prepconn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(prepconn)
ibm_db.close(prepconn)
if (server.DBMS_NAME[0:2] == "AS"):
pattern = self.expected_AS(callstack[1][1])
elif (server.DBMS_NAME == "DB2"):
pattern = self.expected_ZOS(callstack[1][1])
elif (server.DBMS_NAME[0:3] == "IDS"):
pattern = self.expected_IDS(callstack[1][1])
else:
pattern = self.expected_LUW(callstack[1][1])
sym = ['\[','\]','\(','\)']
for chr in sym:
pattern = re.sub(chr, '\\' + chr, pattern)
pattern = re.sub('%s', '.*?', pattern)
pattern = re.sub('%d', '\\d+', pattern)
result = re.match(pattern, self.capture(testFuncName))
self.assertNotEqual(result, None)
finally:
del callstack
def run_test_017(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", { ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_FORWARD_ONLY})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_OFF})
if result:
rows = ibm_db.num_rows(result)
print "affected row:", rows
else:
print ibm_db.stmt_errormsg()
ibm_db.close(conn)
else:
print "no connection:", ibm_db.conn_errormsg()
def run_test_195(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if ((server.DBMS_NAME[0:3] != 'IDS') and (server.DBMS_NAME[0:2] != "AS")):
drop = 'DROP TABLE test_195'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
create = 'CREATE TABLE test_195 (id INTEGER, data XML)'
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO test_195 values (0, '<TEST><def><xml/></def></TEST>')"
ibm_db.exec_immediate(conn, insert)
sql = "SELECT data FROM test_195"
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
result = ibm_db.fetch_assoc(stmt)
while( result ):
print "Output:", result
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(conn)
else:
print "Native XML datatype is not supported."
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_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 run_test_038(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101")
row = ibm_db.fetch_row(result)
while ( row ):
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101")
ibm_db.execute(result2)
row2 = ibm_db.fetch_row(result2)
while ( row2 ):
print("%s : %s : %s : %s : %s\n" % (ibm_db.result(result2, 0), \
ibm_db.result(result2, 1), \
ibm_db.result(result2, 2), \
ibm_db.result(result2, 3), \
ibm_db.result(result2, 5)))
row2 = ibm_db.fetch_row(result2)
row = ibm_db.fetch_row(result)
def run_test_03a(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0")
while ( ibm_db.fetch_row(stmt) ):
breed = ibm_db.result(stmt, 1)
print ("string(%d) \"%s\"" % (len(breed), breed))
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ibm_db.result(stmt, "name")
else:
name = ibm_db.result(stmt, "NAME")
print ("string(%d) \"%s\"" % (len(name), name))
# following field does not exist in result set
if (server.DBMS_NAME[0:3] == 'IDS'):
name = ibm_db.result(stmt, "passport")
else:
name = ibm_db.result(stmt, "PASSPORT")
print (name)
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_180(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
result = ''
result2 = ''
try:
result = ibm_db.exec_immediate(conn,"insert int0 t_string values(123,1.222333,'one to one')")
except:
pass
if result:
cols = ibm_db.num_fields(result)
print("col:", cols,", ")
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
try:
result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
except:
pass
if result:
cols = ibm_db.num_fields(result)
print("col:", cols,", ")
rows = ibm_db.num_rows(result)
print("affected row:", rows)
else:
print(ibm_db.stmt_errormsg())
else:
print("no connection")
def run_test_032(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 6")
while (ibm_db.fetch_row(stmt)):
if (server.DBMS_NAME[0:3] == 'IDS'):
id = ibm_db.result(stmt, "id")
breed = ibm_db.result(stmt, "breed")
name = ibm_db.result(stmt, "name")
weight = ibm_db.result(stmt, "weight")
else:
id = ibm_db.result(stmt, "ID")
breed = ibm_db.result(stmt, "BREED")
name = ibm_db.result(stmt, "NAME")
weight = ibm_db.result(stmt, "WEIGHT")
print "int(%d)" % id
print "string(%d) \"%s\"" % (len(breed), breed)
print "string(%d) \"%s\"" % (len(name), name)
print "string(%d) \"%s\"" % (len(str(weight)), weight)
ibm_db.close(conn)
else:
print "Connection failed."
def run_test_006(self):
options1 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
options2 = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
options1 = options2
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options2)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print (data[0])
data = ibm_db.fetch_both(stmt)
print ("")
stmt = ibm_db.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", options1)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while ( data ):
print (data[0])
data = ibm_db.fetch_both(stmt)
ibm_db.close(conn)
else:
print ("Connection failed.")
def run_test_112(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
drop = "DROP TABLE ftest"
try:
ibm_db.exec_immediate( conn, drop )
except:
pass
create = "CREATE TABLE ftest ( \"TEST\" INTEGER, \"test\" INTEGER, \"Test\" INTEGER )"
ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO ftest VALUES (1,2,3)"
ibm_db.exec_immediate(conn, insert)
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM ftest")
num1 = ibm_db.field_num(stmt, "TEST")
num2 = ibm_db.field_num(stmt, 'test')
num3 = ibm_db.field_num(stmt, 'Test')
print("int(%d)" % num1)
print("int(%d)" % num2)
print("int(%d)" % num3)
else:
print("Connection failed.")
def run_test_150(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")
row = ibm_db.fetch_assoc(result)
while ( row ):
#print "%5d " % row['ID']
#print "%-10s " % row['NAME']
#print "%5d " % row['DEPT']
#print "%-7s " % row['JOB']
#print "%5d " % row['YEARS']
#print "%15s " % row['SALARY']
#print "%10s " % row['COMM']
if (row['YEARS'] == None):
row['YEARS'] = 0
if (row['COMM'] == None):
row['COMM'] = ''
print "%5d %-10s %5d %-7s %5s %15s %10s " % (row['ID'], row['NAME'], row['DEPT'], row['JOB'], row['YEARS'], row['SALARY'], row['COMM'])
row = ibm_db.fetch_assoc(result)
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 get_conn(self):
conn = ibm_db.connect("HOSTNAME=%s;PROTOCOL=TCPIP;PORT=%d;DATABASE=%s;UID=%s;PWD=%s;"
% (self.host, self.port, self.dbname, self.user, self.password), '', '')
# turn off autocommit: SQL_AUTOCOMMIT_OFF
if self.manual_commit:
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
return conn
def run_test_100(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, "SELECT * FROM animals ORDER BY breed")
fields1 = ibm_db.num_fields(stmt)
print("int(%d)" % fields1)
stmt = ibm_db.exec_immediate(conn, "SELECT name, breed FROM animals ORDER BY breed")
fields2 = ibm_db.num_fields(stmt)
print("int(%d)" % fields2)
stmt = ibm_db.exec_immediate(conn, "DELETE FROM animals")
fields3 = ibm_db.num_fields(stmt)
print("int(%d)" % fields3)
stmt = ibm_db.exec_immediate(conn, "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)")
fields4 = ibm_db.num_fields(stmt)
print("int(%d)" % fields4)
stmt = ibm_db.exec_immediate(conn, "SELECT name, breed, 'TEST' FROM animals")
fields5 = ibm_db.num_fields(stmt)
print("int(%d)" % fields5)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def dB_init():
dbtry = 0
while (dbtry < 3):
try:
if 'VCAP_SERVICES' in os.environ:
hasVcap = True
import json
vcap_services = json.loads(os.environ['VCAP_SERVICES'])
if 'dashDB' in vcap_services:
hasdashDB = True
service = vcap_services['dashDB'][0]
credentials = service["credentials"]
url = 'DATABASE=%s;uid=%s;pwd=%s;hostname=%s;port=%s;' % ( credentials["db"],credentials["username"],credentials["password"],credentials["host"],credentials["port"])
print "VCAP",url
else:
hasdashDB = False
else:
hasVcap = False
url = 'DATABASE=%s;uid=%s;pwd=%s;hostname=%s;port=%s;' % (DB_NAME,DB_USER_NAME,DB_PASSWORD,DB_HOST,DB_PORT)
connection = ibm_db.connect(url, '', '')
if (active(connection)):
return connection
except Exception as error:
logging.debug("dataBase connection_ERROR : " + str(error))
dbtry+=1
return None
def run_test_090(self):
try:
conn = ibm_db.connect("x", config.user, config.password)
print("??? No way.")
except:
err = ibm_db.conn_errormsg()
print(err)
def run_test_034(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")
row = ibm_db.fetch_assoc(result)
if row:
# printf("%5d ",row['ID'])
# printf("%-10s ",row['NAME'])
# printf("%5d ",row['DEPT'])
# printf("%-7s ",row['JOB'])
# printf("%5d ", row['YEARS'])
# printf("%15s ", row['SALARY'])
# printf("%10s ", row['COMM'])
# puts ""
print "%5d %-10s %5d %-7s %5d %15s %10s" % (
row["ID"],
row["NAME"],
row["DEPT"],
row["JOB"],
row["YEARS"],
row["SALARY"],
row["COMM"],
)
ibm_db.close(conn)
def dashdB_Init():
global connection,url
dbtry = 0
while(dbtry <3):
try:
if 'VCAP_SERVICES' in os.environ:
hasVcap = True
import json
vcap_services = json.loads(os.environ['VCAP_SERVICES'])
if 'dashDB' in vcap_services:
hasdashDB = True
service = vcap_services['dashDB'][0]
credentials = service["credentials"]
url = 'DATABASE=%s;uid=%s;pwd=%s;hostname=%s;port=%s;' % ( credentials["db"],credentials["username"],credentials["password"],credentials["host"],credentials["port"])
else:
hasdashDB = False
else:
hasVcap = False
url = 'DATABASE=%s;uid=%s;pwd=%s;hostname=%s;port=%s;' % (DB_NAME,DB_USER_NAME,DB_PASSWORD,DB_HOST,DB_PORT)
connection = ibm_db.connect(url, '', '')
if (active(connection)):
return connection
except Exception as dberror:
logging.error("dberror Exception %s"%dberror)
dbtry+=1
return False
class IbmDbTestFunctions(unittest.TestCase):
prepconn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(prepconn)
ibm_db.close(prepconn)
# See the tests.py comments for this function.
def setUp(self):
pass
# This function captures the output of the current test file.
def capture(self, func):
buffer = StringIO()
sys.stdout = buffer
func()
sys.stdout = sys.__stdout__
# str() ensures not Unicode object on Python 2
var = str(buffer.getvalue())
var = var.replace('\n', '').replace('\r', '')
return var
# This function grabs the expected output of the current test function for LUW,
# located at the bottom of the current test file.
def expected_LUW(self, fileName):
fileHandle = open(fileName, 'r')
fileInput = fileHandle.read().split('#__LUW_EXPECTED__')[-1].split(
'#__ZOS_EXPECTED__')[0].replace('\n', '').replace('#', '')
fileHandle.close()
return fileInput
# This function grabs the expected output of the current test function for IDS,
# located at the bottom of the current test file.
def expected_IDS(self, fileName):
fileHandle = open(fileName, 'r')
fileInput = fileHandle.read().split('#__IDS_EXPECTED__')[-1].replace(
'\n', '').replace('#', '')
fileHandle.close()
return fileInput
# This function grabs the expected output of the current test function for zOS,
# located at the bottom of the current test file.
def expected_ZOS(self, fileName):
fileHandle = open(fileName, 'r')
fileInput = fileHandle.read().split('#__ZOS_EXPECTED__')[-1].split(
'#__SYSTEMI_EXPECTED__')[0].replace('\n', '').replace('#', '')
fileHandle.close()
return fileInput
# This function grabs the expected output of the current test function for zOS,
# located at the bottom of the current test file.
def expected_AS(self, fileName):
fileHandle = open(fileName, 'r')
fileInput = fileHandle.read().split('#__SYSTEMI_EXPECTED__')[-1].split(
'#__IDS_EXPECTED__')[0].replace('\n', '').replace('#', '')
fileHandle.close()
return fileInput
# This function compares the captured outout with the expected out of
# the current test file.
def assert_expect(self, testFuncName):
callstack = inspect.stack(0)
try:
if (self.server.DBMS_NAME[0:2] == "AS"):
self.assertEqual(self.capture(testFuncName),
self.expected_AS(callstack[1][1]))
elif (self.server.DBMS_NAME == "DB2"):
self.assertEqual(self.capture(testFuncName),
self.expected_ZOS(callstack[1][1]))
elif (self.server.DBMS_NAME[0:3] == "IDS"):
self.assertEqual(self.capture(testFuncName),
self.expected_IDS(callstack[1][1]))
else:
self.assertEqual(self.capture(testFuncName),
self.expected_LUW(callstack[1][1]))
finally:
del callstack
# This function will compare using Regular Expressions
# based on the servre
def assert_expectf(self, testFuncName):
callstack = inspect.stack(0)
try:
if (self.server.DBMS_NAME[0:2] == "AS"):
pattern = self.expected_AS(callstack[1][1])
elif (self.server.DBMS_NAME == "DB2"):
pattern = self.expected_ZOS(callstack[1][1])
elif (self.server.DBMS_NAME[0:3] == "IDS"):
pattern = self.expected_IDS(callstack[1][1])
else:
pattern = self.expected_LUW(callstack[1][1])
sym = ['\[', '\]', '\(', '\)']
for chr in sym:
pattern = re.sub(chr, '\\' + chr, pattern)
pattern = re.sub('%s', '.*?', pattern)
if sys.version_info >= (3, 7):
pattern = re.sub('%d', r'\\d+', pattern)
else:
pattern = re.sub('%d', '\\d+', pattern)
result = re.match(pattern, self.capture(testFuncName))
self.assertNotEqual(result, None)
finally:
del callstack
#def assert_throw_blocks(self, testFuncName):
# callstack = inspect.stack(0)
# try:
# This function needs to be declared here, regardless of if there
# is any body to this function
def runTest(self):
pass
def value_based_prediction():
if (check_correct_login("p")):
if (request.method == "POST"):
age = int(request.form["age"])
weight = float(request.form["weight"])
height = float(request.form["height"])
ap_hi = float(request.form["ap_hi"])
ap_lo = float(request.form["ap_lo"])
chol = int(request.form["chol"])
gluc = int(request.form["gluc"])
gender = int(request.form["gender"])
smoke = int(request.form["smoke"])
alco = int(request.form["alco"])
active = int(request.form["active"])
enm = float(request.form["enm"])
csd = float(request.form["csd"])
dob = float(request.form["dob"])
gd = float(request.form["gd"])
dms = float(request.form["dms"])
features = [
enm, csd, dob, gd, dms, weight, ap_hi, ap_lo, smoke, gender
]
lab_dict = request.form
dis_count = enm + csd + dob + gd + dms
features.append(dis_count)
fin_features = [np.array(features)]
result = diabetes_model.predict(fin_features)
result = result[0]
diab = 0
card = 0
if result:
diab = 1
df["age"] = age
if (gender == 0):
df["gender"] = 1
df["ap_hi"] = ap_hi
df["ap_lo"] = ap_lo
df["smoke"] = smoke
df["alco"] = alco
df["active"] = active
bmi = weight / ((height / 100)**2)
df["bmi"] = bmi
df["pulse_pressure"] = ap_hi - ap_lo
if (bmi < 18.5):
df["Underweight"] = 1
elif (bmi < 25):
df["Healthy"] = 1
elif (30 <= bmi < 35):
df["Obese"] = 1
elif (35 <= bmi < 40):
df["Severly Obese"] = 1
elif (35 <= bmi < 40):
df["Abnormal"] = 1
if (chol < 200):
df["chol_1"] = 1
elif (chol > 240):
df["chol_3"] = 1
if (gluc < 115):
df["gluc_1"] = 1
elif (gluc > 185):
df["gluc_3"] = 1
ypred = cardio_model.predict(df)
if (ypred[0] > (0.5)):
card = 1
elif (ypred[0] > (0.3)):
card = 2
prob = int(ypred[0] * 100)
# return "<h1>"+str(card)+""+str(diab)+"</h1>"
conn = ibm_db.connect(dsn, "", "")
from datetime import datetime
now = datetime.now()
formatted_date = now.strftime('%Y-%m-%d %H:%M:%S')
lis = [
session["user_id"], formatted_date, age, weight, height, ap_hi,
ap_lo, chol, gluc, gender, smoke, alco, active, enm, csd, dob,
gd, dms, card, diab
]
sql = "insert into history values %r;" % (tuple(lis), )
res = ibm_db.exec_immediate(conn, sql)
return render_template("result_vbp.html",
card=card,
diab=diab,
prob=prob)
return render_template("p_value_pred.html")
else:
flash("Patient Login Required !", category="error")
return redirect(url_for('login'))
def run_test_200(self):
conn = ibm_db.connect(config.database, config.user, config.password)
serverinfo = ibm_db.server_info(conn)
server = serverinfo.DBMS_NAME[0:3]
if (server == 'IDS'):
procedure = """
CREATE FUNCTION multiResults()
RETURNING CHAR(16), INT;
DEFINE p_name CHAR(16);
DEFINE p_id INT;
FOREACH c1 FOR
SELECT name, id
INTO p_name, p_id
FROM animals
ORDER BY name
RETURN p_name, p_id WITH RESUME;
END FOREACH;
END FUNCTION;
"""
else:
procedure = """
CREATE PROCEDURE multiResults ()
RESULT SETS 3
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT name, id
FROM animals
ORDER BY name;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, id
FROM animals
WHERE id < 4
ORDER BY name DESC;
DECLARE c3 CURSOR WITH RETURN FOR
SELECT name, id
FROM animals
WHERE weight < 5.0
ORDER BY name;
OPEN c1;
OPEN c2;
OPEN c3;
END
"""
if conn:
try:
ibm_db.exec_immediate(conn, 'DROP PROCEDURE multiResults')
except:
pass
ibm_db.exec_immediate(conn, procedure)
stmt = ibm_db.exec_immediate(conn, 'CALL multiResults()')
#print(stmt)
print("Fetching first result set")
row = ibm_db.fetch_tuple(stmt)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(stmt)
if (server == 'IDS'):
print(
"Fetching second result set (should fail -- IDS does not support multiple result sets)"
)
else:
print("Fetching second result set")
#print(stmt)
res = ibm_db.next_result(stmt)
if res:
row = ibm_db.fetch_tuple(res)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(res)
if (server == 'IDS'):
print(
"Fetching third result set (should fail -- IDS does not support multiple result sets)"
)
else:
print("Fetching third result set")
res2 = ibm_db.next_result(stmt)
if res2:
row = ibm_db.fetch_tuple(res2)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(res2)
print("Fetching fourth result set (should fail)")
res3 = ibm_db.next_result(stmt)
if res3:
row = ibm_db.fetch_tuple(res3)
while (row):
for i in row:
print(i)
row = ibm_db.fetch_tuple(res3)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_080(self):
try:
conn = ibm_db.connect("x", config.user, config.password)
print("??? No way.")
except:
print(ibm_db.conn_error())
from nltk.tokenize import RegexpTokenizer
from nltk.corpus import stopwords
from itertools import chain
from datetime import datetime, date, time, timedelta
import re, string
from textblob import TextBlob
import os
import time
import ibm_db
##################### Extracción de datos operaciones #########################
conn = ibm_db.connect(
"DATABASE=bludb;HOSTNAME=slpedw.iadb.org;PORT=50001;security=ssl;UID=mariarey;PWD=password;",
"", "") #Abriendo conexión con repositorio de datos DB2
sql = "SELECT DISTINCT C.OPER_NUM as OPERATION_NUMBER, C.OPER_ENGL_NM as OPERATION_NAME, C.OPERTYP_ENGL_NM AS OPERATION_TYPE_NAME, C.MODALITY_CD AS OPERATION_MODALITY, C.PREP_RESP_DEPT_CD AS DEPARTMENT, C.PREP_RESP_DIV_CD AS DIVISION,\
C.REGN AS REGION, C.CNTRY_BENFIT AS COUNTRY, C.STS_CD AS STATUS, C.STG_ENGL_NM AS STAGE, C.STS_ENGL_NM AS TAXONOMY, C.OPER_EXEC_STS AS EXEC_STS, C.APPRVL_DT AS APPROVAL_DATE, C.APPRVL_DT_YR as APPROVAL_YEAR,\
C.ORIG_APPRVD_USEQ_AMNT AS APPROVAL_AMOUNT, C.CURNT_DISB_EXPR_DT as CURRENT_EXPIRATION_DATE, C.RELTN_NUM AS RELATED_OPER,C.FACILITY_TYP_CD AS RELATION_TYPE, C.OPER_TYP_CD AS OPERATION_TYPE, A.OBJTV_ENGL as OBJECTIVE_EN,\
A.OBJTV_SPANISH as OBJECTIVE_ES, B.CMPNT_STMNT as COMPONENT_NAME, B.OUTPUT_DEFNTN as OUTPUT_NAME, C.FACILITY_TYP_ENGL_NM AS OUTPUT_DESCRIPTION \
FROM ODS.SPD_ODS_HOPERMAS C \
JOIN ( select OPER_NUM, MAX(DW_CRTE_TS) AS MAX_DT from ODS.SPD_ODS_HOPERMAS GROUP BY OPER_NUM) t ON C.OPER_NUM= t.OPER_NUM and C.DW_CRTE_TS = t.MAX_DT \
JOIN ODS.OPER_ODS_OPER A ON C.OPER_NUM = A.OPER_NUM \
JOIN ODS.OPER_ODS_OUTPUT_IND B ON C.OPER_NUM = B.OPER_NUM \
WHERE C.APPRVL_DT_YR > 2015 AND C.PREP_RESP_DEPT_CD='SCL' AND DATE(C.APPRVL_DT)<DATE(NOW()) " #SQL query de datos deseados, MRT: se agrega filtro de fecha de aprobación menor al día de hoy y se quita =ACTIVE
stmt = ibm_db.exec_immediate(conn, sql) #Querying data
#Creando base de datos con query
def __init__(self, target_db, db_user, db_password):
self.target_db = target_db
self.db_user = db_user
self.db_password = db_password
self.dbconn = ibm_db.connect(self.target_db, self.db_user, self.db_password)
def run_test_050(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ac = ibm_db.autocommit(conn)
print(ac)
except mariadb.Error as error:
print("ERROR: No se pudo conectar a MongoDB :".format(error))
MariaDB_Cnx.close()
elif (MBD == "7"):
import ibm_db
################################################
# Conexión con una base de datos no catalogada #
################################################
# ibm_db.connect("DATABASE=DBAS; HOSTNAME=HOST; PORT=PORT; PROTOCOL=TCPIP; UID=USER; PWD=PASS;", "", "")
# Conn = ibm_db.connect(DBAS, USER, PASS)
#####################################################
# Conexión con una base de datos local o catalogada #
#####################################################
try:
Conn = ibm_db.connect(DBAS, USER, PASS)
Sql = QRY
Stmt = ibm_db.exec_immediate(Conn, Sql)
if Conn:
if (TdC != "2"):
print("Número de filas afectadas : ", ibm_db.num_rows(Stmt))
else:
Result = ibm_db.fetch_both(Stmt)
while (Result):
print(Result)
Result = ibm_db.fetch_both(Stmt)
except:
print("ERROR: No se pudo realizar la conecxión :",
ibm_db.conn_errormsg())
else:
print("AVISO: La conexión se realizó de manera existosa.....")
def register2(request, forename, surname, postcode, email):
# DB2 Connection
ibm_db_conn = ibm_db.connect(
"DATABASE=GCUKPRD;HOSTNAME=prddgcd001;PORT=50002;PROTOCOL=TCPIP;UID=costaa;PWD=London07;",
"", "")
conn = ibm_db_dbi.Connection(ibm_db_conn)
cur = conn.cursor()
cur.execute(
"""
select distinct t1.PER_GID, t1.PER_TITLE_CODE as TITLE, t1.PER_FIRST_INITIAL as INITIAL, t1.PER_FORENAME as FORENAME, t1.PER_SURNAME as SURNAME, t1.PER_STATUS_CODE as STATUS,
CASE t1.PER_GENDER_CODE
WHEN '1' THEN 'MALE'
WHEN '2' THEN 'FEMALE'
ELSE 'Other'
END as GENDER,
t3.PER_CP_CAT_CODE as CONTACT_TYPE, t3.PER_CP_VALUE as CONTACT_VALUE, t2.PER_POSTAL_ADDR_POSTCODE as POSTCODE, t2.PER_POSTAL_ADDR_LINE_1 as ADDRESS_LINE1, t2.PER_POSTAL_ADDR_LINE_2 as ADDRESS_LINE2
from OPGCUK.PERSON_OPGC t1
join OPGCUK.PERSON_CONTACT_POINT_ADDR_OPGC t2 on t1.PER_GID = t2.PER_GID
join OPGCUK.PERSON_CONTACT_POINT_OPGC t3 on t3.PER_GID = t1.PER_GID
where t2.PER_POSTAL_ADDR_POSTCODE = ?
and t1.PER_SURNAME = ?
and t1.PER_FIRST_INITIAL = ?""",
[upper(postcode), upper(surname),
upper(forename[0])])
cust = []
for obj in cur.fetchall():
cust.append({
"PER_GID": obj[0],
"TITLE": obj[1],
"INITIAL": obj[2],
"FORENAME": obj[3],
"SURNAME": obj[4],
"STATUS": obj[5],
"GENDER": obj[6],
"CONTACT_TYPE": obj[7],
"CONTACT_VALUE": obj[8],
"POSTCODE": obj[9],
"ADDRESS_LINE1": obj[10],
"ADDRESS_LINE2": obj[11]
})
tab_len = len(cust)
cur.execute(
"""
select distinct t2.PER_GID, t4.ITEM_ID as PLAN
from OPGCUK.PERSON_ITEM_RELATIONSHIP_OPGC t2
join OPGCUK.ITEM_OPGC t3 on t3.ITEM_GID = t2.ITEM_GID and t2.ITEM_REL_CAT = 'OWN'
join OPGCUK.ITEM_SOURCE_DETAIL_OPGC t4 on t4.ITEM_GID = t3.ITEM_GID
join REFDATA_INT.REF_ITEM t5 on t3.ITEM_CODE = t5.ITEM_CODE and t5.COUNTRY_CODE = 'GBR'
where left(t4.ITEM_ID,4) = 'PLN:'
and t3.ITEM_COVER_DG_CONTR_STATUS_CODE in ('N','R','L') -- exclude Cancelled
and t2.PER_GID IN (
select distinct t1.PER_GID
from OPGCUK.PERSON_OPGC t1
join OPGCUK.PERSON_CONTACT_POINT_ADDR_OPGC t2 on t1.PER_GID = t2.PER_GID
where t2.PER_POSTAL_ADDR_POSTCODE = ?
and t1.PER_SURNAME = ?
and t1.PER_FIRST_INITIAL = ?)""",
[upper(postcode), upper(surname),
upper(forename[0])])
plans = []
for obj in cur.fetchall():
plans.append({"PER_GID": obj[0], "PLAN": obj[1]})
# DB2 Connection CLOSE
cur.close()
conn.close()
gid = ''
if request.method == 'POST':
form = Register2(request.POST)
if form.is_valid():
phone = form.cleaned_data.get('phone')
line1 = form.cleaned_data.get('line1')
plan = form.cleaned_data.get('plan')
gid_count = 0
if plan:
for row in plans:
if plan == row['PLAN']:
gid = row['PER_GID']
gid_count = 1
else:
i = 0
for row in cust:
if row['CONTACT_VALUE'] == phone or line1 == row[
'ADDRESS_LINE1'] or line1 == row['ADDRESS_LINE2']:
i += 1
if i == 1:
gid = row['PER_GID']
gid_count = 1
elif gid != row['PER_GID']:
gid_count = gid_count + 1
if gid_count == 1:
messages.success(
request,
f'Customer {forename} {surname} @ {postcode} was found.')
# phone = ''
return redirect('find-dashboard', gid, forename, surname,
postcode, email)
elif plan and gid_count == 0:
messages.warning(request, f'Invalid Plan Number: {plan}')
form = Register2()
else:
messages.warning(request,
f'Please enter more details or Plan Number.')
form = Register2()
else:
i = 0
for row in cust:
if row['CONTACT_VALUE'] == upper(
email) or forename == row['FORENAME']:
i += 1
if i == 1:
gid = row['PER_GID']
elif gid != row['PER_GID']:
messages.warning(
request,
f'Please enter additional details required for your identification.'
)
if i == 1:
print('Found one - GID :', gid)
messages.success(
request,
f'Thank you {forename} {surname} your account was created.')
phone = ''
return redirect('find-dashboard', gid, forename, surname, postcode,
email)
else:
messages.warning(
request,
f'Please enter additional details required for your identification.'
)
# send data to form
form = Register2()
args = {
'form': form,
'table': cust,
'tab_len': tab_len,
'plans': plans,
'forename': forename,
'surname': surname,
'postcode': postcode,
'email': email
}
return render(request, 'find/home.html', args)
args = {
'form': form,
'table': cust,
'tab_len': tab_len,
'plans': plans,
'forename': forename,
'surname': surname,
'postcode': postcode,
'email': email
}
return render(request, 'find/home.html', args)
import ibm_db, os, json
connection = ibm_db.connect(
"DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=vft79804;PWD=7c4r31g+3bwb6fxf;",
"", "")
# -*- coding: utf-8 -*-
import ibm_db
import pandas as pd
conn = ibm_db.connect("dsn=sample", "administrator", "ctl7220fe")
sql = "SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE EMPNO > ? AND EMPNO < ?"
stmt = ibm_db.prepare(conn, sql)
max = 50
min = 0
# Explicitly bind parameters
ibm_db.bind_param(stmt, 1, min)
ibm_db.bind_param(stmt, 2, max)
ibm_db.execute(stmt)
# Process results
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
df = pd.DataFrame(dictionary.items())
print(df, '-----------------------------------')
while dictionary != False:
for key in dictionary:
print(key, dictionary[key])
dictionary = ibm_db.fetch_both(stmt)
sql1 = "update A1 set N = ?"
stmt1 = ibm_db.prepare(conn, sql1)
i = 'scy'
ibm_db.bind_param(stmt1, 1, i)
ibm_db.execute(stmt1)
#
dsn = ("DRIVER={0};"
"DATABASE={1};"
"HOSTNAME={2};"
"PORT={3};"
"PROTOCOL={4};"
"UID={5};"
"PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port,
dsn_protocol, dsn_uid, dsn_pwd)
#print the connection string to check correct values are specified
print(dsn)
#DO NOT MODIFY THIS CELL. Just RUN it with Shift + Enter
#Create database connection
try:
conn = ibm_db.connect(dsn, "", "")
print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname)
except:
print("Unable to connect: ", ibm_db.conn_errormsg())
#Retrieve Metadata for the Database Server
server = ibm_db.server_info(conn)
print("DBMS_NAME: ", server.DBMS_NAME)
print("DBMS_VER: ", server.DBMS_VER)
print("DB_NAME: ", server.DB_NAME)
#Retrieve Metadata for the Database Client / Driver
client = ibm_db.client_info(conn)
def run_test_decfloat(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info(conn)
drop = "DROP TABLE STOCKPRICE"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the table stockprice
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECIMAL(16))"
else:
create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECFLOAT(16))"
result = ibm_db.exec_immediate(conn, create)
# Insert Directly
insert = "INSERT INTO STOCKPRICE (id, company, stockshare, stockprice) VALUES (10,'Megadeth', 100.002, 990.356736488388374888532323)"
result = ibm_db.exec_immediate(conn, insert)
# Prepare and Insert in the stockprice table
stockprice = (\
(20, "Zaral", 102.205, "100.234"),\
(30, "Megabyte", 98.65, "1002.112"),\
(40, "Visarsoft", 123.34, "1652.345"),\
(50, "Mailersoft", 134.22, "1643.126"),\
(60, "Kaerci", 100.97, "9876.765")\
)
insert = 'INSERT INTO STOCKPRICE (id, company, stockshare,stockprice) VALUES (?,?,?,?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for company in stockprice:
result = ibm_db.execute(stmt, company)
id = 70
company = 'Nirvana'
stockshare = 100.1234
stockprice = "100.567"
try:
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, company)
ibm_db.bind_param(stmt, 3, stockshare)
ibm_db.bind_param(stmt, 4, stockprice)
error = ibm_db.execute(stmt)
except:
excp = sys.exc_info()
# slot 1 contains error message
print excp[1]
# Select the result from the table and
query = 'SELECT * FROM STOCKPRICE ORDER BY id'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while (data):
print "%s : %s : %s : %s\n" % (data[0], data[1], data[2],
data[3])
data = ibm_db.fetch_both(stmt)
try:
stmt = ibm_db.prepare(conn, query, {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
ibm_db.execute(stmt)
rc = ibm_db.fetch_row(stmt, -1)
print "Fetch Row -1:%s " % str(rc)
except:
print "Requested row number must be a positive value"
ibm_db.close(conn)
else:
print "Connection failed."
def run_test_createdbNX(self):
database = 'test001'
conn_str = "DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (
database, config.hostname, config.port, config.user,
config.password)
conn_str_attach = "attach=true;HOSTNAME=%s;PORT=%d;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (
config.hostname, config.port, config.user, config.password
) #for create db or drop db API it is nessesory that connection only attach to the DB server not to any existing database of DB server
conn_attach = ibm_db.connect(conn_str_attach, '', '')
if conn_attach:
conn = False
try:
conn = ibm_db.connect(conn_str, '', '')
except:
pass
if conn:
ibm_db.close(conn)
conn = False
try:
ibm_db.dropdb(conn_attach, database)
except:
print('Errors occurred during drop database')
try:
# call createdbNX without codeset argument when specified database not exeist
rc = ibm_db.createdbNX(conn_attach, database)
if rc:
conn = ibm_db.connect(conn_str, '', '')
if conn:
print('database created sucessfully')
ibm_db.close(conn)
conn = False
else:
print('database is not created')
else:
print('Error occurred during create db if not exist')
conn = ibm_db.connect(conn_str, '', '')
if conn:
ibm_db.close(conn)
conn = False
# call recreate db with codeset argument when specified database exist
rc = ibm_db.createdbNX(conn_attach, database, 'iso88591')
if rc:
conn = ibm_db.connect(conn_str, '', '')
server_info = ibm_db.server_info(conn)
if conn and (server_info.DB_CODEPAGE != 819):
print('database with codeset created sucessfully')
ibm_db.close(conn)
conn = False
else:
print('Database not created')
else:
print(
'Error occurred during create db if not exist with codeset'
)
#drop database
rc = ibm_db.dropdb(conn_attach, database)
if rc:
try:
conn = ibm_db.connect(conn_str, '', '')
except:
print('datbase droped sucessfully')
if conn:
print('Errors occurred during drop database')
ibm_db.close(conn)
conn = False
else:
print('Errors occurred during drop database')
except:
print(ibm_db.conn_errormsg())
pass
ibm_db.close(conn_attach)
else:
print(ibm_db.conn_errormsg())
def get(self):
self.set_header('Content-Type', 'application/json')
# fetch data from db and return a json result = {}
# hotel: id, name, latitude, longitude
# attraction: id, name, latitude, longitude
# hawker center: id, latitude, longitude
bnb_dict = {}
attr_dict = {}
hawker_center_dict = {}
conn = ibm_db.connect(
"DATABASE=BLUDB;HOSTNAME=dashdb-entry-yp-dal09-09.services.dal.bluemix.net;\
PORT=50000;PROTOCOL=TCPIP;UID=dash9787;\
PWD=X_c03EeYTe#u;", "", "")
sql_airbnb = "SELECT ROOMID,NAME,LATITUDE,LONGITUDE,PRICE,RATING,IMGURL,ROOMURL FROM AIRBNB"
stmt = ibm_db.exec_immediate(conn, sql_airbnb)
while True:
dict_airbnb = ibm_db.fetch_assoc(stmt)
if dict_airbnb is False:
break
bnb_dict[int(dict_airbnb['ROOMID'].strip())] = {
'id': int(dict_airbnb['ROOMID'].strip()),
'name': dict_airbnb['NAME'].strip(),
'price': float(dict_airbnb['PRICE'].strip()),
'rating': float(dict_airbnb['RATING'].strip()),
'lat': float(dict_airbnb['LATITUDE'].strip()),
'lng': float(dict_airbnb['LONGITUDE'].strip()),
'img': dict_airbnb['IMGURL'],
'roomURL': dict_airbnb['ROOMURL'],
}
sql_attr = "SELECT ATTRACTIONID,NAME,LATITUDE,LONGITUDE,POPULARITY, RATING, CATEGORY,TICKET_PRICE FROM TOURISM_ATTRACTIONS"
stmt = ibm_db.exec_immediate(conn, sql_attr)
while True:
dict_attr = ibm_db.fetch_assoc(stmt)
if dict_attr is False:
break
attr_dict[int(dict_attr['ATTRACTIONID'].strip())] = {
'ATTRACTIONID': int(dict_attr['ATTRACTIONID'].strip()),
'NAME': dict_attr['NAME'].strip(),
'TICKET_PRICE': float(dict_attr['TICKET_PRICE'].strip()),
'LATITUDE': float(dict_attr['LATITUDE'].strip()),
'LONGITUDE': float(dict_attr['LONGITUDE'].strip()),
'CATEGORY': dict_attr['CATEGORY'],
'POPULARITY': dict_attr['POPULARITY'],
'RATING': dict_attr['RATING'],
}
sql_food = "SELECT FOODID,NAME,LATITUDE,LONGITUDE FROM FOOD"
stmt = ibm_db.exec_immediate(conn, sql_food)
while True:
dict_food = ibm_db.fetch_assoc(stmt)
if dict_food is False:
break
hawker_center_dict[dict_food['FOODID']] = {
'id': dict_food['FOODID'],
'name': dict_food['NAME'],
'lat': float(dict_food['LATITUDE'].strip()),
'lng': float(dict_food['LONGITUDE'].strip()),
}
dict_all = {
'hotels': bnb_dict,
'attractions': attr_dict,
'hawker_centers': hawker_center_dict
}
self.write(json_encode(dict_all))
def run_test_024(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info(conn)
if conn != 0:
drop = 'DROP TABLE test_primary_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
drop = 'DROP TABLE test_foreign_keys'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
statement = 'CREATE TABLE test_primary_keys (id INTEGER NOT NULL, PRIMARY KEY(id))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_primary_keys VALUES (1)"
result = ibm_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_keys (name VARCHAR(30) NOT NULL, idf INTEGER NOT NULL, FOREIGN KEY(idf) REFERENCES test_primary_keys(id), \
PRIMARY KEY(name))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_keys VALUES ('vince', 1)"
result = ibm_db.exec_immediate(conn, statement)
statement = 'CREATE TABLE test_foreign_keys (namef VARCHAR(30) NOT NULL, id INTEGER NOT NULL, FOREIGN KEY(namef) REFERENCES test_keys(name))'
result = ibm_db.exec_immediate(conn, statement)
statement = "INSERT INTO test_foreign_keys VALUES ('vince', 1)"
result = ibm_db.exec_immediate(conn, statement)
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user,
'test_primary_keys')
else:
stmt = ibm_db.foreign_keys(conn, None, None,
'TEST_PRIMARY_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, None, None, None,
config.user, 'test_keys')
else:
stmt = ibm_db.foreign_keys(conn, None, None, None, None, None,
'TEST_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user,
'test_keys', None, None, None)
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None,
None, None)
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user,
'test_keys', None, config.user,
'test_foreign_keys')
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None,
None, 'TEST_FOREIGN_KEYS')
row = ibm_db.fetch_tuple(stmt)
print(row[2])
print(row[3])
print(row[6])
print(row[7])
try:
stmt = ibm_db.foreign_keys(conn, None, None, None, None, None,
None)
row = ibm_db.fetch_tuple(stmt)
except:
if (not stmt):
print(ibm_db.stmt_errormsg())
if (server.DBMS_NAME[0:3] == 'IDS'):
stmt = ibm_db.foreign_keys(conn, None, config.user,
'test_keys', None, 'dummy_schema')
else:
stmt = ibm_db.foreign_keys(conn, None, None, 'TEST_KEYS', None,
'dummy_schema')
row = ibm_db.fetch_tuple(stmt)
if (not row):
print("No Data Found")
else:
print(row)
ibm_db.close(conn)
else:
print(ibm_db.conn_errormsg())
print("Connection failed\n")
def run_test_148(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
##### Set up #####
serverinfo = ibm_db.server_info( conn )
server = serverinfo.DBMS_NAME[0:3]
try:
sql = "DROP TABLE sptb"
ibm_db.exec_immediate(conn, sql)
except:
pass
try:
sql = "DROP PROCEDURE sp"
ibm_db.exec_immediate(conn, sql)
except:
pass
if (server == 'IDS'):
sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 INT8, c5 CLOB)"
else:
sql = "CREATE TABLE sptb (c1 INTEGER, c2 FLOAT, c3 VARCHAR(10), c4 BIGINT, c5 CLOB)"
ibm_db.exec_immediate(conn, sql)
sql = "INSERT INTO sptb (c1, c2, c3, c4, c5) VALUES (1, 5.01, 'varchar', 3271982, 'clob data clob data')"
ibm_db.exec_immediate(conn, sql)
if (server == 'IDS'):
sql = """CREATE PROCEDURE sp(OUT out1 INTEGER, OUT out2 FLOAT, OUT out3 VARCHAR(10), OUT out4 INT8, OUT out5 CLOB);
SELECT c1, c2, c3, c4, c5 INTO out1, out2, out3, out4, out5 FROM sptb; END PROCEDURE;"""
else:
sql = """CREATE PROCEDURE sp(OUT out1 INTEGER, OUT out2 FLOAT, OUT out3 VARCHAR(10), OUT out4 BIGINT, OUT out5 CLOB)
DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN
SELECT c1, c2, c3, c4, c5 INTO out1, out2, out3, out4, out5 FROM sptb; END"""
ibm_db.exec_immediate(conn, sql)
#############################
##### Run the test #####
out1 = 0
out2 = 0.00
out3 = ""
out4 = 0
out5 = ""
stmt, out1, out2, out3, out4, out5 = ibm_db.callproc(conn, 'sp', (out1, out2, out3, out4, out5))
print("out 1:")
print(out1)
print("out 2:")
print(out2)
print("out 3:")
print(out3)
print("out 4:")
print(out4)
print("out 5:")
print(out5)
#############################
else:
print("Connection failed.")
def run_test_008(self):
op = {ibm_db.ATTR_CASE: ibm_db.CASE_NATURAL}
conn = ibm_db.connect(config.database, config.user, config.password,
op)
server = ibm_db.server_info(conn)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn, None, None, "employee")
else:
result = ibm_db.columns(conn, None, None, "EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if (row.has_key('TABLE_NAME')):
value1 = row['TABLE_NAME']
if (row.has_key('COLUMN_NAME')):
value2 = row['COLUMN_NAME']
if (row.has_key('table_name')):
value3 = row['table_name']
if (row.has_key('column_name')):
value4 = row['column_name']
print value1
print value2
print value3
print value4
op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
ibm_db.set_option(conn, op, 1)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn, None, None, "employee")
else:
result = ibm_db.columns(conn, None, None, "EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if (row.has_key('TABLE_NAME')):
value1 = row['TABLE_NAME']
if (row.has_key('COLUMN_NAME')):
value2 = row['COLUMN_NAME']
if (row.has_key('table_name')):
value3 = row['table_name']
if (row.has_key('column_name')):
value4 = row['column_name']
print value1
print value2
print value3
print value4
op = {ibm_db.ATTR_CASE: ibm_db.CASE_LOWER}
ibm_db.set_option(conn, op, 1)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.columns(conn, None, None, "employee")
else:
result = ibm_db.columns(conn, None, None, "EMPLOYEE")
row = ibm_db.fetch_both(result)
value1 = None
value2 = None
value3 = None
value4 = None
if (row.has_key('TABLE_NAME')):
value1 = row['TABLE_NAME']
if (row.has_key('COLUMN_NAME')):
value2 = row['COLUMN_NAME']
if (row.has_key('table_name')):
value3 = row['table_name']
if (row.has_key('column_name')):
value4 = row['column_name']
print value1
print value2
print value3
print value4
def run_test_066(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.tables(conn, None, config.user.lower(), 'animals')
else:
result = ibm_db.tables(conn, None, config.user.upper(), 'ANIMALS')
# NOTE: This is a workaround
# function fetch_object() to be implemented...
# row = ibm_db.fetch_object(result)
class Row:
pass
data = ibm_db.fetch_assoc(result)
while ( data ):
row = Row()
if (server.DBMS_NAME[0:3] == 'IDS'):
row.table_schem = data['table_schem']
row.table_name = data['table_name']
row.table_type = data['table_type']
row.remarks = data['remarks']
print "Schema: %s" % row.table_schem
print "Name: %s" % row.table_name
print "Type: %s" % row.table_type
print "Remarks: %s\n" % row.remarks
else:
row.TABLE_SCHEM = data['TABLE_SCHEM']
row.TABLE_NAME = data['TABLE_NAME']
row.TABLE_TYPE = data['TABLE_TYPE']
row.REMARKS = data['REMARKS']
print "Schema: %s" % row.TABLE_SCHEM
print "Name: %s" % row.TABLE_NAME
print "Type: %s" % row.TABLE_TYPE
print "Remarks: %s\n" % row.REMARKS
# row = ibm_db.fetch_object(result)
data = ibm_db.fetch_assoc(result)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, config.user.lower(), 'animal_pics')
else:
result = ibm_db.tables(conn, None, config.user.upper(), 'ANIMAL_PICS')
# row = ibm_db.fetch_object(result)
data = ibm_db.fetch_assoc(result)
while (data ):
row = Row()
if (server.DBMS_NAME[0:3] == 'IDS'):
row.table_schem = data['table_schem']
row.table_name = data['table_name']
row.table_type = data['table_type']
row.remarks = data['remarks']
print "Schema: %s" % row.table_schem
print "Name: %s" % row.table_name
print "Type: %s" % row.table_type
print "Remarks: %s\n" % row.remarks
else:
row.TABLE_SCHEM = data['TABLE_SCHEM']
row.TABLE_NAME = data['TABLE_NAME']
row.TABLE_TYPE = data['TABLE_TYPE']
row.REMARKS = data['REMARKS']
print "Schema: %s" % row.TABLE_SCHEM
print "Name: %s" % row.TABLE_NAME
print "Type: %s" % row.TABLE_TYPE
print "Remarks: %s\n" % row.REMARKS
# row = ibm_db.fetch_object(result)
data = ibm_db.fetch_assoc(result)
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.tables(conn, None, config.user.lower(), 'anime_cat')
else:
result = ibm_db.tables(conn, None, config.user.upper(), 'ANIME_CAT')
# row = ibm_db.fetch_object(result)
data = ibm_db.fetch_assoc(result)
while ( data ):
row = Row()
if (server.DBMS_NAME[0:3] == 'IDS'):
row.table_schem = data['table_schem']
row.table_name = data['table_name']
row.table_type = data['table_type']
row.remarks = data['remarks']
print "Schema: %s" % row.table_schem
print "Name: %s" % row.table_name
print "Type: %s" % row.table_type
print "Remarks: %s\n" % row.remarks
else:
row.TABLE_SCHEM = data['TABLE_SCHEM']
row.TABLE_NAME = data['TABLE_NAME']
row.TABLE_TYPE = data['TABLE_TYPE']
row.REMARKS = data['REMARKS']
print "Schema: %s" % row.TABLE_SCHEM
print "Name: %s" % row.TABLE_NAME
print "Type: %s" % row.TABLE_TYPE
print "Remarks: %s\n" % row.REMARKS
# row = ibm_db.fetch_object(result)
data = ibm_db.fetch_assoc(result)
ibm_db.free_result(result)
ibm_db.close(conn)
def getDbConnection(self):
try:
url="DATABASE="+DB2_SAMPLE_DB+";HOSTNAME="+DB2_HOST+";PORT="+DB2_PORT+";PROTOCOL=TCPIP;UID="+DB2_USERNAME+";PWD="+DB2_PASSWORD+";"
db = ibm_db.connect(url, "", "") #Connect to an uncataloged database
self.connection = db
sql_modify_Station = "UPDATE Station " \
"SET food=%s, water=%s, clothes=%s, medicine=%s" \
" WHERE Driver=%s" %(food, water, clothes, medicine, stationId)
ibm_db.exec_immediate(conn, sql_modify_Station)
dict = {}
if food == 0:
dict['food'] = 0
if water == 0:
dict['water'] = 0
if clothes == 0:
dict['clothes'] = 0
if medicine == 0:
dict['medicine'] = 0
ibm_db.close(conn)
return dict
except:
ibm_db.close(conn)
return False
if __name__ == "__main__":
conn = ibm_db.connect(
"DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-04.services.dal.bluemix.net;PORT=50001;PROTOCOL=TCPIP;UID=tpj29337;PWD=8kzn@v6p7wlb4r75;Security=SSL;",
"", "")
# s = "SELECT * FROM Vehicle"
# print(ibm_db.fetch_both(ibm_db.exec_immediate(conn, s)))
def run_test_bool_callproc(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if (not conn):
print("Could not make a connection.")
return 0
server = ibm_db.server_info(conn)
if (not server.DBMS_NAME.startswith('DB2/')):
print("Boolean is not supported")
return 0
try:
ibm_db.exec_immediate(conn, "DROP PROCEDURE bool_procparams")
ibm_db.exec_immediate(conn, "DROP TABLE bool_test")
except:
pass
try:
ibm_db.exec_immediate(
conn,
"CREATE TABLE bool_test(col1 BOOLEAN, description varchar(50))"
)
except:
pass
try:
procedure = """create procedure bool_procparams(in parm1 boolean, out param2 boolean)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR SELECT * from bool_test;
OPEN c1;
set param2 = parm1;
END"""
ibm_db.exec_immediate(conn, procedure)
except Exception as e:
print(str(e))
exit(-1)
try:
insert_sql = "INSERT INTO bool_test values(?, ?)"
stmt = ibm_db.prepare(conn, insert_sql)
rows = ((True, 'bindparam true'), (False, 'bindparam false'),
(None, 'bindparam None'))
for row in rows:
ibm_db.bind_param(stmt, 1, row[0])
ibm_db.bind_param(stmt, 2, row[1])
ibm_db.execute(stmt)
stmt = None
inparam = 11
outparam = -1
stmt, inparam, outparam = ibm_db.callproc(conn, 'bool_procparams',
(inparam, outparam))
print("Fetching first result set")
row = ibm_db.fetch_row(stmt)
while row:
row0 = ibm_db.result(stmt, 0)
row1 = ibm_db.result(stmt, 1)
print(row0)
print(row1)
row = ibm_db.fetch_row(stmt)
ibm_db.close(conn)
except Exception as e:
print("Error:{}".format(str(e)))
def home(request):
if request.method == 'POST':
form = Register1(request.POST)
if form.is_valid():
forename = form.cleaned_data.get('forename')
surname = form.cleaned_data.get('surname')
postcode = form.cleaned_data.get('postcode')
email = form.cleaned_data.get('email')
#request.session['_cust'] = form
# DB2 Connection
ibm_db_conn = ibm_db.connect(
"DATABASE=GCUKPRD;HOSTNAME=prddgcd001;PORT=50002;PROTOCOL=TCPIP;UID=costaa;PWD=London07;",
"", "")
conn = ibm_db_dbi.Connection(ibm_db_conn)
cur = conn.cursor()
# count the number of customers that match surname and postcode
cur.execute(
"""
select distinct t1.PER_GID
from OPGCUK.PERSON_OPGC t1
join OPGCUK.PERSON_CONTACT_POINT_ADDR_OPGC t2 on t1.PER_GID = t2.PER_GID
where t2.PER_POSTAL_ADDR_POSTCODE = ?
and t1.PER_SURNAME = ?
and t1.PER_FIRST_INITIAL = ?""",
[upper(postcode),
upper(surname),
upper(forename[0])])
gid_all = []
for obj in cur.fetchall():
gid_all.append({"PER_GID": obj[0]})
gid_num = len(gid_all)
gid = ''
for row in gid_all:
gid = row['PER_GID']
# DB2 Connection CLOSE
cur.close()
conn.close()
if gid_num == 0:
messages.warning(
request,
f'No customers in the database with the information provided.'
)
elif gid_num == 1:
messages.success(
request,
f'Customer {forename} {surname} @ {postcode} was found.')
# phone = ''
return redirect('find-dashboard', gid, forename, surname,
postcode, email)
else:
# messages.warning(request, f'Please enter additional details required for your identification.')
return redirect('find-register2', forename, surname, postcode,
email)
else:
form = Register1()
return render(request, 'find/home.html', {'form': form})
def run_test_decimal(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info(conn)
drop = "DROP TABLE STOCKSHARE"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the table stockprice
create = "CREATE TABLE STOCKSHARE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7, 2))"
result = ibm_db.exec_immediate(conn, create)
# Insert Directly
insert = "INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (10, 'Megadeth', 100.002)"
result = ibm_db.exec_immediate(conn, insert)
# Prepare and Insert in the stockprice table
stockprice = (\
(20, "Zaral", 102.205),\
(30, "Megabyte", "98.65"),\
(40, "Visarsoft", Decimal("123.34")),\
(50, "Mailersoft", Decimal("134.222")),\
(60, "Kaerci", Decimal("100.976"))\
)
insert = 'INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (?,?,?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for company in stockprice:
result = ibm_db.execute(stmt, company)
id = 70
company = 'Nirvana'
stockshare = Decimal("100.1234")
try:
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, company)
ibm_db.bind_param(stmt, 3, stockshare)
error = ibm_db.execute(stmt)
except:
excp = sys.exc_info()
# slot 1 contains error message
print excp[1]
# Select the result from the table and
query = 'SELECT * FROM STOCKSHARE ORDER BY id'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both(stmt)
while (data):
print "%s : %s : %s\n" % (data[0], data[1], data[2])
data = ibm_db.fetch_both(stmt)
try:
stmt = ibm_db.prepare(conn, query, {
ibm_db.SQL_ATTR_CURSOR_TYPE:
ibm_db.SQL_CURSOR_KEYSET_DRIVEN
})
ibm_db.execute(stmt)
rc = ibm_db.fetch_row(stmt, -1)
print "Fetch Row -1:%s " % str(rc)
except:
print "Requested row number must be a positive value"
ibm_db.close(conn)
else:
print "Connection failed."
#__END__
#__LUW_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
#__ZOS_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
#__IDS_EXPECTED__
#10 : Megadeth : 100.00
#20 : Zaral : 102.20
#30 : Megabyte : 98.65
#40 : Visarsoft : 123.34
#50 : Mailersoft : 134.22
#60 : Kaerci : 100.97
#70 : Nirvana : 100.12
#Requested row number must be a positive value
import ibm_db
import os
from flask import Flask, render_template, url_for, request, redirect
from werkzeug.utils import secure_filename
conn = ibm_db.connect(
"DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-10.services.dal.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=zgx10325;PWD=nk^ttsk5lffq6v0h;",
"", "")
app = Flask(__name__)
img_path = 'static/images/'
app.config['UPLOAD_FOLDER'] = img_path
app.config['SECRET_KEY'] = 'blah blah blah blah'
@app.route('/')
def index():
arr = []
query = "SELECT * FROM PEOPLE"
stmt = ibm_db.exec_immediate(conn, query)
data = ibm_db.fetch_tuple(stmt)
while data:
arr.append(data)
data = ibm_db.fetch_tuple(stmt)
return render_template('main.html', table=arr)
@app.route('/search', methods=['POST', 'GET'])
def search_user():
if request.method == 'POST':
user = a
if o == "-p":
pwd = a
if o == "-t":
targetdb = a
if db is None or user is None or pwd is None or targetdb is None:
print("Usage: DBMove.py [-h <host> -P <port>] -d <db> -u <user> -p <pwd> -t <target>")
sys.exit(1)
db = db.upper()
targetdb = targetdb.upper()
cfg = (db, host, port, user, pwd)
conn = ibm_db.connect("DATABASE=%s; HOSTNAME=%s; PORT=%s; PROTOCOL=TCPIP; UID=%s; PWD=%s" % cfg, "", "")
get_db_type = "values nya.get_db_type()"
find_edges = """
SELECT rtrim(t.tabschema) || '.' || rtrim(t.tabname)
, coalesce(rtrim(r.reftabschema) || '.' || rtrim(r.reftabname), 'dummy')
FROM syscat.tables t
LEFT JOIN syscat.references r
ON (t.tabschema, t.tabname) = (r.tabschema, r.tabname)
WHERE t.tabschema not like 'SYS%'
AND t.type = 'T'
AND rtrim(t.tabschema) not like 'NYA_%'
AND t.tabschema <> 'TMP'
ORDER BY 1
"""
import ibm_db
conn_string = "DATABASE=Ifr;HOSTNAME=172.18.8.117;PORT=50000;UID=AMASISFULL;PWD=AMASISFULL;"
conn = None
try:
ibm_db.connect(conn_string, "", "")
except Exception as ex:
print(ex)
if conn:
print('success')
else:
print('failure')
def run_test_cursortype(self):
if sys.platform == 'zos':
options = {}
else:
options = {ibm_db.SQL_ATTR_INFO_PROGRAMNAME: 'TestProgram'}
conn = ibm_db.connect(config.database, config.user, config.password,
options)
if conn:
drop = "DROP TABLE TEMP_TEST"
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the table temp_test
create = "CREATE TABLE TEMP_TEST (id INTEGER, name CHAR(16))"
result = ibm_db.exec_immediate(conn, create)
insert = "INSERT INTO temp_test values (1, 'cat')"
ibm_db.exec_immediate(conn, insert)
print("Setting cursor type to SQL_CURSOR_FORWARD_ONLY")
op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test WHERE id > 1",
op)
val = ibm_db.cursor_type(stmt)
print("statement cursor type = ", end="")
print(val, end="\n")
value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
print(value)
print()
print("Setting cursor type to SQL_CURSOR_KEYSET_DRIVEN")
op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test", op)
val = ibm_db.cursor_type(stmt)
print("statement cursor type = ", end="")
print(val, end="\n")
value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
print(value)
print()
print("Setting cursor type to SQL_CURSOR_STATIC")
op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC}
stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test", op)
val = ibm_db.cursor_type(stmt)
print("statement cursor type = ", end="")
print(val)
value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
print(value)
print()
print("Setting cursor type to SQL_CURSOR_DYNAMIC - zOS only")
op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_DYNAMIC}
stmt = ibm_db.prepare(conn, "SELECT * FROM temp_test", op)
val = ibm_db.cursor_type(stmt)
print("statement cursor type = ", end="")
print(val)
value = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
print(value)
print()
ibm_db.close(conn)
else:
print("Connection failed.")
def dashboard(request, gid, forename, surname, postcode, email):
# DB2 Connection
ibm_db_conn = ibm_db.connect(
"DATABASE=GCUKPRD;HOSTNAME=prddgcd001;PORT=50002;PROTOCOL=TCPIP;UID=costaa;PWD=London07;",
"", "")
conn = ibm_db_dbi.Connection(ibm_db_conn)
cur = conn.cursor()
# retrieve all plans from customer
cur.execute(
"""
select distinct t2.PER_GID, t3.ITEM_GID, t4.ITEM_ID as PLAN, t3.ITEM_LOC_ADDR_LINE_1 as ITEM_LOCATION, t3.ITEM_LOC_POSTCODE AS POSTCODE, t3.ITEM_MANUF_BRAND_CODE AS BRAND, t3.ITEM_MODEL_NUM AS MODEL, t3.ITEM_SERIAL_NUM AS SERIAL,
date(t3.ITEM_PURCHASE_DTS) AS PURCHASE_DATE, date(t3.ITEM_COVER_DG_CONTR_RENEWAL_DTS) AS RENEWAL_DATE,
CASE t3.ITEM_COVER_DG_CONTR_STATUS_CODE
WHEN 'N' THEN 'NEW'
WHEN 'R' THEN 'RENEWED'
WHEN 'L' THEN 'LAPSED'
WHEN 'C' THEN 'CANCELLED'
ELSE 'ERROR'
END as PLAN_STATUS,
t3.ITEM_CODE AS ITEM_CODE, t5.ITEM_DESCRIPTION AS DESCRIPTION, t5.ITEM_FAMILY_CODE AS FAMILY
from OPGCUK.PERSON_ITEM_RELATIONSHIP_OPGC t2
join OPGCUK.ITEM_OPGC t3 on t3.ITEM_GID = t2.ITEM_GID and t2.ITEM_REL_CAT = 'OWN'
join OPGCUK.ITEM_SOURCE_DETAIL_OPGC t4 on t4.ITEM_GID = t3.ITEM_GID
join REFDATA_INT.REF_ITEM t5 on t3.ITEM_CODE = t5.ITEM_CODE and t5.COUNTRY_CODE = 'GBR'
where t2.PER_GID = ?
and left(t4.ITEM_ID,4) = 'PLN:'
and t3.ITEM_COVER_DG_CONTR_STATUS_CODE in ('N','R','L')""", [gid])
plans = []
for obj in cur.fetchall():
plans.append({
"PER_GID": obj[0],
"ITEM_GID": obj[1],
"PLAN": obj[2],
"ITEM_LOCATION": obj[3],
"POSTCODE": obj[4],
"BRAND": obj[5],
"MODEL": obj[6],
"SERIAL": obj[7],
"PURCHASE_DATE": obj[8],
"RENEWAL_DATE": obj[9],
"PLAN_STATUS": obj[10],
"ITEM_CODE": obj[11],
"DESCRIPTION": obj[12],
"FAMILY": obj[13]
})
tab_len = len(plans)
# retrieve all mailers from customer
cur.execute(
"""
select distinct t2.PER_GID, t3.ITEM_GID, t3.ITEM_LOC_ADDR_LINE_1 as ITEM_LOCATION, t3.ITEM_LOC_POSTCODE AS POSTCODE,
t3.ITEM_MANUF_BRAND_CODE AS BRAND, t3.ITEM_MODEL_NUM AS MODEL, t3.ITEM_SERIAL_NUM AS SERIAL,
date(t3.ITEM_PURCHASE_DTS) AS PURCHASE_DATE,
t3.ITEM_CODE AS ITEM_CODE, t5.ITEM_DESCRIPTION AS DESCRIPTION, t5.ITEM_FAMILY_CODE AS FAMILY
from OPGCUK.PERSON_ITEM_RELATIONSHIP_OPGC t2
join OPGCUK.ITEM_OPGC t3 on t3.ITEM_GID = t2.ITEM_GID and t2.ITEM_REL_CAT = 'OWN'
join OPGCUK.ITEM_SOURCE_DETAIL_OPGC t4 on t4.ITEM_GID = t3.ITEM_GID
join REFDATA_INT.REF_ITEM t5 on t3.ITEM_CODE = t5.ITEM_CODE and t5.COUNTRY_CODE = 'GBR'
where t2.PER_GID = ?
and left(t4.ITEM_ID,4) <> 'PLN:'
and t3.ITEM_COVER_DG_CONTR_STATUS_CODE is NULL""", [gid])
mailers = []
for obj in cur.fetchall():
mailers.append({
"PER_GID": obj[0],
"ITEM_GID": obj[1],
"ITEM_LOCATION": obj[2],
"POSTCODE": obj[3],
"BRAND": obj[4],
"MODEL": obj[5],
"SERIAL": obj[6],
"PURCHASE_DATE": obj[7],
"ITEM_CODE": obj[8],
"DESCRIPTION": obj[9],
"FAMILY": obj[10]
})
tab_len = tab_len + len(mailers)
# DB2 Connection CLOSE
cur.close()
conn.close()
# send data to form
form = Dashboard(
initial={
'gid': gid,
'forename': forename,
'surname': surname,
'postcode': postcode,
'email': email
})
args = {
'form': form,
'plans': plans,
'mailers': mailers,
'item_len': tab_len
}
return render(request, 'find/dashboard.html', args)
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime, timedelta
from flask import Flask, render_template, url_for, flash, redirect, request
from flask_wtf import FlaskForm
from flask_wtf.file import FileField, FileRequired, FileAllowed
from flask_uploads import UploadSet, configure_uploads, IMAGES, patch_request_class
from flask_bootstrap import Bootstrap
from wtforms import StringField, IntegerField, SubmitField, SelectField
from wtforms.validators import DataRequired
import sys
import os
import ibm_db
conn = ibm_db.connect(
"DATABASE=BLUDB;HOSTNAME=dashdb-txn-sbox-yp-dal09-10.services.dal.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=pqc12497;PWD=n2l8-gc4t4khhkw1;",
"", "")
app = Flask(__name__)
bootstrap = Bootstrap(app)
# configurations
app.config['SECRET_KEY'] = 'blah blah blah blah'
cf_port = os.getenv("PORT")
# Routes
# Index Page
@app.route('/', methods=['POST', 'GET'])