def run_test_312(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) query = "INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)" if conn: stmt = IfxPy.prepare(conn, query) params = ['STG', 'Systems & Technology', '123456', 'RSF', 'Fiji'] print("Binding parameters") for i, p in enumerate(params, 1): IfxPy.bind_param(stmt, i, Wrapper(p)) if IfxPy.execute(stmt): print("Executing statement") IfxPy.execute(stmt) # force the cache to be unbound for i, p in enumerate(params, 1): IfxPy.bind_param(stmt, i, p) IfxPy.rollback(conn) else: print("Connection failed.")
def run_test_020(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print(rows) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) ac = IfxPy.autocommit(conn) if ac != 0: print("Cannot set IfxPy.SQL_AUTOCOMMIT_OFF\nCannot run test") #continue IfxPy.exec_immediate(conn, "DELETE FROM animals") stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print(rows) IfxPy.rollback(conn) stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print(rows) IfxPy.close(conn) else: print("Connection failed.")
def run_test_022(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print rows IfxPy.autocommit(conn, 0) ac = IfxPy.autocommit(conn) if ac != 0: print "Cannot set IfxPy.AUTOCOMMIT_OFF\nCannot run test" #continue IfxPy.exec_immediate( conn, "INSERT INTO animals values (7,'bug','Brain Bug',10000.1)") stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print rows IfxPy.rollback(conn) stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print rows IfxPy.close(conn) else: print "Connection failed."
def run_test_040(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) # Drop the test table, in case it exists drop = 'DROP TABLE animals' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the test table create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))' result = IfxPy.exec_immediate(conn, create) insert = "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)" IfxPy.exec_immediate(conn, insert) stmt = IfxPy.exec_immediate(conn, "select * from animals") onerow = IfxPy.fetch_tuple(stmt) for element in onerow: print(element) IfxPy.rollback(conn)
def run_test_143(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) insert1 = "INSERT INTO animals (id, breed, name, weight) VALUES (NULL, 'ghost', NULL, ?)" select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL' if conn: stmt = IfxPy.prepare(conn, insert1) animal = None IfxPy.bind_param(stmt, 1, animal) if IfxPy.execute(stmt): stmt = IfxPy.exec_immediate(conn, select) row = IfxPy.fetch_tuple(stmt) while ( row ): #row.each { |child| print child } for i in row: print(i) row = IfxPy.fetch_tuple(stmt) IfxPy.rollback(conn) else: print("Connection failed.")
def run_test_018(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_ON) if conn: stmt = IfxPy.prepare(conn, "SELECT * from animals WHERE weight < 10.0" ) IfxPy.set_option(stmt, {IfxPy.SQL_ATTR_ROWCOUNT_PREFETCH : IfxPy.SQL_ROWCOUNT_PREFETCH_ON}, 2) result = IfxPy.execute(stmt) if result: rows = IfxPy.num_rows(stmt) print("affected row:", rows) IfxPy.free_result(stmt) else: print(IfxPy.stmt_errormsg()) IfxPy.set_option(stmt, {IfxPy.SQL_ATTR_ROWCOUNT_PREFETCH : IfxPy.SQL_ROWCOUNT_PREFETCH_OFF}, 2) result = IfxPy.execute(stmt) if result: rows = IfxPy.num_rows(stmt) print("affected row:", rows) IfxPy.free_result(stmt) else: print(IfxPy.stmt_errormsg()) IfxPy.set_option(stmt, {IfxPy.SQL_ATTR_ROWCOUNT_PREFETCH : IfxPy.SQL_ROWCOUNT_PREFETCH_ON}, 2) result = IfxPy.execute(stmt) if result: rows = IfxPy.num_rows(stmt) print("affected row:", rows) else: print(IfxPy.stmt_errormsg()) IfxPy.close(conn) else: print("no connection:", IfxPy.conn_errormsg())
def run_test_6561(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) stmt = IfxPy.exec_immediate( conn, "INSERT INTO animals (id, breed, name, weight) VALUES (null, null, null, null)" ) statement = "SELECT count(id) FROM animals" result = IfxPy.exec_immediate(conn, statement) if ((not result) and IfxPy.stmt_error()): print "ERROR: %s" % (IfxPy.stmt_errormsg(), ) row = IfxPy.fetch_tuple(result) while (row): for i in row: print i row = IfxPy.fetch_tuple(result) IfxPy.rollback(conn) IfxPy.close(conn) else: print "Connection failed."
def run_test_147(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) stmt = IfxPy.prepare( conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)") id = "\"999\"" breed = None name = 'PythonDS' try: IfxPy.bind_param(stmt, 1, id) IfxPy.bind_param(stmt, 2, breed) IfxPy.bind_param(stmt, 3, name) error = IfxPy.execute(stmt) print "Should not make it this far" except: excp = sys.exc_info() # slot 1 contains error message print excp[1] else: print "Connection failed."
def run_test_311(self): # Make a connection conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_ON ) # Drop the tab_num_literals table, in case it exists drop = 'DROP TABLE tab_num_literals' result = '' try: result = IfxPy.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 = IfxPy.exec_immediate(conn, create) insert = "INSERT INTO tab_num_literals values ('11.22', '33.44', '55.66')" res = IfxPy.exec_immediate(conn, insert) print "Number of inserted rows:", IfxPy.num_rows(res) stmt = IfxPy.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col1 = '11'") IfxPy.execute(stmt) data = IfxPy.fetch_both(stmt) while ( data ): print data[0] print data[1] print data[2] data = IfxPy.fetch_both(stmt) sql = "UPDATE tab_num_literals SET col1 = 77 WHERE col2 = 33.44" res = IfxPy.exec_immediate(conn, sql) print "Number of updated rows:", IfxPy.num_rows(res) stmt = IfxPy.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col2 > '33'") IfxPy.execute(stmt) data = IfxPy.fetch_both(stmt) while ( data ): print data[0] print data[1] print data[2] data = IfxPy.fetch_both(stmt) sql = "DELETE FROM tab_num_literals WHERE col1 > '10.0'" res = IfxPy.exec_immediate(conn, sql) print "Number of deleted rows:", IfxPy.num_rows(res) stmt = IfxPy.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col3 < '56'") IfxPy.execute(stmt) data = IfxPy.fetch_both(stmt) while ( data ): print data[0] print data[1] print data[2] data = IfxPy.fetch_both(stmt) IfxPy.rollback(conn) IfxPy.close(conn)
def run_test_052(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) IfxPy.autocommit(conn, 0) ac = IfxPy.autocommit(conn) print(ac)
def run_test_003(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) sql = 'UPDATE animals SET id = 9' res = IfxPy.exec_immediate(conn, sql) print "Number of affected rows: %d" % IfxPy.num_rows(res) IfxPy.rollback(conn) IfxPy.close(conn) else: print "Connection failed."
def run_test_011(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) stmt = IfxPy.exec_immediate( conn, "DELETE FROM animals WHERE weight > 10.0") print("Number of affected rows: %d" % IfxPy.num_rows(stmt)) IfxPy.rollback(conn) IfxPy.close(conn) else: print("Connection failed.")
def run_test_010(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) stmt = IfxPy.exec_immediate( conn, "UPDATE animals SET name = 'flyweight' WHERE weight < 10.0") print "Number of affected rows: %d" % IfxPy.num_rows(stmt) IfxPy.rollback(conn) IfxPy.close(conn) else: print "Connection failed."
def run_test_133(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if (not conn): print "Connection failed." return 0 IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) print "Starting test ..." res = '' sql = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)" try: stmt = IfxPy.prepare(conn, sql) res = IfxPy.execute( stmt, (128, 'hacker of human and technological nature', 'Wez the ruler of all things PECL', 88.3)) stmt = IfxPy.prepare( conn, "SELECT breed, name FROM animals WHERE id = ?") res = IfxPy.execute(stmt, (128, )) row = IfxPy.fetch_assoc(stmt) for i in row: print i IfxPy.rollback(conn) print "Done" except: print "SQLSTATE: %s" % IfxPy.stmt_error(stmt) print "Message: %s" % IfxPy.stmt_errormsg(stmt) try: stmt = IfxPy.prepare( conn, "SELECT breed, name FROM animals WHERE id = ?") res = IfxPy.execute(stmt, (128, )) row = IfxPy.fetch_assoc(stmt) if (row): for i in row: print i print res print "SQLSTATE: %s" % IfxPy.stmt_error(stmt) print "Message: %s" % IfxPy.stmt_errormsg(stmt) except: print "An Exception is not expected" print "SQLSTATE: %s" % IfxPy.stmt_error(stmt) print "Message: %s" % IfxPy.stmt_errormsg(stmt) IfxPy.rollback(conn) print "Done"
def run_test_111(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)" IfxPy.exec_immediate(conn, insert) stmt = IfxPy.exec_immediate( conn, "SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed" ) if (server.DBMS_NAME[0:3] == 'Inf'): num1 = IfxPy.field_num(stmt, "id") num2 = IfxPy.field_num(stmt, "breed") num3 = IfxPy.field_num(stmt, "number") num4 = IfxPy.field_num(stmt, "NUMBER") num5 = IfxPy.field_num(stmt, "bREED") num6 = IfxPy.field_num(stmt, 8) num7 = IfxPy.field_num(stmt, 1) num8 = IfxPy.field_num(stmt, "WEIGHT") else: num1 = IfxPy.field_num(stmt, "ID") num2 = IfxPy.field_num(stmt, "BREED") num3 = IfxPy.field_num(stmt, "NUMBER") num4 = IfxPy.field_num(stmt, "number") num5 = IfxPy.field_num(stmt, "Breed") num6 = IfxPy.field_num(stmt, 8) num7 = IfxPy.field_num(stmt, 1) num8 = IfxPy.field_num(stmt, "weight") print "%s" % num1 print "int(%d)" % num2 print "int(%d)" % num3 print "%s" % num4 print "%s" % num5 print "%s" % num6 print "int(%d)" % num7 print "%s" % num8 IfxPy.rollback(conn) else: print "Connection failed."
def run_test_021(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print rows IfxPy.autocommit(conn, 0) ac = IfxPy.autocommit(conn) if ac != 0: print "Cannot set IfxPy.AUTOCOMMIT_OFF\nCannot run test" #continue IfxPy.exec_immediate(conn, "DELETE FROM animals") stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print rows IfxPy.commit(conn) stmt = IfxPy.exec_immediate(conn, "SELECT count(*) FROM animals") res = IfxPy.fetch_tuple(stmt) rows = res[0] print rows # Populate the animal table animals = ((0, 'cat', 'Pook', 3.2), (1, 'dog', 'Peaches', 12.3), (2, 'horse', 'Smarty', 350.0), (3, 'gold fish', 'Bubbles', 0.1), (4, 'budgerigar', 'Gizmo', 0.2), (5, 'goat', 'Rickety Ride', 9.7), (6, 'llama', 'Sweater', 150)) insert = 'INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)' stmt = IfxPy.prepare(conn, insert) if stmt: for animal in animals: result = IfxPy.execute(stmt, animal) IfxPy.commit(conn) IfxPy.close(conn) else: print "Connection failed."
def run_test_051(self): options = {IfxPy.SQL_ATTR_AUTOCOMMIT: IfxPy.SQL_AUTOCOMMIT_OFF} conn = IfxPy.connect(config.ConnStr, config.user, config.password, options) ac = IfxPy.autocommit(conn) print ac
def run_test_121(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) insert = "INSERT INTO animals values (7, 'cat', 'Benji', 5.1)" IfxPy.exec_immediate(conn, insert) stmt = IfxPy.exec_immediate( conn, "SELECT breed, COUNT(breed) AS number FROM animals GROUP BY breed ORDER BY breed" ) name1 = IfxPy.field_name(stmt, 0) name2 = IfxPy.field_name(stmt, 1) name3 = IfxPy.field_name(stmt, 2) name4 = IfxPy.field_name(stmt, 3) if (server.DBMS_NAME[0:3] == 'Inf'): name5 = IfxPy.field_name(stmt, "breed") name6 = IfxPy.field_name(stmt, 7) name7 = IfxPy.field_name(stmt, '"nUMBER"') name8 = IfxPy.field_name(stmt, "number") else: name5 = IfxPy.field_name(stmt, "BREED") name6 = IfxPy.field_name(stmt, 7) name7 = IfxPy.field_name(stmt, '"Number"') name8 = IfxPy.field_name(stmt, "NUMBER") print("string(%d) \"%s\"" % (len(name1), name1)) print("string(%d) \"%s\"" % (len(name2), name2)) print("%s" % name3) print("%s" % name4) print("string(%d) \"%s\"" % (len(name5), name5)) print("%s" % name6) print("%s" % name7) print("string(%d) \"%s\"" % (len(name8), name8)) IfxPy.rollback(conn) else: print("Connection failed.")
def run_test_145(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) stmt = IfxPy.prepare( conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)") id = 999 breed = None name = 'PythonDS' IfxPy.bind_param(stmt, 1, id) IfxPy.bind_param(stmt, 2, breed) IfxPy.bind_param(stmt, 3, name) # After this statement, we expect that the BREED column will contain # an SQL NULL value, while the NAME column contains an empty string IfxPy.execute(stmt) # After this statement, we expect that the BREED column will contain # an SQL NULL value, while the NAME column contains an empty string. # Use the dynamically bound parameters to ensure that the code paths # for both IfxPy.bind_param and IfxPy.execute treat Python Nones and empty # strings the right way. IfxPy.execute(stmt, (1000, None, 'PythonDS')) result = IfxPy.exec_immediate( conn, "SELECT id, breed, name FROM animals WHERE breed IS NULL") row = IfxPy.fetch_tuple(result) while (row): for i in row: print(i) row = IfxPy.fetch_tuple(result) IfxPy.rollback(conn) else: print("Connection failed.")
def run_test_100(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) stmt = IfxPy.exec_immediate( conn, "SELECT * FROM animals ORDER BY breed") fields1 = IfxPy.num_fields(stmt) print("int(%d)" % fields1) stmt = IfxPy.exec_immediate( conn, "SELECT name, breed FROM animals ORDER BY breed") fields2 = IfxPy.num_fields(stmt) print("int(%d)" % fields2) stmt = IfxPy.exec_immediate(conn, "DELETE FROM animals") fields3 = IfxPy.num_fields(stmt) print("int(%d)" % fields3) stmt = IfxPy.exec_immediate( conn, "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)") fields4 = IfxPy.num_fields(stmt) print("int(%d)" % fields4) stmt = IfxPy.exec_immediate( conn, "SELECT name, breed, 'TEST' FROM animals") fields5 = IfxPy.num_fields(stmt) print("int(%d)" % fields5) IfxPy.rollback(conn) else: print("Connection failed.")
def run_test_049(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_OFF) insert = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)" select = 'SELECT id, breed, name, weight FROM animals WHERE weight IS NULL' if conn: stmt = IfxPy.prepare(conn, insert) if IfxPy.execute(stmt, (None, 'ghost', None, None)): stmt = IfxPy.exec_immediate(conn, select) row = IfxPy.fetch_tuple(stmt) while (row): #row.each { |child| puts child } for child in row: print(child) row = IfxPy.fetch_tuple(stmt) IfxPy.rollback(conn) else: print("Connection failed.")
import numpy as np import pandas as pd import matplotlib.pyplot as plt # %matplotlib inline import IfxPy ######################################## # data = pd.read_csv('./train.csv') table_name = "train1" UserInform = 25000 ConStr = "SERVER=ids0;DATABASE=db1;HOST=127.0.0.1;SERVICE=9088;UID=informix;PWD=xxxxx;" try: # netstat -a | findstr 9088 conn = IfxPy.connect(ConStr, "", "") IfxPy.autocommit(conn, IfxPy.SQL_AUTOCOMMIT_ON) except Exception as e: print('ERROR: Connect failed') print(e) quit() sql = "SELECT * FROM {}".format(table_name) print(sql) stmt = IfxPy.exec_immediate(conn, sql) tu = IfxPy.fetch_tuple(stmt) ls = [] rc = 0 while tu != False: rc = rc + 1 ls.append(tu)