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'])