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_015(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate( conn, "insert into t_string values(123,1.222333,'one to one')") if result: cols = ibm_db.num_fields(result) # NOTE: Removed '\n' from the following and a few more prints here (refer to ruby test_015.rb) print("col:", cols) rows = ibm_db.num_rows(result) print("affected row:", rows) else: print(ibm_db.stmt_errormsg()) result = ibm_db.exec_immediate(conn, "delete from t_string where a=123") 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()) ibm_db.close(conn) else: print("no connection:", ibm_db.conn_errormsg())
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_018(self): conn = ibm_db.connect(config.database, config.user, config.password) ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON) if conn: stmt = ibm_db.prepare(conn, "SELECT * from animals WHERE weight < 10.0" ) ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON}, 2) result = ibm_db.execute(stmt) if result: rows = ibm_db.num_rows(stmt) print "affected row:", rows ibm_db.free_result(stmt) else: print ibm_db.stmt_errormsg() ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_OFF}, 2) result = ibm_db.execute(stmt) if result: rows = ibm_db.num_rows(stmt) print "affected row:", rows ibm_db.free_result(stmt) else: print ibm_db.stmt_errormsg() ibm_db.set_option(stmt, {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON}, 2) result = ibm_db.execute(stmt) if result: rows = ibm_db.num_rows(stmt) 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_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_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_311(self): # Make a connection conn = ibm_db.connect(config.database, config.user, config.password) if conn: ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON ) # Drop the tab_num_literals table, in case it exists drop = 'DROP TABLE tab_num_literals' result = '' try: result = ibm_db.exec_immediate(conn, drop) except: pass # Create the animal table create = "CREATE TABLE tab_num_literals (col1 INTEGER, col2 FLOAT, col3 DECIMAL(7,2))" result = ibm_db.exec_immediate(conn, create) insert = "INSERT INTO tab_num_literals values ('11.22', '33.44', '55.66')" res = ibm_db.exec_immediate(conn, insert) print "Number of inserted rows:", ibm_db.num_rows(res) stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col1 = '11'") ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print data[0] print data[1] print data[2] data = ibm_db.fetch_both(stmt) sql = "UPDATE tab_num_literals SET col1 = 77 WHERE col2 = 33.44" res = ibm_db.exec_immediate(conn, sql) print "Number of updated rows:", ibm_db.num_rows(res) stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col2 > '33'") ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print data[0] print data[1] print data[2] data = ibm_db.fetch_both(stmt) sql = "DELETE FROM tab_num_literals WHERE col1 > '10.0'" res = ibm_db.exec_immediate(conn, sql) print "Number of deleted rows:", ibm_db.num_rows(res) stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col3 < '56'") ibm_db.execute(stmt) data = ibm_db.fetch_both(stmt) while ( data ): print data[0] print data[1] print data[2] data = ibm_db.fetch_both(stmt) ibm_db.rollback(conn) ibm_db.close(conn)
def run_test_err_executemany(self): conn = ibm_db.connect(config.database, config.user, config.password) serverinfo = ibm_db.server_info(conn) server = serverinfo.DBMS_NAME[0:3] if conn: try: ibm_db.exec_immediate(conn, 'DROP TABLE CLI0126E') except: pass create_ddl = "create table CLI0126E \ (\ offer_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\ position_id int NOT NULL,\ title VARCHAR(5000) NOT NULL,\ type VARCHAR(5000),\ quantity decimal(10, 3) NOT NULL,\ price_btc decimal(10, 8) NOT NULL,\ city VARCHAR(5000),\ country VARCHAR(500)\ )" try: ibm_db.exec_immediate(conn, create_ddl) except: pass insert_statement = "INSERT INTO CLI0126E (position_id, title, type, quantity, price_btc, city, country)\ VALUES (?, ?, ?, ?, ?, ?, ?)" stmt = ibm_db.prepare(conn, insert_statement) # deliberately use wrong size decimal values to trigger CLI0111E (hidden by execute_many()). parms1 = (15628, 'correct value in row1 column4', '', 1999999.0, 0.0067762, 'Belarus', 'Belarus1') parms2 = (15629, 'incorrect value in row2 column4', '', 99999999.0, 0.0067762, 'Belarus', 'Belarus2') parms3 = (15630, 'correct value in row3 column4', '', 1999999.0, 0.0067762, 'Belarus', 'Belarus3') parms = (parms1, parms2, parms3) try: ibm_db.execute_many(stmt, parms) print( str(ibm_db.num_rows(stmt)) + " - Rows inserted successfully") except: print( "Failed to insert multiple-rows with ibm_db.execute_many()" ) print(ibm_db.stmt_errormsg()) print("Number of rows inserted: " + str(ibm_db.num_rows(stmt))) ibm_db.close(conn) else: print("Connection failed.")
def run_test_execute_many(self): conn = ibm_db.connect(config.database, config.user, config.password) server = ibm_db.server_info( conn ) if( not server.DBMS_NAME.startswith('DB2/')): print("Boolean is not supported") return 0 if conn: # Drop the tabmany table, in case it exists drop = "DROP TABLE TABMANY" try: result = ibm_db.exec_immediate(conn, drop) except: pass #create table tabmany create = "CREATE TABLE TABMANY(id SMALLINT NOT NULL, name VARCHAR(32), bflag boolean)" ibm_db.exec_immediate(conn, create) #Populate the tabmany table with execute_many insert = "INSERT INTO TABMANY (id, name, bflag) VALUES(?, ?, ?)" params = ((10, 'Sanders', True), (20, 'Pernal', False), (30, 'Marenghi', True), (40, 'OBrien', False)) stmt_insert = ibm_db.prepare(conn, insert) ibm_db.execute_many(stmt_insert, params) #check the number of rows inserted row_count = ibm_db.num_rows(stmt_insert) print(row_count) # check the inserted columns select = "SELECT * FROM TABMANY" stmt_select = ibm_db.exec_immediate(conn, select) cols = ibm_db.fetch_tuple( stmt_select ) while( cols ): print("%s, %s, %s" % (cols[0], cols[1], cols[2])) cols = ibm_db.fetch_tuple( stmt_select ) #populate the tabmany table params = ((50, 'Hanes', False), (55, 'Mike', False, 'Extra'), (55.5, 'invalid row','not a bool'), (60, 'Quigley'), (70, None, None), [75, 'List', True] ) try: ibm_db.execute_many(stmt_insert, params) except Exception as inst: #check the no. of inserted rows row_count = ibm_db.num_rows(stmt_insert) #check the exception raised by execute_many API print(inst) print(row_count) ibm_db.close(conn) else: print(ibm_db.conn_errormsg())
def run_test_execute_many(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: # Drop the tabmany table, in case it exists drop = "DROP TABLE TABMANY" try: result = ibm_db.exec_immediate(conn, drop) except: pass #create table tabmany create = "CREATE TABLE TABMANY(id SMALLINT NOT NULL, name VARCHAR(32))" ibm_db.exec_immediate(conn, create) #Populate the tabmany table with execute_many insert = "INSERT INTO TABMANY (id, name) VALUES(?, ?)" params = ((10, 'Sanders'), (20, 'Pernal'), (30, 'Marenghi'), (40, 'OBrien')) stmt_insert = ibm_db.prepare(conn, insert) ibm_db.execute_many(stmt_insert, params) #check the number of rows inserted row_count = ibm_db.num_rows(stmt_insert) print(row_count) # chaeck the inserted columns select = "SELECT * FROM TABMANY" stmt_select = ibm_db.exec_immediate(conn, select) cols = ibm_db.fetch_tuple(stmt_select) while (cols): print("%s, %s" % (cols[0], cols[1])) cols = ibm_db.fetch_tuple(stmt_select) #populate the tabmany table params = ((50, 'Hanes'), (55, ), (55.5, 'invalid row'), (60, 'Quigley'), (70, None)) try: ibm_db.execute_many(stmt_insert, params) except Exception as inst: #check the no. of inserted rows row_count = ibm_db.num_rows(stmt_insert) #check the exception raised by execute_many API print(inst) print(row_count) ibm_db.close(conn) else: print(ibm_db.conn_errormsg())
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 create_resource(self, name, desc, units, amount): rname = name.current_item rdesc = desc.text runits = units.text ramount = amount.text valid_str = "" if rname == "": valid_str = "Select an Item" elif runits == "": valid_str = "Units is blank" elif ramount == "": valid_str = "Cost is blank" if valid_str != "": ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog(title='Alert !', text=valid_str, size_hint=(0.7, 1), buttons=[ok_button]) self.dialog.open() else: query1 = f'''INSERT INTO RESOURCES(CONTACT_ID, ACTIVITY_ID, NAME, DESC, UNITS, AMOUNT, DATE) VALUES ({globalvariables.var_userid}, {globalvariables.var_act_id}, '{rname}', '{rdesc}', {runits}, {ramount}, SYSDATE)''' # run direct SQL stmt = ibm_db.exec_immediate(connection.conn, query1) if ibm_db.num_rows(stmt) > 0: #self.ids['name'].text="" self.ids['desc'].text = "" self.ids['units'].text = "" self.ids['amount'].text = "" self.snackbar = Snackbar(text="Item Added!") self.snackbar.show()
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 make_payment(self,payamount): amount = payamount.text valid_str="" if amount == "": valid_str = "Amount is blank" elif int(amount) < 10: valid_str = "Minimum Amount to donate is 10" if valid_str != "": ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog(title='Alert !', text=valid_str, size_hint=(0.7,1), buttons=[ok_button]) self.dialog.open() else: query = f'''INSERT INTO TRANSACTION(ACTIVITY_ID, CONTACT_ID, TXN_DATE, AMOUNT) VALUES ({globalvariables.var_act_id}, {globalvariables.var_userid}, SYSDATE, {amount})''' # run direct SQL stmt = ibm_db.exec_immediate(connection.conn, query) if ibm_db.num_rows(stmt) > 0 : payment_mail(globalvariables.var_email, globalvariables.var_fname, amount, globalvariables.var_org_name, globalvariables.var_act_name) self.ids['payamount'].text = "" #Setting the values to NULL after sucessfull registration ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog(title='Payment Successfull!', text="Thank you for your donation!", size_hint=(0.7,1), buttons=[ok_button]) self.dialog.open() self.manager.transition.direction = 'right' self.manager.current = 'my_transaction_window' self.manager.get_screen('my_transaction_window').load_txn_page()
def update_caption_name(): name = str(request.form["name"]) keyword = str(request.form["keyword"]) if name == "" or keyword == "": result_dict = {} result_dict["RESULT"] = 0 return render_template('update_caption.html', result=result_dict) sql = "UPDATE names SET KEYWORDS = ? WHERE NAME=?" stmt = ibm_db.prepare(connect.connection, sql) ibm_db.bind_param(stmt, 1, keyword) ibm_db.bind_param(stmt, 2, name) result = ibm_db.execute(stmt) result = ibm_db.num_rows(stmt) sql = "SELECT * FROM names WHERE NAME = ?" stmt = ibm_db.prepare(connect.connection, sql) ibm_db.bind_param(stmt, 1, name) result = ibm_db.execute(stmt) result_dict = ibm_db.fetch_assoc(stmt) print(result_dict) result_dict["RESULT"] = result return render_template('update_caption.html', result=result_dict)
def run_test_101(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')") if result: cols = ibm_db.num_fields(result) print("col: %d" % cols) rows = ibm_db.num_rows(result) print("affected row: %d" % rows) result = ibm_db.exec_immediate(conn,"delete from t_string where a=123") if result: cols = ibm_db.num_fields(result) print("col: %d" % cols) rows = ibm_db.num_rows(result) print("affected row: %d" % rows) else: print("no connection");
def main(): conn = None stmt = None if not conf.has_option('passwd'): conf.conf.set(conf.section, 'passwd', unicode(getpass.getpass('Пароль: '), 'utf-8')) try: conn = ibm_db.connect('DATABASE=%s;HOSTNAME=%s;PORT=%d;PROTOCOL=%s;UID=%s;PWD=%s;' % (conf.get('database'), conf.get('hostname'), conf.getint('port'), conf.get('protocol'), conf.get('user'), conf.get('passwd')), '', '') stmt = ibm_db.exec_immediate(conn, unicode(args.request[0], 'utf-8')) try: result = ibm_db.fetch_tuple(stmt) except: rows = ibm_db.num_rows(stmt) if rows != -1: print u'Обработано строк %d' % rows else: print u'Команда выполнена' return if result: column_conv = [] head = u'' underline=u'' for i in xrange(len(result)): if i != 0: head += u'|' underline += u'+' name = ibm_db.field_name(stmt, i) size = ibm_db.field_display_size(stmt, i) if len(name) > size: size = len(name) if ibm_db.field_nullable(stmt, i) and len(u'NULL') > size: size = len(u'NULL') type_field = ibm_db.field_type(stmt, i) if type_field == 'float' or type_field == 'real' or type_field == 'decimal': column_conv.append({'size': size, 'format': u'{0:%d.%df}' % (size, (size - ibm_db.field_precision(stmt, i))), 'fn': convert_to_float}) elif type_field == 'int' or type_field == 'bigint': column_conv.append({'size': size, 'format': u'{0:%dd}' % size, 'fn': convert_to_int}) else: column_conv.append({'size': size, 'format': u'{0:%ds}' % size, 'fn': without_convert}) head += name.center(size) underline += u'-' * size print head print underline while( result ): print conv(result, column_conv) result = ibm_db.fetch_tuple(stmt) else: print u'Результата не возвращено' except Exception as e: print >> sys.stderr, e sys.exit(-1) finally: if stmt: ibm_db.free_result(stmt) if conn: ibm_db.close(conn)
def create_org(self, groupname, regnum, desc): ContentNavigationDrawer.populateNavDrawerValues(self) groupname = groupname.text regnum = regnum.text desc = desc.text valid_str = "" if groupname == "": valid_str = "Name is blank" elif regnum == "": valid_str = "Registration is blank" elif desc == "": valid_str = "Description is blank" if valid_str != "": ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog(title='Alert !', text=valid_str, size_hint=(0.7, 1), buttons=[ok_button]) self.dialog.open() else: status = "Y" query1 = f'''INSERT INTO ORG(NAME, REGISTRATION, DESC, OWNER_ID, STATUS) VALUES (UPPER('{groupname}'),UPPER('{regnum}'),'{desc}',{globalvariables.var_userid}, '{status}')''' # run direct SQL stmt = ibm_db.exec_immediate(connection.conn, query1) if ibm_db.num_rows(stmt) > 0: query2 = f'''SELECT ORG_ID FROM ORG WHERE REGISTRATION=UPPER('{regnum}') ''' stmt = ibm_db.exec_immediate(connection.conn, query2) orglist = ibm_db.fetch_both(stmt) orgid = "" while (orglist): orgid = orglist[0] query3 = f'''INSERT INTO CONTACT_ORG(ORG_ID, CONTACT_ID, MEMBER_FLAG, STATUS) VALUES ({orgid},{globalvariables.var_userid},'Y', '{status}')''' stmt1 = ibm_db.exec_immediate(connection.conn, query3) orglist = ibm_db.fetch_both(stmt) self.ids[ 'groupname'].text = "" #Setting the values to NULL after sucessfull registration self.ids['regnum'].text = "" self.ids['desc'].text = "" #To upload Org Logo if globalvariables.var_img_path != "": logo_path = globalvariables.var_img_path tgt_logo_path = "org_" + str(orgid) + ".png" upload_org_logo(logo_path, tgt_logo_path) globalvariables.var_img_path = "" ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog(title='Successfully Registered', text="Lets start helping!", size_hint=(0.7, 1), buttons=[ok_button]) self.dialog.open() self.manager.transition.direction = 'left' self.manager.current = 'home_window' self.manager.get_screen('home_window').load_home_page()
def insert_new_user(self, fname, lname, uname, pwd1, pwd2): fname = fname.text lname = lname.text email = uname.text pwd1 = pwd1.text pwd2 = pwd2.text regex = '^[a-z0-9]+[\._]?[a-z0-9]+[@]\w+[.]\w{2,3}$' valid_str = "" print(str(re.search(regex, email))) if fname == "": valid_str = "First Name is blank" elif lname == "": valid_str = "Last Name is blank" elif email == "": valid_str = "Email is blank" elif pwd1 == "": valid_str = "Password is blank" elif pwd2 == "": valid_str = "Confirm Password is blank" elif pwd1 != pwd2: valid_str = "Passwords not matching" elif re.search(regex, email) is None: valid_str = "Email is not valid" if valid_str != "": ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog(title='Alert !', text=valid_str, size_hint=(0.7, 1), buttons=[ok_button]) self.dialog.open() else: encrypted_pwd = encrypt_pwd(pwd1) query = f'''INSERT INTO CONTACT(FIRST_NAME, LAST_NAME, EMAIL, PASSCODE) VALUES (UPPER('{fname}'),UPPER('{lname}'),'{email}',{repr(encrypted_pwd)}) ''' # run direct SQL stmt = ibm_db.exec_immediate(connection.conn, query) if ibm_db.num_rows(stmt) > 0: send_registration_mail(email, fname, lname) #Call function to send mail self.ids[ 'fname'].text = "" #Setting the values to NULL after sucessfull registration self.ids['lname'].text = "" self.ids['uname'].text = "" self.ids['pwd1'].text = "" self.ids['pwd2'].text = "" ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog( title='Successfully Registered', text="Please use login screen to login.", size_hint=(0.7, 1), buttons=[ok_button]) self.dialog.open() self.manager.transition.direction = 'right' self.manager.current = 'login_window'
def run_test_execute_many(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: # Drop the tabmany table, in case it exists drop = "DROP TABLE TABMANY" try: result = ibm_db.exec_immediate(conn, drop) except: pass #create table tabmany create = "CREATE TABLE TABMANY(id SMALLINT NOT NULL, name VARCHAR(32))" ibm_db.exec_immediate(conn, create) #Populate the tabmany table with execute_many insert = "INSERT INTO TABMANY (id, name) VALUES(?, ?)" params = ((10, 'Sanders'), (20, 'Pernal'), (30, 'Marenghi'), (40, 'OBrien')) stmt_insert = ibm_db.prepare(conn, insert) ibm_db.execute_many(stmt_insert, params) #check the number of rows inserted row_count = ibm_db.num_rows(stmt_insert) print row_count # chaeck the inserted columns select = "SELECT * FROM TABMANY" stmt_select = ibm_db.exec_immediate(conn, select) cols = ibm_db.fetch_tuple( stmt_select ) while( cols ): print "%s, %s" % (cols[0], cols[1]) cols = ibm_db.fetch_tuple( stmt_select ) #populate the tabmany table params = ((50, 'Hanes'), (55, ), (55.5, 'invalid row'), (60, 'Quigley'), (70, None) ) try: ibm_db.execute_many(stmt_insert, params) except Exception, inst: #check the no. of inserted rows row_count = ibm_db.num_rows(stmt_insert) #check the exception raised by execute_many API print inst print row_count ibm_db.close(conn)
def run_test_011(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, "DELETE FROM animals WHERE weight > 10.0") print ("Number of affected rows: %d" % ibm_db.num_rows( stmt )) ibm_db.rollback(conn) ibm_db.close(conn) else: print ("Connection failed.")
def run_test_220(self): conn = ibm_db.pconnect(config.database, config.user, config.password) if conn: ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) stmt = ibm_db.exec_immediate(conn, "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0") print("Number of affected rows:", ibm_db.num_rows( stmt )) ibm_db.rollback(conn) ibm_db.close(conn) else: print("Connection failed.")
def execute_statements(conn: str, sql: str): """ Execute whatever SQL statements """ try: stmt = ibm_db.exec_immediate(conn, sql) print("Number of affected rows: ", ibm_db.num_rows(stmt)) except Exception: print(f"Failed execute query: {ibm_db.stmt_errormsg}") raise
def execute_sql(self, sql_com): self.isautapp() try: global db stmt = ibm_db.exec_immediate(db, sql_com) if stmt: self.num_row_aff = ibm_db.num_rows(stmt) return stmt except Exception as e: self.funlog().logger.error("database error") raise (e, None, sys.exc_info()[2])
def run_test_003(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) sql = 'UPDATE animals SET id = 9' res = ibm_db.exec_immediate(conn, sql) print("Number of affected rows: %d" % ibm_db.num_rows(res)) ibm_db.rollback(conn) ibm_db.close(conn) else: print("Connection failed.")
def run_test_003(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) sql = 'UPDATE animals SET id = 9' res = ibm_db.exec_immediate(conn, sql) print ("Number of affected rows: %d" % ibm_db.num_rows(res)) ibm_db.rollback(conn) ibm_db.close(conn) 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 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_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_016(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')") 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()) result = ibm_db.exec_immediate(conn,"delete from t_string where a=123") 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()) ibm_db.close(conn) else: print ("no connection:", ibm_db.conn_errormsg())
def forgot_password(self, username): email = username.text check_str = "" if email is "": check_str = "Please enter an Email" if check_str != "": ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog(title='Alert', text=check_str, size_hint=(0.7, 1), buttons=[ok_button]) self.dialog.open() else: query = f"""SELECT CONTACT_ID,FIRST_NAME,LAST_NAME FROM CONTACT WHERE EMAIL='{email}' """ # run direct SQL stmt = ibm_db.exec_immediate(connection.conn, query) userrow = ibm_db.fetch_tuple(stmt) if userrow != False: new_pwd = self.get_random_string() print(new_pwd) encrypted_pwd = encrypt_pwd(new_pwd) query = f'''UPDATE CONTACT SET PASSCODE={repr(encrypted_pwd)} WHERE EMAIL='{email}' ''' # run direct SQL stmt = ibm_db.exec_immediate(connection.conn, query) if ibm_db.num_rows(stmt) > 0: print("Password changed") forgot_password_mail(email, userrow[1], userrow[2], new_pwd) #Call function to send mail self.ids[ 'username'].text = "" #Setting the values to NULL after sucessfull registration self.ids['pwd'].text = "" ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog( title='Password Changed', text= "Mail sent with new password. Please change you password after login", size_hint=(0.7, 1), buttons=[ok_button]) self.dialog.open() else: ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog(title='Alert', text="Email is not registered", size_hint=(0.7, 1), buttons=[ok_button]) self.dialog.open()
def run_test_015(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate(conn,"insert into t_string values(123,1.222333,'one to one')") if result: cols = ibm_db.num_fields(result) # NOTE: Removed '\n' from the following and a few more prints here (refer to ruby test_015.rb) print "col:", cols rows = ibm_db.num_rows(result) print "affected row:", rows else: print ibm_db.stmt_errormsg() result = ibm_db.exec_immediate(conn,"delete from t_string where a=123") 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() ibm_db.close(conn) else: print "no connection:", ibm_db.conn_errormsg()
def consultaDB2(sql, conn): try: stmt = ibm_db.exec_immediate(conn, sql) except: print('Code SQL with problem!\nError Code: {0}'.format( ibm_db.stmt_error())) sys.exit() #print('Number of Fields: {0}'.format(ibm_db.num_fields(stmt))) print('Number of rows deleted, inserted, or updated: {0}'.format( ibm_db.num_rows(stmt))) return stmt
def run_test_016(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: result = ibm_db.exec_immediate( conn, "insert into t_string values(123,1.222333,'one to one')") 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() result = ibm_db.exec_immediate(conn, "delete from t_string where a=123") 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() ibm_db.close(conn) else: print "no connection:", ibm_db.conn_errormsg()
def update_keyword_name(): name = str(request.form["name"]) keyword = str(request.form["keyword"]) sql = "UPDATE names SET KEYWORDS = ? WHERE NAME=?" stmt = ibm_db.prepare(connect.connection, sql) ibm_db.bind_param(stmt, 1, keyword) ibm_db.bind_param(stmt, 2, name) result = ibm_db.execute(stmt) result = ibm_db.num_rows(stmt) return render_template('Update_Keyword.html', result=result)
def run_test_221(self): pconn = list(range(100)) for i in range(100): pconn[i] = ibm_db.pconnect(config.database, config.user, config.password) if pconn[33]: conn = pconn[22] ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF) stmt = ibm_db.exec_immediate(pconn[33], "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0") print("Number of affected rows:", ibm_db.num_rows( stmt )) ibm_db.rollback(conn) ibm_db.close(pconn[33]) else: print("Connection failed.")
def join_group(self): query = f'''INSERT INTO CONTACT_ORG(ORG_ID, CONTACT_ID, STATUS, MEMBER_FLAG) VALUES ({globalvariables.var_org_id},{globalvariables.var_userid}, 'N', 'P')''' stmt = ibm_db.exec_immediate(connection.conn, query) if ibm_db.num_rows(stmt) > 0: ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog( title='Group Request Submitted', text= "Your group would be available in My Groups when a moderator approves", size_hint=(0.7, 1), buttons=[ok_button]) self.dialog.open() self.manager.transition.direction = 'left' self.manager.current = 'home_window' self.manager.get_screen('home_window').load_home_page()
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]) if sys.version_info >= (3, ): print(type(data[0]) is int) print(type(data[1]) is int) print(type(data[2]) is int) else: print(type(data[0]) is long) print(type(data[1]) is long) print(type(data[2]) is long) data = ibm_db.fetch_both(stmt) ibm_db.close(conn)
def saveInsight(self, snippet, insightJson): insight = json.loads(insightJson) print(type(insight)) bigFive = insight['tree']['children'][0]['children'][0]['children'][0]['name'] bigFivePerc = insight['tree']['children'][0]['children'][0]['children'][0]['percentage'] snippet = snippet.replace(",", "") snippet = snippet.replace(".", "") if self.sqlConn: result = ibm_db.exec_immediate(self.sqlConn,"insert into insights (TEXT_SNIPPET, BIG_FIVE, BIG_FIVE_PERCENTAGE) values('"+str(snippet)+"' , '"+str(bigFive)+"' , '"+str(bigFivePerc)+"' )") if result: rows = ibm_db.num_rows(result) print "affected row:", rows return rows else: print("ERROR: Connection not found")
def execute(self, conn): try: stmt = ibm_db.exec_immediate(conn, self.sql) if ibm_db.num_fields(stmt) > 0: result = [] dictionary = ibm_db.fetch_assoc(stmt) print("here") while dictionary != False: result.append(dictionary) dictionary = ibm_db.fetch_assoc(stmt) return {'result': result, 'message': '1 Query executed.'}, 200 else: return {'result': [], 'message': str(ibm_db.num_rows(stmt)) + 'rows affected.'}, 200 except Exception as e: app.logger.error(e) msg = ibm_db.stmt_errormsg() if not msg: msg = str(e) return {'result': [], 'message': 'Query failed: ' + msg}, 500
def create_activity(self, actname, loc, drop_item, target): actname = actname.text loc = loc.text disaster = drop_item.current_item target = target.text valid_str = "" if actname == "": valid_str = "Name is blank" elif loc == "": valid_str = "Location is blank" elif target == "": valid_str = "Target Amount is blank" if valid_str != "": ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog(title='Alert !', text=valid_str, size_hint=(0.7, 1), buttons=[ok_button]) self.dialog.open() else: status = "Y" query1 = f'''INSERT INTO ACTIVITY(ORG_ID, NAME, LOCATION, DISASTER, TARGET_AMT, STATUS) VALUES ({globalvariables.var_org_id},UPPER('{actname}'),'{loc}','{disaster}',{target}, '{status}')''' # run direct SQL stmt = ibm_db.exec_immediate(connection.conn, query1) if ibm_db.num_rows(stmt) > 0: self.ids[ 'actname'].text = "" #Setting the values to NULL after sucessfull registration self.ids['loc'].text = "" self.ids['drop_item'].text = "" self.ids['target'].text = "" self.ids['planned_amount'].text = "" ok_button = MDFlatButton(text='OK', on_release=self.dialog_close) self.dialog = MDDialog(title='Activity Created', text="Lets start helping!", size_hint=(0.7, 1), buttons=[ok_button]) self.dialog.open() self.manager.transition.direction = 'left' self.manager.current = 'activity_window' self.manager.get_screen('activity_window').load_activity_page( globalvariables.var_org_name)
def modify_name_room_number_name(): name = str(request.form["name"]) room = str(request.form["room"]) if name == "" or room == "": result = 0 return render_template('modify_name_room_number.html', result=result) sql = "UPDATE names SET NAME = ? WHERE ROOM=?" stmt = ibm_db.prepare(connect.connection, sql) ibm_db.bind_param(stmt, 1, name) ibm_db.bind_param(stmt, 2, room) result = ibm_db.execute(stmt) result = ibm_db.num_rows(stmt) return render_template('modify_name_room_number.html', result=result)
args_list[2]) statement = ibm_db.exec_immediate(conn, full_sql) break elif len(args_list) == 2: for row_item in mon_list: if args_list[0] == row_item[0] and args_list[1] == row_item[4]: sql = """update MONITOR.DB2MON_MONITOR set DB_MON_VALUE = '%s', DB_MON_TIME = current timestamp where DB_NODE_ID = '%s' and DB_MON_SP_NAME = '%s'""" full_sql = sql % (actual_value, args_list[0], args_list[1]) statement = ibm_db.exec_immediate(conn, full_sql) break # store the status for debugging if statement: num_of_updates = ibm_db.num_rows(statement) if num_of_updates > 0: update_status = 'success' else: update_status = 'failed' ibm_db.close(conn) return def CheckValidArgs(mon_list, args_list): """Verify the arguments passed are something valid to check. Args: mon_list: A list of tuples containing monitoring values and thresholds. args_list: A list of arugments passed to nagios.
def run_test_execute_many(self): conn = ibm_db.connect(config.database, config.user, config.password) if conn: # Drop the tabmany table, in case it exists drop = "DROP TABLE TABMANY" try: result = ibm_db.exec_immediate(conn, drop) except: pass #create table tabmany create = "CREATE TABLE TABMANY(id SMALLINT NOT NULL, name VARCHAR(32))" ibm_db.exec_immediate(conn, create) #Populate the tabmany table with execute_many insert = "INSERT INTO TABMANY (id, name) VALUES(?, ?)" params = ((10, 'Sanders'), (20, 'Pernal'), (30, 'Marenghi'), (40, 'OBrien')) stmt_insert = ibm_db.prepare(conn, insert) ibm_db.execute_many(stmt_insert, params) #check the number of rows inserted row_count = ibm_db.num_rows(stmt_insert) print(row_count) # chaeck the inserted columns select = "SELECT * FROM TABMANY" stmt_select = ibm_db.exec_immediate(conn, select) cols = ibm_db.fetch_tuple( stmt_select ) while( cols ): print("%s, %s" % (cols[0], cols[1])) cols = ibm_db.fetch_tuple( stmt_select ) #populate the tabmany table params = ((50, 'Hanes'), (55, ), (55.5, 'invalid row'), (60, 'Quigley'), (70, None) ) try: ibm_db.execute_many(stmt_insert, params) except Exception as inst: #check the no. of inserted rows row_count = ibm_db.num_rows(stmt_insert) #check the exception raised by execute_many API print(inst) print(row_count) ibm_db.close(conn) else: print(ibm_db.conn_errormsg()) #__END__ #__LUW_EXPECTED__ #4 #10, Sanders #20, Pernal #30, Marenghi #40, OBrien #Error 1: Value parameter tuple: 2 has less no of param #Error 2: Value parameters array 3 is not homogeneous with privious parameters array #3 #__ZOS_EXPECTED__ #4 #10, Sanders #20, Pernal #30, Marenghi #40, OBrien #Error 1: Value parameter tuple: 2 has less no of param #Error 2: Value parameters array 3 is not homogeneous with privious parameters array #3 #__IDS_EXPECTED__ #4 #10, Sanders #20, Pernal #30, Marenghi #40, OBrien #Error 1: Value parameter tuple: 2 has less no of param #Error 2: Value parameters array 3 is not homogeneous with privious parameters array #3