def run_test_006(self): options1 = {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN} options2 = {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_FORWARD_ONLY} conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: serverinfo = IfxPy.server_info(conn) if (serverinfo.DBMS_NAME[0:3] == 'Inf'): options1 = options2 stmt = IfxPy.prepare( conn, "SELECT name FROM animals WHERE weight < 10.0", options2) IfxPy.execute(stmt) data = IfxPy.fetch_both(stmt) while (data): print(data[0]) data = IfxPy.fetch_both(stmt) print("") stmt = IfxPy.prepare( conn, "SELECT name FROM animals WHERE weight < 10.0", options1) IfxPy.execute(stmt) data = IfxPy.fetch_both(stmt) while (data): print(data[0]) data = IfxPy.fetch_both(stmt) IfxPy.close(conn) else: print("Connection failed.")
def run_test_014(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) serverinfo = IfxPy.server_info(conn) query = 'SELECT * FROM animals ORDER BY name' if (serverinfo.DBMS_NAME[0:3] != 'Inf'): stmt = IfxPy.prepare( conn, query, {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN}) else: stmt = IfxPy.prepare(conn, query) IfxPy.execute(stmt) data = IfxPy.fetch_both(stmt) while (data): print "%s : %s : %s : %s\n" % (data[0], data[1], data[2], data[3]) data = IfxPy.fetch_both(stmt) try: stmt = IfxPy.prepare( conn, query, {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN}) IfxPy.execute(stmt) rc = IfxPy.fetch_row(stmt, -1) print "Fetch row -1: %s" % str(rc) except: print "Requested row number must be a positive value" IfxPy.close(conn)
def run_test_124(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: result = IfxPy.exec_immediate( conn, "select * from staff, employee, org where employee.lastname in ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT') order by org.location,employee.lastname,staff.id" ) cols = IfxPy.num_fields(result) j = 0 row = IfxPy.fetch_both(result) while (row): for i in range(0, cols): field = IfxPy.field_name(result, i) value = row[IfxPy.field_name(result, i)] if (value == None): value = '' print("%s:%s" % (field, value)) print("---------") j += 1 if (j == 10): break row = IfxPy.fetch_both(result) IfxPy.close(conn) print("done") else: print(IfxPy.conn_errormsg())
def run_test_161(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) if (server.DBMS_NAME[0:3] == 'Inf'): op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER} IfxPy.set_option(conn, op, 1) result = IfxPy.exec_immediate( conn, "select * from emp_act order by projno desc") row = IfxPy.fetch_both(result) count = 1 while (row): print( "Record", count, ": %6s %-6s %3d %9s %10s %10s %6s " % (row[0], row[1], row[2], row['EMPTIME'], row['EMSTDATE'], row['EMENDATE'], row[0])) result2 = IfxPy.exec_immediate( conn, "select * from employee where employee.empno='" + row['EMPNO'] + "'") row2 = IfxPy.fetch_both(result2) if row2: print(">>%s,%s,%s,%s,%s,%s,%s" % (row2['EMPNO'], row2['FIRSTNME'], row2['MIDINIT'], row2[3], row2[3], row2[5], row2[6])) count = count + 1 if (count > 10): break row = IfxPy.fetch_both(result)
def run_test_061(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = IfxPy.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))' try: result = IfxPy.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))' try: result = IfxPy.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))' try: result = IfxPy.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))' try: result = IfxPy.exec_immediate(conn, create) except: pass if conn: server = IfxPy.server_info(conn) op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER} IfxPy.set_option(conn, op, 1) result = IfxPy.tables(conn, None, 't') i = 0 row = IfxPy.fetch_both(result) while (row): str = row['TABLE_SCHEM'] + row['TABLE_NAME'] + row['TABLE_TYPE'] if (i < 4): print(str) i = i + 1 row = IfxPy.fetch_both(result) IfxPy.exec_immediate(conn, 'DROP TABLE t.t1') IfxPy.exec_immediate(conn, 'DROP TABLE t.t2') IfxPy.exec_immediate(conn, 'DROP TABLE t.t3') IfxPy.exec_immediate(conn, 'DROP TABLE t.t4') print("done!") else: print("no connection: %s" % IfxPy.conn_errormsg())
def run_test_062(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = IfxPy.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))' try: result = IfxPy.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))' try: result = IfxPy.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))' try: result = IfxPy.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))' try: result = IfxPy.exec_immediate(conn, create) except: pass if conn: schema = 't' result = IfxPy.tables(conn, None, schema) i = 0 row = IfxPy.fetch_both(result) while (row): str = row[1] + "/" + row[2] + "/" + row[3] if (i < 4): print(str) i = i + 1 row = IfxPy.fetch_both(result) IfxPy.exec_immediate(conn, 'DROP TABLE t.t1') IfxPy.exec_immediate(conn, 'DROP TABLE t.t2') IfxPy.exec_immediate(conn, 'DROP TABLE t.t3') IfxPy.exec_immediate(conn, 'DROP TABLE t.t4') print("done!") else: print("no connection: #{IfxPy.conn_errormsg}")
def DBConnect(sql): constr = "SERVER=pronto_net;DATABASE=sysmaster;HOST=pronto;SERVICE=9088;UID=informix;PWD=pronto1$;" conn = IfxPy.connect(constr, "", "") stmt = IfxPy.exec_immediate(conn, sql) dictionary = IfxPy.fetch_both(stmt) while dictionary != False: logging.info("touch " + dictionary[0]) logging.info("chown informix:informix" + dictionary[0]) logging.info("chmod 660 " + dictionary[0]) dictionary = IfxPy.fetch_both(stmt) IfxPy.close(conn)
def run_test_264(self): # Make a connection conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: server = IfxPy.server_info(conn) if (server.DBMS_NAME[0:3] == 'Inf'): op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER} IfxPy.set_option(conn, op, 1) # Drop the tab_bigint table, in case it exists drop = 'DROP TABLE tab_bigint' result = '' try: result = IfxPy.exec_immediate(conn, drop) except: pass # Create the tab_bigint table if (server.DBMS_NAME[0:3] == 'Inf'): 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 = IfxPy.exec_immediate(conn, create) insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)" res = IfxPy.exec_immediate(conn, insert) print "Number of inserted rows:", IfxPy.num_rows(res) stmt = IfxPy.prepare(conn, "SELECT * FROM tab_bigint") IfxPy.execute(stmt) data = IfxPy.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 = IfxPy.fetch_both(stmt) # test IfxPy.result for fetch of bigint stmt1 = IfxPy.prepare(conn, "SELECT col2 FROM tab_bigint") IfxPy.execute(stmt1) IfxPy.fetch_row(stmt1, 0) if (server.DBMS_NAME[0:3] != 'Inf'): row1 = IfxPy.result(stmt1, 'COL2') else: row1 = IfxPy.result(stmt1, 'col2') print row1 IfxPy.close(conn)
def run_test_060(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) create = 'CREATE SCHEMA AUTHORIZATION t' try: result = IfxPy.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t1( c1 INTEGER, c2 VARCHAR(40))' try: result = IfxPy.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t2( c1 INTEGER, c2 VARCHAR(40))' try: result = IfxPy.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t3( c1 INTEGER, c2 VARCHAR(40))' try: result = IfxPy.exec_immediate(conn, create) except: pass create = 'CREATE TABLE t.t4( c1 INTEGER, c2 VARCHAR(40))' try: result = IfxPy.exec_immediate(conn, create) except: pass if conn: result = IfxPy.tables(conn, None, 't') i = 0 row = IfxPy.fetch_both(result) while (row): if (i < 4): print("/%s/%s" % (row[1], row[2])) i = i + 1 row = IfxPy.fetch_both(result) IfxPy.exec_immediate(conn, 'DROP TABLE t.t1') IfxPy.exec_immediate(conn, 'DROP TABLE t.t2') IfxPy.exec_immediate(conn, 'DROP TABLE t.t3') IfxPy.exec_immediate(conn, 'DROP TABLE t.t4') print("done!") else: print("no connection: #{IfxPy.conn_errormsg}")
def run_test_013(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: serverinfo = IfxPy.server_info( conn ) if (serverinfo.DBMS_NAME[0:3] != 'Inf'): stmt = IfxPy.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0", {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN}) else: stmt = IfxPy.prepare(conn, "SELECT name FROM animals WHERE weight < 10.0") IfxPy.execute(stmt) data = IfxPy.fetch_both( stmt ) while (data): print data[0] data = IfxPy.fetch_both( stmt ) IfxPy.close(conn) else: print "Connection failed."
def run_test_191(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info( conn ) if conn: result = IfxPy.columns(conn,None,config.user,"emp_photo"); i = 0 row = IfxPy.fetch_both(result) while ( row ): if ( (row['COLUMN_NAME'] != 'emp_rowid') and (i < 3) ): print "%s,%s,%s,%s\n" % (row['TABLE_SCHEM'], row['TABLE_NAME'], row['COLUMN_NAME'], row['IS_NULLABLE']) i = i + 1 row = IfxPy.fetch_both(result) print "done!" else: print "no connection: ", IfxPy.conn_errormsg()
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_160(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) if (server.DBMS_NAME[0:3] == 'Inf'): op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER} IfxPy.set_option(conn, op, 1) result = IfxPy.exec_immediate(conn, "select * from emp_act") row = IfxPy.fetch_both(result) while (row): #printf("%6s ",row[0]) #printf("%-6s ",row[1]) #printf("%3d ",row[2]) #printf("%9s ",row['EMPTIME']) #printf("%10s ", row['EMSTDATE']) #printf("%10s ", row['EMENDATE']) #printf("%6s ", row[0]) #puts "" print("%6s %-6s %3d %9s %10s %10s %6s " % (row[0], row[1], row[2], row['EMPTIME'], row['EMSTDATE'], row['EMENDATE'], row[0])) row = IfxPy.fetch_both(result)
#!/root/anaconda3/bin/ipython # Environment settings for Database import os try: informix_dir=os.environ['INFORMIXDIR'] except KeyError: print ( "INFORMIX PATH is not setup - Exit ") os.exit() try: ld_lib_path=os.environ['LD_LIBRARY_PATH'] LD_LIBRARY_PATH=informix_dir+"/lib:"+informix_dir+"/lib/esql:"+informix_dir+"/lib/cli:"+ld_lib_path os.environ['LD_LIBRARY_PATH']=LD_LIBRARY_PATH except KeyError: print ( "No environment set for LD_LIBRARY_PATH") LD_LIBRARY_PATH=informix_dir+"/lib:"+informix_dir+"/lib/esql:"+informix_dir+"/lib/cli" os.environ['LD_LIBRARY_PATH']=LD_LIBRARY_PATH os.environ print(os.environ) import IfxPy conStr = "SERVER=pronto_net;DATABASE=sysmaster;HOST=pronto;SERVICE=9088;UID=informix;PWD=pronto1$;" conn = IfxPy.connect( conStr, "", "") sql = "select fname from syschunks" stmt = IfxPy.exec_immediate(conn, sql) dictionary = IfxPy.fetch_both(stmt) while dictionary != False: print(dictionary[0]) dictionary = IfxPy.fetch_both(stmt) IfxPy.close(conn)
def run_test_008(self): op = {IfxPy.ATTR_CASE: IfxPy.CASE_NATURAL} conn = IfxPy.connect(config.ConnStr, config.user, config.password, op) server = IfxPy.server_info(conn) result = IfxPy.columns(conn, None, None, "employee") row = IfxPy.fetch_both(result) value1 = None value2 = None value3 = None value4 = None if (row.has_key('TABLE_NAME')): value1 = row['TABLE_NAME'] if (row.has_key('COLUMN_NAME')): value2 = row['COLUMN_NAME'] if (row.has_key('table_name')): value3 = row['table_name'] if (row.has_key('column_name')): value4 = row['column_name'] print value1 print value2 print value3 print value4 op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER} IfxPy.set_option(conn, op, 1) result = IfxPy.columns(conn, None, None, "employee") row = IfxPy.fetch_both(result) value1 = None value2 = None value3 = None value4 = None if (row.has_key('TABLE_NAME')): value1 = row['TABLE_NAME'] if (row.has_key('COLUMN_NAME')): value2 = row['COLUMN_NAME'] if (row.has_key('table_name')): value3 = row['table_name'] if (row.has_key('column_name')): value4 = row['column_name'] print value1 print value2 print value3 print value4 op = {IfxPy.ATTR_CASE: IfxPy.CASE_LOWER} IfxPy.set_option(conn, op, 1) result = IfxPy.columns(conn, None, None, "employee") row = IfxPy.fetch_both(result) value1 = None value2 = None value3 = None value4 = None if (row.has_key('TABLE_NAME')): value1 = row['TABLE_NAME'] if (row.has_key('COLUMN_NAME')): value2 = row['COLUMN_NAME'] if (row.has_key('table_name')): value3 = row['table_name'] if (row.has_key('column_name')): value4 = row['column_name'] print value1 print value2 print value3 print value4
def run_test_154(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) server = IfxPy.server_info(conn) if (server.DBMS_NAME[0:3] == 'Inf'): op = {IfxPy.ATTR_CASE: IfxPy.CASE_UPPER} IfxPy.set_option(conn, op, 1) try: statement = 'DROP TABLE fetch_test' result = IfxPy.exec_immediate(conn, statement) except: pass server = IfxPy.server_info(conn) if (server.DBMS_NAME[0:3] == 'Inf'): statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB, col3 INTEGER)' st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)" st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)" st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)" st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)" else: statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB(20), col3 INTEGER)' st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)" st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)" st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)" st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)" result = IfxPy.exec_immediate(conn, statement) result = IfxPy.exec_immediate(conn, st0) result = IfxPy.exec_immediate(conn, st1) result = IfxPy.exec_immediate(conn, st2) result = IfxPy.exec_immediate(conn, st3) statement = "SELECT col1, col2 FROM fetch_test" result = IfxPy.prepare(conn, statement) IfxPy.execute(result) row = IfxPy.fetch_tuple(result) while (row): #printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n", # row[0],row[0].length, row[1],row[1].length) print "\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long." %\ (row[0], len(row[0]), row[1], len(row[1])) row = IfxPy.fetch_tuple(result) result = IfxPy.prepare(conn, statement) IfxPy.execute(result) row = IfxPy.fetch_assoc(result) while (row): #printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n", # row['COL1'], row['COL1'].length, row['COL2'], row['COL2'].length) print "\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long." %\ (row['COL1'], len(row['COL1']), row['COL2'], len(row['COL2'])) row = IfxPy.fetch_assoc(result) result = IfxPy.prepare(conn, statement) IfxPy.execute(result) row = IfxPy.fetch_both(result) while (row): #printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n", # row['COL1'], row['COL1'].length, row[1], row[1].length) print "\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n" % \ (row['COL1'],len(row['COL1']), row[1], len(row[1])) row = IfxPy.fetch_both(result) IfxPy.close(conn)
def run_test_decimal(self): conn = IfxPy.connect(config.ConnStr, config.user, config.password) if conn: serverinfo = IfxPy.server_info( conn ) drop = "DROP TABLE STOCKSHARE" try: result = IfxPy.exec_immediate(conn,drop) except: pass # Create the table stockprice create = "CREATE TABLE STOCKSHARE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7, 2))" result = IfxPy.exec_immediate(conn, create) # Insert Directly insert = "INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (10, 'Megadeth', 100.002)" result = IfxPy.exec_immediate(conn, insert) # Prepare and Insert in the stockprice table stockprice = (\ (20, "Zaral", 102.205),\ (30, "Megabyte", "98.65"),\ (40, "Visarsoft", Decimal("123.34")),\ (50, "Mailersoft", Decimal("134.222")),\ (60, "Kaerci", Decimal("100.976"))\ ) insert = 'INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (?,?,?)' stmt = IfxPy.prepare(conn,insert) if stmt: for company in stockprice: result = IfxPy.execute(stmt,company) id = 70 company = 'Nirvana' stockshare = Decimal("100.1234") try: IfxPy.bind_param(stmt, 1, id) IfxPy.bind_param(stmt, 2, company) IfxPy.bind_param(stmt, 3, stockshare) error = IfxPy.execute(stmt); except: excp = sys.exc_info() # slot 1 contains error message print excp[1] # Select the result from the table and query = 'SELECT * FROM STOCKSHARE ORDER BY id' if (serverinfo.DBMS_NAME[0:3] != 'Inf'): stmt = IfxPy.prepare(conn, query, {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN}) else: stmt = IfxPy.prepare(conn, query) IfxPy.execute(stmt) data = IfxPy.fetch_both( stmt ) while ( data ): print "%s : %s : %s\n" % (data[0], data[1], data[2]) data = IfxPy.fetch_both( stmt ) try: stmt = IfxPy.prepare(conn, query, {IfxPy.SQL_ATTR_CURSOR_TYPE: IfxPy.SQL_CURSOR_KEYSET_DRIVEN}) IfxPy.execute(stmt) rc = IfxPy.fetch_row(stmt, -1) print "Fetch Row -1:%s " %str(rc) except: print "Requested row number must be a positive value" IfxPy.close(conn) else: print "Connection failed." #__END__ #__LUW_EXPECTED__ #10 : Megadeth : 100.00 #20 : Zaral : 102.20 #30 : Megabyte : 98.65 #40 : Visarsoft : 123.34 #50 : Mailersoft : 134.22 #60 : Kaerci : 100.97 #70 : Nirvana : 100.12 #Requested row number must be a positive value #__ZOS_EXPECTED__ #10 : Megadeth : 100.00 #20 : Zaral : 102.20 #30 : Megabyte : 98.65 #40 : Visarsoft : 123.34 #50 : Mailersoft : 134.22 #60 : Kaerci : 100.97 #70 : Nirvana : 100.12 #Requested row number must be a positive value #__IDS_EXPECTED__ #10 : Megadeth : 100.00 #20 : Zaral : 102.21 #30 : Megabyte : 98.65 #40 : Visarsoft : 123.34 #50 : Mailersoft : 134.22 #60 : Kaerci : 100.98 #70 : Nirvana : 100.12 #Requested row number must be a positive value
def my_Sample(): 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, "", "") except Exception as e: print('ERROR: Connect failed') print(e) quit() SetupSqlSet = [ "create table t1 ( c1 int, c2 char(20), c3 int, c4 int ) ;", "insert into t1 values( 1, 'Sunday', 101, 201 );", "insert into t1 values( 2, 'Monday', 102, 202 );", "insert into t1 values( 3, 'Tuesday', 103, 203 );", "insert into t1 values( 4, 'Wednesday', 104, 204 );", "insert into t1 values( 5, 'Thursday', 105, 2005 );", "insert into t1 values( 6, 'Friday', 106, 206 );", "insert into t1 values( 7, 'Saturday', 107, 207 );" ] try: sql = "drop table t1;" print(sql) stmt = IfxPy.exec_immediate(conn, sql) except: print('FYI: drop table failed') i = 0 for sql in SetupSqlSet: i += 1 print(sql) stmt = IfxPy.exec_immediate(conn, sql) # The first record executed is for create table i -= 1 # Select records sql = "SELECT * FROM t1" stmt = IfxPy.exec_immediate(conn, sql) dictionary = IfxPy.fetch_both(stmt) rc = 0 while dictionary != False: rc += 1 print("-- Record {0} --".format(rc)) print("c1 is : ", dictionary[0]) print("c2 is : ", dictionary[1]) print("c3 is : ", dictionary["c3"]) print("c4 is : ", dictionary[3]) print(" ") dictionary = IfxPy.fetch_both(stmt) print() print("Total Record Inserted {}".format(i)) print("Total Record Selected {}".format(rc)) # Free up memory used by result and then stmt too IfxPy.free_result(stmt) IfxPy.free_stmt(stmt) IfxPy.close(conn) print("Done")
def LoadCsvSample(csv_file_name, table_name, ConStr): try: # netstat -a | findstr 9088 conn = IfxPy.connect(ConStr, "", "") except Exception as e: print('ERROR: Connect failed') print(e) quit() try: sql = "drop table {};".format(table_name) print(sql) stmt = IfxPy.exec_immediate(conn, sql) except: print('FYI: drop table failed') # head -n 5 sample.csv # "Store","DayOfWeek","Date","Sales","Customers","Open","Promo","StateHoliday","SchoolHoliday" # 1,5,2015-07-31,5263,555,1,1,"0","1" # 2,5,2015-07-31,6064,625,1,1,"0","1" # 3,5,2015-07-31,8314,821,1,1,"0","1" # 4,5,2015-05-31,13995,1498,1,1,"0","1" # The colum and its type for full list is. # Store int64 # DayOfWeek int64 # Date object # Sales int64 # Customers int64 # Open int64 # Promo int64 # StateHoliday object # SchoolHoliday int64 sql = ''' create table {} ( Store int, DayOfWeek int, Date LVARCHAR, Sales int, Customers int, Open int, Promo int, StateHoliday char(6), SchoolHoliday int ); '''.format(table_name) stmt = IfxPy.exec_immediate(conn, sql) sql = ''' INSERT INTO {} ( Store, DayOfWeek, Date, Sales, Customers, Open, Promo, StateHoliday, SchoolHoliday ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ); '''.format(table_name) stmt = IfxPy.prepare(conn, sql) c1 = None c2 = None c3 = None c4 = None c5 = None c6 = None c7 = None c8 = None c9 = None IfxPy.bind_param(stmt, 1, c1, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 2, c2, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 3, c3, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR) IfxPy.bind_param(stmt, 4, c4, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 5, c5, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 6, c6, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 7, c7, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) IfxPy.bind_param(stmt, 8, c8, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_CHAR) IfxPy.bind_param(stmt, 9, c9, IfxPy.SQL_PARAM_INPUT, IfxPy.SQL_INTEGER) # Read the CSV file and insert into the table with open(csv_file_name, 'r') as csv_file: csv_reader = csv.reader(csv_file, delimiter=',') row_count = 0 for row in csv_reader: row_count += 1 # Convert the List to tuple tup = tuple(row) if row_count == 1: print("Header :", tup) else: print(tup) IfxPy.execute(stmt, tup) print(f'Rows Inserted is {row_count-1} .') # SELECT the inserted rows sql = "SELECT * FROM t1" stmt = IfxPy.exec_immediate(conn, sql) dictionary = IfxPy.fetch_both(stmt) rc = 0 while dictionary != False: rc = rc + 1 # print ("-- Record {0} --".format(rc)) # print ("c1 is : ", dictionary[0]) # print ("c2 is : ", dictionary[1]) # print ("c3 is : ", dictionary[2]) # print ("c4 is : ", dictionary[3]) # print ("c5 is : ", dictionary[4]) # print ("c6 is : ", dictionary[5]) # print ("c7 is : ", dictionary[6]) # print ("c8 is : ", dictionary[7]) # print ("c9 is : ", dictionary[8]) # print (" ") dictionary = IfxPy.fetch_both(stmt) print(f'Rows Selected is {rc} .') IfxPy.close(conn) print("Done")