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_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_039(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.prepare( conn, "SELECT * FROM animals", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}) else: result = ibm_db.prepare(conn, "SELECT * FROM animals") ibm_db.execute(result) row = ibm_db.fetch_row(result) while (row): if (serverinfo.DBMS_NAME[0:3] != 'IDS'): result2 = ibm_db.prepare(conn, "SELECT * FROM animals", { ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN }) else: result2 = ibm_db.prepare(conn, "SELECT * FROM animals") ibm_db.execute(result2) while (ibm_db.fetch_row(result2)): print("%s : %s : %s : %s" % (ibm_db.result(result2, 0), \ ibm_db.result(result2, 1), \ ibm_db.result(result2, 2), \ ibm_db.result(result2, 3))) row = ibm_db.fetch_row(result)
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 callback_worker(call): if call.data=='yes': try: i=0 conn = ibm_db.connect(dsn, "", "") print ("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname) bot.send_message(author_id,"Рассылаю...") #Select all selectQuery = "select * from "+TABLENAME selectStmt = ibm_db.exec_immediate(conn, selectQuery) while ibm_db.fetch_row(selectStmt) != False: print ("Sent to: ID:", ibm_db.result(selectStmt, 0), " @username:"******"USERNAME")) bot.send_message(ibm_db.result(selectStmt, 0),mess) i+=1 bot.send_message(author_id,"Пользователей, получивших ваше оповещение: {} ".format(i)) log.info('Разослано пользователям: '+str(i)) except: print ("Unable to connect: ", ibm_db.conn_errormsg()) ibm_db.close(conn) print ("Connection closed") else: bot.send_message(author_id,"Хорошо, не буду")
def run_test_114(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = "drop table numericliteral" try: ibm_db.exec_immediate(conn, drop) except: pass create = "create table numericliteral ( id INTEGER, num INTEGER )" ibm_db.exec_immediate(conn, create) insert = "INSERT INTO numericliteral (id, num) values (1,5)" ibm_db.exec_immediate(conn, insert) insert = "UPDATE numericliteral SET num = '10' WHERE num = '5'" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.prepare(conn, "SELECT * FROM numericliteral") ibm_db.execute(stmt) result = ibm_db.fetch_row(stmt) while (result): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) print(row0) print(row1) result = ibm_db.fetch_row(stmt) else: print("Connection failed.")
def run_test_114(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = "drop table numericliteral" try: ibm_db.exec_immediate( conn, drop ) except: pass create = "create table numericliteral ( id INTEGER, num INTEGER )" ibm_db.exec_immediate(conn, create) insert = "INSERT INTO numericliteral (id, num) values (1,5)" ibm_db.exec_immediate(conn, insert) insert = "UPDATE numericliteral SET num = '10' WHERE num = '5'" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.prepare(conn, "SELECT * FROM numericliteral") ibm_db.execute(stmt) result = ibm_db.fetch_row( stmt ) while ( result ): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) print row0 print row1 result = ibm_db.fetch_row( stmt ) else: print "Connection failed."
def run_test_InsertRetrieveDateTimeTypeColumn(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = 'DROP TABLE tab_datetime' result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass t_val = datetime.time(10, 42, 34) d_val = datetime.date(1981, 7, 8) #ts_val = datetime.datetime.today() ts_val = datetime.datetime(1981, 7, 8, 10, 42, 34, 10) server = ibm_db.server_info(conn) if (server.DBMS_NAME[0:3] == 'IDS'): statement = "CREATE TABLE tab_datetime (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO FRACTION(5))" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (t_val, d_val, ts_val)) else: statement = "CREATE TABLE tab_datetime (col1 TIME, col2 DATE, col3 TIMESTAMP)" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (t_val, d_val, ts_val)) if (server.DBMS_NAME.startswith('DB2/')): d_val = datetime.date(2019, 10, 16) ts_val = datetime.datetime(2019, 10, 16, 23, 0, 0, 0) statement = "INSERT INTO tab_datetime (col1, col2, col3) values ('24:00:00', ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (d_val, ts_val)) statement = "SELECT * FROM tab_datetime" result = ibm_db.exec_immediate(conn, statement) for i in range(0, ibm_db.num_fields(result)): print(str(i) + ":" + ibm_db.field_type(result, i)) statement = "SELECT * FROM tab_datetime" stmt = ibm_db.prepare(conn, statement) rc = ibm_db.execute(stmt) result = ibm_db.fetch_row(stmt) while (result): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) row2 = ibm_db.result(stmt, 2) print(type(row0), row0) print(type(row1), row1) print(type(row2), row2) result = ibm_db.fetch_row(stmt) ibm_db.close(conn) else: print("Connection failed.")
def run_test_6792(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = 'DROP TABLE table_6792' result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass t_val = '10:42:34' d_val = '1981-07-08' ts_val = '1981-07-08 10:42:34' ts_withT_val = '2013-06-06T15:30:39' server = ibm_db.server_info(conn) if (server.DBMS_NAME[0:3] == 'IDS'): statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND, col4 DATETIME YEAR TO SECOND)" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (col1, col2, col3) values (?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (t_val, d_val, ts_val)) else: statement = "CREATE TABLE table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP, col4 TIMESTAMP)" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (col1, col2, col3, col4) values (?, ?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (t_val, d_val, ts_val, ts_withT_val)) statement = "SELECT * FROM table_6792" result = ibm_db.exec_immediate(conn, statement) for i in range(0, ibm_db.num_fields(result)): print(str(i) + ":" + ibm_db.field_type(result, i)) statement = "SELECT * FROM table_6792" stmt = ibm_db.prepare(conn, statement) rc = ibm_db.execute(stmt) result = ibm_db.fetch_row(stmt) while (result): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) row2 = ibm_db.result(stmt, 2) row3 = ibm_db.result(stmt, 3) print(row0) print(row1) print(row2) print(row3) result = ibm_db.fetch_row(stmt) ibm_db.close(conn) else: print("Connection failed.")
def run_test_115(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) if conn: drop = "drop table numericliteral" try: ibm_db.exec_immediate( conn, drop ) except: pass create = "create table numericliteral ( id INTEGER, data VARCHAR(50) )" ibm_db.exec_immediate(conn, create) insert = "INSERT INTO numericliteral (id, data) values (12, 'NUMERIC LITERAL TEST')" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral") ibm_db.execute(stmt) # NOTE: This is a workaround # function fetch_object() to be implemented... # row = ibm_db.fetch_object(stmt, 0) class Row: pass row = Row() ibm_db.fetch_row(stmt, 0) if (server.DBMS_NAME[0:3] != 'IDS'): row.DATA = ibm_db.result(stmt, 'DATA') else: row.DATA = ibm_db.result(stmt, 'data') print(row.DATA) insert = "UPDATE numericliteral SET data = '@@@@@@@@@@' WHERE id = '12'" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral") ibm_db.execute(stmt) # row = ibm_db.fetch_object(stmt, 0) ibm_db.fetch_row(stmt, 0) if (server.DBMS_NAME[0:3] != 'IDS'): row.DATA = ibm_db.result(stmt, 'DATA') else: row.DATA = ibm_db.result(stmt, 'data') print(row.DATA) else: print("Connection failed.")
def run_test_115(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) if conn: drop = "drop table numericliteral" try: ibm_db.exec_immediate(conn, drop) except: pass create = "create table numericliteral ( id INTEGER, data VARCHAR(50) )" ibm_db.exec_immediate(conn, create) insert = "INSERT INTO numericliteral (id, data) values (12, 'NUMERIC LITERAL TEST')" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral") ibm_db.execute(stmt) # NOTE: This is a workaround # function fetch_object() to be implemented... # row = ibm_db.fetch_object(stmt, 0) class Row: pass row = Row() ibm_db.fetch_row(stmt, 0) if (server.DBMS_NAME[0:3] != 'IDS'): row.DATA = ibm_db.result(stmt, 'DATA') else: row.DATA = ibm_db.result(stmt, 'data') print row.DATA insert = "UPDATE numericliteral SET data = '@@@@@@@@@@' WHERE id = '12'" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.prepare(conn, "SELECT data FROM numericliteral") ibm_db.execute(stmt) # row = ibm_db.fetch_object(stmt, 0) ibm_db.fetch_row(stmt, 0) if (server.DBMS_NAME[0:3] != 'IDS'): row.DATA = ibm_db.result(stmt, 'DATA') else: row.DATA = ibm_db.result(stmt, 'data') print row.DATA else: print "Connection failed."
def run_test_264(self): # Make a connection conn = ibm_db.connect(config.database, config.user, config.password) if conn: 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) # Drop the tab_bigint table, in case it exists drop = "DROP TABLE tab_bigint" result = "" try: result = ibm_db.exec_immediate(conn, drop) except: pass # Create the tab_bigint table if server.DBMS_NAME[0:3] == "IDS": create = "CREATE TABLE tab_bigint (col1 INT8, col2 INT8, col3 INT8, col4 INT8)" else: create = "CREATE TABLE tab_bigint (col1 BIGINT, col2 BIGINT, col3 BIGINT, col4 BIGINT)" result = ibm_db.exec_immediate(conn, create) insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)" res = ibm_db.exec_immediate(conn, insert) print "Number of inserted rows:", ibm_db.num_rows(res) stmt = ibm_db.prepare(conn, "SELECT * FROM tab_bigint") ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while data: print data[0] print data[1] print data[2] print data[3] print type(data[0]) is long print type(data[1]) is long print type(data[2]) is long data = ibm_db.fetch_both(stmt) # test ibm_db.result for fetch of bigint stmt1 = ibm_db.prepare(conn, "SELECT col2 FROM tab_bigint") ibm_db.execute(stmt1) ibm_db.fetch_row(stmt1, 0) if server.DBMS_NAME[0:3] != "IDS": row1 = ibm_db.result(stmt1, "COL2") else: row1 = ibm_db.result(stmt1, "col2") print row1 ibm_db.close(conn)
def run_test_264(self): # Make a connection conn = ibm_db.connect(config.database, config.user, config.password) if conn: 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) # Drop the tab_bigint table, in case it exists drop = 'DROP TABLE tab_bigint' result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass # Create the tab_bigint table if (server.DBMS_NAME[0:3] == 'IDS'): create = "CREATE TABLE tab_bigint (col1 INT8, col2 INT8, col3 INT8, col4 INT8)" else: create = "CREATE TABLE tab_bigint (col1 BIGINT, col2 BIGINT, col3 BIGINT, col4 BIGINT)" result = ibm_db.exec_immediate(conn, create) insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)" res = ibm_db.exec_immediate(conn, insert) print("Number of inserted rows:", ibm_db.num_rows(res)) stmt = ibm_db.prepare(conn, "SELECT * FROM tab_bigint") ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print(data[0]) print(data[1]) print(data[2]) print(data[3]) print(type(data[0]) is int) print(type(data[1]) is int) print(type(data[2]) is int) data = ibm_db.fetch_both(stmt) # test ibm_db.result for fetch of bigint stmt1 = ibm_db.prepare(conn, "SELECT col2 FROM tab_bigint") ibm_db.execute(stmt1) ibm_db.fetch_row(stmt1, 0) if (server.DBMS_NAME[0:3] != 'IDS'): row1 = ibm_db.result(stmt1, 'COL2') else: row1 = ibm_db.result(stmt1, 'col2') print(row1) ibm_db.close(conn)
def run_test_InsertRetrieveDateTimeTypeColumn(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = 'DROP TABLE tab_datetime' result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass t_val = datetime.time(10, 42, 34) d_val = datetime.date(1981, 7, 8) #ts_val = datetime.datetime.today() ts_val = datetime.datetime(1981, 7, 8, 10, 42, 34, 10) server = ibm_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): statement = "CREATE TABLE tab_datetime (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO FRACTION(5))" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (t_val, d_val, ts_val)) else: statement = "CREATE TABLE tab_datetime (col1 TIME, col2 DATE, col3 TIMESTAMP)" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (t_val, d_val, ts_val)) statement = "SELECT * FROM tab_datetime" result = ibm_db.exec_immediate(conn, statement) for i in range(0, ibm_db.num_fields(result)): print(str(i) + ":" + ibm_db.field_type(result,i)) statement = "SELECT * FROM tab_datetime" stmt = ibm_db.prepare(conn, statement) rc = ibm_db.execute(stmt) result = ibm_db.fetch_row(stmt) while ( result ): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) row2 = ibm_db.result(stmt, 2) print(type(row0), row0) print(type(row1), row1) print(type(row2), row2) result = ibm_db.fetch_row(stmt) ibm_db.close(conn) else: print("Connection failed.")
def getResByRow(conn, sql): ''' Call fetch_row ''' n = 0 try: stmt = db2.exec_immediate(conn, sql) res = db2.fetch_row(stmt) while res != False: print 'Result from :', db2.result(stmt, 0) n += db2.result(stmt, 0) res = db2.fetch_row(stmt) except: print "Transaction couldn't be completed:" , db2.stmt_errormsg() else: return n
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_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 main(args): #Connect to the DB2 database conn = ibm_db.connect("DATABASE=TEST;HOSTNAME=172.17.0.1;PORT=50000;PROTOCOL=TCPIP;UID=DB2INST1;PWD=db2inst1-pwd;", "", "") cmd = "DELETE FROM TESTTABLE WHERE name='Angela'" result = ibm_db.exec_immediate(conn, cmd) #output of the above can succeed or fail. Either is fine. # Insert into TestTable cmd = "INSERT INTO TESTTABLE (NAME, AGE, LOCATION) VALUES ('Angela', 27, 'Texas')" result = ibm_db.exec_immediate(conn, cmd) if not result: return {"err":"error :"+cmd} # Select from TestTable (the row just inserted) cmd = "SELECT * FROM TESTTABLE WHERE name='Angela'" result = ibm_db.exec_immediate(conn, cmd) if not result: return {"err":"error :"+cmd} else: ibm_db.fetch_both(result,0) value = ibm_db.result(result,"NAME") # Make sure the row was correctly inserted if value != 'Angela' : return {"err":"Expected name 'Angela', but instead found: "+ value} # Delete the row from TestTable cmd = "DELETE FROM TESTTABLE WHERE name='Angela'" result = ibm_db.exec_immediate(conn, cmd) if not result: return {"err":"error :"+cmd} # If no detected errors occurred so far; return Success status return {"message":"Tested db2 create, select, and delete of a table row."}
def unschedule_tasks(conn): print("Unscheduling tasks ...") # unschedule tasks from Admin Task Scheduler stmt = ibm_db.exec_immediate( conn, "SELECT * FROM SYSCAT.TABLES WHERE TABSCHEMA = 'SYSTOOLS' AND TABNAME = 'ADMIN_TASK_LIST'" ) if ibm_db.fetch_assoc(stmt): # get all tasks belonging to schema IBMHIST stmt = ibm_db.exec_immediate( conn, "SELECT NAME FROM SYSTOOLS.ADMIN_TASK_LIST WHERE PROCEDURE_SCHEMA = 'IBMHIST'" ) while ibm_db.fetch_row(stmt): # get name collection_name = ibm_db.result(stmt, "NAME") # unschedule task ibm_db.exec_immediate( conn, "CALL SYSPROC.ADMIN_TASK_REMOVE( '%s', NULL )" % (collection_name)) # delete tasks from IBMHIST.TAB_TASKS stmt = ibm_db.exec_immediate( conn, "SELECT * FROM SYSCAT.TABLES WHERE TABSCHEMA = 'IBMHIST' AND TABNAME = 'TAB_TASKS'" ) if ibm_db.fetch_assoc(stmt): ibm_db.exec_immediate(conn, "DELETE FROM IBMHIST.TAB_TASKS")
def main(args): ssldsn = args["__bx_creds"]["dashDB"]["ssldsn"] conn = ibm_db.connect(ssldsn, "", "") if conn: print("Connection succeeded.") else: print("Connection failed.") return {"error": "Error connecting to db2"} # Select from TestTable (the row just inserted). # # Due to the db2 lite plan we use, the schema name is fixed to be # the same as the username. We cut the value of the UID field # in the ssldsn to get it and insert it in the select statement. # The ssldsn looks like this: # ssldsn="DATABASE=BLUDB;HOSTNAME=dashdb-xxxx.services.dal.bluemix.net;PORT=50001;PROTOCOL=TCPIP;UID=yyyyyyy;PWD=<hidden>;" # ssldsndict = dict(x.split("=") for x in ssldsn.rstrip(";").split(";")) print("user={}".format(ssldsndict["UID"])) cmd = "SELECT HISP_DESC FROM {}.HISPANIC_ORIGIN WHERE HISP_CODE='03'".format( ssldsndict["UID"]) result = ibm_db.exec_immediate(conn, cmd) if not result: return {"error": "error :" + cmd} else: ibm_db.fetch_both(result, 0) value = ibm_db.result(result, "HISP_DESC") ibm_db.close(conn) return {"HISP_DESC": value}
def run_test_6792(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = 'DROP TABLE table_6792' result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass server = ibm_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND)" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (col1, col2, col3) values ('10:42:34', '1981-07-08', '1981-07-08 10:42:34')" result = ibm_db.exec_immediate(conn, statement) else: statement = "CREATE TABLE table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP)" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (col1, col2, col3) values ('10.42.34', '1981-07-08', '1981-07-08-10.42.34')" result = ibm_db.exec_immediate(conn, statement) statement = "SELECT * FROM table_6792" result = ibm_db.exec_immediate(conn, statement) for i in range(0, ibm_db.num_fields(result)): print str(i) + ":" + ibm_db.field_type(result,i) statement = "SELECT * FROM table_6792" stmt = ibm_db.prepare(conn, statement) rc = ibm_db.execute(stmt) result = ibm_db.fetch_row(stmt) while ( result ): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) row2 = ibm_db.result(stmt, 2) print row0 print row1 print row2 result = ibm_db.fetch_row(stmt) ibm_db.close(conn) else: print "Connection failed."
def run_test_031(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0") while (ibm_db.fetch_row(stmt)): id = ibm_db.result(stmt, 0) print("int(%d)" % id) breed = ibm_db.result(stmt, 1) print("string(%d) \"%s\"" % (len(breed), breed)) name = ibm_db.result(stmt, 2) print("string(%d) \"%s\"" % (len(name), name)) weight = ibm_db.result(stmt, 3) print("string(%d) \"%s\"" % (len(str(weight)), weight)) ibm_db.close(conn) else: print("Connection failed.")
def run_test_031(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: stmt = ibm_db.exec_immediate(conn, "SELECT id, breed, name, weight FROM animals WHERE id = 0") while (ibm_db.fetch_row(stmt)): id = ibm_db.result(stmt, 0) print "int(%d)" % id breed = ibm_db.result(stmt, 1) print "string(%d) \"%s\"" % (len(breed), breed) name = ibm_db.result(stmt, 2) print "string(%d) \"%s\"" % (len(name), name) weight = ibm_db.result(stmt, 3) print "string(%d) \"%s\"" % (len(str(weight)), weight) ibm_db.close(conn) else: print "Connection failed."
def run_test_033(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)): weight = ibm_db.result(stmt, 3) print "string(%d) \"%s\"" % (len(str(weight)), weight) 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) ibm_db.close(conn) else: print "Connection failed."
def items(): page = '' if db2conn: stmt = ibm_db.exec_immediate(db2conn, "SELECT * FROM " + db2cred['username'] + ".ITEMS") # Look through each row, while there is a row while ibm_db.fetch_row(stmt) != False: # Get the rows Item column value page += ibm_db.result(stmt, "ITEM") + '\n' return page
def run_test_033(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)): weight = ibm_db.result(stmt, 3) print "string(%d) \"%s\"" % (len(str(weight)), weight) 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) ibm_db.close(conn) else: print "Connection failed."
def connectBD2(): # 连接数据库 # conn = ibm_db.connect('DATABASE=uibs;HOSTNAME=10.125.192.32;PORT=60000;PROTOCOL=TCPIP;UID=db2inst1;PWD=db2inst1;', '', '') conn = ibm_db.connect(conn_str, '', '') # 获取数据库服务器信息 server = ibm_db.server_info(conn) # 查询数据库sql sql ='select PID, "NAME" from PRODUCT' arr = [] dc = {} if conn: # 执行查询语句 stmt = ibm_db.exec_immediate(conn, sql) # 处理查询结果 while (ibm_db.fetch_row(stmt)): acno, dc[acno] = ibm_db.result(stmt, 0), ibm_db.result(stmt, 1) arr.append(acno) # 打印服务器名称 print(server.DBMS_NAME) # 关闭数据库连接 ibm_db.close(conn) return arr, dc
def run_test_113(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = "DROP TABLE datetest" try: ibm_db.exec_immediate( conn, drop ) except: pass create = "CREATE TABLE datetest ( id INTEGER, mydate DATE )" ibm_db.exec_immediate(conn, create) server = ibm_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): insert = "INSERT INTO datetest (id, mydate) VALUES (1,'1982-03-27')" ibm_db.exec_immediate(conn, insert) insert = "INSERT INTO datetest (id, mydate) VALUES (2,'1981-07-08')" ibm_db.exec_immediate(conn, insert) else: insert = "INSERT INTO datetest (id, mydate) VALUES (1,'1982-03-27')" ibm_db.exec_immediate(conn, insert) insert = "INSERT INTO datetest (id, mydate) VALUES (2,'1981-07-08')" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.prepare(conn, "SELECT * FROM datetest") ibm_db.execute(stmt) result = ibm_db.fetch_row( stmt ) while ( result ): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) print row0 print row1 result = ibm_db.fetch_row( stmt ) else: print "Connection failed."
def run_test_113(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = "DROP TABLE datetest" try: ibm_db.exec_immediate(conn, drop) except: pass create = "CREATE TABLE datetest ( id INTEGER, mydate DATE )" ibm_db.exec_immediate(conn, create) server = ibm_db.server_info(conn) if (server.DBMS_NAME[0:3] == 'IDS'): insert = "INSERT INTO datetest (id, mydate) VALUES (1,'1982-03-27')" ibm_db.exec_immediate(conn, insert) insert = "INSERT INTO datetest (id, mydate) VALUES (2,'1981-07-08')" ibm_db.exec_immediate(conn, insert) else: insert = "INSERT INTO datetest (id, mydate) VALUES (1,'1982-03-27')" ibm_db.exec_immediate(conn, insert) insert = "INSERT INTO datetest (id, mydate) VALUES (2,'1981-07-08')" ibm_db.exec_immediate(conn, insert) stmt = ibm_db.prepare(conn, "SELECT * FROM datetest") ibm_db.execute(stmt) result = ibm_db.fetch_row(stmt) while (result): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) print(row0) print(row1) result = ibm_db.fetch_row(stmt) else: print("Connection failed.")
def main(args): global conn ssldsn = args["__bx_creds"]["dashDB"]["ssldsn"] if globals().get("conn") is None: print("creating connection") conn = ibm_db.connect(ssldsn, "", "") else: print("reusing connection") drop = "DROP TABLE CUSTOMER_FEEDBACK" result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass statement = "CREATE TABLE CUSTOMER_FEEDBACK (CUST_ID INTEGER, FEEDBACK VARCHAR(255), date TIMESTAMP)" result = ibm_db.exec_immediate(conn, statement) # Check table creation statement = "SELECT * FROM CUSTOMER_FEEDBACK" result = ibm_db.exec_immediate(conn, statement) for i in range(0, ibm_db.num_fields(result)): print(str(i), ":", ibm_db.field_type(result, i)) ts_val = datetime.datetime.today() print("the time is", ts_val) # Try to insert sample test row statement = "INSERT INTO CUSTOMER_FEEDBACK (CUST_ID, FEEDBACK, date) VALUES (?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (10001, "IBM Functions Rocks!", ts_val)) statement = "SELECT * FROM CUSTOMER_FEEDBACK" stmt = ibm_db.prepare(conn, statement) rc = ibm_db.execute(stmt) result = ibm_db.fetch_row(stmt) print(ibm_db.result(stmt, 0), ibm_db.result(stmt, 1), ibm_db.result(stmt, 2)) if not result: return {"err": "error :" + statement} return {"result": "succesfully created TABLE CUSTOMER_FEEDBACK"}
def main(args): global conn ssldsn = args["__bx_creds"]["dashDB"]["ssldsn"] if globals().get("conn") is None: print("creating connection") conn = ibm_db.connect(ssldsn, "", "") else: print("reusing connection") drop = "DROP TABLE CUSTOMER_FEEDBACK" result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass statement = "CREATE TABLE CUSTOMER_FEEDBACK (CUST_ID INTEGER, FEEDBACK VARCHAR(255), date TIMESTAMP)" result = ibm_db.exec_immediate(conn, statement) # Check table creation statement = "SELECT * FROM CUSTOMER_FEEDBACK" result = ibm_db.exec_immediate(conn, statement) for i in range(0, ibm_db.num_fields(result)): print(str(i), ":", ibm_db.field_type(result, i)) ts_val = datetime.datetime.today() print("the time is", ts_val) # Try to insert sample test row statement = "INSERT INTO CUSTOMER_FEEDBACK (CUST_ID, FEEDBACK, date) VALUES (?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (10001, "IBM Functions Rocks!", ts_val)) statement = "SELECT * FROM CUSTOMER_FEEDBACK" stmt = ibm_db.prepare(conn, statement) rc = ibm_db.execute(stmt) result = ibm_db.fetch_row(stmt) print(ibm_db.result(stmt, 0), ibm_db.result( stmt, 1), ibm_db.result(stmt, 2)) if not result: return {"err": "error :" + statement} return {"result": "succesfully created TABLE CUSTOMER_FEEDBACK"}
def check_community_owners(community_id): active = False sql = "select DISTINCT LOWER_LOGIN from sncomm.memberlogin where member_uuid in (SELECT DISTINCT MEMBER_UUID from sncomm.member WHERE COMMUNITY_UUID = '" + community_id + "' AND ROLE='1' AND LOWER_LOGIN NOT LIKE '%@%');" stmt = ibm_db.prepare(conn_comm, sql) try: ibm_db.execute(stmt) while ibm_db.fetch_row(stmt) != False: member_login = ibm_db.result(stmt, 0) if check_if_user_active(member_login): active = True return True break else: return False except: print "Transaction couldn't be completed, error getting communities data: " , ibm_db.stmt_errormsg() sys.exit(3)
def dexec_db2(conn,schema,table_name,field_name,sql_fld,test_type): if sql_fld is None: if test_type == 'COUNT': sql = """select count(*) from """ + schema + """.""" + table_name + """ with ur""" stmt = ibm_db.exec_immediate(conn, sql) elif test_type == 'SUM': sql = """select sum("""+field_name+""") from """ + schema + """.""" + table_name + """ with ur""" stmt = ibm_db.exec_immediate(conn, sql) else: stmt = ibm_db.exec_immediate(conn, sql_fld) cnt = [] while ibm_db.fetch_row(stmt) != False: cnt.append(ibm_db.result(stmt, 0)) return cnt
def main(args): ssldsn = args["__bx_creds"]["dashDB"]["ssldsn"] conn = ibm_db.connect(ssldsn, "", "") if conn: print("Connection succeeded.") else: print("Connection failed.") return {"error": "Error connecting to db2"} # Select from TestTable (the row just inserted) cmd = "SELECT HISP_DESC FROM SAMPLES.HISPANIC_ORIGIN WHERE HISP_CODE='03'" result = ibm_db.exec_immediate(conn, cmd) if not result: return {"error": "error :" + cmd} else: ibm_db.fetch_both(result, 0) value = ibm_db.result(result, "HISP_DESC") ibm_db.close(conn) return {"HISP_DESC": value}
def GetListOfComponentsRecentlyModified(modified_since, xmeta_user='******', xmeta_password='******'): """ modified_since should be a datetime ( utc) ( or None). What's the best way of providing db credentials to this? Initial MVP - just doing jobdefns """ if modified_since is None: logMessage.info('Getting list of all DataStage components') else: logMessage.info( 'Getting list of DataStage components modified since :' + str(modified_since)) # https://www.ibm.com/docs/en/db2/9.7?topic=db-fetching-rows-columns-from-result-sets #https://stackoverflow.com/questions/6044326/how-to-connect-python-to-db2 ## might need rh-python36-python-devel.x86_64 to be able to run this - nope that didn't help/ ## maybe need some optional repos? yum install python3-devel.x86_64 --enablerepo=rhel-7-server-optional-rpms ## Yep, that sorted it. yum install python3-devel.x86_64 --enablerepo=rhel-7-server-optional-rpms #from ibm_db import connect, exec_immediate database = 'XMETA' database_schema = 'XMETA' ## Setup input args import ibm_db # Careful with the punctuation here - we have 3 arguments. # The first is a big string with semicolons in it. # (Strings separated by only whitespace, newlines included, # are automatically joined together, in case you didn't know.) # The last two are emptry strings. conn = ibm_db.connect( 'DATABASE=' + database + ';' 'HOSTNAME=localhost;' # 127.0.0.1 or localhost works if it's local 'PORT=50000;' 'PROTOCOL=TCPIP;' 'UID=' + xmeta_user + ';' 'PWD=' + xmeta_password + ';', '', '') ## Example ..get all tables #from ibm_db import tables #t = db2_results(tables(connection)) #print(t) # This SQL would show you the different types of class and users that modify things. #db2 "select CLASSNAME_XMETA, XMETA_MODIFIED_BY_USER_XMETA, count(*) from DATASTAGEX_DSITEM group by CLASSNAME_XMETA, XMETA_MODIFIED_BY_USER_XMETA " ## Maybe we should not include things last modified by DataStageSystemUser in the backup - I assume they are default components. #SQL="select NAME_XMETA, XMETA_MODIFICATION_TIMESTAMP_XMETA \ # from ISMETA01.DATASTAGEX_DSJOBDEF \ # order by XMETA_MODIFICATION_TIMESTAMP_XMETA desc \ # fetch first 6 rows only" ##ob I created has modification date 2020-10-08-08.29.14.000000 #That's shown as 1602142154825 in db2 # #So, need to divide the number by 1000 and add as seconds to 1970-01-01 # Convert the timestamp input from datetime import datetime dt = datetime.today() # Get timezone naive now seconds = dt.timestamp() import time import datetime where_clause = """ t2.XMETA_MODIFIED_BY_USER_XMETA <> 'DataStageSystemUser' and t2.XMETA_MODIFIED_BY_USER_XMETA <> 'admin01' """ if modified_since is not None: where_clause += ' and t2.XMETA_MODIFICATION_TIMESTAMP_XMETA >= ' + str( modified_since.timestamp() * 1000) #sql=""" # select XMETA_MODIFIED_BY_USER_XMETA, PROJECTNAMESPACE_XMETA, CLASSNAME_XMETA, NAME_XMETA, XMETA_MODIFICATION_TIMESTAMP_XMETA # from XMETA.DATASTAGEX_DSITEM # where """ + where_clause + """ # ; # """ sql = """ select t1.CATEGORY_XMETA, t2.NAME_XMETA, t2.XMETA_MODIFICATION_TIMESTAMP_XMETA, t2.CLASSNAME_XMETA, t2.PROJECTNAMESPACE_XMETA from """ + database_schema + """.DATASTAGEX_DSJOBDEF t1 inner join """ + database_schema + """.DATASTAGEX_DSITEM t2 on ( t1.XMETA_REPOS_OBJECT_ID_XMETA = t2.REPOSID_XMETA ) where """ + where_clause + """ ; """ ## Need to include component path in here too. ## I guess need to join to these or tables like these """" DATASTAGEX_DSIMSVIEWSET CATEGORY_XMETA DATASTAGEX_DSJCLTEMPLATE CATEGORY_XMETA DATASTAGEX_DSJOBDEF CATEGORY_XMETA DATASTAGEX_DSMACHINEPROFILE CATEGORY_XMETA DATASTAGEX_DSPARAMETERSET CATEGORY_XMETA DATASTAGEX_DSROUTINE CATEGORY_XMETA DATASTAGEX_DSSHAREDCONTAINERDEF CATEGORY_XMETA DATASTAGEX_DSSTAGETYPE CATEGORY_XMETA DATASTAGEX_DSTABLEDEFINITION CATEGORY_XMETA DATASTAGEX_DSTRANSFORM CATEGORY_XMETA VWDATASTAGEX_DSIMSROOTOBJECT CATEGORY_XMETA VWMMI_ABSTRACTPERSISTENTADMINPROPERTY CATEGORY_XMETA maybe better to have something liek. select t1.CATEGORY_XMETA , t2.* from DATASTAGEX_DSJOBDEF t1 inner join DATASTAGEX_DSITEM t2 on ( t1.XMETA_REPOS_OBJECT_ID_XMETA = t2.REPOSID_XMETA ) """ ## build dictionary of #( engine, project) [ (component_type, component_name, component_last_modified_timestamp) , .. , .. ] objectsList = {} # #sql = "SELECT * FROM XMETA.DATASTAGEX_DSITEM " stmt = ibm_db.exec_immediate(conn, sql) #counter=0 while ibm_db.fetch_row(stmt) != False: #counter+=1 #mod_timestamp=datetime.datetime.fromtimestamp(mod_timestamp_unix,tz=datetime.timezone.utc) # Let's just stick with utc. #formatted_modification_timestamp=mod_timestamp.strftime("%A, %B %d, %Y %I:%M:%S") project_namespace = ibm_db.result(stmt, "PROJECTNAMESPACE_XMETA") project_namespace_tuple = tuple(project_namespace.split(':')) component_type = ibm_db.result(stmt, "CLASSNAME_XMETA") component_name = ibm_db.result(stmt, "NAME_XMETA") mod_timestamp_unix = int( ibm_db.result(stmt, "XMETA_MODIFICATION_TIMESTAMP_XMETA")) / 1000 component_last_modified_ts = datetime.datetime.fromtimestamp( mod_timestamp_unix, tz=datetime.timezone.utc) category = ibm_db.result(stmt, "CATEGORY_XMETA") try: objectsList[project_namespace_tuple] += [ (component_type, component_name, component_last_modified_ts, category) ] except KeyError: objectsList[project_namespace_tuple] = [ (component_type, component_name, component_last_modified_ts, category) ] return objectsList
# If The SQL Statement Could Not Be Executed, Display An Error Message And Exit if resultSet is False: print("\nERROR: Unable to execute the SQL statement specified.\n") conn.closeConnection() exit(-1) # Otherwise, Complete The Status Message else: print("Done!\n") # Display A Report Header print("Query results:\n") print("DEPTNAME") print("____________________________") # As Long As There Are Records In The Result Set Produced, ... while (ibm_db.fetch_row(resultSet) is True): # Display The Data Retrieved print(ibm_db.result(resultSet, 0)) # Add A Blank Line To The End Of The Report print() # Close The Database Connection That Was Opened Earlier conn.closeConnection() # Return Control To The Operating System exit()
insertQuery2 = "insert into INSTRUCTOR values (2, 'Raul', 'Chong', 'Markham', 'CA'), (3, 'Hima', 'Vasudevan', 'Chicago', 'US')" insertStmt2 = ibm_db.exec_immediate(conn, insertQuery2) ########################## #Construct the query that retrieves all rows from the INSTRUCTOR table selectQuery = "select * from INSTRUCTOR" #Execute the statement selectStmt = ibm_db.exec_immediate(conn, selectQuery) #Fetch the Dictionary (for the first row only) ibm_db.fetch_both(selectStmt) #Fetch the rest of the rows and print the ID and FNAME for those rows while ibm_db.fetch_row(selectStmt) != False: print(" ID:", ibm_db.result(selectStmt, 0), " FNAME:", ibm_db.result(selectStmt, "FNAME")) ############################ #Update query updateQuery = "update INSTRUCTOR set CITY='MOOSETOWN' where FNAME='Rav'" updateStmt = ibm_db.exec_immediate(conn, updateQuery) ########################### # Retrieve data into pandas import pandas import ibm_db_dbi #connection for pandas pconn = ibm_db_dbi.Connection(conn)
def chat(): # check if user is logged in if not session.get('logged_in'): return render_template('index.html') else: user_ip = request.remote_addr user_agent = request.headers.get('User-Agent') session['unique_conversation_id'] = str(user_ip) + "__" + str(user_agent) context["conversation_id"] = str(hashlib.sha256(session['unique_conversation_id'].encode('utf-8')).hexdigest()) if request.method == "POST": if 'cardNo' in request.form: sql = "SELECT * FROM CARD WHERE CARDNO = ?" stmt = ibm_db.prepare(conn, sql) param = request.form["cardNo"], ibm_db.execute(stmt, param) if ibm_db.fetch_row(stmt) == False: sql = "SELECT * FROM CARD ORDER BY ID DESC fetch first 1 row only" stmt = ibm_db.exec_immediate(conn, sql) card = 1 while ibm_db.fetch_row(stmt) != False: card = card + int(ibm_db.result(stmt, "ID")) # Save new customer card info sql = "INSERT INTO CARD (ID, BVN, ACCNO, CARDNO, CARDVENDOR, CARDTYPE, CVC, EXPIRY) VALUES (?, ?, ?, ?, ?, ?, ?, ?)" stmt = ibm_db.prepare(conn, sql) param = card, user["bvn"], request.form["cardAccountNo"], request.form["cardNo"], request.form["cardVendor"], request.form["cardType"], request.form["cvc"], request.form["cardExpiry"], ibm_db.execute(stmt, param) msg = Message('CASBA: New Card', sender = '*****@*****.**', recipients = [str(user["email"])]) msg.body = "You have added a new card" mail.send(msg) flash('You have successfully added a card!') sql2 = "".join(["SELECT * FROM CARD WHERE BVN = ", str(user['bvn'])]) stmt2 = ibm_db.exec_immediate(conn, sql2) userCardDF = pandas.read_sql(sql2 , pconn) if userCardDF.empty: user.update({'#Card': 0}) return redirect(url_for('chat')) else: user.update({'#Card': len(userCardDF.index)}) userCard = {} for i in range(0, user["#Card"]): userCard[i] = userCardDF.iloc[i].tolist() user.update({'Card': userCard}) #print user["Account"][0][0] return redirect(url_for('chat')) return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode) else: flash('You have card already registered!') return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode) elif 'accountNo' in request.form: sql = "SELECT * FROM ACCOUNT WHERE ACCNO = ?" stmt = ibm_db.prepare(conn, sql) param = request.form["accountNo"], ibm_db.execute(stmt, param) if ibm_db.fetch_row(stmt) == False: sql = "SELECT * FROM ACCOUNT ORDER BY ID DESC fetch first 1 row only" stmt = ibm_db.exec_immediate(conn, sql) account = 1 while ibm_db.fetch_row(stmt) != False: account = account + int(ibm_db.result(stmt, "ID")) # Save new customer info sql = "INSERT INTO ACCOUNT (ID, BVN, ACCNO, ACCBANK, ACCNAME, ACCTYPE, ACCBALANCE) VALUES (?, ?, ?, ?, ?, ?, ?)" stmt = ibm_db.prepare(conn, sql) param = account, user["bvn"], request.form["accountNo"], request.form["accountBank"], request.form["accountName"], request.form["accountType"], request.form["accountBalance"], ibm_db.execute(stmt, param) msg = Message('CASBA: New Account', sender = '*****@*****.**', recipients = [str(user["email"])]) msg.body = "You have added a new account" mail.send(msg) flash('You were successfully setup an account!') sql1 = "".join(["SELECT * FROM ACCOUNT WHERE BVN = ", str(user['bvn'])]) stmt1 = ibm_db.exec_immediate(conn, sql1) userAccountDF = pandas.read_sql(sql1 , pconn) if userAccountDF.empty: user.update({'accountIn': 0}) return redirect(url_for('chat')) else: user.update({'accountIn': len(userAccountDF.index)}) userAccount = {} for i in range(0, user["accountIn"]): userAccount[i] = userAccountDF.iloc[i].tolist() user.update({'Account': userAccount}) #print user["Account"][0][0] return redirect(url_for('chat')) return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode) else: flash('You have account already registered!') return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode) else: flash('Unsuccessful account setup!') return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode) else: return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode)
if check_if_user_active(member_login): active = True return True break else: return False except: print "Transaction couldn't be completed, error getting communities data: " , ibm_db.stmt_errormsg() sys.exit(3) # Query DB2 database sql = "select DISTINCT COMMUNITY_UUID, NAME from sncomm.community;" stmt = ibm_db.prepare(conn_comm, sql) try: ibm_db.execute(stmt) while ibm_db.fetch_row(stmt) != False: community_id = ibm_db.result(stmt, 0) name = ibm_db.result(stmt, 1) if not check_community_owners(community_id): list =list + name + " ID: " + community_id + "\n" count = count + 1 except: print "Transaction couldn't be completed, error getting communities: " , ibm_db.stmt_errormsg() sys.exit(3) # Nagios standard feedback if (count < int(args.warning)): print("Communites are OK, " + str(count) + " Communities without an owner: " + list.encode('utf-8') + "| Number of Communities=" + str(count) + ";" + str(args.warning) + ";" + str(args.critical) + ";;") sys.exit(0) elif (count < int(args.critical)): print("Warning: " + str(count) + " Communities without an owner: "+ list.encode('utf-8') + "| Number of Communities=" + str(count) + ";" + str(args.warning) + ";" + str(args.critical) + ";;") sys.exit(1)
def run_test_6792(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: drop = 'DROP TABLE table_6792' result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass t_val = '10:42:34' d_val = '1981-07-08' ts_val = '1981-07-08 10:42:34' ts_withT_val = '2013-06-06T15:30:39' server = ibm_db.server_info( conn ) if (server.DBMS_NAME[0:3] == 'IDS'): statement = "CREATE TABLE table_6792 (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO SECOND, col4 DATETIME YEAR TO SECOND)" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (col1, col2, col3) values (?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (t_val, d_val, ts_val)) else: statement = "CREATE TABLE table_6792 (col1 TIME, col2 DATE, col3 TIMESTAMP, col4 TIMESTAMP)" result = ibm_db.exec_immediate(conn, statement) statement = "INSERT INTO table_6792 (col1, col2, col3, col4) values (?, ?, ?, ?)" stmt = ibm_db.prepare(conn, statement) result = ibm_db.execute(stmt, (t_val, d_val, ts_val, ts_withT_val)) statement = "SELECT * FROM table_6792" result = ibm_db.exec_immediate(conn, statement) for i in range(0, ibm_db.num_fields(result)): print str(i) + ":" + ibm_db.field_type(result,i) statement = "SELECT * FROM table_6792" stmt = ibm_db.prepare(conn, statement) rc = ibm_db.execute(stmt) result = ibm_db.fetch_row(stmt) while ( result ): row0 = ibm_db.result(stmt, 0) row1 = ibm_db.result(stmt, 1) row2 = ibm_db.result(stmt, 2) row3 = ibm_db.result(stmt, 3) print row0 print row1 print row2 print row3 result = ibm_db.fetch_row(stmt) ibm_db.close(conn) else: print "Connection failed." #__END__ #__LUW_EXPECTED__ #0:time #1:date #2:timestamp #3:timestamp #10:42:34 #1981-07-08 #1981-07-08 10:42:34 #2013-06-06 15:30:39 #__ZOS_EXPECTED__ #0:time #1:date #2:timestamp #3:timestamp #10:42:34 #1981-07-08 #1981-07-08 10:42:34 #2013-06-06 15:30:39 #__SYSTEMI_EXPECTED__ #0:time #1:date #2:timestamp #3:timestamp #10:42:34 #1981-07-08 #1981-07-08 10:42:34 #2013-06-06 15:30:39 #__IDS_EXPECTED__ #0:time #1:date #2:timestamp #3:timestamp #10:42:34 #1981-07-08 #1981-07-08 10:42:34 #2013-06-06 15:30:39
def index(): if request.method == "POST": if 'signupBVN' in request.form: sql = "SELECT * FROM CUSTOMER WHERE BVN = ?" stmt = ibm_db.prepare(conn, sql) param = request.form["signupBVN"], ibm_db.execute(stmt, param) if ibm_db.fetch_row(stmt) == False: sql = "SELECT * FROM CUSTOMER ORDER BY ID DESC fetch first 1 row only" stmt = ibm_db.exec_immediate(conn, sql) customer = 1 while ibm_db.fetch_row(stmt) != False: customer = customer + int(ibm_db.result(stmt, "ID")) # Save new customer info sql = "INSERT INTO CUSTOMER (ID, BVN, FIRSTNAME, LASTNAME, DATEOFBIRTH, PHONENUMBER, EMAIL, PASSWORD, CITY, DOC) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" stmt = ibm_db.prepare(conn, sql) param = customer, request.form["signupBVN"], request.form["inputFName"], request.form["inputLName"], request.form["inputDOB"], request.form["inputPhone"], request.form["inputEmail"], request.form["inputPassword"], request.form["inputLocation"], datetime.date.today(), ibm_db.execute(stmt, param) # Save security info for new customer sql = "INSERT INTO SECURITY (ID, BVN, SQ1, SA1, SQ2, SA2, SQ3, SA3) VALUES (?, ?, ?, ?, ?, ?, ?, ?)" stmt = ibm_db.prepare(conn, sql) param = customer, request.form["signupBVN"], request.form["inputSQ1"], request.form["inputSA1"], request.form["inputSQ2"], request.form["inputSA2"], request.form["inputSQ3"], request.form["inputSA3"], ibm_db.execute(stmt, param) msg = Message('Welcome to CASBA', sender = '*****@*****.**', recipients = [str(request.form["inputEmail"])]) msg.body = "Thank you for registering to use of Cognitive Banking (CogniBank) service. Enjoy the experience of using artifical intelligence to organise your finances." mail.send(msg) flash('You were successfully registered!') return redirect(url_for('index')) else: flash('You were already registered!') return redirect(url_for('index')) elif 'loginBVN' in request.form: sql = "SELECT * FROM CUSTOMER WHERE BVN = ?" stmt = ibm_db.prepare(conn, sql) param = request.form["loginBVN"], ibm_db.execute(stmt, param) if ibm_db.fetch_row(stmt) != False: # check if user credentials match if request.form["inputPassword"] == ibm_db.result(stmt, "PASSWORD"): session['logged_in'] = True user.update({'bvn': str(request.form["loginBVN"]), 'fName': str(ibm_db.result(stmt, "FIRSTNAME")), 'lName': str(ibm_db.result(stmt, "LASTNAME")), 'email': str(ibm_db.result(stmt, "EMAIL")) }) # sql = "SELECT * FROM ACCOUNT WHERE BVN = ?" # stmt = ibm_db.prepare(conn, sql) # param = request.form["loginBVN"], # ibm_db.execute(stmt, param) sql1 = "".join(["SELECT * FROM ACCOUNT WHERE BVN = ", str(request.form["loginBVN"])]) stmt1 = ibm_db.exec_immediate(conn, sql1) userAccountDF = pandas.read_sql(sql1 , pconn) sql2 = "".join(["SELECT * FROM CARD WHERE BVN = ", str(user['bvn'])]) stmt2 = ibm_db.exec_immediate(conn, sql2) userCardDF = pandas.read_sql(sql2 , pconn) if userAccountDF.empty: user.update({'accountIn': 0}) if userCardDF.empty: user.update({'#Card': 0}) return redirect(url_for('chat')) else: user.update({'accountIn': len(userAccountDF.index)}) userAccount = {} for i in range(0, user["accountIn"]): userAccount[i] = userAccountDF.iloc[i].tolist() user.update({'Account': userAccount}) #print user["Account"][0][0] if userCardDF.empty: user.update({'#Card': 0}) return redirect(url_for('chat')) else: user.update({'#Card': len(userCardDF.index)}) userCard = {} for i in range(0, user["#Card"]): userCard[i] = userCardDF.iloc[i].tolist() user.update({'Card': userCard}) #print user["Account"][0][0] return redirect(url_for('chat')) else: flash('Wrong credentials, please try again or create an account!') return redirect(url_for('index')) else: flash('Wrong credentials, please try again or create an account!') return redirect(url_for('index')) elif 'passwordBVN' in request.form: sql = "SELECT * FROM SECURITY WHERE BVN = ?" stmt = ibm_db.prepare(conn, sql) param = request.form["passwordBVN"], ibm_db.execute(stmt, param) # check if security questions match if ibm_db.fetch_row(stmt) != False: if str(ibm_db.result(stmt, "SQ1")) == str(request.form["inputSQ1"]) and str(ibm_db.result(stmt, "SA1")) == str(request.form["inputSA1"]): if str(ibm_db.result(stmt, "SQ2")) == str(request.form["inputSQ2"]) and str(ibm_db.result(stmt, "SA2")) == str(request.form["inputSA2"]): if str(ibm_db.result(stmt, "SQ3")) == str(request.form["inputSQ3"]) and str(ibm_db.result(stmt, "SA3")) == str(request.form["inputSA3"]): new_password = gen_random_string(password_charset, 6) sql = "UPDATE CUSTOMER SET PASSWORD = ? WHERE BVN = ?" stmt = ibm_db.prepare(conn, sql) param = new_password, request.form["passwordBVN"], ibm_db.execute(stmt, param) sql = "SELECT * FROM CUSTOMER WHERE BVN = ?" stmt = ibm_db.prepare(conn, sql) param = request.form["passwordBVN"], ibm_db.execute(stmt, param) if ibm_db.fetch_row(stmt) != False: msg = Message('Password Reset', sender = '*****@*****.**', recipients = [str(ibm_db.result(stmt, "EMAIL"))]) msg.body = ' '.join(["Here is your new password", new_password]) mail.send(msg) flash('Your password has been reset! Please check your email.') return redirect(url_for('index')) else: flash('You are not registered! Please try again or create an account.') return redirect(url_for('index')) else: flash('Incorrect Security Question & Answer') return redirect(url_for('index')) else: flash('Incorrect Security Question & Answer') return redirect(url_for('index')) else: flash('Incorrect Security Question & Answer') return redirect(url_for('index')) else: flash('You are not a registered user! Please create an account.') return redirect(url_for('index')) else: return render_template('index.html')
dsn_protocol, dsn_uid, dsn_pwd) #print(dsn) try: conn = ibm_db.connect(dsn, "", "") print("Connected!") except: print("Unable to connect to database") #1 createstmt=ibm_db.exec_immediate(conn,"create table Employee(name varchar(20), id int, dept char(10))") #2 to insert a record """ins="insert into Employee values('Amritsar','18','BCA')" insertstmt=ibm_db.exec_immediate(conn,ins)""" #3 to drop a table #dropstmt=ibm_db.exec_immediate(conn,"drop table Employee") #4 getting data from table selectquery = "select * from Employee" selectstmt = ibm_db.exec_immediate(conn, selectquery) while ibm_db.fetch_row(selectstmt) != False: print("Name:", ibm_db.result(selectstmt, 0), " ID: ", ibm_db.result(selectstmt, 1), " Dept: ", ibm_db.result(selectstmt, 2)) ibm_db.close(conn)
uid = args.user pwd = args.pw # Connect to DB2 database try: conn_people = ibm_db.connect("DATABASE=" + peopledb + ";HOSTNAME=" + args.host_ip + ";PORT=" + port + ";PROTOCOL=TCPIP;UID=" + uid + ";PWD=" + pwd + ";", "", "") conn_comm = ibm_db.connect("DATABASE=" + communitydb + ";HOSTNAME=" + args.host_ip + ";PORT=" + port + ";PROTOCOL=TCPIP;UID=" + uid + ";PWD=" + pwd + ";", "", "") except: print "No connection to DBs:", ibm_db.conn_errormsg() sys.exit(3) # Query DB2 database sql = "select DISTINCT COMMUNITY_UUID, NAME, LASTMOD from sncomm.community;" stmt = ibm_db.prepare(conn_comm, sql) try: ibm_db.execute(stmt) while ibm_db.fetch_row(stmt) != False: name = ibm_db.result(stmt, "NAME") lastupdate = ibm_db.result(stmt, "LASTMOD") if (datetime.datetime.now() - datetime.timedelta(days=int(args.critical)) > lastupdate): count_critical = count_critical + 1 list_critical = list_critical + name + "\n" elif (datetime.datetime.now() - datetime.timedelta(days=int(args.warning)) > lastupdate): count_warning = count_warning + 1 list_warning = list_warning + name + "\n" except: print "Transaction couldn't be completed, error getting communities: " , ibm_db.stmt_errormsg() sys.exit(3) # Nagios standard feedback if (count_critical!=0): print("Critical: There are communites which were not updates since " + args.critical + " days" + list_critical.encode('utf-8') + "| Warning Number=" + str(count_warning) + ";" + str(args.warning) + ";" + str(args.critical) + ";;") sys.exit(2)
# Otherwise, Complete The Status Message else: print("Done!\n") # Display A Report Header print("Query results:\n") print("DEPTNAME") print("____________________________") # As Long As There Are Records In The Result Set Produced, ... while (ibm_db.fetch_row(resultSet) is True): # Retrieve The Data From The Current Row try: dataValue = ibm_db.result(resultSet, 0) except Exception: pass # Display The Data Retrieved if not dataValue is None: print(dataValue) # Add A Blank Line To The End Of The Report print() # Close The Database Connection That Was Opened Earlier conn.closeConnection() # Return Control To The Operating System exit()
try: conn = ibm_db.connect(dsn, "", "") print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname) #Deleting only the rows which meet some criteria ## DeleteQuery = "delete from "+TABLENAME+" WHERE id="+'EXAMPLE' ## DeleteStmt = ibm_db.exec_immediate(conn, DeleteQuery) # Or you may just want to delete everything: DeleteQuery = "delete from " + TABLENAME DeleteStmt = ibm_db.exec_immediate(conn, DeleteQuery) #Checking the result selectQuery = "select * from " + TABLENAME selectStmt = ibm_db.exec_immediate(conn, selectQuery) #Fetch the Dictionary, if the only row is enough for you #ibm_db.fetch_both(selectStmt) #Fetch all the rows and print the first([0]) and the second ("USERNAME") columns for those rows while ibm_db.fetch_row(selectStmt) != False: print(" ID:", ibm_db.result(selectStmt, 0), " @username:"******"USERNAME")) except: print("something went wrong, but I don't know what exactly ", ibm_db.conn_errormsg()) ibm_db.close(conn) print("Connection closed")
def config_IBMHIST(conn, coll_path, arch_path, max_size, arch_cmd, arch_ext): print("Configuring IBMHIST settings ...") # delete from IBMHIST.TAB_CONFIG print(" Deleting configurations from IBMHIST.TAB_CONFIG ...") ibm_db.exec_immediate(conn, "DELETE FROM IBMHIST.TAB_CONFIG") # rename current collection directory to avoid name conflicts stmt = ibm_db.exec_immediate( conn, "SELECT PATH FROM IBMHIST.TAB_DIRS WHERE STATUS = 'COLL'") while ibm_db.fetch_row(stmt): # get orig path orig_path = ibm_db.result(stmt, "PATH") # get new path by appending current minute and current second new_path = orig_path + '_old' + str( datetime.datetime.now().minute) + str( datetime.datetime.now().second) # rename print(" Renaming collection directory '%s' to '%s' ..." % (orig_path, new_path)) shutil.move(orig_path, new_path) # update IBMHIST.TAB_DIRS ibm_db.exec_immediate( conn, "UPDATE IBMHIST.TAB_DIRS SET PATH = '%s' WHERE PATH = '%s'" % (new_path, orig_path)) # add data collection path configuration print(" Setting COLL_PATH to '%s' ..." % (coll_path)) assert os.path.exists( coll_path), "Data collection path: %s does not exist" % (coll_path) stmt, ret_coll_path, retcode = ibm_db.callproc( conn, 'IBMHIST.PATH_READABLE_WRITABLE', (coll_path, 0)) assert retcode is 0 or retcode is None, "Data collection path: %s does not provide fenced external functions read/write access" % ( coll_path) ibm_db.exec_immediate( conn, "INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'COLL_PATH', '%s', 'DIRECTORY PATH OF DATA COLLECTION') " % (coll_path)) # add data archival path configuration print(" Setting ARCH_PATH to '%s' ..." % (arch_path)) assert os.path.exists( arch_path), "Data archival path: %s does not exist" % (arch_path) stmt, ret_arch_path, retcode = ibm_db.callproc( conn, 'IBMHIST.PATH_READABLE_WRITABLE', (arch_path, 0)) assert retcode is 0 or retcode is None, "Data archival path: %s does not provide fenced external functions read/write access" % ( arch_path) ibm_db.exec_immediate( conn, "INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'ARCH_PATH', '%s', 'DIRECTORY PATH OF DATA ARCHIVAL') " % (arch_path)) # add max size configuration print(" Setting MAX_SIZE to '%s' bytes ..." % (max_size)) assert str.isdigit(max_size), "Max size: %s is not an integer" % (max_size) ibm_db.exec_immediate( conn, "INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'MAX_SIZE', '%s', 'MAX SIZE IN BYTES OF COLLECTION AND ARCHIVAL') " % (max_size)) # add archival command and archival extension print(" Setting ARCH_CMD to '%s' and ARCH_EXT to '%s' ..." % (arch_cmd, arch_ext)) # test archival functionality by archiving sql folder src, dest = "sql", "arch_test" + arch_ext arch_cmd_test = arch_cmd.replace("_src_", src).replace("_dest_", dest) if os.path.exists(dest): os.remove(dest) subprocess.check_call(arch_cmd_test, shell=True) assert os.path.exists( dest ), "Data archival command: %s failed, could not find archival %s " % ( arch_cmd, dest) os.remove(dest) ibm_db.exec_immediate( conn, "INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'ARCH_CMD', '%s', 'COMMAND USED TO ARCHIVE HOURLY DIRECTORIES') " % arch_cmd) ibm_db.exec_immediate( conn, "INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'ARCH_EXT', '%s', 'EXTENSION OF ARCHIVE HOURLY DIRECTORIES') " % arch_ext) # add task_details.json path configuration task_details_path = os.path.realpath("task_details.json") print(" Setting TASK_DETAILS_PATH to '%s' ..." % (task_details_path)) ibm_db.exec_immediate( conn, "INSERT INTO IBMHIST.TAB_CONFIG VALUES ( 'TASK_DETAILS_PATH', '%s', 'LOCATION OF task_details.json FILE') " % task_details_path)
def sql(self, line, cell=None): # Before we event get started, check to see if you have connected yet. Without a connection we # can't do anything. You may have a connection request in the code, so if that is true, we run those, # otherwise we connect immediately # If your statement is not a connect, and you haven't connected, we need to do it for you global settings global hdbc, hstmt, connected select = ["SELECT", "WITH", "VALUES"] noBlock = 0 sqlBlock = 1 db2Block = 2 # If you use %sql (line) we just run the SQL. If you use %%SQL the entire cell is run. flag_delim = ";" flag_results = True flag_sqlType = noBlock flag_quiet = False flag_json = False flag_timer = False flag_plot = 0 flag_cell = False flag_output = False flag_resultset = False flag_dataframe = False # The parameters must be in the line, not in the cell i.e. %sql -c Parms = line.strip() if len(Parms) == 0: if cell == None: sqlhelp() return if len(cell.strip()) == 0: sqlhelp() return # Check of you just want help if Parms == "?": sqlhelp() return if Parms.upper() == "? CONNECT": connected_help() return # If you issue a CONNECT statement in %sql then we run this first before auto-connecting if findKeyword(Parms,"CONNECT") == True: parseConnect(Parms) return # We need to check to see if we are connected before running any SQL if connected == False: db2_doConnect() if connected == False: return # Default result set size if settings["maxrows"] == -1: pandas.reset_option('max_rows') else: pandas.options.display.max_rows = settings["maxrows"] # Display rows as JSON structure if Parms.find("-j") >= 0: flag_json = True Parms = Parms.replace("-j"," ") # Load sample tables for scripts if Parms.find('-sampledata') >= 0: db2_create_sample() return # Execute the SQL so that it behaves like a SELECT statement if Parms.find("-s") >= 0: flag_sqlType = sqlBlock Parms = Parms.replace("-s"," ") # Execute the SQL but return the results in an array (basically a two-dimensional array) if Parms.find("-r") >= 0: flag_resultset = True Parms = Parms.replace("-r", " ") # Execute the SQL so that it behaves like an INSERT, DELETE, UPDATE or no result set if Parms.find("-n") >= 0: flag_sqlType = db2Block Parms = Parms.replace("-n"," ") # Quiet execution (no errors or completed messages) if Parms.find("-q") >= 0: flag_quiet = True Parms = Parms.replace("-q"," ") # Retrieve all rows (do not use the default limit) if Parms.find("-a") >= 0: pandas.reset_option('max_rows') Parms = Parms.replace("-a"," ") # Set the delimiter to @ instead of a semi-colon for procedures, triggers, and functions if Parms.find("-d") >= 0: flag_delim = "@" Parms = Parms.replace("-d"," ") # Timer function (not that useful, but worth a try) if Parms.find("-t") >= 0: flag_timer = True Parms = Parms.replace("-t"," ") # Plot functions -pb = bar, -pp = pie, -pl = line if Parms.find("-pb") >= 0: flag_plot = 1 Parms = Parms.replace("-pb"," ") if Parms.find("-pp") >= 0: flag_plot = 2 Parms = Parms.replace("-pp"," ") if Parms.find("-pl") >= 0: flag_plot = 3 Parms = Parms.replace("-pl"," ") if Parms.find("-i") >= 0: flag_plot = 4 Parms = Parms.replace("-i"," ") remainder = Parms.strip() # Split the line according to your delimiter if cell is None: sqlLines = [remainder] flag_cell = False else: cell = re.sub('.*?--.*$',"",cell,flags=re.M) remainder = cell.replace("\n"," ") sqlLines = remainder.split(flag_delim) flag_cell = True # For each line figure out if you run it as a command (db2) or select (sql) for sql in sqlLines: # Split the line so we know what the first keyword is. We only look at the first one. There may # be SQL that returns output that we may not know about keywords = sql.split() if len(keywords) == 0: continue sqlcmd = keywords[0].upper() if (flag_timer == True): count = sqlTimer(flag_sqlType, sql) if flag_quiet == False and count != -1: print("Total iterations in %s second(s): %s" % (runtime,count)) return(count) elif (flag_plot != 0): try: df = pandas.read_sql(sql,hstmt) except Exception as err: db2_error(False) return if flag_plot == 4: ShellAccess.pdf = df display(pdf) return plt.style.use('ggplot') plt.figure() col_count = len(df.columns) if flag_plot == 1: # Bar Chart if (col_count >= 2): xlabel = df.columns.values[0] ylabel = df.columns.values[1] _ = df.plot(kind='bar',x=xlabel,y=ylabel); else: _ = df.plot(kind='bar'); elif flag_plot == 2: # Pie if (col_count >= 2): xlabel = df.columns.values[0] xname = df[xlabel].tolist() yname = df.columns.values[1] _ = df.plot(kind='pie',y=yname,labels=xname); else: yname = df.columns.values[0] _ = df.plot(kind='pie',y=yname); elif flag_plot == 3: # Line Chart if (col_count >= 2): xlabel = df.columns.values[0] ylabel = df.columns.values[1] _ = df.plot(kind='line',x=xlabel,y=ylabel) ; else: _ = df.plot(kind='line') ; else: return plt.show() return elif (flag_sqlType == sqlBlock) or (sqlcmd in select and flag_sqlType != db2Block): if flag_json == True: try: stmt = ibm_db.exec_immediate(hdbc,sql); row_count = 0 while( ibm_db.fetch_row(stmt) ): row_count = row_count + 1 jsonVal = ibm_db.result(stmt,0) formatted_JSON = json.dumps(json.loads(jsonVal), indent=4, separators=(',', ': ')) # Print JSON Structure if row_count > 1: print() print("Row: %d" % row_count) print(formatted_JSON) flag_output = True except Exception as err: db2_error(flag_quiet) else: if flag_resultset == True: row_count = 0 resultSet = [] try: stmt = ibm_db.exec_immediate(hdbc,sql) result = ibm_db.fetch_tuple(stmt) while (result): row = [] for col in result: row.append(col) resultSet.append(row) result = ibm_db.fetch_tuple(stmt) return(resultSet) except Exception as err: db2_error(False) else: try: dp = pandas.read_sql(sql, hstmt) if flag_dataframe == True: return(dp) else: # pDisplay(dp) flag_output = True return(dp) except Exception as err: db2_error(flag_quiet) else: try: ibm_db.exec_immediate(hdbc,sql); if flag_cell == False and flag_quiet == False: print("Command completed.") except Exception as err: db2_error(flag_quiet) if flag_cell == True and flag_output == False: print("Command completed.")
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)))