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)
