def search(self, identifier_code: str) -> Status:
"""Search for a patient in the data base.
Args:
self: The data base manager.
identifier_code: The identifier code of the patient.
"""
# Declare the upload command.
sql_command = """
SELECT Chance, ID_Number, Hospital, Name
FROM KPD60209.Patients
WHERE Identifier = ?;
"""
stmt = ibm_db.prepare(self.conn, sql_command)
# Explicitly bind the parameter.
ibm_db.bind_param(stmt, 1, identifier_code)
# Excute the sql command.
ibm_db.execute(stmt)
# Receive the results.
results = ibm_db.fetch_both(stmt)
if results != False:
# If found.
return Status(int(results["CHANCE"]), results["HOSPITAL"],
results["ID_NUMBER"], results["NAME"])
return None
def getnames(name=None):
try:
if request.method == "POST":
mag = request.form['mag']
#connect to db
conn = ibm_db.connect("DATABASE="+db2cred['db']+";HOSTNAME="+db2cred['hostname']+";PORT="+str(db2cred['port'])+";UID="+db2cred['username']+";PWD="+db2cred['password']+";","","")
if conn:
print("in if loop")
sql='select * from RZG77856.ALL_MONTH where "MAG">?'
prep = ibm_db.prepare(conn,sql)
ibm_db.bind_param(prep, 1, mag)
ibm_db.execute(prep)
rows = []
print("conn 2")
# fetching the result
result = ibm_db.fetch_assoc(prep)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(prep)
# close database connection
ibm_db.close(conn)
print("conn 3")
return render_template('cresult.html', rows=rows)
else:
print("no connection established")
return render_template('main.html')
except Exception as e:
print(e)
return "<html><body><p>In Exception</p></body></html>"
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"
if sys.platform == 'zos':
rc = ibm_db.bind_param(stmt, 1, picture,
ibm_db.SQL_PARAM_INPUT, ibm_db.SQL_BLOB)
else:
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 run_test_312(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
query = "INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)"
if conn:
stmt = ibm_db.prepare(conn, query)
params = ['STG', 'Systems & Technology', '123456', 'RSF', 'Fiji']
print("Binding parameters")
for i, p in enumerate(params, 1):
ibm_db.bind_param(stmt, i, Wrapper(p))
if ibm_db.execute(stmt):
print("Executing statement")
ibm_db.execute(stmt)
# force the cache to be unbound
for i, p in enumerate(params, 1):
ibm_db.bind_param(stmt, i, p)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_143(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.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 = ibm_db.prepare(conn, insert1)
animal = None
ibm_db.bind_param(stmt, 1, animal)
if ibm_db.execute(stmt):
stmt = ibm_db.exec_immediate(conn, select)
row = ibm_db.fetch_tuple(stmt)
while (row):
#row.each { |child| print child }
for i in row:
print(i)
row = ibm_db.fetch_tuple(stmt)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def largest_n():
mode = request.args.get('mode1') if request.args.get(
'mode1') else request.args.get('mode2')
mode = mode.split(' ')[1]
start = time.time()
number = request.args.get('number') if request.args.get('number') else 5
if mode == 'RDB':
# connect to DB2
db2conn = ibm_db.connect(db2cred['ssldsn'], "", "")
if db2conn:
sql = "SELECT * FROM EARTHQUAKE ORDER BY MAG DESC FETCH FIRST ? ROWS ONLY;"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, number)
ibm_db.execute(stmt)
rows = []
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(db2conn)
elif mode == 'Memcache':
tmp = cache_csv.sort_values(by='MAG', ascending=False)
rows = tmp[:int(number)].reset_index().to_dict(orient='records')
end = time.time()
elapse = end - start
return render_template('large_n.html', ci=rows, elapse=elapse)
def run_test_143(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.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 = ibm_db.prepare(conn, insert1)
animal = None
ibm_db.bind_param(stmt, 1, animal)
if ibm_db.execute(stmt):
stmt = ibm_db.exec_immediate(conn, select)
row = ibm_db.fetch_tuple(stmt)
while ( row ):
#row.each { |child| print child }
for i in row:
print(i)
row = ibm_db.fetch_tuple(stmt)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def delete_model(self, model_name):
if not self.is_postgre_sql:
sql_statement = "DELETE FROM %s.%s where ENTITY_TYPE_ID = ? and MODEL_NAME = ?" % (
self.quoted_schema, self.quoted_store_tablename)
try:
stmt = ibm_db.prepare(self.db_connection, sql_statement)
try:
ibm_db.bind_param(stmt, 1, self.entity_type_id)
ibm_db.bind_param(stmt, 2, model_name)
ibm_db.execute(stmt)
finally:
ibm_db.free_result(stmt)
except Exception as ex:
raise Exception(
'Deletion of model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
else:
sql_statement = "DELETE FROM %s.%s" % (self.quoted_schema, self.quoted_store_tablename)
sql_statement += ' where entity_type_id = %s and model_name = %s'
try:
dbhelper.execute_postgre_sql_query(self.db_connection, sql_statement, (self.entity_type_id, model_name))
except Exception as ex:
raise Exception(
'Deletion of model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
logger.info('Model %s has been deleted from table %s.%s' % (
model_name, self.quoted_schema, self.quoted_store_tablename))
def run_test_142(self):
sql = "SELECT id, breed, name, weight FROM animals WHERE weight < ? AND weight > ?"
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.prepare(conn, sql)
weight = 200.05
mass = 2.0
ibm_db.bind_param(stmt, 1, weight, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 2, mass, ibm_db.SQL_PARAM_INPUT)
result = ibm_db.execute(stmt)
if ( result ):
row = ibm_db.fetch_tuple(stmt)
while ( row ):
#row.each { |child| print child }
for i in row:
print i
row = ibm_db.fetch_tuple(stmt)
ibm_db.close(conn)
else:
print "Connection failed."
def query_db(self, query, args=()):
"""Submits database query.
Examples:
for user in query_db('select * from users'):
print user['username'], 'has the id', user['user_id']
for user in query_db('select * from users where username = ?', [the_username]):
print user['username'], 'has the id', user['user_id']
Returns list
list = list of rows, where each row is represented using tuple
"""
rows = []
if self._conn:
log.debug("Running query\n" + query)
log.debug("Query params: " + pprint.pformat(args))
stmt = ibm_db.prepare(self._conn, query)
for i, param in enumerate(args):
ibm_db.bind_param(stmt, i, param)
ibm_db.execute(stmt)
if re.search('create|insert|update|delete', query, re.I):
return rows
row = ibm_db.fetch_tuple(stmt)
while (row):
rows.append(row)
row = ibm_db.fetch_tuple(stmt)
return rows
def count_scale():
start = request.args.get('start', default='2020-06-01')
end = request.args.get('end', default='2020-06-01')
start = '2020-06-01' if start == '' else start
end = '2020-06-08' if end == '' else end
start = datetime.datetime.strptime(start, "%Y-%m-%d")
end = datetime.datetime.strptime(end, "%Y-%m-%d")
scale = request.args.get('scale', '3')
# connect to DB2
db2conn = ibm_db.connect(db2cred['ssldsn'], "", "")
if db2conn:
sql = "SELECT * FROM EARTHQUAKE WHERE MAGTYPE=\'ml\' AND MAG>=?"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, scale)
ibm_db.execute(stmt)
rows = []
result = ibm_db.fetch_assoc(stmt)
while result != False:
curr_date = result['TIME'][:10]
curr_date = datetime.datetime.strptime(curr_date, "%Y-%m-%d")
if start <= curr_date <= end:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(db2conn)
return render_template('count_scale.html', ci=rows)
def searchinmagintudeclusters():
try:
db2conn = ibm_db.connect("DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] + ";PORT=" + str(
db2cred['port']) + ";UID=" + db2cred['username'] + ";PWD=" + db2cred['password'] + ";", "", "")
mag = request.form['mag']
mag2 = mag+".99"
print mag2
sql = "select * from earth where (\"mag\" between ? and ?)"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt,1,mag)
ibm_db.bind_param(stmt, 2, mag2)
ibm_db.execute(stmt)
result = ibm_db.fetch_assoc(stmt)
row = []
while result != False:
row.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(db2conn)
return render_template("searchinmagintudeclusters.html", row=row)
except:
print "Exception Occured in Display Method"
return render_template("home.html")
def searchwithmag(latitude1=None,longitude1=None,latitude2=None,longitude2=None):
try:
longitude1 = float(request.form['longitude1'])
latitude1 = float(request.form['latitude1'])
longitude2 = float(request.form['longitude2'])
latitude2 = float(request.form['latitude2'])
mag = float(request.form['mag'])
Query_1="select PLACE, LATITUDE, LONGITUDE, TIME from QUAKES where \"LATITUDE\" BETWEEN ? and ? and \"LONGITUDE\" BETWEEN ? and ? and \"MAG\" >= ? "
select_Stmt = ibm_db.prepare(conn, Query_1)
ibm_db.bind_param(select_Stmt, 1, str(latitude1))
ibm_db.bind_param(select_Stmt, 2, str(latitude2))
ibm_db.bind_param(select_Stmt, 3, str(longitude1))
ibm_db.bind_param(select_Stmt, 4, str(longitude2))
ibm_db.bind_param(select_Stmt, 5, str(mag))
ibm_db.execute(select_Stmt)
row=[]
result = ibm_db.fetch_assoc(select_Stmt)
while result:
row.append(result)
result = ibm_db.fetch_assoc(select_Stmt)
return render_template('searchwithmag.html', row=row)
except:
# print "Exception Occured in Display Method"
return render_template("main.html")
def searchcount():
try:
db2conn = ibm_db.connect(
"DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] +
";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] +
";PWD=" + db2cred['password'] + ";", "", "")
room = request.form['uname']
sql = "Select count(*) from earth WHERE \"mag\" > ?"
print room
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, room)
ibm_db.execute(stmt)
result = ibm_db.fetch_assoc(stmt)
row = []
while result != False:
row.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
ibm_db.close(db2conn)
return render_template("searchcount.html", row=row)
except:
print "Exception Occured in Display Method"
return render_template("home.html")
def city(name=None):
# connect to DB2
rows=[]
try:
db2conn = ibm_db.connect(db2cred['ssldsn'], "","")
if db2conn:
# we have a Db2 connection, query the database
sql="select * from cities where name=? order by population desc"
# Note that for security reasons we are preparing the statement first,
# then bind the form input as value to the statement to replace the
# parameter marker.
stmt = ibm_db.prepare(db2conn,sql)
ibm_db.bind_param(stmt, 1, name)
ibm_db.execute(stmt)
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
return render_template('city.html', ci=rows)
except:
app.logger.error('could not establish Db2 connection')
errorMsg = ibm_db.conn_errormsg()
app.logger.error(errorMsg)
return render_template('city.html', ci=[])
def search_by_romm_number():
room = str(request.form["room"])
print(room)
if room == "":
print("Here")
result_dict = {"Room number": str(room), "RESULT": 0}
return render_template('Search_by_room.html', result=result_dict)
sql = "SELECT * FROM names WHERE ROOM = ?"
stmt = ibm_db.prepare(connect.connection, sql)
ibm_db.bind_param(stmt, 1, room)
result = ibm_db.execute(stmt)
result_dict = ibm_db.fetch_assoc(stmt)
print(result_dict)
if result_dict is False:
result_dict = {"Room number": str(room), "RESULT": 0}
# print(result_dict)
else:
result_dict["RESULT"] = 1
if room is "":
result_dict = {"Room number": str(room), "RESULT": 0}
return render_template('Search_by_room.html', result=result_dict)
def run_test_312(self):
conn = ibm_db.connect(config.database, config.user, config.password)
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
query = "INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)"
if conn:
stmt = ibm_db.prepare(conn, query)
params = ['STG', 'Systems & Technology', '123456', 'RSF', 'Fiji']
print("Binding parameters")
for i,p in enumerate(params, 1):
ibm_db.bind_param(stmt, i, Wrapper(p))
if ibm_db.execute(stmt):
print("Executing statement")
ibm_db.execute(stmt)
# force the cache to be unbound
for i,p in enumerate(params, 1):
ibm_db.bind_param(stmt, i, p)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def _get_cache(self, cache_filename, cache_pathname):
# Remove file on disc if there is one
try:
if os.path.exists(cache_pathname):
os.remove(cache_pathname)
except Exception as ex:
raise Exception('Removal of old cache file %s failed.' % cache_pathname) from ex
if not self.is_postgre_sql:
sql_statement = "SELECT PARQUET_FILE FROM %s.%s WHERE ENTITY_TYPE_ID = ? AND PARQUET_NAME = ?" % (
self.quoted_schema, self.quoted_cache_tablename)
stmt = ibm_db.prepare(self.db_connection, sql_statement)
try:
ibm_db.bind_param(stmt, 1, self.entity_type_id)
ibm_db.bind_param(stmt, 2, cache_filename)
ibm_db.execute(stmt)
row = ibm_db.fetch_tuple(stmt)
if row is False:
row = None
except Exception as ex:
raise Exception(
'Retrieval of cache %s failed with sql statement "%s"' % (cache_filename, sql_statement)) from ex
finally:
ibm_db.free_result(stmt)
else:
sql_statement = 'SELECT parquet_file FROM %s.%s' % (self.quoted_schema, self.quoted_cache_tablename)
sql_statement += ' WHERE entity_type_id = %s AND parquet_name = %s'
try:
row = dbhelper.execute_postgre_sql_select_query(self.db_connection, sql_statement,
(self.entity_type_id, cache_filename),
fetch_one_only=True)
except Exception as ex:
raise Exception(
'Retrieval of cache %s failed with sql statement "%s"' % (cache_filename, sql_statement)) from ex
cache_found = False
if row is not None:
cache_found = True
parquet = row[0]
if parquet is not None and len(parquet) > 0:
try:
f = open(cache_pathname, "wb")
try:
f.write(parquet)
logger.info('Cache %s has been retrieved from table %s.%s and stored under %s' % (
cache_filename, self.quoted_schema, self.quoted_cache_tablename, cache_pathname))
finally:
f.close()
except Exception as ex:
raise Exception('Writing cache file %s to disc failed.' % cache_pathname) from ex
else:
logger.info('The cache %s is empty' % cache_filename)
else:
logger.info('No cache found for %s' % cache_filename)
return cache_found
def insert_data_into_alert_table(self, key_and_msg_and_db_parameter=[]):
logger.info("Processing %s alerts. This alert may contain duplicates, "
"so need to process the alert before inserting into Database." % len(key_and_msg_and_db_parameter))
updated_key_and_msg = []
postgres_sql = "insert into " + self.quotedSchema + "." + self.quotedTableName + " (entity_id, timestamp, entity_type_id, data_item_name, severity, priority,domain_status) values (%s, %s, %s, %s, %s, %s, %s)"
db2_sql = "insert into " + self.quotedSchema + "." + self.quotedTableName + " (ENTITY_ID, TIMESTAMP, ENTITY_TYPE_ID, DATA_ITEM_NAME, SEVERITY, PRIORITY,DOMAIN_STATUS) values (?, ?, ?, ?, ?, ?, ?) "
total_count = 0
count = 0
start_time = dt.datetime.now()
if self.is_postgre_sql:
for key, msg, db_params in key_and_msg_and_db_parameter:
count += 1
try:
dbhelper.execute_postgre_sql_query(self.db_connection, sql=postgres_sql, params=db_params)
updated_key_and_msg.append((key, msg))
except Exception as ex:
if ex.pgcode != '23505':
raise ex
if count == 500:
total_count += count
count = 0
logger.info('Alerts that have been processed so far: %d' % total_count)
else:
try:
stmt = ibm_db.prepare(self.db_connection, db2_sql)
try:
for key, msg, db_params in key_and_msg_and_db_parameter:
count += 1
for i, param in enumerate(iterable=db_params, start=1):
ibm_db.bind_param(stmt, i, param)
try:
ibm_db.execute(stmt)
updated_key_and_msg.append((key, msg))
except Exception as ex:
if "SQLSTATE=23505" not in ex.args[0]:
raise Exception('Inserting alert %s into table %s.%s failed.' % (
str(db_params), self.quotedSchema, self.quotedTableName)) from ex
if count == 500:
total_count += count
count = 0
logger.info('Alerts that have been processed so far: %d' % total_count)
finally:
ibm_db.free_result(stmt)
except Exception as ex:
raise Exception(
'Inserting alerts into table %s.%s failed.' % (self.quotedSchema, self.quotedTableName)) from ex
logger.info('%d new alerts out of %d processed alerts have been inserted into table %s.%s in %d seconds.' % (
len(updated_key_and_msg), len(key_and_msg_and_db_parameter), self.quotedSchema, self.quotedTableName,
(dt.datetime.now() - start_time).total_seconds()))
return updated_key_and_msg
def insert(sql, params):
if __db2_connection__ is not None:
p_stmt = ibm_db.prepare(__db2_connection__, sql)
for i, (k, v) in enumerate(params.items()):
ibm_db.bind_param(p_stmt, (i + 1), v)
ibm_db.execute(p_stmt)
def bnb(conn, attra_list, days, attra_price, budget):
#bnb_dist_dict = defaultdict(list)
bnb_dict = dict()
for attra in attra_list:
sql = """SELECT d.ROOMID, a.PRICE, a.COSTPERFORMANCE
FROM DISTANCE d JOIN AIRBNB a ON d.ROOMID=a.ROOMID
WHERE ATTRACTIONID = ?
ORDER BY DISTANCE ASC LIMIT 20"""
stmt = ibm_db.prepare(conn, sql)
ibm_db.bind_param(stmt, 1, attra)
if (ibm_db.execute(stmt) is not None):
dictionary = ibm_db.fetch_assoc(stmt)
while dictionary != False:
bnb_RID = dictionary['ROOMID']
bnb_CP = dictionary['COSTPERFORMANCE']
bnb_PRICE = dictionary['PRICE']
# bnb_dict[bnb_RID] = bnb_CP
#print((int(bnb_PRICE) * days + attra_price) * 0.12)
if ((int(bnb_PRICE) * days + attra_price) * 0.12 <= budget):
bnb_dict[bnb_RID] = bnb_CP
else:
pass
dictionary = ibm_db.fetch_assoc(stmt)
#bnb_dist_dict[str(attra)].append(bnb_dict)
sorted_cp = sorted(bnb_dict.items(), key=lambda x: x[1], reverse=True)
# list of tuples
bnb_filtered = sorted_cp[:5]
# extract specific items corresponding to the roomid from the above list
rec_bnb = [x[0] for x in bnb_filtered]
#print(rec_bnb)
rec_bnb_list = []
# hawker center
#print('>>>>>> Airbnb')
for roomid in rec_bnb:
sql = "SELECT FOODID FROM DISTANCE_BNB_FOOD WHERE ROOMID =" + str(roomid) + "AND DISTANCE <= 2"\
" ORDER BY DISTANCE ASC LIMIT 3"
#print('[{roomid}->>>{sql}]'.format(roomid=roomid, sql=sql))
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
#print('result-->>', dictionary)
roomid_list = []
bnbs_dict = dict()
while dictionary != False:
rec_roomid = dictionary['FOODID']
roomid_list.append(int(rec_roomid))
bnbs_dict['id'] = int(roomid)
bnbs_dict['hawkerCenters'] = roomid_list
dictionary = ibm_db.fetch_assoc(stmt)
rec_bnb_list.append(bnbs_dict)
return rec_bnb_list
def delete_all_caches(self):
# Delete all cache entries for this entity type locally
cache_filename, cache_pathname, base_path = self._get_cache_filename(
None, None)
if os.path.exists(base_path):
try:
file_listing = os.listdir(base_path)
except Exception as ex:
raise Exception('Failure to list content of directory %s' %
base_path) from ex
for filename in file_listing:
if filename.startswith(DBDataCache.CACHE_FILE_STEM):
full_path = '%s/%s' % (base_path, filename)
try:
os.remove(full_path)
except Exception as ex:
raise Exception('Removal of file %s failed' %
full_path) from ex
# Delete all cache entries for this entity type in database
if not self.is_postgre_sql:
sql_statement = "DELETE FROM %s.%s where ENTITY_TYPE_ID = ?" % (
self.quoted_schema, self.quoted_cache_tablename)
try:
stmt = ibm_db.prepare(self.db_connection, sql_statement)
try:
ibm_db.bind_param(stmt, 1, self.entity_type_id)
ibm_db.execute(stmt)
finally:
ibm_db.free_result(stmt)
except Exception as ex:
raise Exception(
'Deletion of cache files failed with sql statement "%s"' %
sql_statement) from ex
else:
sql_statement = "DELETE FROM %s.%s" % (
self.quoted_schema,
self.quoted_cache_tablename,
)
sql_statement += ' where entity_type_id = %s'
try:
dbhelper.execute_postgre_sql_query(self.db_connection,
sql_statement,
(self.entity_type_id, ))
except Exception as ex:
raise Exception(
'Deletion of cache files failed with sql statement %s' %
sql_statement) from ex
logger.info(
'All caches have been deleted from table %s.%s for entity type id %d'
% (self.quoted_schema, self.quoted_cache_tablename,
self.entity_type_id))
def update():
room = request.form["room"]
keywords = request.form["keywords"]
sql = "UPDATE people SET name = ? WHERE points = ?"
stmt = ibm_db.prepare(connect.connection, sql)
ibm_db.bind_param(stmt, 1, keywords)
ibm_db.bind_param(stmt, 2, room)
result = ibm_db.execute(stmt)
return redirect(url_for("just_hello"))
def delete_user(user):
if validate_credentials(user) == False:
return False
if is_user(user) == False:
return False
delete_query = "DELETE FROM USERS WHERE USERNAME = ?"
delete_stmt = ibm_db.prepare(conn, delete_query)
ibm_db.bind_param(delete_stmt, 1, user)
ibm_db.execute(delete_stmt)
return True
def update_range_name():
name = str(request.form["first"])
keyword = str(request.form["second"])
if name == "" or keyword == "":
obj = 0
return render_template('Range.html', result=obj)
print("ger")
print(name, keyword)
sql = " select * from names where GRADE < ? and GRADE >?"
stmt = ibm_db.prepare(connect.connection, sql)
ibm_db.bind_param(stmt, 1, name)
ibm_db.bind_param(stmt, 2, keyword)
result = ibm_db.execute(stmt)
ret = []
result_dict = ibm_db.fetch_assoc(stmt)
while result_dict is not False:
# print(json.dumps(result_dict))
ret.append(result_dict)
result_dict = ibm_db.fetch_assoc(stmt)
data = requests.get(base_url)
parse_dict = xmltodict.parse(data.text)
image_data = parse_dict["ListBucketResult"]["Contents"]
img_lt = []
for data in image_data:
img_lt.append(data["Key"])
if ret is not False:
for key in ret:
if key["PICTURE"] not in img_lt:
key["PICTURE"] = None
obj = {}
obj['list'] = ret
if name is "" or keyword is "":
obj = 0
# print(obj)
# if result_dict is False:
# result_dict = {"name":str(name), "RESULT":0}
# print(result_dict)
# else:
# result_dict["RESULT"] = 1
#
# if name is "" or keyword is "":
# result_dict = {"name":str(name), "RESULT":0}
return render_template('Range.html', result=obj)
def retrieve_model(self, model_name, deserialize=True):
if not self.is_postgre_sql:
sql_statement = "SELECT MODEL FROM %s.%s WHERE ENTITY_TYPE_ID = ? AND MODEL_NAME = ?" % (
self.quoted_schema, self.quoted_store_tablename)
stmt = ibm_db.prepare(self.db_connection, sql_statement)
try:
ibm_db.bind_param(stmt, 1, self.entity_type_id)
ibm_db.bind_param(stmt, 2, model_name)
ibm_db.execute(stmt)
row = ibm_db.fetch_tuple(stmt)
if row is False:
model = None
else:
model = row[0]
except Exception as ex:
raise Exception(
'Retrieval of model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
finally:
ibm_db.free_result(stmt)
else:
sql_statement = 'SELECT model FROM %s.%s' % (self.quoted_schema, self.quoted_store_tablename)
sql_statement += ' WHERE entity_type_id = %s AND model_name = %s'
try:
row = dbhelper.execute_postgre_sql_select_query(self.db_connection, sql_statement,
(self.entity_type_id, model_name), fetch_one_only=True)
if row is None:
model = None
else:
model = bytes(row[0])
except Exception as ex:
raise Exception(
'Retrieval of model %s failed with sql statement "%s"' % (model_name, sql_statement)) from ex
if model is not None:
logger.info('Model %s of size %d bytes has been retrieved from table %s.%s' % (
model_name, len(model) if model is not None else 0, self.quoted_schema, self.quoted_store_tablename))
else:
logger.info('Model %s does not exist in table %s.%s' % (
model_name, self.quoted_schema, self.quoted_store_tablename))
if model is not None and deserialize:
try:
model = pickle.loads(model)
except Exception as ex:
raise Exception(
'Deserialization of model %s that has been retrieved from ModelStore failed.' % model_name) from ex
return model
def run_test_booleanInsertSelect(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if (not conn):
print("Could not make a connection.")
return 0
server = ibm_db.server_info(conn)
if (not server.DBMS_NAME.startswith('DB2/')):
print("Boolean is not supported")
return 0
try:
ibm_db.exec_immediate(conn, "DROP TABLE bool_test")
except:
pass
try:
ibm_db.exec_immediate(
conn,
"CREATE TABLE bool_test(col1 BOOLEAN, description varchar(50))"
)
except Exception as e:
print("Error : {}\n".format(str(e)))
exit(-1)
try:
insert_sql = "INSERT INTO bool_test values(?, ?)"
stmt = ibm_db.prepare(conn, insert_sql)
rows = ((True, 'bindparam true'), (False, 'bindparam false'),
(None, 'bindparam None'))
for row in rows:
ibm_db.bind_param(stmt, 1, row[0])
ibm_db.bind_param(stmt, 2, row[1])
ibm_db.execute(stmt)
stmt = None
select_sql = 'SELECT * FROM bool_test'
stmt = ibm_db.exec_immediate(conn, select_sql)
result = ibm_db.fetch_tuple(stmt)
while result:
for col in result:
print(col)
result = ibm_db.fetch_tuple(stmt)
ibm_db.close(conn)
except Exception as e:
print("Error:{}".format(str(e)))
def ritch(magfrom=None, magto=None, datefromritch=None, datetoritch=None):
# connect to DB2
db2conn = ibm_db.connect(
"DATABASE=" + db2cred['db'] + ";HOSTNAME=" + db2cred['hostname'] +
";PORT=" + str(db2cred['port']) + ";UID=" + db2cred['username'] +
";PWD=" + db2cred['password'] + ";", "", "")
if db2conn:
# we have a Db2 connection, query the database
sql = 'select count(*) from EARTHQUAKE where ("mag" BETWEEN ? AND ?) AND ("time" between ? AND ?)'
#Note that for security reasons we are preparing the statement first,
# then bind the form input as value to the statement to replace the
# parameter marker.
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, magfrom)
ibm_db.bind_param(stmt, 2, magto)
ibm_db.bind_param(stmt, 3, datefromritch)
ibm_db.bind_param(stmt, 4, datetoritch)
ibm_db.execute(stmt)
rows = []
# fetch the result
result = ibm_db.fetch_assoc(stmt)
while result != False:
rows.append(result.copy())
result = ibm_db.fetch_assoc(stmt)
# close database connection
ibm_db.close(db2conn)
return render_template('ritcher.html', r=rows)
def db_insert(self, session_id, ani, loading):
"""db insert"""
try:
db2conn = ibm_db.connect(loading.db_link, loading.db_username, loading.db_password)
if db2conn:
sql = "INSERT INTO AFRIEND(ANI,SESSION_ID) VALUES(?,?)"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, ani)
ibm_db.bind_param(stmt, 2, session_id)
ibm_db.execute(stmt)
return True
except Exception as e:
return False
def db_select(self, ani, loading):
"""db select"""
db2conn = ibm_db.connect(loading.db_link, loading.db_username, loading.db_password)
if db2conn:
sql = "SELECT SESSION_ID FROM AFRIEND WHERE ANI=?"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, ani)
ibm_db.execute(stmt)
result = ibm_db.fetch_assoc(stmt)
if not result:
return False
ibm_db.close(db2conn)
return result.get('SESSION_ID')
def searchincountryname(latitude1=None,longitude1=None,latitude2=None,longitude2=None):
try:
longitude1 = float(request.form['longitude1'])
latitude1 = float(request.form['latitude1'])
longitude2 = float(request.form['longitude2'])
latitude2 = float(request.form['latitude2'])
Query_1="select NAME from LATLONG where \"LATITUDE\" BETWEEN ? and ? and \"LONGITUDE\" BETWEEN ? and ? "
select_Stmt = ibm_db.prepare(conn, Query_1)
ibm_db.bind_param(select_Stmt, 1, str(latitude1))
ibm_db.bind_param(select_Stmt, 2, str(latitude2))
ibm_db.bind_param(select_Stmt, 3, str(longitude1))
ibm_db.bind_param(select_Stmt, 4, str(longitude2))
ibm_db.execute(select_Stmt)
row=[]
result = ibm_db.fetch_assoc(select_Stmt)
while result:
row.append(result)
result = ibm_db.fetch_assoc(select_Stmt)
return render_template('searchincountryname.html', row=row)
except:
# print "Exception Occured in Display Method"
return render_template("main.html")
def db_update(self, session_id, ani, loading):
"""db update"""
try:
db2conn = ibm_db.connect(loading.db_link, loading.db_username, loading.db_password)
if db2conn:
sql = "UPDATE AFRIEND SET SESSION_ID = ? WHERE ANI = ?"
stmt = ibm_db.prepare(db2conn, sql)
ibm_db.bind_param(stmt, 1, session_id)
ibm_db.bind_param(stmt, 2, ani)
ibm_db.execute(stmt)
return True
except Exception as e:
return False
def searchwithradius(latitude=None,longitude=None,radius=None):
try:
longitude = float(request.form['longitude'])
latitude = float(request.form['latitude'])
radius = float(request.form['radius'])
lat1 = latitude - (radius / 69)
lat2 = latitude + (radius / 69)
long1 = longitude - (radius / 69)
long2 = longitude + (radius / 69)
Query_1="select * from QUAKES where \"LATITUDE\" > ? and \"LATITUDE\" < ? and \"LONGITUDE\" > ? and \"LONGITUDE\" < ? "
select_Stmt = ibm_db.prepare(conn, Query_1)
ibm_db.bind_param(select_Stmt, 1, str(lat1))
ibm_db.bind_param(select_Stmt, 2, str(lat2))
ibm_db.bind_param(select_Stmt, 3, str(long1))
ibm_db.bind_param(select_Stmt, 4, str(long2))
ibm_db.execute(select_Stmt)
row=[]
result = ibm_db.fetch_assoc(select_Stmt)
while result:
row.append(result)
result = ibm_db.fetch_assoc(select_Stmt)
return render_template('searchwithradius.html', row=row)
except:
# print "Exception Occured in Display Method"
return render_template("main.html")
def run_test_6528(self):
conn = ibm_db.connect(config.database, config.user, config.password)
server = ibm_db.server_info( conn )
if conn:
if (server.DBMS_NAME[0:3] == 'IDS'):
sql = "SELECT TRIM(TRAILING FROM name) FROM animals WHERE breed = ?"
else:
sql = "SELECT RTRIM(name) FROM animals WHERE breed = ?"
stmt = ibm_db.prepare(conn, sql)
var = "cat"
ibm_db.bind_param(stmt, 1, var, ibm_db.SQL_PARAM_INPUT)
self.checked_db2_execute(stmt)
ibm_db.close(conn)
else:
print("Connection failed.")
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 prepareBindings(connection, outFile, jLine, preparedStmtList):
outFile.write("Binding values: {} \n".format(jLine.bindings))
currentSqlOper = getPreparedStmtSql(jLine.statementId, preparedStmtList)
currentHdl = getPreparedStmtHdl(jLine.statementId, preparedStmtList)
for paramDict in jLine.bindings:
stmt = ibm_db.bind_param(currentHdl, paramDict['index'], paramDict['value'])
if not stmt:
outFile.write("operation FAILED")
def run_test_140(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.prepare(conn, "SELECT id, breed, name, weight FROM animals WHERE id = ?")
animal = 0
ibm_db.bind_param(stmt, 1, animal)
if ibm_db.execute(stmt):
row = ibm_db.fetch_tuple(stmt)
while ( row ):
#roiw.each { |child| puts child }
for i in row:
print(i)
row = ibm_db.fetch_tuple(stmt)
else:
print("Connection failed.")
def run_test_141(self):
sql = "SELECT id, breed, name, weight FROM animals WHERE id < ? AND weight > ?"
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.prepare(conn, sql)
animal = 5
mass = 2.0
ibm_db.bind_param(stmt, 1, animal)
ibm_db.bind_param(stmt, 2, mass)
if ibm_db.execute(stmt):
row = ibm_db.fetch_tuple(stmt)
while ( row ):
#row.each { |child| print child }
for i in row:
print i
row = ibm_db.fetch_tuple(stmt)
ibm_db.close(conn)
else:
print "Connection failed."
def run_test_145(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.prepare(conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)")
id = 999
breed = None
name = 'PythonDS'
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, breed)
ibm_db.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
ibm_db.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 ibm_db.bind_param and ibm_db.execute treat Python Nones and empty
# strings the right way.
ibm_db.execute(stmt, (1000, None, 'PythonDS'))
result = ibm_db.exec_immediate(conn, "SELECT id, breed, name FROM animals WHERE breed IS NULL")
row = ibm_db.fetch_tuple(result)
while ( row ):
for i in row:
print(i)
row = ibm_db.fetch_tuple(result)
ibm_db.rollback(conn)
else:
print("Connection failed.")
def run_test_147(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.prepare(conn, "INSERT INTO animals (id, breed, name) VALUES (?, ?, ?)")
id = '"999"'
breed = None
name = "PythonDS"
try:
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, breed)
ibm_db.bind_param(stmt, 3, name)
error = ibm_db.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_000():
# 建立数据库连接
conn = ibm_db.connect(conn_str, "", "")
if conn:
# 获取数据库服务信息
server = ibm_db.server_info(conn)
print("连接数据库服务器：", server.DBMS_NAME)
# 删除已经存在的表
drop_sql = "DROP TABLE animals"
try:
result = ibm_db.exec_immediate(conn, drop_sql)
except Exception as e:
# 若不存在表，打印信息，程序继续往下执行
print(drop_sql, "删除的表不存在：", e)
# 创建表 animal
create_sql = "CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))"
# 执行建表语句
result = ibm_db.exec_immediate(conn, create_sql)
# 插入数据
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_sql = "INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)"
# 准备 写这个似乎以前是Java程序员 stmt这很Java
stmt = ibm_db.prepare(conn, insert_sql)
if stmt:
# 循环
for animal in animals:
# 执行插入动作
result = ibm_db.execute(stmt, animal)
print(animal, ", 插入结果：", result)
# 创建视图
# 首先删除视图
drop_sql = "DROP VIEW anime_cat"
try:
result = ibm_db.exec_immediate(conn, drop_sql)
except Exception as e:
# 若不存在表，打印信息，程序继续往下执行
print(drop_sql, "删除的视图不存在：", e)
# 接着创建视图
ibm_db.exec_immediate(
conn,
"""
CREATE VIEW anime_cat AS
SELECT name, breed FROM animals WHERE id = 0
""",
)
# 删除表 animal_pics
drop_sql = "DROP TABLE animal_pics"
try:
result = ibm_db.exec_immediate(conn, drop_sql)
except Exception as e:
# 若不存在表，打印信息，程序继续往下执行
print(drop_sql, "删除的表不存在：", e)
# 创建表
create_sql = "CREATE TABLE animal_pics (name VARCHAR(32), picture BLOB)"
# 执行建表语句
result = ibm_db.exec_immediate(conn, create_sql)
# 插入数据
animals = (("Spook", "spook.png"), ("Helmut", "pic1.jpg"))
insert_sql = "INSERT INTO animal_pics (name, picture) VALUES (?, ?)"
# 预插入
stmt = ibm_db.prepare(conn, insert_sql)
if not stmt:
print("Attempt to prepare statament failed.")
return 0
# 准备数据
for animal in animals:
name = animal[0]
# 读取硬盘文件
print("文件位置：", os.path.abspath(__file__))
print("所属文件夹：", os.path.dirname(os.path.abspath(__file__)))
print("读取图片位置：", os.path.dirname(os.path.abspath(__file__)) + "/" + animal[1])
fileHandle = open(os.path.dirname(os.path.abspath(__file__)) + "/" + animal[1], "rb") # 以二进制方式读取文件
picture = fileHandle.read()
if not picture:
print('Could not retrieve picture "%s".' % animal[1])
return 0
# 输入流
ibm_db.bind_param(stmt, 1, name, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 2, picture, ibm_db.SQL_PARAM_INPUT)
# 执行插入
result = ibm_db.execute(stmt)
print(animal, ", 插入结果：", result)
# 关闭数据库连接
ibm_db.close(conn)
def doEverything():
output = []
# Get database connectivity information
url = getDatabaseInfo()
# connect to database
output.append("Connecting to " + url)
try:
conn = ibm_db.connect(url, '', '')
except:
output.append("Could not establish connection to database: " + ibm_db.conn_errormsg())
return output
output.append("Connection successful")
# set up variables and data
tableName = "pythonDRDAGalaxy"
try:
# 1 Create table
output.append("\n#1 Create table")
sql = "create table if not exists " + tableName + "(name VARCHAR(255), population INTEGER, longitude DECIMAL(8,4), latitude DECIMAL(8,4),countryCode INTEGER)"
ibm_db.exec_immediate(conn, sql)
output.append( "\tCreate a table named: " + tableName)
output.append("\tCreate Table SQL: " + sql)
# 2 Inserts
output.append("\n#2 Inserts")
# 2.1 Insert a single document into a table
output.append("#2.1 Insert a single document into a table")
sql = "insert into " + tableName + " values(?,?,?,?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, kansasCity.name)
ibm_db.bind_param(statement, 2, kansasCity.population)
ibm_db.bind_param(statement, 3, kansasCity.longitude)
ibm_db.bind_param(statement, 4, kansasCity.latitude)
ibm_db.bind_param(statement, 5, kansasCity.countryCode)
ibm_db.execute(statement)
output.append("\tCreate Document -> " + kansasCity.name + " , " + str(kansasCity.population) +
" , " + str(kansasCity.longitude) + " , " + str(kansasCity.latitude) + " , " + str(kansasCity.countryCode))
output.append("\tSingle Insert SQL: " + sql)
# 2.2 Insert multiple documents into a table
# Currently there is no support for batch inserts with ibm_db
output.append("#2.2: Insert multiple documents into a table. \n\tCurrently there is no support batch inserts")
sql = "insert into " + tableName + " values(?,?,?,?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, seattle.name)
ibm_db.bind_param(statement, 2, seattle.population)
ibm_db.bind_param(statement, 3, seattle.longitude)
ibm_db.bind_param(statement, 4, seattle.latitude)
ibm_db.bind_param(statement, 5, seattle.countryCode)
ibm_db.execute(statement)
sql = "insert into " + tableName + " values(?,?,?,?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, newYork.name)
ibm_db.bind_param(statement, 2, newYork.population)
ibm_db.bind_param(statement, 3, newYork.longitude)
ibm_db.bind_param(statement, 4, newYork.latitude)
ibm_db.bind_param(statement, 5, newYork.countryCode)
ibm_db.execute(statement)
#Alternate way to insert without bindings
sql = "insert into " + tableName + " values" + tokyo.toSQL()
ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableName + " values" + madrid.toSQL()
ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableName + " values" + melbourne.toSQL()
ibm_db.exec_immediate(conn, sql)
# 3 Queries
output.append("\n#3 Queries")
# 3.1 Find one document in a table that matches a query condition
output.append("#3.1 Find one document in a table that matches a query condition")
sql = "select * from " + tableName + " where population > 8000000 and countryCode = 1"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
output.append("\tFirst document matching query -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
output.append("\tQuery By name SQL: " + sql)
# 3.2 Find documents in a table that match a query condition
output.append("#3.2 Find documents in a table that match a query condition")
sql = "select * from " + tableName + " where population > 8000000 and longitude > 40.0"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
output.append("\tFind all documents with name: " + kansasCity.name)
while dictionary != False:
output.append("\tFound document name -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
output.append( "\tQuery All By name SQL: " + sql)
# 3.3 Find all documents in a table
output.append("#3.3 Find all documents in a table")
sql = "select * from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
output.append( "\tFind all documents in table: " + tableName)
while dictionary != False:
output.append("\tFound document name -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
output.append("\tFind All Documents SQL: " + sql)
output.append("#3.4 Count documents in a table")
sql = "select count(*) from " + tableName + " where longitude < 40.0"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
output.append("Documents in table with longitude less than 40.0: " + str(len(dictionary)))
output.append("#3.5 Order documents in a table")
sql = "select * from " + tableName + " order by population"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
output.append("\tFound Document -> name: " + str(dictionary[0]) + " population: " + str(dictionary[1]) + " latitude: " +
str(dictionary[2]) + " longitude: " + str(dictionary[3]) + " countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
output.append("#3.6 Joins")
tableJoin = "country";
sql = "create table if not exists " + tableJoin + " (countryCode INTEGER, countryName VARCHAR(255))";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (1,\"United States of America\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (44,\"United Kingdom\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (81,\"Japan\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (34,\"Spain\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (61,\"Australia\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = ("select table1.name, table1.population, table1.longitude, table1.latitude, table1.countryCode, table2.countryName from " +
tableName + " table1 inner join " + tableJoin + " table2 on table1.countryCode=table2.countryCode")
stmt =ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
output.append("\tJoined -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", latitude: " +
str(dictionary[2]) + ", longitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]) + ", countryName: " + str(dictionary[5]))
dictionary = ibm_db.fetch_both(stmt)
output.append("#3.7 Distinct documents in a table")
sql = "select distinct countryCode from " + tableName + " where longitude > 40.0"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
output.append("Distinct countryCodes of documents in table with longitude greater than 40.0: ")
while dictionary != False:
output.append("\tJoined -> countryCode: " + str(dictionary[0]))
dictionary = ibm_db.fetch_both(stmt)
output.append("#3.8 Projection Clause")
sql = "select distinct name, countryCode from " + tableName + " where population > 8000000"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
output.append("Projection of name and countryCode where population is greater than 8000000: ")
while dictionary != False:
output.append("\tJoined -> name: " + str(dictionary[0]) + ", countryCode: " + str(dictionary[1]))
dictionary = ibm_db.fetch_both(stmt)
# 4 Update documents in a table
output.append("\n#4 Update documents in a table")
sql = "update " + tableName + " set countryCode = ? where name = ?"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, 999)
ibm_db.bind_param(statement, 2, seattle.name)
ibm_db.execute(statement)
output.append( "\tDocument to update: " + seattle.name)
output.append("\tUpdate By name SQL: " + sql)
# 5 Delete documents in a table
output.append("\n#5 Delete documents in a table")
sql = "delete from " + tableName + " where name like '" + newYork.name + "'"
ibm_db.exec_immediate(conn, sql)
output.append("\tDelete documents with name: " + newYork.name)
output.append("\tDelete By name SQL: " + sql)
output.append("\n#6 Transactions")
# ibm_db.autocommit(conn, False)
#
# sql = "insert into " + tableName + " values" + sydney.toSQL()
# ibm_db.exec_immediate(conn, sql)
#
# ibm_db.commit(conn)
#
# sql = "update " + tableName + " set countryCode = 998 where name = 'Seattle'"
# ibm_db.exec_immediate(conn, sql)
#
# ibm_db.rollback(conn)
#
# ibm_db.autocommit(conn, True)
output.append( "\tFind all documents in table: " + tableName)
sql = "select * from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
output.append("\tFound document name -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
output.append("\n#7 Commands")
output.append("\n#7.1 Count")
sql = "select count(*) from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
output.append("Number of documents in table " + str(len(dictionary)))
# 8 Drop a table
output.append("\n#8 Drop a table")
sql = "drop table " + tableName;
ibm_db.exec_immediate(conn, sql)
sql = "drop table " + tableJoin;
ibm_db.exec_immediate(conn, sql)
output.append("\tDrop table: " + tableName)
output.append("\tDrop Table SQL: " + sql)
except Exception as e:
logging.exception(e)
output.append("EXCEPTION (see log for details): " + str(e))
finally:
if conn:
ibm_db.close(conn)
output.append("\nConnection closed")
return output
def doEverything():
output = []
# Get database connectivity information
url = getDatabaseInfo()
# connect to database
output.append("Connecting to " + url)
try:
conn = ibm_db.connect(url, '', '')
except:
output.append("Could not establish connection to database: " + ibm_db.conn_errormsg())
return output
output.append("Connection successful")
# set up variables and data
tableName = "pythonDRDATest"
user1 = DataFormat("test1", 1)
user2 = DataFormat("test2", 2)
user3 = DataFormat("test3", 3)
try:
# 1 Create table
output.append("\n#1 Create table")
sql = "create table " + tableName + "(name varchar(255), value integer)"
ibm_db.exec_immediate(conn, sql)
output.append( "\tCreate a table named: " + tableName)
output.append("\tCreate Table SQL: " + sql)
# 2 Inserts
output.append("\n#2 Inserts")
# 2.1 Insert a single document into a table
output.append("#2.1 Insert a single document into a table")
sql = "insert into " + tableName + " values(?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, user1.name)
ibm_db.bind_param(statement, 2, user1.value)
ibm_db.execute(statement)
output.append("\tCreate Document -> " + user1.name + " : " + str(user1.value))
output.append("\tSingle Insert SQL: " + sql)
sql = "insert into " + tableName + " values(?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, user2.name)
ibm_db.bind_param(statement, 2, user2.value)
ibm_db.execute(statement)
output.append("\tCreate Document -> " + user2.name + " : " + str(user2.value))
output.append("\tSingle Insert SQL: " + sql)
sql = "insert into " + tableName + " values(?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, user3.name)
ibm_db.bind_param(statement, 2, user3.value)
ibm_db.execute(statement)
output.append("\tCreate Document -> " + user3.name + " : " + str(user3.value))
output.append("\tSingle Insert SQL: " + sql)
# 2.2 Insert multiple documents into a table
# Currently there is no support for batch inserts with ibm_db
output.append("#2.2: Insert multiple documents into a table. \n\tCurrently there is no support batch inserts")
# 3 Queries
output.append("\n#3 Queries")
# 3.1 Find one document in a table that matches a query condition
output.append("#3.1 Find one document in a table that matches a query condition")
sql = "select * from " + tableName + " where name LIKE '" + user1.name + "'"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
output.append("\tFind document with name: " + user1.name)
output.append("\tFirst document with name -> name: " + str(dictionary[0]) + " value: " + str(dictionary[1]))
output.append("\tQuery By name SQL: " + sql)
# 3.2 Find documents in a table that match a query condition
output.append("#3.2 Find documents in a table that match a query condition")
sql = "select * from " + tableName + " where name LIKE '" + user1.name + "'"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
output.append("\tFind all documents with name: " + user1.name)
while dictionary != False:
output.append("\tFound Document -> name: " + str(dictionary[0]) + " value: " + str(dictionary[1]))
dictionary = ibm_db.fetch_both(stmt)
output.append( "\tQuery All By name SQL: " + sql)
# 3.3 Find all documents in a table
output.append("#3.3 Find all documents in a table")
sql = "select * from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
output.append( "\tFind all documents in table: " + tableName)
while dictionary != False:
output.append("\tFound Document -> name: " + str(dictionary[0]) + " value: " + str(dictionary[1]))
dictionary = ibm_db.fetch_both(stmt)
output.append("\tFind All Documents SQL: " + sql)
# 4 Update documents in a table
output.append("\n#4 Update documents in a table")
sql = "update " + tableName + " set value = ? where name = ?"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, 4)
ibm_db.bind_param(statement, 2, user2.name)
ibm_db.execute(statement)
output.append( "\tDocument to update: " + user2.name)
output.append("\tUpdate By name SQL: " + sql)
# 5 Delete documents in a table
output.append("\n#5 Delete documents in a table")
sql = "delete from " + tableName + " where name like '" + user1.name + "'"
ibm_db.exec_immediate(conn, sql)
output.append("\tDelete documents with name: " + user1.name)
output.append("\tDelete By name SQL: " + sql)
# 6 Drop a table
output.append("\n#6 Drop a table")
sql = "drop table " + tableName;
ibm_db.exec_immediate(conn, sql)
output.append("\tDrop table: " + tableName)
output.append("\tDrop Table SQL: " + sql)
except Exception as e:
logging.exception(e)
output.append("EXCEPTION (see log for details): " + str(e))
finally:
if conn:
ibm_db.close(conn)
output.append("\nConnection closed")
return output
def doEverything():
commands = []
# connect to database
host = ""
port = "9090"
username = ""
password = "******"
database = "s0523215"
url = "HOSTNAME=" + host + ";PORT=" + port + ";DATABASE="+ database + ";PROTOCOL=TCPIP;UID=" + username +";PWD="+ password + ";"
conn = ibm_db.connect(url, '', '')
commands.append("Connected to " + url)
# set up variables and data
tableName = "pythonDRDAGalaxy"
# 1 Create table
commands.append("\n#1 Create table")
sql = "create table if not exists " + tableName + "(name VARCHAR(255), population INTEGER, longitude DECIMAL(8,4), latitude DECIMAL(8,4),countryCode INTEGER)"
ibm_db.exec_immediate(conn, sql)
commands.append( "\tCreate a table named: " + tableName)
commands.append("\tCreate Table SQL: " + sql)
# 2 Inserts
commands.append("\n#2 Inserts")
# 2.1 Insert a single document into a table
commands.append("#2.1 Insert a single document into a table")
sql = "insert into " + tableName + " values(?,?,?,?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, kansasCity.name)
ibm_db.bind_param(statement, 2, kansasCity.population)
ibm_db.bind_param(statement, 3, kansasCity.longitude)
ibm_db.bind_param(statement, 4, kansasCity.latitude)
ibm_db.bind_param(statement, 5, kansasCity.countryCode)
ibm_db.execute(statement)
commands.append("\tCreate Document -> " + kansasCity.name + " , " + str(kansasCity.population) +
" , " + str(kansasCity.longitude) + " , " + str(kansasCity.latitude) + " , " + str(kansasCity.countryCode))
commands.append("\tSingle Insert SQL: " + sql)
# 2.2 Insert multiple documents into a table
# Currently there is no support for batch inserts with ibm_db
commands.append("#2.2: Insert multiple documents into a table. \n\tCurrently there is no support batch inserts")
sql = "insert into " + tableName + " values(?,?,?,?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, seattle.name)
ibm_db.bind_param(statement, 2, seattle.population)
ibm_db.bind_param(statement, 3, seattle.longitude)
ibm_db.bind_param(statement, 4, seattle.latitude)
ibm_db.bind_param(statement, 5, seattle.countryCode)
ibm_db.execute(statement)
sql = "insert into " + tableName + " values(?,?,?,?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, newYork.name)
ibm_db.bind_param(statement, 2, newYork.population)
ibm_db.bind_param(statement, 3, newYork.longitude)
ibm_db.bind_param(statement, 4, newYork.latitude)
ibm_db.bind_param(statement, 5, newYork.countryCode)
ibm_db.execute(statement)
#Alternate way to insert without bindings
sql = "insert into " + tableName + " values" + tokyo.toSQL()
ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableName + " values" + madrid.toSQL()
ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableName + " values" + melbourne.toSQL()
ibm_db.exec_immediate(conn, sql)
# 3 Queries
commands.append("\n#3 Queries")
# 3.1 Find one document in a table that matches a query condition
commands.append("#3.1 Find one document in a table that matches a query condition")
sql = "select * from " + tableName + " where population > 8000000 and countryCode = 1"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFirst document matching query -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
commands.append("\tQuery By name SQL: " + sql)
# 3.2 Find documents in a table that match a query condition
commands.append("#3.2 Find documents in a table that match a query condition")
sql = "select * from " + tableName + " where population > 8000000 and longitude > 40.0"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFind all documents with name: " + kansasCity.name)
while dictionary != False:
commands.append("\tFound document name -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
commands.append( "\tQuery All By name SQL: " + sql)
# 3.3 Find all documents in a table
commands.append("#3.3 Find all documents in a table")
sql = "select * from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append( "\tFind all documents in table: " + tableName)
while dictionary != False:
commands.append("\tFound document name -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFind All Documents SQL: " + sql)
commands.append("#3.4 Count documents in a table")
sql = "select count(*) from " + tableName + " where longitude < 40.0"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("Documents in table with longitude less than 40.0: " + str(len(dictionary)))
commands.append("#3.5 Order documents in a table")
sql = "select * from " + tableName + " order by population"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
commands.append("\tFound Document -> name: " + str(dictionary[0]) + " population: " + str(dictionary[1]) + " latitude: " +
str(dictionary[2]) + " longitude: " + str(dictionary[3]) + " countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("#3.6 Joins")
tableJoin = "country";
sql = "create table if not exists " + tableJoin + " (countryCode INTEGER, countryName VARCHAR(255))";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (1,\"United States of America\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (44,\"United Kingdom\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (81,\"Japan\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (34,\"Spain\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (61,\"Australia\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = ("select table1.name, table1.population, table1.longitude, table1.latitude, table1.countryCode, table2.countryName from " +
tableName + " table1 inner join " + tableJoin + " table2 on table1.countryCode=table2.countryCode")
stmt =ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
commands.append("\tJoined -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", latitude: " +
str(dictionary[2]) + ", longitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]) + ", countryName: " + str(dictionary[5]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("#3.7 Distinct documents in a table")
sql = "select distinct countryCode from " + tableName + " where longitude > 40.0"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("Distinct countryCodes of documents in table with longitude greater than 40.0: ")
while dictionary != False:
commands.append("\tJoined -> countryCode: " + str(dictionary[0]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("#3.8 Projection Clause")
sql = "select distinct name, countryCode from " + tableName + " where population > 8000000"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("Projection of name and countryCode where population is greater than 8000000: ")
while dictionary != False:
commands.append("\tJoined -> name: " + str(dictionary[0]) + ", countryCode: " + str(dictionary[1]))
dictionary = ibm_db.fetch_both(stmt)
# 4 Update documents in a table
commands.append("\n#4 Update documents in a table")
sql = "update " + tableName + " set countryCode = ? where name = ?"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, 999)
ibm_db.bind_param(statement, 2, seattle.name)
ibm_db.execute(statement)
commands.append( "\tDocument to update: " + seattle.name)
commands.append("\tUpdate By name SQL: " + sql)
# 5 Delete documents in a table
commands.append("\n#5 Delete documents in a table")
sql = "delete from " + tableName + " where name like '" + newYork.name + "'"
ibm_db.exec_immediate(conn, sql)
commands.append("\tDelete documents with name: " + newYork.name)
commands.append("\tDelete By name SQL: " + sql)
commands.append("\n#6 Transactions")
ibm_db.autocommit(conn, False)
sql = "insert into " + tableName + " values" + sydney.toSQL()
ibm_db.exec_immediate(conn, sql)
ibm_db.commit(conn)
sql = "update " + tableName + " set countryCode = 998 where name = 'Seattle'"
ibm_db.exec_immediate(conn, sql)
ibm_db.rollback(conn)
ibm_db.autocommit(conn, True)
commands.append( "\tFind all documents in table: " + tableName)
sql = "select * from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
commands.append("\tFound document name -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("\n#7 Commands")
commands.append("\n#7.1 Count")
sql = "select count(*) from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("Number of documents in table " + str(len(dictionary)))
# 8 Drop a table
commands.append("\n#8 Drop a table")
sql = "drop table " + tableName;
ibm_db.exec_immediate(conn, sql)
sql = "drop table " + tableJoin;
ibm_db.exec_immediate(conn, sql)
commands.append("\tDrop table: " + tableName)
commands.append("\tDrop Table SQL: " + sql)
ibm_db.close(conn)
commands.append("\nConnection closed")
return commands
def run_test_decimal(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
drop = "DROP TABLE STOCKSHARE"
try:
result = ibm_db.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 = ibm_db.exec_immediate(conn, create)
# Insert Directly
insert = "INSERT INTO STOCKSHARE (id, company, stockshare) VALUES (10, 'Megadeth', 100.002)"
result = ibm_db.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 = ibm_db.prepare(conn,insert)
if stmt:
for company in stockprice:
result = ibm_db.execute(stmt,company)
id = 70
company = 'Nirvana'
stockshare = Decimal("100.1234")
try:
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, company)
ibm_db.bind_param(stmt, 3, stockshare)
error = ibm_db.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] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both( stmt )
while ( data ):
print("%s : %s : %s\n" % (data[0], data[1], data[2]))
data = ibm_db.fetch_both( stmt )
try:
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
ibm_db.execute(stmt)
rc = ibm_db.fetch_row(stmt, -1)
print("Fetch Row -1:%s " %str(rc))
except:
print("Requested row number must be a positive value")
ibm_db.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.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
def run_test_decfloat(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
serverinfo = ibm_db.server_info( conn )
drop = "DROP TABLE STOCKPRICE"
try:
result = ibm_db.exec_immediate(conn,drop)
except:
pass
# Create the table stockprice
if (serverinfo.DBMS_NAME[0:3] == 'IDS'):
create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECIMAL(16))"
else:
create = "CREATE TABLE STOCKPRICE (id SMALLINT NOT NULL, company VARCHAR(30), stockshare DECIMAL(7,2), stockprice DECFLOAT(16))"
result = ibm_db.exec_immediate(conn, create)
# Insert Directly
insert = "INSERT INTO STOCKPRICE (id, company, stockshare, stockprice) VALUES (10,'Megadeth', 100.002, 990.356736488388374888532323)"
result = ibm_db.exec_immediate(conn, insert)
# Prepare and Insert in the stockprice table
stockprice = (\
(20, "Zaral", 102.205, "100.234"),\
(30, "Megabyte", 98.65, "1002.112"),\
(40, "Visarsoft", 123.34, "1652.345"),\
(50, "Mailersoft", 134.22, "1643.126"),\
(60, "Kaerci", 100.97, "9876.765")\
)
insert = 'INSERT INTO STOCKPRICE (id, company, stockshare,stockprice) VALUES (?,?,?,?)'
stmt = ibm_db.prepare(conn,insert)
if stmt:
for company in stockprice:
result = ibm_db.execute(stmt,company)
id = 70
company = 'Nirvana'
stockshare = 100.1234
stockprice = "100.567"
try:
ibm_db.bind_param(stmt, 1, id)
ibm_db.bind_param(stmt, 2, company)
ibm_db.bind_param(stmt, 3, stockshare)
ibm_db.bind_param(stmt, 4, stockprice)
error = ibm_db.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 STOCKPRICE ORDER BY id'
if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
else:
stmt = ibm_db.prepare(conn, query)
ibm_db.execute(stmt)
data = ibm_db.fetch_both( stmt )
while ( data ):
print("%s : %s : %s : %s\n" % (data[0], data[1], data[2], data[3]))
data = ibm_db.fetch_both( stmt )
try:
stmt = ibm_db.prepare(conn, query, {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
ibm_db.execute(stmt)
rc = ibm_db.fetch_row(stmt, -1)
print("Fetch Row -1:%s " %str(rc))
except:
print("Requested row number must be a positive value")
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_000(self):
# Make a connection
conn = ibm_db.connect(config.database, config.user, config.password)
# Get the server type
server = ibm_db.server_info( conn )
# Drop the animal table, in case it exists
drop = 'DROP TABLE animals'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the animal table
create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))'
result = ibm_db.exec_immediate(conn, create)
# 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 = ibm_db.prepare(conn, insert)
if stmt:
for animal in animals:
result = ibm_db.execute(stmt, animal)
# Drop the test view, in case it exists
drop = 'DROP VIEW anime_cat'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create test view
ibm_db.exec_immediate(conn, """CREATE VIEW anime_cat AS
SELECT name, breed FROM animals
WHERE id = 0""")
# Drop the animal_pics table
drop = 'DROP TABLE animal_pics'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the animal_pics table
create = 'CREATE TABLE animal_pics (name VARCHAR(32), picture BLOB)'
result = ibm_db.exec_immediate(conn, create)
# Populate the view table
animals = (\
('Spook', 'spook.png'),\
('Helmut', 'pic1.jpg'),\
)
insert = 'INSERT INTO animal_pics (name, picture) VALUES (?, ?)'
stmt = ibm_db.prepare(conn, insert)
if (not stmt):
print "Attempt to prepare statement failed."
return 0
for animal in animals:
name = animal[0]
fileHandle = open(os.path.dirname(os.path.abspath(__file__)) + '/' + animal[1], 'rb')
picture = fileHandle.read()
if (not picture):
print "Could not retrieve picture '%s'." % animal[1]
return 0
ibm_db.bind_param(stmt, 1, name, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 2, picture, ibm_db.SQL_PARAM_INPUT)
result = ibm_db.execute(stmt)
# Drop the department table, in case it exists
drop = 'DROP TABLE department'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the department table
create = 'CREATE TABLE department (deptno CHAR(3) NOT NULL, deptname VARCHAR(29) NOT NULL, mgrno CHAR(6), admrdept CHAR(3) NOT NULL, location CHAR(16))'
result = ibm_db.exec_immediate(conn, create)
# Populate the department table
department = (\
('A00', 'SPIFFY COMPUTER SERVICE DIV.', '000010', 'A00', None),\
('B01', 'PLANNING', '000020', 'A00', None),\
('C01', 'INFORMATION CENTER', '000030', 'A00', None),\
('D01', 'DEVELOPMENT CENTER', None, 'A00', None),\
('D11', 'MANUFACTURING SYSTEMS', '000060', 'D01', None),\
('D21', 'ADMINISTRATION SYSTEMS', '000070', 'D01', None),\
('E01', 'SUPPORT SERVICES', '000050', 'A00', None),\
('E11', 'OPERATIONS', '000090', 'E01', None),\
('E21', 'SOFTWARE SUPPORT', '000100', 'E01', None)\
)
insert = 'INSERT INTO department (deptno, deptname, mgrno, admrdept, location) VALUES (?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for dept in department:
result = ibm_db.execute(stmt, dept)
# Drop the emp_act table, in case it exists
drop = 'DROP TABLE emp_act'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the emp_act table
create = 'CREATE TABLE emp_act (empno CHAR(6) NOT NULL, projno CHAR(6) NOT NULL, actno SMALLINT NOT NULL, emptime DECIMAL(5,2), emstdate DATE, emendate DATE)'
result = ibm_db.exec_immediate(conn, create)
# Populate the emp_act table
emp_act = (\
('000010', 'MA2100', 10, 0.50, '1982-01-01', '1982-11-01'),\
('000010', 'MA2110', 10, 1.00, '1982-01-01', '1983-02-01'),\
('000010', 'AD3100', 10, 0.50, '1982-01-01', '1982-07-01'),\
('000020', 'PL2100', 30, 1.00, '1982-01-01', '1982-09-15'),\
('000030', 'IF1000', 10, 0.50, '1982-06-01', '1983-01-01'),\
('000030', 'IF2000', 10, 0.50, '1982-01-01', '1983-01-01'),\
('000050', 'OP1000', 10, 0.25, '1982-01-01', '1983-02-01'),\
('000050', 'OP2010', 10, 0.75, '1982-01-01', '1983-02-01'),\
('000070', 'AD3110', 10, 1.00, '1982-01-01', '1983-02-01'),\
('000090', 'OP1010', 10, 1.00, '1982-01-01', '1983-02-01'),\
('000100', 'OP2010', 10, 1.00, '1982-01-01', '1983-02-01'),\
('000110', 'MA2100', 20, 1.00, '1982-01-01', '1982-03-01'),\
('000130', 'IF1000', 90, 1.00, '1982-01-01', '1982-10-01'),\
('000130', 'IF1000', 100, 0.50, '1982-10-01', '1983-01-01'),\
('000140', 'IF1000', 90, 0.50, '1982-10-01', '1983-01-01'),\
('000140', 'IF2000', 100, 1.00, '1982-01-01', '1982-03-01'),\
('000140', 'IF2000', 100, 0.50, '1982-03-01', '1982-07-01'),\
('000140', 'IF2000', 110, 0.50, '1982-03-01', '1982-07-01'),\
('000140', 'IF2000', 110, 0.50, '1982-10-01', '1983-01-01'),\
('000150', 'MA2112', 60, 1.00, '1982-01-01', '1982-07-15'),\
('000150', 'MA2112', 180, 1.00, '1982-07-15', '1983-02-01'),\
('000160', 'MA2113', 60, 1.00, '1982-07-15', '1983-02-01'),\
('000170', 'MA2112', 60, 1.00, '1982-01-01', '1983-06-01'),\
('000170', 'MA2112', 70, 1.00, '1982-06-01', '1983-02-01'),\
('000170', 'MA2113', 80, 1.00, '1982-01-01', '1983-02-01'),\
('000180', 'MA2113', 70, 1.00, '1982-04-01', '1982-06-15'),\
('000190', 'MA2112', 70, 1.00, '1982-02-01', '1982-10-01'),\
('000190', 'MA2112', 80, 1.00, '1982-10-01', '1983-10-01'),\
('000200', 'MA2111', 50, 1.00, '1982-01-01', '1982-06-15'),\
('000200', 'MA2111', 60, 1.00, '1982-06-15', '1983-02-01'),\
('000210', 'MA2113', 80, 0.50, '1982-10-01', '1983-02-01'),\
('000210', 'MA2113', 180, 0.50, '1982-10-01', '1983-02-01'),\
('000220', 'MA2111', 40, 1.00, '1982-01-01', '1983-02-01'),\
('000230', 'AD3111', 60, 1.00, '1982-01-01', '1982-03-15'),\
('000230', 'AD3111', 60, 0.50, '1982-03-15', '1982-04-15'),\
('000230', 'AD3111', 70, 0.50, '1982-03-15', '1982-10-15'),\
('000230', 'AD3111', 80, 0.50, '1982-04-15', '1982-10-15'),\
('000230', 'AD3111', 180, 1.00, '1982-10-15', '1983-01-01'),\
('000240', 'AD3111', 70, 1.00, '1982-02-15', '1982-09-15'),\
('000240', 'AD3111', 80, 1.00, '1982-09-15', '1983-01-01'),\
('000250', 'AD3112', 60, 1.00, '1982-01-01', '1982-02-01'),\
('000250', 'AD3112', 60, 0.50, '1982-02-01', '1982-03-15'),\
('000250', 'AD3112', 60, 0.50, '1982-12-01', '1983-01-01'),\
('000250', 'AD3112', 60, 1.00, '1983-01-01', '1983-02-01'),\
('000250', 'AD3112', 70, 0.50, '1982-02-01', '1982-03-15'),\
('000250', 'AD3112', 70, 1.00, '1982-03-15', '1982-08-15'),\
('000250', 'AD3112', 70, 0.25, '1982-08-15', '1982-10-15'),\
('000250', 'AD3112', 80, 0.25, '1982-08-15', '1982-10-15'),\
('000250', 'AD3112', 80, 0.50, '1982-10-15', '1982-12-01'),\
('000250', 'AD3112', 180, 0.50, '1982-08-15', '1983-01-01'),\
('000260', 'AD3113', 70, 0.50, '1982-06-15', '1982-07-01'),\
('000260', 'AD3113', 70, 1.00, '1982-07-01', '1983-02-01'),\
('000260', 'AD3113', 80, 1.00, '1982-01-01', '1982-03-01'),\
('000260', 'AD3113', 80, 0.50, '1982-03-01', '1982-04-15'),\
('000260', 'AD3113', 180, 0.50, '1982-03-01', '1982-04-15'),\
('000260', 'AD3113', 180, 1.00, '1982-04-15', '1982-06-01'),\
('000260', 'AD3113', 180, 0.50, '1982-06-01', '1982-07-01'),\
('000270', 'AD3113', 60, 0.50, '1982-03-01', '1982-04-01'),\
('000270', 'AD3113', 60, 1.00, '1982-04-01', '1982-09-01'),\
('000270', 'AD3113', 60, 0.25, '1982-09-01', '1982-10-15'),\
('000270', 'AD3113', 70, 0.75, '1982-09-01', '1982-10-15'),\
('000270', 'AD3113', 70, 1.00, '1982-10-15', '1983-02-01'),\
('000270', 'AD3113', 80, 1.00, '1982-01-01', '1982-03-01'),\
('000270', 'AD3113', 80, 0.50, '1982-03-01', '1982-04-01'),\
('000280', 'OP1010', 130, 1.00, '1982-01-01', '1983-02-01'),\
('000290', 'OP1010', 130, 1.00, '1982-01-01', '1983-02-01'),\
('000300', 'OP1010', 130, 1.00, '1982-01-01', '1983-02-01'),\
('000310', 'OP1010', 130, 1.00, '1982-01-01', '1983-02-01'),\
('000320', 'OP2011', 140, 0.75, '1982-01-01', '1983-02-01'),\
('000320', 'OP2011', 150, 0.25, '1982-01-01', '1983-02-01'),\
('000330', 'OP2012', 140, 0.25, '1982-01-01', '1983-02-01'),\
('000330', 'OP2012', 160, 0.75, '1982-01-01', '1983-02-01'),\
('000340', 'OP2013', 140, 0.50, '1982-01-01', '1983-02-01'),\
('000340', 'OP2013', 170, 0.50, '1982-01-01', '1983-02-01'),\
('000020', 'PL2100', 30, 1.00, '1982-01-01', '1982-09-15')\
)
insert = 'INSERT INTO emp_act (empno, projno, actno, emptime, emstdate, emendate) VALUES (?, ?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for emp in emp_act:
result = ibm_db.execute(stmt, emp)
# Drop the employee table, in case it exists
drop = 'DROP TABLE employee'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the employee table
create = 'CREATE TABLE employee (empno CHAR(6) NOT NULL, firstnme VARCHAR(12) NOT NULL, midinit CHAR(1) NOT NULL, lastname VARCHAR(15) NOT NULL, workdept CHAR(3), phoneno CHAR(4), hiredate DATE, job CHAR(8), edlevel SMALLINT NOT NULL, sex CHAR(1), birthdate DATE, salary DECIMAL(9,2), bonus DECIMAL(9,2), comm DECIMAL(9,2))'
result = ibm_db.exec_immediate(conn, create)
# Populate the employee table
employee = (
('000010', 'CHRISTINE', 'I', 'HAAS', 'A00', '3978', '1965-01-01', 'PRES', 18, 'F', '1933-08-24', 52750.00, 1000, 4220),
('000020', 'MICHAEL', 'L', 'THOMPSON', 'B01', '3476', '1973-10-10', 'MANAGER', 18, 'M' ,'1948-02-02', 41250.00, 800, 3300),
('000030', 'SALLY', 'A', 'KWAN', 'C01', '4738', '1975-04-05', 'MANAGER', 20, 'F' ,'1941-05-11', 38250.00, 800, 3060),
('000050', 'JOHN', 'B', 'GEYER', 'E01', '6789', '1949-08-17', 'MANAGER', 16, 'M' ,'1925-09-15', 40175.00, 800, 3214),
('000060', 'IRVING', 'F', 'STERN', 'D11', '6423', '1973-09-14', 'MANAGER', 16, 'M' ,'1945-07-07', 32250.00, 500, 2580),
('000070', 'EVA', 'D', 'PULASKI', 'D21', '7831', '1980-09-30', 'MANAGER', 16, 'F' ,'1953-05-26', 36170.00, 700, 2893),
('000090', 'EILEEN', 'W', 'HENDERSON', 'E11', '5498', '1970-08-15', 'MANAGER', 16, 'F' ,'1941-05-15', 29750.00, 600, 2380),
('000100', 'THEODORE', 'Q', 'SPENSER', 'E21', '0972', '1980-06-19', 'MANAGER', 14, 'M' ,'1956-12-18', 26150.00, 500, 2092),
('000110', 'VINCENZO', 'G', 'LUCCHESSI', 'A00', '3490', '1958-05-16', 'SALESREP', 19, 'M' ,'1929-11-05', 46500.00, 900, 3720),
('000120', 'SEAN', '' , 'OCONNELL', 'A00', '2167', '1963-12-05', 'CLERK', 14, 'M' ,'1942-10-18', 29250.00, 600, 2340),
('000130', 'DOLORES', 'M', 'QUINTANA', 'C01', '4578', '1971-07-28', 'ANALYST', 16, 'F' ,'1925-09-15', 23800.00, 500, 1904),
('000140', 'HEATHER', 'A', 'NICHOLLS', 'C01', '1793', '1976-12-15', 'ANALYST', 18, 'F' ,'1946-01-19', 28420.00, 600, 2274),
('000150', 'BRUCE', '' , 'ADAMSON', 'D11', '4510', '1972-02-12', 'DESIGNER', 16, 'M' ,'1947-05-17', 25280.00, 500, 2022),
('000160', 'ELIZABETH', 'R', 'PIANKA', 'D11', '3782', '1977-10-11', 'DESIGNER', 17, 'F' ,'1955-04-12', 22250.00, 400, 1780),
('000170', 'MASATOSHI', 'J', 'YOSHIMURA', 'D11', '2890', '1978-09-15', 'DESIGNER', 16, 'M' ,'1951-01-05', 24680.00, 500, 1974),
('000180', 'MARILYN', 'S', 'SCOUTTEN', 'D11', '1682', '1973-07-07', 'DESIGNER', 17, 'F' ,'1949-02-21', 21340.00, 500, 1707),
('000190', 'JAMES', 'H', 'WALKER', 'D11', '2986', '1974-07-26', 'DESIGNER', 16, 'M' ,'1952-06-25', 20450.00, 400, 1636),
('000200', 'DAVID', '' , 'BROWN', 'D11', '4501', '1966-03-03', 'DESIGNER', 16, 'M' ,'1941-05-29', 27740.00, 600, 2217),
('000210', 'WILLIAM', 'T', 'JONES', 'D11', '0942', '1979-04-11', 'DESIGNER', 17, 'M' ,'1953-02-23', 18270.00, 400, 1462),
('000220', 'JENNIFER', 'K', 'LUTZ', 'D11', '0672', '1968-08-29', 'DESIGNER', 18, 'F' ,'1948-03-19', 29840.00, 600, 2387),
('000230', 'JAMES', 'J', 'JEFFERSON', 'D21', '2094', '1966-11-21', 'CLERK', 14, 'M' ,'1935-05-30', 22180.00, 400, 1774),
('000240', 'SALVATORE', 'M', 'MARINO', 'D21', '3780', '1979-12-05', 'CLERK', 17, 'M' ,'1954-03-31', 28760.00, 600, 2301),
('000250', 'DANIEL', 'S', 'SMITH', 'D21', '0961', '1969-10-30', 'CLERK', 15, 'M' ,'1939-11-12', 19180.00, 400, 1534),
('000260', 'SYBIL', 'P', 'JOHNSON', 'D21', '8953', '1975-09-11', 'CLERK', 16, 'F' ,'1936-10-05', 17250.00, 300, 1380),
('000270', 'MARIA', 'L', 'PEREZ', 'D21', '9001', '1980-09-30', 'CLERK', 15, 'F' ,'1953-05-26', 27380.00, 500, 2190),
('000280', 'ETHEL', 'R', 'SCHNEIDER', 'E11', '8997', '1967-03-24', 'OPERATOR', 17, 'F' ,'1936-03-28', 26250.00, 500, 2100),
('000290', 'JOHN', 'R', 'PARKER', 'E11', '4502', '1980-05-30', 'OPERATOR', 12, 'M' ,'1946-07-09', 15340.00, 300, 1227),
('000300', 'PHILIP', 'X', 'SMITH', 'E11', '2095', '1972-06-19', 'OPERATOR', 14, 'M' ,'1936-10-27', 17750.00, 400, 1420),
('000310', 'MAUDE', 'F', 'SETRIGHT', 'E11', '3332', '1964-09-12', 'OPERATOR', 12, 'F' ,'1931-04-21', 15900.00, 300, 1272),
('000320', 'RAMLAL', 'V', 'MEHTA', 'E21', '9990', '1965-07-07', 'FIELDREP', 16, 'M' ,'1932-08-11', 19950.00, 400, 1596),
('000330', 'WING', '' , 'LEE', 'E21', '2103', '1976-02-23', 'FIELDREP', 14, 'M' ,'1941-07-18', 25370.00, 500, 2030),
('000340', 'JASON', 'R', 'GOUNOT', 'E21', '5698', '1947-05-05', 'FIELDREP', 16, 'M' ,'1926-05-17', 23840.00, 500, 1907)
)
insert = 'INSERT INTO employee (empno, firstnme, midinit, lastname, workdept, phoneno, hiredate, job, edlevel, sex, birthdate, salary, bonus, comm) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for emp in employee:
result = ibm_db.execute(stmt, emp)
# Drop the emp_photo table, in case it exists
drop = 'DROP TABLE emp_photo'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the emp_photo table
create = 'CREATE TABLE emp_photo (empno CHAR(6) NOT NULL, photo_format VARCHAR(10) NOT NULL, picture BLOB, PRIMARY KEY(empno, photo_format))'
try:
result = ibm_db.exec_immediate(conn, create)
except:
pass
# Populate the emp_photo table
emp_photo = (\
('000130', 'jpg', 'pic1.jpg'),\
('000130', 'png', 'spook.png'),\
('000140', 'jpg', 'pic1.jpg'),\
('000140', 'png', 'spook.png'),\
('000150', 'jpg', 'pic1.jpg'),\
('000150', 'png', 'spook.png'),\
('000190', 'jpg', 'pic1.jpg'),\
('000190', 'png', 'spook.png')\
)
insert = 'INSERT INTO emp_photo (empno, photo_format, picture) VALUES (?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for photo in emp_photo:
empno = photo[0]
photo_format = photo[1]
fileHandler = open(os.path.dirname(os.path.abspath(__file__)) + '/' + photo[2], 'rb')
picture = fileHandler.read()
ibm_db.bind_param(stmt, 1, empno, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 2, photo_format, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 3, picture, ibm_db.SQL_PARAM_INPUT)
result = ibm_db.execute(stmt)
# Drop the org table, in case it exists
drop = 'DROP TABLE org'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the org table
create = 'CREATE TABLE org (deptnumb SMALLINT NOT NULL, deptname VARCHAR(14), manager SMALLINT, division VARCHAR(10), location VARCHAR(13))'
result = ibm_db.exec_immediate(conn, create)
# Populate the org table
org = (\
(10, 'Head Office', 160, 'Corporate', 'New York'),\
(15, 'New England', 50, 'Eastern', 'Boston'),\
(20, 'Mid Atlantic', 10, 'Eastern', 'Washington'),\
(38, 'South Atlantic', 30, 'Eastern', 'Atlanta'),\
(42, 'Great Lakes', 100, 'Midwest', 'Chicago'),\
(51, 'Plains', 140, 'Midwest', 'Dallas'),\
(66, 'Pacific', 270, 'Western', 'San Francisco'),\
(84, 'Mountain', 290, 'Western', 'Denver')\
)
insert = 'INSERT INTO org (deptnumb, deptname, manager, division, location) VALUES (?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for orgpart in org:
result = ibm_db.execute(stmt, orgpart)
# Drop the project table, in case it exists
drop = 'DROP TABLE project'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the project table
create = 'CREATE TABLE project (projno CHAR(6) NOT NULL, projname VARCHAR(24) NOT NULL, deptno CHAR(3) NOT NULL, respemp CHAR(6) NOT NULL, prstaff DECIMAL(5,2), prstdate DATE, prendate DATE, majproj CHAR(6))'
result = ibm_db.exec_immediate(conn, create)
# Populate the project table
project = (\
('AD3100', 'ADMIN SERVICES', 'D01', '000010', 6.5, '1982-01-01', '1983-02-01', ''),\
('AD3110', 'GENERAL ADMIN SYSTEMS', 'D21', '000070', 6, '1982-01-01', '1983-02-01', 'AD3100'),\
('AD3111', 'PAYROLL PROGRAMMING', 'D21', '000230', 2, '1982-01-01', '1983-02-01', 'AD3110'),\
('AD3112', 'PERSONNEL PROGRAMMING', 'D21', '000250', 1, '1982-01-01', '1983-02-01', 'AD3110'),\
('AD3113', 'ACCOUNT PROGRAMMING', 'D21', '000270', 2, '1982-01-01', '1983-02-01', 'AD3110'),\
('IF1000', 'QUERY SERVICES', 'C01', '000030', 2, '1982-01-01', '1983-02-01', None),\
('IF2000', 'USER EDUCATION', 'C01', '000030', 1, '1982-01-01', '1983-02-01', None),\
('MA2100', 'WELD LINE AUTOMATION', 'D01', '000010', 12, '1982-01-01', '1983-02-01', None),\
('MA2110', 'W L PROGRAMMING', 'D11', '000060', 9, '1982-01-01', '1983-02-01', 'MA2100'),\
('MA2111', 'W L PROGRAM DESIGN', 'D11', '000220', 2, '1982-01-01', '1982-12-01', 'MA2110'),\
('MA2112', 'W L ROBOT DESIGN', 'D11', '000150', 3, '1982-01-01', '1982-12-01', 'MA2110'),\
('MA2113', 'W L PROD CONT PROGS', 'D11', '000160', 3, '1982-02-15', '1982-12-01', 'MA2110'),\
('OP1000', 'OPERATION SUPPORT', 'E01', '000050', 6, '1982-01-01', '1983-02-01', None),\
('OP1010', 'OPERATION', 'E11', '000090', 5, '1982-01-01', '1983-02-01', 'OP1000'),\
('OP2000', 'GEN SYSTEMS SERVICES', 'E01', '000050', 5, '1982-01-01', '1983-02-01', None),\
('OP2010', 'SYSTEMS SUPPORT', 'E21', '000100', 4, '1982-01-01', '1983-02-01', 'OP2000'),\
('OP2011', 'SCP SYSTEMS SUPPORT', 'E21', '000320', 1, '1982-01-01', '1983-02-01', 'OP2010'),\
('OP2012', 'APPLICATIONS SUPPORT', 'E21', '000330', 1, '1982-01-01', '1983-02-01', 'OP2010'),\
('OP2013', 'DB/DC SUPPORT', 'E21', '000340', 1, '1982-01-01', '1983-02-01', 'OP2010'),\
('PL2100', 'WELD LINE PLANNING', 'B01', '000020', 1, '1982-01-01', '1982-09-15', 'MA2100')\
)
insert = 'INSERT INTO project (projno, projname, deptno, respemp, prstaff, prstdate, prendate, majproj) VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for proj in project:
result = ibm_db.execute(stmt, proj)
# Drop the sales table, in case it exists
drop = 'DROP TABLE sales'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the sales table
create = 'CREATE TABLE sales (sales_date DATE, sales_person VARCHAR(15), region VARCHAR(15), sales INT)'
result = ibm_db.exec_immediate(conn, create)
# Populate the sales table
sales = (\
('1995-12-31', 'LUCCHESSI', 'Ontario-South', 1),\
('1995-12-31', 'LEE', 'Ontario-South', 3),\
('1995-12-31', 'LEE', 'Quebec', 1),\
('1995-12-31', 'LEE', 'Manitoba', 2),\
('1995-12-31', 'GOUNOT', 'Quebec', 1),\
('1996-03-29', 'LUCCHESSI', 'Ontario-South', 3),\
('1996-03-29', 'LUCCHESSI', 'Quebec', 1),\
('1996-03-29', 'LEE', 'Ontario-South', 2),\
('1996-03-29', 'LEE', 'Ontario-North', 2),\
('1996-03-29', 'LEE', 'Quebec', 3),\
('1996-03-29', 'LEE', 'Manitoba', 5),\
('1996-03-29', 'GOUNOT', 'Ontario-South', 3),\
('1996-03-29', 'GOUNOT', 'Quebec', 1),\
('1996-03-29', 'GOUNOT', 'Manitoba', 7),\
('1996-03-30', 'LUCCHESSI', 'Ontario-South', 1),\
('1996-03-30', 'LUCCHESSI', 'Quebec', 2),\
('1996-03-30', 'LUCCHESSI', 'Manitoba', 1),\
('1996-03-30', 'LEE', 'Ontario-South', 7),\
('1996-03-30', 'LEE', 'Ontario-North', 3),\
('1996-03-30', 'LEE', 'Quebec', 7),\
('1996-03-30', 'LEE', 'Manitoba', 4),\
('1996-03-30', 'GOUNOT', 'Ontario-South', 2),\
('1996-03-30', 'GOUNOT', 'Quebec', 18),\
('1996-03-30', 'GOUNOT', 'Manitoba', 1),\
('1996-03-31', 'LUCCHESSI', 'Manitoba', 1),\
('1996-03-31', 'LEE', 'Ontario-South', 14),\
('1996-03-31', 'LEE', 'Ontario-North', 3),\
('1996-03-31', 'LEE', 'Quebec', 7),\
('1996-03-31', 'LEE', 'Manitoba', 3),\
('1996-03-31', 'GOUNOT', 'Ontario-South', 2),\
('1996-03-31', 'GOUNOT', 'Quebec', 1),\
('1996-04-01', 'LUCCHESSI', 'Ontario-South', 3),\
('1996-04-01', 'LUCCHESSI', 'Manitoba', 1),\
('1996-04-01', 'LEE', 'Ontario-South', 8),\
('1996-04-01', 'LEE', 'Ontario-North', None),\
('1996-04-01', 'LEE', 'Quebec', 8),\
('1996-04-01', 'LEE', 'Manitoba', 9),\
('1996-04-01', 'GOUNOT', 'Ontario-South', 3),\
('1996-04-01', 'GOUNOT', 'Ontario-North', 1),\
('1996-04-01', 'GOUNOT', 'Quebec', 3),\
('1996-04-01', 'GOUNOT', 'Manitoba', 7)\
)
insert = 'INSERT INTO sales (sales_date, sales_person, region, sales) VALUES (?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for sale in sales:
result = ibm_db.execute(stmt, sale)
# Drop the stored procedure, in case it exists
drop = 'DROP PROCEDURE match_animal'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the stored procedure
if (server.DBMS_NAME[0:3] == 'IDS'):
result = ibm_db.exec_immediate(conn, """
CREATE PROCEDURE match_animal(first_name VARCHAR(128), INOUT second_name VARCHAR(128), OUT animal_weight DOUBLE PRECISION )
DEFINE match_name INT;
LET match_name = 0;
FOREACH c1 FOR
SELECT COUNT(*) INTO match_name FROM animals
WHERE name IN (second_name)
IF (match_name > 0)
THEN LET second_name = 'TRUE';
END IF;
END FOREACH;
FOREACH c2 FOR
SELECT SUM(weight) INTO animal_weight FROM animals
WHERE name in (first_name, second_name)
END FOREACH;
END PROCEDURE;""")
else:
result = ibm_db.exec_immediate(conn, """
CREATE PROCEDURE match_animal(IN first_name VARCHAR(128), INOUT second_name VARCHAR(128), OUT animal_weight DOUBLE)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE match_name INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT COUNT(*) FROM animals
WHERE name IN (second_name);
DECLARE c2 CURSOR FOR
SELECT SUM(weight) FROM animals
WHERE name in (first_name, second_name);
DECLARE c3 CURSOR WITH RETURN FOR
SELECT name, breed, weight FROM animals
WHERE name BETWEEN first_name AND second_name
ORDER BY name;
OPEN c1;
FETCH c1 INTO match_name;
IF (match_name > 0)
THEN SET second_name = 'TRUE';
END IF;
CLOSE c1;
OPEN c2;
FETCH c2 INTO animal_weight;
CLOSE c2;
OPEN c3;
END""")
result = None
# Drop the staff table, in case it exists
drop = 'DROP TABLE staff'
try:
result = ibm_db.exec_immediate(conn, drop)
except:
pass
# Create the staff table
create = 'CREATE TABLE staff (id SMALLINT NOT NULL, name VARCHAR(9), dept SMALLINT, job CHAR(5), years SMALLINT, salary DECIMAL(7,2), comm DECIMAL(7,2))';
result = ibm_db.exec_immediate(conn, create)
# Populate the staff table
staff = (\
(10, 'Sanders', 20, 'Mgr', 7, 18357.50, None),\
(20, 'Pernal', 20, 'Sales', 8, 18171.25, 612.45),\
(30, 'Marenghi', 38, 'Mgr', 5, 17506.75, None),\
(40, 'OBrien', 38, 'Sales', 6, 18006.00, 846.55),\
(50, 'Hanes', 15, 'Mgr', 10, 20659.80, None),\
(60, 'Quigley', 38, 'Sales', None, 16808.30, 650.25),\
(70, 'Rothman', 15, 'Sales', 7, 16502.83, 1152.00),\
(80, 'James', 20, 'Clerk', None, 13504.60, 128.20),\
(90, 'Koonitz', 42, 'Sales', 6, 18001.75, 1386.70),\
(100, 'Plotz', 42, 'Mgr' , 7, 18352.80, None),\
(110, 'Ngan', 15, 'Clerk', 5, 12508.20, 206.60),\
(120, 'Naughton', 38, 'Clerk', None, 12954.75, 180.00),\
(130, 'Yamaguchi', 42, 'Clerk', 6, 10505.90, 75.60),\
(140, 'Fraye', 51, 'Mgr' , 6, 21150.00, None),\
(150, 'Williams', 51, 'Sales', 6, 19456.50, 637.65),\
(160, 'Molinare', 10, 'Mgr' , 7, 22959.20, None),\
(170, 'Kermisch', 15, 'Clerk', 4, 12258.50, 110.10),\
(180, 'Abrahams', 38, 'Clerk', 3, 12009.75, 236.50),\
(190, 'Sneider', 20, 'Clerk', 8, 14252.75, 126.50),\
(200, 'Scoutten', 42, 'Clerk', None, 11508.60, 84.20),\
(210, 'Lu', 10, 'Mgr' , 10, 20010.00, None),\
(220, 'Smith', 51, 'Sales', 7, 17654.50, 992.80),\
(230, 'Lundquist', 51, 'Clerk', 3, 13369.80, 189.65),\
(240, 'Daniels', 10, 'Mgr' , 5, 19260.25, None),\
(250, 'Wheeler', 51, 'Clerk', 6, 14460.00, 513.30),\
(260, 'Jones', 10, 'Mgr' , 12, 21234.00, None),\
(270, 'Lea', 66, 'Mgr' , 9, 18555.50, None),\
(280, 'Wilson', 66, 'Sales', 9, 18674.50, 811.50),\
(290, 'Quill', 84, 'Mgr' , 10, 19818.00, None),\
(300, 'Davis', 84, 'Sales', 5, 15454.50, 806.10),\
(310, 'Graham', 66, 'Sales', 13, 21000.00, 200.30),\
(320, 'Gonzales', 66, 'Sales', 4, 16858.20, 844.00),\
(330, 'Burke', 66, 'Clerk', 1, 10988.00, 55.50),\
(340, 'Edwards', 84, 'Sales', 7, 17844.00, 1285.00),\
(350, 'Gafney', 84, 'Clerk', 5, 13030.50, 188.00)\
)
insert = 'INSERT INTO staff (id, name, dept, job, years, salary, comm) VALUES (?, ?, ?, ?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for emp in staff:
result = ibm_db.execute(stmt, emp)
try:
result = ibm_db.exec_immediate(conn, 'DROP TABLE t_string')
except:
pass
result = ibm_db.exec_immediate(conn, 'CREATE TABLE t_string(a INTEGER, b DOUBLE PRECISION, c VARCHAR(100))')
print "Preperation complete"
def bind(stmt, num_params, params):
for i in range(1,num_params+1):
ibm_db.bind_param(stmt,i,params[i-1])
def doEverything():
commands = []
# connect to database
conn = ibm_db.connect(url, '', '')
commands.append("Connected to " + url)
# set up variables and data
tableName = "pythonDRDATest"
user1 = DataFormat("test1", 1)
user2 = DataFormat("test2", 2)
user3 = DataFormat("test3", 3)
# 1 Create table
commands.append("\n#1 Create table")
sql = "create table " + tableName + "(name varchar(255), value integer)"
ibm_db.exec_immediate(conn, sql)
commands.append( "\tCreate a table named: " + tableName)
commands.append("\tCreate Table SQL: " + sql)
# 2 Inserts
commands.append("\n#2 Inserts")
# 2.1 Insert a single document into a table
commands.append("#2.1 Insert a single document into a table")
sql = "insert into " + tableName + " values(?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, user1.name)
ibm_db.bind_param(statement, 2, user1.value)
ibm_db.execute(statement)
commands.append("\tCreate Document -> " + user1.name + " : " + str(user1.value))
commands.append("\tSingle Insert SQL: " + sql)
sql = "insert into " + tableName + " values(?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, user2.name)
ibm_db.bind_param(statement, 2, user2.value)
ibm_db.execute(statement)
commands.append("\tCreate Document -> " + user2.name + " : " + str(user2.value))
commands.append("\tSingle Insert SQL: " + sql)
sql = "insert into " + tableName + " values(?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, user3.name)
ibm_db.bind_param(statement, 2, user3.value)
ibm_db.execute(statement)
commands.append("\tCreate Document -> " + user3.name + " : " + str(user3.value))
commands.append("\tSingle Insert SQL: " + sql)
# 2.2 Insert multiple documents into a table
# Currently there is no support for batch inserts with ibm_db
commands.append("#2.2: Insert multiple documents into a table. \n\tCurrently there is no support batch inserts")
# 3 Queries
commands.append("\n#3 Queries")
# 3.1 Find one document in a table that matches a query condition
commands.append("#3.1 Find one document in a table that matches a query condition")
sql = "select * from " + tableName + " where name LIKE '" + user1.name + "'"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFind document with name: " + user1.name)
commands.append("\tFirst document with name -> name: " + str(dictionary[0]) + " value: " + str(dictionary[1]))
commands.append("\tQuery By name SQL: " + sql)
# 3.2 Find documents in a table that match a query condition
commands.append("#3.2 Find documents in a table that match a query condition")
sql = "select * from " + tableName + " where name LIKE '" + user1.name + "'"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFind all documents with name: " + user1.name)
while dictionary != False:
commands.append("\tFound Document -> name: " + str(dictionary[0]) + " value: " + str(dictionary[1]))
dictionary = ibm_db.fetch_both(stmt)
commands.append( "\tQuery All By name SQL: " + sql)
# 3.3 Find all documents in a table
commands.append("#3.3 Find all documents in a table")
sql = "select * from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append( "\tFind all documents in table: " + tableName)
while dictionary != False:
commands.append("\tFound Document -> name: " + str(dictionary[0]) + " value: " + str(dictionary[1]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFind All Documents SQL: " + sql)
# 4 Update documents in a table
commands.append("\n#4 Update documents in a table")
sql = "update " + tableName + " set value = ? where name = ?"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, 4)
ibm_db.bind_param(statement, 2, user2.name)
ibm_db.execute(statement)
commands.append( "\tDocument to update: " + user2.name)
commands.append("\tUpdate By name SQL: " + sql)
# 5 Delete documents in a table
commands.append("\n#5 Delete documents in a table")
sql = "delete from " + tableName + " where name like '" + user1.name + "'"
ibm_db.exec_immediate(conn, sql)
commands.append("\tDelete documents with name: " + user1.name)
commands.append("\tDelete By name SQL: " + sql)
# 6 Drop a table
commands.append("\n#6 Drop a table")
sql = "drop table " + tableName;
ibm_db.exec_immediate(conn, sql)
commands.append("\tDrop table: " + tableName)
commands.append("\tDrop Table SQL: " + sql)
ibm_db.close(conn)
commands.append("\nConnection closed")
return commands