コード例 #1
0
  def run_test_158(self):
    conn = ibm_db.connect(config.database, config.user, config.password)

    server = ibm_db.server_info( conn )
    if (server.DBMS_NAME[0:3] == 'IDS'):
      op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
      ibm_db.set_option(conn, op, 1)

    result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 50")
    
    output = ''
    row = ibm_db.fetch_assoc(result)
    while ( row ):
      output += str(row['ID']) + ', ' + row['NAME'] + ', ' + str(row['DEPT']) + ', ' + row['JOB'] + ', ' + str(row['YEARS']) + ', ' + str(row['SALARY']) + ', ' + str(row['COMM'])
      row = ibm_db.fetch_assoc(result)
      
    result2 = ibm_db.exec_immediate(conn,"SELECT * FROM department WHERE substr(deptno,1,1) in ('A','B','C','D','E')")
    row2 = ibm_db.fetch_assoc(result2)
    while ( row2 ):
        if (row2['MGRNO'] == None): 
            row2['MGRNO'] = ''
        if (row2['LOCATION'] == None): 
            row2['LOCATION'] = ''
        output += str(row2['DEPTNO']) + ', ' + row2['DEPTNAME'] + ', ' + str(row2['MGRNO']) + ', ' + row2['ADMRDEPT'] + ', ' + row2['LOCATION']
        row2 = ibm_db.fetch_assoc(result2)
    
    result3 = ibm_db.exec_immediate(conn,"SELECT * FROM employee WHERE lastname IN ('HAAS','THOMPSON', 'KWAN', 'GEYER', 'STERN', 'PULASKI', 'HENDERSON', 'SPENSER', 'LUCCHESSI', 'OCONNELL', 'QUINTANA', 'NICHOLLS', 'ADAMSON', 'PIANKA', 'YOSHIMURA', 'SCOUTTEN', 'WALKER', 'BROWN', 'JONES', 'LUTZ', 'JEFFERSON', 'MARINO', 'SMITH', 'JOHNSON', 'PEREZ', 'SCHNEIDER', 'PARKER', 'SMITH', 'SETRIGHT', 'MEHTA', 'LEE', 'GOUNOT')")
    row3 = ibm_db.fetch_tuple(result3)
    while ( row3 ):
        output += row3[0] + ', ' + row3[3] + ', ' + row3[5]
        row3=ibm_db.fetch_tuple(result3)
    print(output)
コード例 #2
0
ファイル: sso_init.py プロジェクト: rafoul/pytest
def populate_role(conn, data):    
    db2.execute(conn, '''
        INSERT INTO SSO_ROLES(ROLE_KEY, NAME, ENABLED, DESC, TENANT_KEY, CREATE_TIME) 
        VALUES(?, ?, ?, ?, ?, ?)
    ''', data, commit=False)
    
    ibm_db.exec_immediate(conn, 'ALTER TABLE SSO_ROLES ALTER COLUMN ROLE_KEY RESTART WITH 4000000')
コード例 #3
0
ファイル: main.py プロジェクト: remyb/DistributedDDL
def exec_query(db, query):
  try:
    ibm_db.exec_immediate(db,query)
  except:
    print "[*] The transaction could not be completed:", query# ,ibm_db.stmt_errormsg()
  else:
    print "[*] Transaction complete: ",query
コード例 #4
0
ファイル: sso_init.py プロジェクト: rafoul/pytest
def populate_user(conn, data):    
    db2.execute(conn, '''
        INSERT INTO SSO_USERS(USER_KEY, PASSWORD, USER_NAME, SALT, TENANT_KEY, CREATE_TIME, MODIFY_TIME)
        VALUES(?, ?, ?, ?, ?, ?, ?)
    ''', data, commit=False)
    
    ibm_db.exec_immediate(conn, 'ALTER TABLE SSO_USERS ALTER COLUMN USER_KEY RESTART WITH 1000')
コード例 #5
0
  def run_test_195(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    server = ibm_db.server_info( conn )

    if ((server.DBMS_NAME[0:3] != 'IDS') and (server.DBMS_NAME[0:2] != "AS")):
      drop = 'DROP TABLE test_195'
      try:
        result = ibm_db.exec_immediate(conn, drop)
      except:
        pass
      create = 'CREATE TABLE test_195 (id INTEGER, data XML)'
      result = ibm_db.exec_immediate(conn, create)
    
      insert = "INSERT INTO test_195 values (0, '<TEST><def><xml/></def></TEST>')"
    
      ibm_db.exec_immediate(conn, insert)
    
      sql =  "SELECT data FROM test_195"
      stmt = ibm_db.prepare(conn, sql)
      ibm_db.execute(stmt)
      result = ibm_db.fetch_assoc(stmt)
      while( result ):
        print "Output:", result
	result = ibm_db.fetch_assoc(stmt)
      ibm_db.close(conn)
    else:
      print "Native XML datatype is not supported."
コード例 #6
0
  def run_test_040(self): 
    conn = ibm_db.connect(config.database, config.user, config.password)

    ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
      
    # Drop the test table, in case it exists
    drop = 'DROP TABLE animals'
    try:
      result = ibm_db.exec_immediate(conn, drop)
    except:
      pass
      
    # Create the test table
    create = 'CREATE TABLE animals (id INTEGER, breed VARCHAR(32), name CHAR(16), weight DECIMAL(7,2))'
    result = ibm_db.exec_immediate(conn, create)
      
    insert = "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)"
      
    ibm_db.exec_immediate(conn, insert)
      
    stmt = ibm_db.exec_immediate(conn, "select * from animals")
    
    onerow = ibm_db.fetch_tuple(stmt)
     
    for element in onerow:
      print element

    ibm_db.rollback(conn)
コード例 #7
0
  def run_test_017(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    if conn:
      result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", { ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
      if result:
        rows = ibm_db.num_rows(result)
        print "affected row:", rows
      else:
        print ibm_db.stmt_errormsg()
      result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_CURSOR_TYPE : ibm_db.SQL_CURSOR_FORWARD_ONLY})
      if result:
        rows = ibm_db.num_rows(result)
        print "affected row:", rows
      else:
        print ibm_db.stmt_errormsg()
      result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_ON})
      if result:
        rows = ibm_db.num_rows(result)
        print "affected row:", rows
      else:
        print ibm_db.stmt_errormsg()
      result = ibm_db.exec_immediate(conn,"SELECT * from animals WHERE weight < 10.0", {ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH : ibm_db.SQL_ROWCOUNT_PREFETCH_OFF})
      if result:
        rows = ibm_db.num_rows(result)
        print "affected row:", rows
      else:
        print ibm_db.stmt_errormsg()


      ibm_db.close(conn)
    else:
      print "no connection:", ibm_db.conn_errormsg()
コード例 #8
0
  def run_test_100(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    
    if conn:
      ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)

      stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals ORDER BY breed")
    
      fields1 = ibm_db.num_fields(stmt)
      
      print("int(%d)" % fields1)
      
      stmt = ibm_db.exec_immediate(conn, "SELECT name, breed FROM animals ORDER BY breed")
      fields2 = ibm_db.num_fields(stmt)
      
      print("int(%d)" % fields2)
      
      stmt = ibm_db.exec_immediate(conn, "DELETE FROM animals")
      fields3 = ibm_db.num_fields(stmt)
      
      print("int(%d)" % fields3)
      
      stmt = ibm_db.exec_immediate(conn, "INSERT INTO animals values (0, 'cat', 'Pook', 3.2)")
      fields4 = ibm_db.num_fields(stmt)
        
      print("int(%d)" % fields4)
      
      stmt = ibm_db.exec_immediate(conn, "SELECT name, breed, 'TEST' FROM animals")
      fields5 = ibm_db.num_fields(stmt)
        
      print("int(%d)" % fields5)

      ibm_db.rollback(conn)
    else:
      print("Connection failed.")
コード例 #9
0
 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.")
コード例 #10
0
  def run_test_054(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    serverinfo = ibm_db.server_info( conn )

    stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals")
    val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
    print(val)

    op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_FORWARD_ONLY}
    stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op)
    val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
    print(val)

    if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
      op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN}
    else:
      op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC}
    stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op)
    val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
    print(val)

    op = {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_STATIC}
    stmt = ibm_db.exec_immediate(conn, "SELECT * FROM animals", op)
    val = ibm_db.get_option(stmt, ibm_db.SQL_ATTR_CURSOR_TYPE, 0)
    print(val)
コード例 #11
0
 def run_test_020(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
     
   if conn:
       
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print rows
     
     ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
     ac = ibm_db.autocommit(conn)
     if ac != 0:
       print "Cannot set ibm_db.SQL_AUTOCOMMIT_OFF\nCannot run test"
       #continue 
     
     ibm_db.exec_immediate(conn, "DELETE FROM animals")
     
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print rows
      
     ibm_db.rollback(conn)
      
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print rows
     ibm_db.close(conn)
   else:
     print "Connection failed."
コード例 #12
0
  def run_test_038(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    serverinfo = ibm_db.server_info( conn )

    if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
      result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
    else:
      result = ibm_db.exec_immediate(conn, "SELECT * FROM staff WHERE id < 101")

    row = ibm_db.fetch_row(result)
    while ( row ):
      if (serverinfo.DBMS_NAME[0:3] != 'IDS'):
        result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101", {ibm_db.SQL_ATTR_CURSOR_TYPE: ibm_db.SQL_CURSOR_KEYSET_DRIVEN})
      else:
        result2 = ibm_db.prepare(conn, "SELECT * FROM staff WHERE id < 101")
      ibm_db.execute(result2)
      row2 = ibm_db.fetch_row(result2)
      while ( row2 ):
        print("%s : %s : %s : %s : %s\n" % (ibm_db.result(result2, 0), \
                                            ibm_db.result(result2, 1), \
                                            ibm_db.result(result2, 2), \
                                            ibm_db.result(result2, 3), \
                                            ibm_db.result(result2, 5)))
        row2 = ibm_db.fetch_row(result2)
      row = ibm_db.fetch_row(result)
コード例 #13
0
    def run_test_156(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        server = ibm_db.server_info(conn)
        if (server.DBMS_NAME[0:3] == 'IDS'):
            op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
            ibm_db.set_option(conn, op, 0)

        result = ibm_db.exec_immediate(conn, "select * from staff")

        row = ibm_db.fetch_assoc(result)
        count = 1
        while (row):
            if (row['YEARS'] == None):
                row['YEARS'] = ''
            if (row['COMM'] == None):
                row['COMM'] = ''
            print row['ID'], row['NAME'], row['JOB'], row['YEARS'], row[
                'SALARY'], row['COMM']
            row = ibm_db.fetch_assoc(result)

        result2 = ibm_db.exec_immediate(
            conn,
            "select * from department where substr(deptno,1,1) in ('A','B','C','D','E')"
        )
        row2 = ibm_db.fetch_assoc(result2)
        while (row2):
            if (row2['MGRNO'] == None):
                row2['MGRNO'] = ''
            print row2['DEPTNO'], row2['DEPTNAME'], row2['MGRNO'], row2[
                'ADMRDEPT'], row2['LOCATION']
            row2 = ibm_db.fetch_assoc(result2)
コード例 #14
0
ファイル: profwrites2.py プロジェクト: svrist/itu
def write(q,data):
    # initialize vars
    write_str = q
    # Connect to DB
    conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
    if conn is None: raise Usage(ibm_db.conn_errormsg())
    ibm_db.autocommit(conn,ibm_db.SQL_AUTOCOMMIT_OFF)
    # Set isolation level
    ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
    if TL:
        ret = ibm_db.exec_immediate(conn, "LOCK TABLE accounts in exclusive mode")
    # Prepare Statements
    write_stmt = ibm_db.prepare(conn, write_str)
    if (write_stmt == False): raise Usage("Failed to prepare write statement")
    for t in data:
        # execute insertN statement
        if (WRITE_MODE == 'insertN'):
            if ibm_db.execute(write_stmt, t) == False:
                raise Usage("Failed to execute insertN statement")
        elif (WRITE_MODE == 'updateN'):
            l = list(t)
            u = [l[j] for j in range(len(l)) if j in ATTLIST]
            if ibm_db.execute(write_stmt, tuple(u)) == False:
                raise Usage("Failed to execute updateN statement")              
        if (TRANS_MODE == 'N'):
            ibm_db.commit(conn)
    if (TRANS_MODE == '1'):
        ibm_db.commit(conn)
    #

    ibm_db.commit(conn)
    # Disconnect from DB
    status = ibm_db.close(conn)
    if status == False: raise Usage("Failed to close db connection.\n") 
コード例 #15
0
 def run_test_022(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
     
   if conn:
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print(rows)
       
     ibm_db.autocommit(conn, 0)
     ac = ibm_db.autocommit(conn)
     if ac != 0:
       print("Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test")
       #continue
       
     ibm_db.exec_immediate(conn, "INSERT INTO animals values (7,'bug','Brain Bug',10000.1)")
       
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print(rows)
       
     ibm_db.rollback(conn)
     
     stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
     res = ibm_db.fetch_tuple(stmt)
     rows = res[0]
     print(rows)
     ibm_db.close(conn)
   else:
     print("Connection failed.")
コード例 #16
0
 def run_test_180(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
   if conn:
     result = ''
     result2 = ''
     try:
       result = ibm_db.exec_immediate(conn,"insert int0 t_string values(123,1.222333,'one to one')")
     except:
       pass
     if result:
       cols = ibm_db.num_fields(result)
       print("col:", cols,", ") 
       rows = ibm_db.num_rows(result)
       print("affected row:", rows)
     else:
       print(ibm_db.stmt_errormsg())
     try:
       result = ibm_db.exec_immediate(conn,"delete from t_string where a=123")
     except:
       pass
     if result:
       cols = ibm_db.num_fields(result)
       print("col:", cols,", ")
       rows = ibm_db.num_rows(result)
       print("affected row:", rows)
     else:
       print(ibm_db.stmt_errormsg())
   
   else:
     print("no connection")
コード例 #17
0
  def run_test_311(self):
    # Make a connection
    conn = ibm_db.connect(config.database, config.user, config.password)

    if conn:
       ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON )

       # Drop the tab_num_literals table, in case it exists
       drop = 'DROP TABLE tab_num_literals'
       result = ''
       try:
         result = ibm_db.exec_immediate(conn, drop)
       except:
         pass
       # Create the animal table
       create = "CREATE TABLE tab_num_literals (col1 INTEGER, col2 FLOAT, col3 DECIMAL(7,2))"
       result = ibm_db.exec_immediate(conn, create)
   
       insert = "INSERT INTO tab_num_literals values ('11.22', '33.44', '55.66')"
       res = ibm_db.exec_immediate(conn, insert)
       print "Number of inserted rows:", ibm_db.num_rows(res)

       stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col1 = '11'")
       ibm_db.execute(stmt)
       data = ibm_db.fetch_both(stmt)
       while ( data ):
         print data[0]
         print data[1]
         print data[2]
         data = ibm_db.fetch_both(stmt)

       sql = "UPDATE tab_num_literals SET col1 = 77 WHERE col2 = 33.44"
       res = ibm_db.exec_immediate(conn, sql)
       print "Number of updated rows:", ibm_db.num_rows(res)

       stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col2 > '33'")
       ibm_db.execute(stmt)
       data = ibm_db.fetch_both(stmt)
       while ( data ):
         print data[0]
         print data[1]
         print data[2]
         data = ibm_db.fetch_both(stmt)
	 
       sql = "DELETE FROM tab_num_literals WHERE col1 > '10.0'"
       res = ibm_db.exec_immediate(conn, sql)
       print "Number of deleted rows:", ibm_db.num_rows(res)

       stmt = ibm_db.prepare(conn, "SELECT col1, col2, col3 FROM tab_num_literals WHERE col3 < '56'")
       ibm_db.execute(stmt)
       data = ibm_db.fetch_both(stmt)
       while ( data ):
         print data[0]
         print data[1]
         print data[2]
         data = ibm_db.fetch_both(stmt)

       ibm_db.rollback(conn)
       ibm_db.close(conn)
コード例 #18
0
ファイル: main.py プロジェクト: remyb/DistributedDDL
def create_catalog(cat, catalog):
  try:
    ibm_db.exec_immediate(cat,"create table dtables (tname char(32), nodedriver char(64), " \
                              "nodeurl char(128), nodeuser char(16), nodepasswd char(16), " \
                              "partmtd int, nodeid int, partcol char(32), partparam1 char(32)," \
                              " partparam2 char(32))")
  except:
    print "[*] NOTICE catalog table exists, continuing..."
コード例 #19
0
ファイル: server.py プロジェクト: JesperLinne/DB2Mobile
def deleteItem():
    page = ''

    if db2conn:
        value = request.args.get('item')
        ibm_db.exec_immediate(db2conn, "DELETE FROM ITEMS WHERE item='" + value + "'")
        page = 'Deleted: ' + value
    else:
        page = 'Cannot delete item'

    return page
コード例 #20
0
def myDeviceEventCallback(event):
  s = json.dumps(event.data)
  print(s)
  s = json.loads(s)
  print(s["d"])

  if db2conn:
    if s["d"]["topic"][:4] == "CSIE":
      ins = ibm_db.exec_immediate(db2conn, "INSERT INTO DASH5369.CSIE (TIME_STAMP, SIGNAL_SENT) VALUES ('%s','%s');" %(s['d']['timestamp'], s['d']['signal']))
    elif s['d']['topic'][:3] == 'MDV':
      ins = ibm_db.exec_immediate(db2conn, "INSERT INTO DASH5369.MDV (ID, TIME_STAMP, SINGAL_RECEIVED, SPEED) VALUES (%s, '%s', '%s', %s);" %(s['d']['id'],s['d']['tiemstamp'],s['d']['signal'], s['d']['speed']))
コード例 #21
0
ファイル: test_warn.py プロジェクト: sasa-tomic/python-ibmdb
    def run_test_warn(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        
        # Get the server type
        serverinfo = ibm_db.server_info( conn )
    
        if conn:

            drop = "DROP TABLE WITH_CLOB"
            try:
                result = ibm_db.exec_immediate(conn,drop)
            except:
                pass

            # Create the table with_clob

            if (serverinfo.DBMS_NAME[0:3] != 'IDS'): 
                create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(1k))"
            else:
                create = "CREATE TABLE WITH_CLOB (id SMALLINT NOT NULL, clob_col CLOB(smart))"
            result = ibm_db.exec_immediate(conn, create)

            # Select the result from the table. This is just to verify we get appropriate warning using
            # ibm_db.stmt_warn() API

            query = 'SELECT * FROM WITH_CLOB'
            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 )
            if data:
                print("Success")
            else:
                print("No Data")
                print(ibm_db.stmt_warn(stmt))
            ibm_db.close(conn)
        else:
            print ("Connection failed.")

#__END__
#__LUW_EXPECTED__
#No Data[IBM][CLI Driver][DB2/%s] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000 SQLCODE=100
#__ZOS_EXPECTED__
#No Data[IBM][CLI Driver][DB2]
# SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000 SQLCODE=100
#__SYSTEMI_EXPECTED__
#No Data
#__IDS_EXPECTED__
#No Data
#[IBM][CLI Driver][IDS/%s] SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.  SQLSTATE=02000 SQLCODE=100
    def run_test_264(self):
        # Make a connection
        conn = ibm_db.connect(config.database, config.user, config.password)

        if conn:
            server = ibm_db.server_info(conn)
            if server.DBMS_NAME[0:3] == "IDS":
                op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
                ibm_db.set_option(conn, op, 1)

            # Drop the tab_bigint table, in case it exists
            drop = "DROP TABLE tab_bigint"
            result = ""
            try:
                result = ibm_db.exec_immediate(conn, drop)
            except:
                pass
            # Create the tab_bigint table
            if server.DBMS_NAME[0:3] == "IDS":
                create = "CREATE TABLE tab_bigint (col1 INT8, col2 INT8, col3 INT8, col4 INT8)"
            else:
                create = "CREATE TABLE tab_bigint (col1 BIGINT, col2 BIGINT, col3 BIGINT, col4 BIGINT)"
            result = ibm_db.exec_immediate(conn, create)

            insert = "INSERT INTO tab_bigint values (-9223372036854775807, 9223372036854775807, 0, NULL)"
            res = ibm_db.exec_immediate(conn, insert)
            print "Number of inserted rows:", ibm_db.num_rows(res)

            stmt = ibm_db.prepare(conn, "SELECT * FROM tab_bigint")
            ibm_db.execute(stmt)
            data = ibm_db.fetch_both(stmt)
            while data:
                print data[0]
                print data[1]
                print data[2]
                print data[3]
                print type(data[0]) is long
                print type(data[1]) is long
                print type(data[2]) is long
                data = ibm_db.fetch_both(stmt)

            # test ibm_db.result for fetch of bigint
            stmt1 = ibm_db.prepare(conn, "SELECT col2 FROM tab_bigint")
            ibm_db.execute(stmt1)
            ibm_db.fetch_row(stmt1, 0)
            if server.DBMS_NAME[0:3] != "IDS":
                row1 = ibm_db.result(stmt1, "COL2")
            else:
                row1 = ibm_db.result(stmt1, "col2")
            print row1

            ibm_db.close(conn)
コード例 #23
0
ファイル: server_db2.py プロジェクト: itucsdb1522/itucsdb1522
def counter_page():
    try:
        connection = ibm_db.connect(app.config['dsn'], '', '')

        query = "UPDATE COUNTER SET N = N + 1"
        ibm_db.exec_immediate(connection, query)

        query = "SELECT N FROM COUNTER"
        statement = ibm_db.exec_immediate(connection, query)
        (count,) = ibm_db.fetch_tuple(statement)
    except:
        count = -1
    return "This page was accessed %d times." % count
コード例 #24
0
  def run_test_046(self):
    conn = ibm_db.connect(config.database, config.user, config.password)

    server = ibm_db.server_info( conn )
    if (server.DBMS_NAME[0:3] == 'IDS'):
      result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, photo_format FROM emp_photo") 
    else:
      result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, length(picture) FROM emp_photo")
    row = ibm_db.fetch_tuple(result)
    while ( row ):
      if row[1] != 'xwd':
        print("<a href='test_046.php?EMPNO=%s&FORMAT=%s' target=_blank>%s - %s - %s bytes</a><br>" % (row[0], row[1], row[0], row[1], row[2]))
      row = ibm_db.fetch_tuple(result)
コード例 #25
0
 def run_test_250(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
   
   result = ibm_db.exec_immediate(conn, "select * from sales")
   result2 = ibm_db.exec_immediate(conn, "select * from staff")
   result3 = ibm_db.exec_immediate(conn, "select * from emp_photo")
   
   r1 = ibm_db.free_result(result)
   r2 = ibm_db.free_result(result2)
   r3 = ibm_db.free_result(result3)
   
   print(r1)
   print(r2)
   print(r3)
コード例 #26
0
  def run_test_InsertRetrieveDateTimeTypeColumn(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    
    if conn:
      drop = 'DROP TABLE tab_datetime'
      result = ''
      try:
        result = ibm_db.exec_immediate(conn, drop)
      except:
        pass
      t_val = datetime.time(10, 42, 34)
      d_val = datetime.date(1981, 7, 8)
      #ts_val = datetime.datetime.today()
      ts_val = datetime.datetime(1981, 7, 8, 10, 42, 34, 10)
      server = ibm_db.server_info( conn )
      if (server.DBMS_NAME[0:3] == 'IDS'):
        statement = "CREATE TABLE tab_datetime (col1 DATETIME HOUR TO SECOND, col2 DATE, col3 DATETIME YEAR TO FRACTION(5))"
        result = ibm_db.exec_immediate(conn, statement)
        statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
        stmt = ibm_db.prepare(conn, statement)
        result = ibm_db.execute(stmt, (t_val, d_val, ts_val))
      else:
        statement = "CREATE TABLE tab_datetime (col1 TIME, col2 DATE, col3 TIMESTAMP)"
        result = ibm_db.exec_immediate(conn, statement)
        statement = "INSERT INTO tab_datetime (col1, col2, col3) values (?, ?, ?)"
        stmt = ibm_db.prepare(conn, statement)
        result = ibm_db.execute(stmt, (t_val, d_val, ts_val))

      statement = "SELECT * FROM tab_datetime"
      result = ibm_db.exec_immediate(conn, statement)
      
      for i in range(0, ibm_db.num_fields(result)):
        print(str(i) + ":" + ibm_db.field_type(result,i))

      statement = "SELECT * FROM tab_datetime"
      stmt = ibm_db.prepare(conn, statement)
      rc = ibm_db.execute(stmt)
      result = ibm_db.fetch_row(stmt)
      while ( result ):
        row0 = ibm_db.result(stmt, 0)
        row1 = ibm_db.result(stmt, 1)
        row2 = ibm_db.result(stmt, 2)
        print(type(row0), row0)
        print(type(row1), row1)
        print(type(row2), row2)
        result = ibm_db.fetch_row(stmt)
      
      ibm_db.close(conn)
    else:
      print("Connection failed.")
コード例 #27
0
 def run_test_241(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
   
   result = ibm_db.exec_immediate(conn, "select * from sales")
   result2 = ibm_db.exec_immediate(conn, "select * from staff")
   result3 = ibm_db.exec_immediate(conn, "select * from emp_photo")
   
   for i in range(0, ibm_db.num_fields(result)):
     print(str(ibm_db.field_width(result,i)))
   
   print("\n-----")
   
   for i in range(0, ibm_db.num_fields(result2)):
     print(str(ibm_db.field_width(result2,ibm_db.field_name(result2,i))))
コード例 #28
0
  def run_test_114(self):
    conn = ibm_db.connect(config.database, config.user, config.password)
    
    if conn:
      drop = "drop table numericliteral"

      try:
        ibm_db.exec_immediate( conn, drop )
      except:
        pass
      
      create = "create table numericliteral ( id INTEGER, num INTEGER )"
      ibm_db.exec_immediate(conn, create)
      
      insert = "INSERT INTO numericliteral (id, num) values (1,5)"
      ibm_db.exec_immediate(conn, insert)

      insert = "UPDATE numericliteral SET num = '10' WHERE num = '5'"
      ibm_db.exec_immediate(conn, insert)
      
      stmt = ibm_db.prepare(conn, "SELECT * FROM numericliteral")
      ibm_db.execute(stmt)

      result = ibm_db.fetch_row( stmt )
      while ( result ):
        row0 = ibm_db.result(stmt, 0)
        row1 = ibm_db.result(stmt, 1)
        print row0
        print row1
        result = ibm_db.fetch_row( stmt )
    else:
      print "Connection failed."
コード例 #29
0
ファイル: server.py プロジェクト: JesperLinne/DB2Mobile
def addItem():

    page = ''

    if db2conn:
        #Get the value of the key 'item' in the query string
        value = request.args.get('item')

        #Execute the insert statement
        ibm_db.exec_immediate(db2conn, "INSERT INTO ITEMS(ITEM) VALUES ('" + value + "')")
        page = 'Added: ' + value
    else:
        page = 'Cannot add item'

    return page
コード例 #30
0
  def run_test_047(self):
    conn = ibm_db.connect(config.database, config.user, config.password)

    server = ibm_db.server_info( conn )
    if (server.DBMS_NAME[0:3] == 'IDS'):
      result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, photo_format from emp_photo")
    else:
      result = ibm_db.exec_immediate(conn, "SELECT empno, photo_format, length(PICTURE) from emp_photo")
    row = ibm_db.fetch_tuple(result)
    while ( row ):
      if (row[1] == 'gif'):
        print "<img src='test_047.php?EMPNO=%s&FORMAT=%s'><br>\n" % (row[0], row[1])
      if (row[1] != 'xwd'):
        print "<a href='test_047.php?EMPNO=%s&FORMAT=%s' target=_blank>%s - %s - %s bytes</a>\n<br>" % (row[0], row[1], row[0], row[1], row[2])
      row = ibm_db.fetch_tuple(result)
コード例 #31
0
def insert():
    mode = request.args.get('mode1') if request.args.get(
        'mode1') else request.args.get('mode2')
    mode = mode.split(' ')[-1]

    etime = request.args.get('time') if request.args.get('time') else -1
    lat = request.args.get('lat') if request.args.get('lat') else -1
    lon = request.args.get('lon') if request.args.get('lon') else -1
    dep = request.args.get('dep') if request.args.get('dep') else -1
    mag = request.args.get('mag') if request.args.get('mag') else -1
    magtype = request.args.get('magtype') if request.args.get(
        'magtype') else -1
    nst = request.args.get('nst') if request.args.get('nst') else -1
    gap = request.args.get('gap') if request.args.get('gap') else -1
    dmin = request.args.get('dmin') if request.args.get('dmin') else -1
    rms = request.args.get('rms') if request.args.get('rms') else -1
    net = request.args.get('net') if request.args.get('net') else -1
    eid = request.args.get('id') if request.args.get('id') else -1
    updated = request.args.get('updated') if request.args.get(
        'updated') else -1
    place = request.args.get('place') if request.args.get('place') else -1
    etype = request.args.get('type') if request.args.get('type') else -1
    he = request.args.get('he') if request.args.get('he') else -1
    de = request.args.get('de') if request.args.get('de') else -1
    me = request.args.get('me') if request.args.get('me') else -1
    mn = request.args.get('mn') if request.args.get('mn') else -1
    status = request.args.get('status') if request.args.get('status') else -1
    locsource = request.args.get('locsource') if request.args.get(
        'locsource') else -1
    magsource = request.args.get('magsource') if request.args.get(
        'magsource') else -1
    start = time.time()
    # connect to DB2
    if mode == 'RDB':
        db2conn = ibm_db.connect(db2cred['ssldsn'], "", "")
        if db2conn:
            sql = """INSERT INTO EARTHQUAKE \
                    (TIME, LATITUDE, LONGTITUDE, DEPTH, MAG, \
                    MAGTYPE, NST, GAP, DMIN, RMS, NET, ID, UPDATED, \
                    PLACE, TYPE, HOTIZONTALERROR, DEPTHERROR, \
                    MAGERROR, MAGNST, STATUS, LOCATIONSOURCE, MAGSOURCE) \
                    VALUES ({}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {});""".format(
                etime, lat, lon, dep, mag, magtype, nst, gap, dmin, rms, net,
                eid, updated, place, etype, he, de, me, mn, status, locsource,
                magsource)
            ibm_db.exec_immediate(db2conn, sql)
    elif mode == 'Memcache':
        tmp = pd.DataFrame([[
            etime,
            float(lat),
            float(lon),
            float(dep),
            float(mag), magtype,
            float(nst),
            float(gap),
            float(dmin),
            float(rms), net, eid, updated, place, etype,
            float(he),
            float(de),
            float(me),
            float(mn), status, locsource, magsource
        ]])
        tmp.columns = [
            'TIME', 'LATITUDE', 'LONGTITUDE', 'DEPTH', 'MAG', 'MAGTYPE', 'NST',
            'GAP', 'DMIN', 'RMS', 'NET', 'ID', 'UPDATED', 'PLACE', 'TYPE',
            'HOTIZONTALERROR', 'DEPTHERROR', 'MAGERROR', 'MAGNST', 'STATUS',
            'LOCATIONSOURCE', 'MAGSOURCE'
        ]
        global cache_csv
        cache_csv = cache_csv.append(tmp, ignore_index=True)

    end = time.time()
    elapse = end - start
    return render_template('index.html', app=appenv, insert_elp=elapse)
コード例 #32
0
def main(params):
    logging.info('Calling fn_wds_operations.')

    try:

        cos_everest_submission_bucket = params.get(
            "cos_everest_submission_bucket", SUBMISSION_BUCKET)
        if cos_everest_submission_bucket is None or "":
            raise Exception("Pass location of the bucket")

        cos_everest_submission_data_folder = params.get(
            "cos_everest_submission_data_folder", SUBMISSION_DATA_FOLDER)
        if cos_everest_submission_data_folder is None or "":
            raise Exception("Pass cos_everest_submission_data_folder")

        doc_type_to_process = params.get("doc_type_to_process",
                                         DOC_TYPE_TO_PROCESS)
        if doc_type_to_process is None or "":
            raise Exception("Pass doc_type_to_process")

        # initialize watson discovery utils
        wds = watsondiscoveryutils.inst()

        # with DB2DBConnection() as db_conn:
        db_conn = db2utils.get_connection()
        print("db_conn: {}".format(db_conn))

        sql = f'''SELECT ID, DOCUMENT_TYPE, DOCUMENT_NAME
                FROM EVERESTSCHEMA.evre_learning_email_attachments where used_for='TRAINING' and DOCUMENT_TYPE IN {tuple(doc_type_to_process)} order by ID'''
        print("sql: {}".format(sql))

        stmt = ibm_db.exec_immediate(db_conn, sql)
        result = ibm_db.fetch_both(stmt)
        msg_id = None
        msg_encoded_id = None
        msg_document_id = None
        while result:
            print("result::{}".format(result))
            msg_id = result["ID"]
            doc_type = result["DOCUMENT_TYPE"]
            obj_storage_doc_path = result["DOCUMENT_NAME"]
            print(f'obj_storage_doc_path: {obj_storage_doc_path}')

            # https://blogs.msdn.microsoft.com/vsofficedeveloper/2008/05/08/office-2007-file-format-mime-types-for-http-content-streaming-2/
            if doc_type.lower() == ".PDF".lower():
                file_content_type = "application/pdf"
            elif doc_type.lower() == ".DOCX".lower():
                file_content_type = "application/vnd.openxmlformats-officedocument.wordprocessingml.document"
            elif doc_type.lower() == ".DOC".lower():
                file_content_type = "application/msword"
            elif doc_type.lower() == ".XLSX".lower():
                file_content_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            elif doc_type.lower() == ".XLS".lower() or doc_type.lower(
            ) == ".XL".lower():
                file_content_type = "application/vnd.ms-excel"
            elif doc_type.lower() == ".XLSM".lower():
                file_content_type = "application/vnd.ms-excel.sheet.macroEnabled.12"
            elif doc_type.lower() == ".XLTM".lower():
                file_content_type = "application/vnd.ms-excel.template.macroEnabled.12"
            elif doc_type.lower() == ".XLTX".lower():
                file_content_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.template"
            elif doc_type.lower() == ".XLSB".lower():
                file_content_type = "application/vnd.ms-excel.sheet.binary.macroEnabled.12"
            elif doc_type.lower() == ".PPTX".lower():
                file_content_type = "application/vnd.openxmlformats-officedocument.presentationml.presentation"
            elif doc_type.lower() == ".HTML".lower():
                file_content_type = "application/xhtml+xml"

            # task = asyncio.create_task(wds_upload_task(wds, cos_everest_submission_bucket, obj_storage_doc_path, doc_bytes, doc_type))

            # await task
            wds_upload_task(wds, cos_everest_submission_bucket,
                            obj_storage_doc_path, file_content_type)

            # iterate thru the resultset
            result = ibm_db.fetch_both(stmt)

    except (ibm_db.conn_error, ibm_db.conn_errormsg, Exception) as err:
        logging.exception(err)
        json_result = json.dumps(err)

    return {"result": "Success"}
コード例 #33
0
def chat():
    # check if user is logged in
    if not session.get('logged_in'):
        return render_template('index.html')
    else:
        user_ip = request.remote_addr
        user_agent = request.headers.get('User-Agent')
        session['unique_conversation_id'] = str(user_ip) + "__" + str(user_agent)
        context["conversation_id"] = str(hashlib.sha256(session['unique_conversation_id'].encode('utf-8')).hexdigest())
        if request.method == "POST":
            if 'cardNo' in request.form:
                sql = "SELECT * FROM CARD WHERE CARDNO = ?"
                stmt = ibm_db.prepare(conn, sql)
                param = request.form["cardNo"],
                ibm_db.execute(stmt, param)
                if ibm_db.fetch_row(stmt) == False:
                    sql = "SELECT * FROM CARD ORDER BY ID DESC fetch first 1 row only"
                    stmt = ibm_db.exec_immediate(conn, sql)
                    card = 1
                    while ibm_db.fetch_row(stmt) != False:
                        card = card + int(ibm_db.result(stmt, "ID"))
                    # Save new customer card info
                    sql = "INSERT INTO CARD (ID, BVN, ACCNO, CARDNO, CARDVENDOR, CARDTYPE, CVC, EXPIRY) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
                    stmt = ibm_db.prepare(conn, sql)
                    param = card, user["bvn"], request.form["cardAccountNo"], request.form["cardNo"], request.form["cardVendor"], request.form["cardType"], request.form["cvc"], request.form["cardExpiry"],
                    ibm_db.execute(stmt, param)
                    msg = Message('CASBA: New Card', sender = '*****@*****.**', recipients = [str(user["email"])])
                    msg.body = "You have added a new card"
                    mail.send(msg)
                    flash('You have successfully added a card!')
                    sql2 = "".join(["SELECT * FROM CARD WHERE BVN = ", str(user['bvn'])])
                    stmt2 = ibm_db.exec_immediate(conn, sql2)
                    userCardDF = pandas.read_sql(sql2 , pconn)
                    if userCardDF.empty:
                        user.update({'#Card': 0})
                        return redirect(url_for('chat'))
                    else:
                        user.update({'#Card': len(userCardDF.index)})
                        userCard = {}
                        for i in range(0, user["#Card"]):
                            userCard[i] = userCardDF.iloc[i].tolist()
                        user.update({'Card': userCard})
                        #print user["Account"][0][0]
                        return redirect(url_for('chat'))
                    return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode)
                else:
                    flash('You have card already registered!')
                    return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode)
            elif 'accountNo' in request.form:
                sql = "SELECT * FROM ACCOUNT WHERE ACCNO = ?"
                stmt = ibm_db.prepare(conn, sql)
                param = request.form["accountNo"],
                ibm_db.execute(stmt, param)
                if ibm_db.fetch_row(stmt) == False:
                    sql = "SELECT * FROM ACCOUNT ORDER BY ID DESC fetch first 1 row only"
                    stmt = ibm_db.exec_immediate(conn, sql)
                    account = 1
                    while ibm_db.fetch_row(stmt) != False:
                        account = account + int(ibm_db.result(stmt, "ID"))
                    # Save new customer info
                    sql = "INSERT INTO ACCOUNT (ID, BVN, ACCNO, ACCBANK, ACCNAME, ACCTYPE, ACCBALANCE) VALUES (?, ?, ?, ?, ?, ?, ?)"
                    stmt = ibm_db.prepare(conn, sql)
                    param = account, user["bvn"], request.form["accountNo"], request.form["accountBank"], request.form["accountName"], request.form["accountType"], request.form["accountBalance"],
                    ibm_db.execute(stmt, param)
                    msg = Message('CASBA: New Account', sender = '*****@*****.**', recipients = [str(user["email"])])
                    msg.body = "You have added a new account"
                    mail.send(msg)
                    flash('You were successfully setup an account!')
                    sql1 = "".join(["SELECT * FROM ACCOUNT WHERE BVN = ", str(user['bvn'])])
                    stmt1 = ibm_db.exec_immediate(conn, sql1)
                    userAccountDF = pandas.read_sql(sql1 , pconn)
                    if userAccountDF.empty:
                        user.update({'accountIn': 0})
                        return redirect(url_for('chat'))
                    else:
                        user.update({'accountIn': len(userAccountDF.index)})
                        userAccount = {}
                        for i in range(0, user["accountIn"]):
                            userAccount[i] = userAccountDF.iloc[i].tolist()
                        user.update({'Account': userAccount})
                        #print user["Account"][0][0]
                        return redirect(url_for('chat'))
                    return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode)
                else:
                    flash('You have account already registered!')
                    return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode)
            else:
                flash('Unsuccessful account setup!')
                return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode)
        else:
            return render_template('chat.html', userJS=json.dumps(user), user=user, async_mode=socketio.async_mode)
コード例 #34
0
if __name__ == '__main__':

    """For usage reference"""

    # conn = DB2DBConnection().call ()
    # sql = "Select * from GOSALES.BRANCH"
    # stmt = ibm_db.exec_immediate(conn, sql)
    # data = ibm_db.fetch_both(stmt)

    try:
        with DB2DBConnection() as db_conn:
            # sql = f'''SELECT encoded_id FROM FINAL TABLE (INSERT INTO EVERESTSCHEMA.evre_learning_email_msgs (encoded_id, 
            #                 business_segment_id, status, document_name) 
            #                 VALUES (GENERATE_UNIQUE(), 
            #                     1,
            #                     'N',
            #                     'testkey') 
            #                 )       
            #                 '''
            sql = f'''SELECT ID, HEX(encoded_id) as encoded_id FROM EVERESTSCHEMA.evre_learning_email_msgs'''
                
            stmt = ibm_db.exec_immediate(db_conn, sql)
            result = ibm_db.fetch_both(stmt)
            msg_document_id = None
            if result:
                msg_document_id = result["ENCODED_ID"]
                
            print(f'encode_id: {msg_document_id}')
    except (ibm_db.conn_error, ibm_db. conn_errormsg, Exception) as err:
        logging.exception(err)
def cancel_open_order_query(conn, order_id):
    query = "UPDATE trade_history SET STATUS = 'CANCELED' where ORDER_ID_OPEN = " + str(
        order_id)
    ibm_db.exec_immediate(conn, query)
    return None
コード例 #36
0
def insert_data(conn,insert_sql):
    '插入数据'
    result = ibm_db.exec_immediate(conn,insert_sql)
    return result
コード例 #37
0
    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
コード例 #38
0
ファイル: DAY_SQL_2.7.py プロジェクト: day-team/DAY_develop
def selectDB(params, table, connection):
    select = "select " + params + " from " + table
    stmt_select = ibm_db.exec_immediate(connection, select)
    cols = ibm_db.fetch_tuple(stmt_select)
    print("Resultados: ")
    print("%s, %s" % (cols[0], cols[1]))
コード例 #39
0
    def run_test_065(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        server = ibm_db.server_info(conn)

        create = 'CREATE SCHEMA AUTHORIZATION t'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        if (server.DBMS_NAME[0:3] == 'IDS'):
            result = ibm_db.tables(conn, None, '%', "t3")
        else:
            result = ibm_db.tables(conn, None, '%', "T3")

        columns = ibm_db.num_fields(result)

        for i in range(0, columns):
            print("%s, " % ibm_db.field_name(result, i))
        print("\n\n")

        row = ibm_db.fetch_tuple(result)
        while (row):
            final = ", " + row[1] + ", " + row[2] + ", " + row[3] + ", , "
            row = ibm_db.fetch_tuple(result)

        print(final)

        ibm_db.free_result(result)

        ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
        ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
        ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
        ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')
    def run_test_017(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        if conn:
            if ('zos' in sys.platform):
                result = ibm_db.exec_immediate(
                    conn, "SELECT * from animals WHERE weight < 10.0")
            else:
                result = ibm_db.exec_immediate(
                    conn, "SELECT * from animals WHERE weight < 10.0", {
                        ibm_db.SQL_ATTR_CURSOR_TYPE:
                        ibm_db.SQL_CURSOR_KEYSET_DRIVEN
                    })
            if result:
                rows = ibm_db.num_rows(result)
                print("affected row:", rows)
            else:
                print(ibm_db.stmt_errormsg())
            if ('zos' in sys.platform):
                result = ibm_db.exec_immediate(
                    conn, "SELECT * from animals WHERE weight < 10.0")
            else:
                result = ibm_db.exec_immediate(
                    conn, "SELECT * from animals WHERE weight < 10.0", {
                        ibm_db.SQL_ATTR_CURSOR_TYPE:
                        ibm_db.SQL_CURSOR_FORWARD_ONLY
                    })
            if result:
                rows = ibm_db.num_rows(result)
                print("affected row:", rows)
            else:
                print(ibm_db.stmt_errormsg())
            if ('zos' in sys.platform):
                result = ibm_db.exec_immediate(
                    conn, "SELECT * from animals WHERE weight < 10.0")
            else:
                result = ibm_db.exec_immediate(
                    conn, "SELECT * from animals WHERE weight < 10.0", {
                        ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH:
                        ibm_db.SQL_ROWCOUNT_PREFETCH_ON
                    })
            if result:
                rows = ibm_db.num_rows(result)
                print("affected row:", rows)
            else:
                print(ibm_db.stmt_errormsg())
            if ('zos' in sys.platform):
                result = ibm_db.exec_immediate(
                    conn, "SELECT * from animals WHERE weight < 10.0")
            else:
                result = ibm_db.exec_immediate(
                    conn, "SELECT * from animals WHERE weight < 10.0", {
                        ibm_db.SQL_ATTR_ROWCOUNT_PREFETCH:
                        ibm_db.SQL_ROWCOUNT_PREFETCH_OFF
                    })
            if result:
                rows = ibm_db.num_rows(result)
                print("affected row:", rows)
            else:
                print(ibm_db.stmt_errormsg())

            ibm_db.close(conn)
        else:
            print("no connection:", ibm_db.conn_errormsg())
コード例 #41
0
ファイル: db2.py プロジェクト: erntye/SCDF-IBM-Challenge
    def read_table(self):
        sql = "SELECT * FROM " + TABLE + ";"
        res = self.results(exec_immediate(connection, sql))

        return res
コード例 #42
0
                          dsn_protocol, dsn_uid, dsn_pwd)

#print(dsn)

try:
    conn = ibm_db.connect(dsn, "", "")
    print("Connected!")

except:
    print("Unable to connect to database")

#1 createstmt=ibm_db.exec_immediate(conn,"create table Employee(name varchar(20), id int, dept char(10))")

#2 to insert a record
"""ins="insert into Employee values('Amritsar','18','BCA')"
insertstmt=ibm_db.exec_immediate(conn,ins)"""

#3 to drop a table
#dropstmt=ibm_db.exec_immediate(conn,"drop table Employee")

#4 getting data from table
selectquery = "select * from Employee"
selectstmt = ibm_db.exec_immediate(conn, selectquery)

while ibm_db.fetch_row(selectstmt) != False:
    print("Name:", ibm_db.result(selectstmt, 0), " ID: ",
          ibm_db.result(selectstmt, 1), " Dept: ",
          ibm_db.result(selectstmt, 2))

ibm_db.close(conn)
コード例 #43
0
    def run_test_154(self):
        conn = ibm_db.connect(config.database, config.user, config.password)

        server = ibm_db.server_info(conn)
        if (server.DBMS_NAME[0:3] == 'IDS'):
            op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
            ibm_db.set_option(conn, op, 1)

        try:
            statement = 'DROP TABLE fetch_test'
            result = ibm_db.exec_immediate(conn, statement)
        except:
            pass

        server = ibm_db.server_info(conn)
        if (server.DBMS_NAME[0:3] == 'IDS'):
            statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB, col3 INTEGER)'
            st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)"
            st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)"
            st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)"
            st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)"
        else:
            statement = 'CREATE TABLE fetch_test (col1 VARCHAR(20), col2 CLOB(20), col3 INTEGER)'
            st0 = "INSERT INTO fetch_test VALUES ('column 0', 'Data in the clob 0', 0)"
            st1 = "INSERT INTO fetch_test VALUES ('column 1', 'Data in the clob 1', 1)"
            st2 = "INSERT INTO fetch_test VALUES ('column 2', 'Data in the clob 2', 2)"
            st3 = "INSERT INTO fetch_test VALUES ('column 3', 'Data in the clob 3', 3)"
        result = ibm_db.exec_immediate(conn, statement)

        result = ibm_db.exec_immediate(conn, st0)
        result = ibm_db.exec_immediate(conn, st1)
        result = ibm_db.exec_immediate(conn, st2)
        result = ibm_db.exec_immediate(conn, st3)

        statement = "SELECT col1, col2 FROM fetch_test"
        result = ibm_db.prepare(conn, statement)
        ibm_db.execute(result)

        row = ibm_db.fetch_tuple(result)
        while (row):
            #printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
            #        row[0],row[0].length, row[1],row[1].length)
            print("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long." %\
              (row[0], len(row[0]), row[1], len(row[1])))
            row = ibm_db.fetch_tuple(result)

        result = ibm_db.prepare(conn, statement)
        ibm_db.execute(result)

        row = ibm_db.fetch_assoc(result)
        while (row):
            #printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
            #        row['COL1'], row['COL1'].length, row['COL2'], row['COL2'].length)
            print("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long." %\
              (row['COL1'], len(row['COL1']), row['COL2'], len(row['COL2'])))
            row = ibm_db.fetch_assoc(result)

        result = ibm_db.prepare(conn, statement)
        ibm_db.execute(result)

        row = ibm_db.fetch_both(result)
        while (row):
            #printf("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n",
            #        row['COL1'], row['COL1'].length, row[1], row[1].length)
            print("\"%s\" from VARCHAR is %d bytes long, \"%s\" from CLOB is %d bytes long.\n" % \
              (row['COL1'],len(row['COL1']), row[1], len(row[1])))
            row = ibm_db.fetch_both(result)

        ibm_db.close(conn)
コード例 #44
0
    def run_test_bool_callproc(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 PROCEDURE bool_procparams")
            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:
            pass

        try:
            procedure = """create procedure bool_procparams(in parm1 boolean, out param2 boolean)
                           RESULT SETS  1
                           LANGUAGE SQL
                           BEGIN
                             DECLARE c1 CURSOR WITH RETURN FOR SELECT * from bool_test;
                             OPEN c1;
                             set param2 = parm1;
                           END"""

            ibm_db.exec_immediate(conn, procedure)
        except Exception as e:
            print(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

            inparam = 11
            outparam = -1
            stmt, inparam, outparam = ibm_db.callproc(conn, 'bool_procparams',
                                                      (inparam, outparam))
            print("Fetching first result set")
            row = ibm_db.fetch_row(stmt)
            while row:
                row0 = ibm_db.result(stmt, 0)
                row1 = ibm_db.result(stmt, 1)
                print(row0)
                print(row1)
                row = ibm_db.fetch_row(stmt)

            ibm_db.close(conn)
        except Exception as e:
            print("Error:{}".format(str(e)))
コード例 #45
0
    def run_test_197(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        server = ibm_db.server_info(conn)

        if conn:
            try:
                rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test")
            except:
                pass
            rc = ibm_db.exec_immediate(
                conn, "CREATE TABLE index_test (id INTEGER, data VARCHAR(50))")
            rc = ibm_db.exec_immediate(
                conn, "CREATE UNIQUE INDEX index1 ON index_test (id)")

            print("Test first index table:")
            if (server.DBMS_NAME[0:3] == 'IDS'):
                result = ibm_db.statistics(conn, None, config.user,
                                           "index_test", True)
            else:
                result = ibm_db.statistics(conn, None, None, "INDEX_TEST",
                                           True)
            row = ibm_db.fetch_tuple(result)
            ## skipping table info row. statistics returns informtation about table itself for informix ###
            if (server.DBMS_NAME[0:3] == 'IDS'):
                row = ibm_db.fetch_tuple(result)
            print(row[2])  # TABLE_NAME
            print(row[3])  # NON_UNIQUE
            print(row[5])  # INDEX_NAME
            print(row[8])  # COLUMN_NAME

            try:
                rc = ibm_db.exec_immediate(conn, "DROP TABLE index_test2")
            except:
                pass
            rc = ibm_db.exec_immediate(
                conn,
                "CREATE TABLE index_test2 (id INTEGER, data VARCHAR(50))")
            rc = ibm_db.exec_immediate(
                conn, "CREATE INDEX index2 ON index_test2 (data)")

            print("Test second index table:")
            if (server.DBMS_NAME[0:3] == 'IDS'):
                result = ibm_db.statistics(conn, None, config.user,
                                           "index_test2", True)
            else:
                result = ibm_db.statistics(conn, None, None, "INDEX_TEST2",
                                           True)
            row = ibm_db.fetch_tuple(result)
            ### skipping table info row. statistics returns informtation about table itself for informix ###
            if (server.DBMS_NAME[0:3] == 'IDS'):
                row = ibm_db.fetch_tuple(result)
            print(row[2])  # TABLE_NAME
            print(row[3])  # NON_UNIQUE
            print(row[5])  # INDEX_NAME
            print(row[8])  # COLUMN_NAME

            print("Test non-existent table:")
            if (server.DBMS_NAME[0:3] == 'IDS'):
                result = ibm_db.statistics(conn, None, config.user,
                                           "non_existent_table", True)
            else:
                result = ibm_db.statistics(conn, None, None,
                                           "NON_EXISTENT_TABLE", True)
            row = ibm_db.fetch_tuple(result)
            if row:
                print("Non-Empty")
            else:
                print("Empty")
        else:
            print('no connection: ' + ibm_db.conn_errormsg())
コード例 #46
0
ファイル: split_pdf.py プロジェクト: arunwagle/submission-git
def main(params):
    logging.info('Calling fn_split_pdf.')

    try:
        cos_everest_submission_bucket = params.get(
            "cos_everest_submission_bucket", None)
        if cos_everest_submission_bucket is None or "":
            raise Exception("Pass location of the bucket")

        final_pdf_folder = params.get("final_pdf_folder", None)
        if final_pdf_folder is None or "":
            raise Exception("Pass pdf folder to split files")

        submissions_data_folder = params.get("submissions_data_folder", None)
        if submissions_data_folder is None or "":
            raise Exception("Pass submissions_data_folder")

        submission_id = params.get("submission_id", None)
        if submission_id is None or "":
            raise Exception("Pass submission_id")

        mode = params.get("mode", None)
        if mode is None or "":
            raise Exception("Pass mode")

        object_storage_key = submissions_data_folder + "/" + \
            mode + "/" + str(submission_id) + "/"

        # + "/" + final_pdf_folder

        extensions = ['pdf']
        regex = r"^" + object_storage_key + ".*(?i)(pdf).*$"

        file_keys = cosutils.get_bucket_contents(cos_everest_submission_bucket,
                                                 regex)

        print(file_keys)

        for key in file_keys:

            if key.lower().endswith(tuple(extensions)):
                file_name = os.path.basename(key)
                file_name_without_ext, file_extension = os.path.splitext(
                    file_name)

                pdf_file_bytes = cosutils.get_item(
                    cos_everest_submission_bucket, key)

                db_conn = db2utils.get_connection()
                print("db_conn: {}".format(db_conn))
                sql = f'''SELECT ID FROM EVERESTSCHEMA.EVRE_LEARNING_EMAIL_ATTACHMENTS 
                where  EVRE_EMAIL_MSG_ID={submission_id} and DOCUMENT_NAME='{key}' '''
                print("sql: {}".format(sql))

                stmt = ibm_db.exec_immediate(db_conn, sql)
                result = ibm_db.fetch_both(stmt)
                pdf_id = -1
                if result:
                    pdf_id = result["ID"]

                # read pdf

                pdf = PdfFileReader(BytesIO(pdf_file_bytes))
                num_of_pages = pdf.getNumPages()
                print("num_of_pages:: {} ", num_of_pages)

                for page in range(num_of_pages):
                    pdf_writer = PdfFileWriter()
                    pdf_writer.addPage(pdf.getPage(page))

                    split_pdf_dir = "final_pdf_split"
                    output_filename_key = '{}{}/{}_page_{}.pdf'.format(
                        object_storage_key, split_pdf_dir,
                        file_name_without_ext, page + 1)
                    tmp = BytesIO()
                    pdf_writer.write(tmp)

                    tmp.seek(0)
                    output_page_bytes = tmp.read()
                    # print("Bytes:: {} ", output_page_bytes)

                    return_val = cosutils.save_file(
                        cos_everest_submission_bucket, output_filename_key,
                        output_page_bytes)
                    if return_val is "SUCCESS":
                        print(
                            "File Uploaded to object storage successfully:: {} ",
                            output_filename_key)

                    db_conn = db2utils.get_connection()
                    print("db_conn: {}".format(db_conn))
                    sql = f'''SELECT ID FROM FINAL TABLE (INSERT INTO EVERESTSCHEMA.EVRE_LEARNING_SPLIT_CONTENT (EVRE_EMAIL_MSG_ID, EVRE_LEARNING_EMAIL_ATTACHMENTS_ID,
                                DOCUMENT_NAME, DOCUMENT_TYPE, CLASSIFICATION_TYPE, STATUS, USED_FOR, DESCRIPTION) 
                                VALUES ({submission_id}, 
                                    {pdf_id},                                 
                                    '{output_filename_key}',
                                    '.pdf',
                                    'N/A',
                                    'N',
                                    'RUNTIME',
                                    'STANDARDIZE_TO_TXT') 
                                )
                                '''
                    print("sql: {}".format(sql))

                    stmt = ibm_db.exec_immediate(db_conn, sql)
                    result = ibm_db.fetch_both(stmt)
                    attachment_id = None
                    if result:
                        attachment_id = result["ID"]
        # end of for loop

        db_conn = db2utils.get_connection()
        sql = f'''SELECT ID, STATUS, TO_CHAR(FIRST_UPDATED,'YYYY-MM-DD HH.MI.SS') as FIRST_UPDATED, 
                TO_CHAR(LAST_UPDATED,'YYYY-MM-DD HH.MI.SS') as LAST_UPDATED FROM FINAL TABLE 
                (UPDATE EVERESTSCHEMA.EVRE_LEARNING_EMAIL_MSGS SET STATUS = 'STANDARDIZE_TO_TXT' where ID = {submission_id})
                '''

        print("sql: {}".format(sql))

        stmt = ibm_db.exec_immediate(db_conn, sql)
        result = ibm_db.fetch_assoc(stmt)

        result_list = []
        if result:
            result_list.append(result)

        result_dict = {}
        result_dict["result"] = result_list
        result_dict["status"] = "SUCCESS"

        return result_dict

    except (ibm_db.conn_error, ibm_db.conn_errormsg, Exception) as err:
        logging.exception(err)
        result_dict = {}
        result_dict["error"] = err
        result_dict["status"] = "FAILURE"
        return result_dict

    return {"result": "Flow should not reach here"}
コード例 #47
0
    def run_test_061(self):
        conn = ibm_db.connect(config.database, config.user, config.password)
        server = ibm_db.server_info(conn)

        create = 'CREATE SCHEMA AUTHORIZATION t'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t1( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t2( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t3( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        create = 'CREATE TABLE t.t4( c1 integer, c2 varchar(40))'
        try:
            result = ibm_db.exec_immediate(conn, create)
        except:
            pass

        if conn:
            server = ibm_db.server_info(conn)
            if (server.DBMS_NAME[0:3] == 'IDS'):
                op = {ibm_db.ATTR_CASE: ibm_db.CASE_UPPER}
                ibm_db.set_option(conn, op, 1)

            if (server.DBMS_NAME[0:3] == 'IDS'):
                result = ibm_db.tables(conn, None, 't')
            else:
                result = ibm_db.tables(conn, None, 'T')
            i = 0
            row = ibm_db.fetch_both(result)
            while (row):
                str = row['TABLE_SCHEM'] + row['TABLE_NAME'] + row['TABLE_TYPE']
                if (i < 4):
                    print(str)
                i = i + 1
                row = ibm_db.fetch_both(result)

            ibm_db.exec_immediate(conn, 'DROP TABLE t.t1')
            ibm_db.exec_immediate(conn, 'DROP TABLE t.t2')
            ibm_db.exec_immediate(conn, 'DROP TABLE t.t3')
            ibm_db.exec_immediate(conn, 'DROP TABLE t.t4')

            print("done!")
        else:
            print("no connection: %s" % ibm_db.conn_errormsg())
dsn_driver = "{IBM DB2 ODBC DRIVER}"
dsn_database = "BLUDB"  # e.g. "BLUDB"
dsn_port = "50000"  # e.g. "50000"
dsn_protocol = "TCPIP"  # i.e. "TCPIP"

dsn = ("DRIVER={0};"
       "DATABASE={1};"
       "HOSTNAME={2};"
       "PORT={3};"
       "PROTOCOL={4};"
       "UID={5};"
       "PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port,
                          dsn_protocol, dsn_uid, dsn_pwd)

#print the connection string to check correct values are specified
print(dsn)

try:
    conn = ibm_db.connect(dsn, "", "")
    print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname)

except:
    print("Unable to connect: ", ibm_db.conn_errormsg())

createQuery = "create table REVIEW(USERNAME VARCHAR(20) PRIMARY KEY NOT NULL, FNAME VARCHAR(20), LNAME VARCHAR(20), REVIEWS VARCHAR(100), SENTIMENT VARCHAR(20))"
#Now fill in the name of the method and execute the statement
createStmt = ibm_db.exec_immediate(conn, createQuery)

ibm_db.close(conn)
    def run_test_311(self):
        # Make a connection
        conn = ibm_db.connect(config.database, config.user, config.password)

        if conn:
            ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_ON)

            # Drop the tab_num_literals table, in case it exists
            drop = 'DROP TABLE tab_num_literals'
            result = ''
            try:
                result = ibm_db.exec_immediate(conn, drop)
            except:
                pass
            # Create the animal table
            create = "CREATE TABLE tab_num_literals (col1 INTEGER, col2 FLOAT, col3 DECIMAL(7,2))"
            result = ibm_db.exec_immediate(conn, create)

            insert = "INSERT INTO tab_num_literals values ('11.22', '33.44', '55.66')"
            res = ibm_db.exec_immediate(conn, insert)
            print("Number of inserted rows:", ibm_db.num_rows(res))

            stmt = ibm_db.prepare(
                conn,
                "SELECT col1, col2, col3 FROM tab_num_literals WHERE col1 = '11'"
            )
            ibm_db.execute(stmt)
            data = ibm_db.fetch_both(stmt)
            while (data):
                print(data[0])
                print(data[1])
                print(data[2])
                data = ibm_db.fetch_both(stmt)

            sql = "UPDATE tab_num_literals SET col1 = 77 WHERE col2 = 33.44"
            res = ibm_db.exec_immediate(conn, sql)
            print("Number of updated rows:", ibm_db.num_rows(res))

            stmt = ibm_db.prepare(
                conn,
                "SELECT col1, col2, col3 FROM tab_num_literals WHERE col2 > '33'"
            )
            ibm_db.execute(stmt)
            data = ibm_db.fetch_both(stmt)
            while (data):
                print(data[0])
                print(data[1])
                print(data[2])
                data = ibm_db.fetch_both(stmt)

            sql = "DELETE FROM tab_num_literals WHERE col1 > '10.0'"
            res = ibm_db.exec_immediate(conn, sql)
            print("Number of deleted rows:", ibm_db.num_rows(res))

            stmt = ibm_db.prepare(
                conn,
                "SELECT col1, col2, col3 FROM tab_num_literals WHERE col3 < '56'"
            )
            ibm_db.execute(stmt)
            data = ibm_db.fetch_both(stmt)
            while (data):
                print(data[0])
                print(data[1])
                print(data[2])
                data = ibm_db.fetch_both(stmt)

            ibm_db.rollback(conn)
            ibm_db.close(conn)
コード例 #50
0
ファイル: test_decfloat.py プロジェクト: rohitvg/python-ibmdb
    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.")
コード例 #51
0
        else:
            print("Login successful!\n")

    while (user is not None):
        print("Welcome to SocialNet, {0}!".format(user[1]))
        print_menu()
        print(user)
        print("\nSocialNet Command Line Interface:\n")
        while (True):
            command = input("\t->  ")
            args = command.split()
            if (args[0].lower() in available_commands):
                if (args[0].lower() == "view"):
                    uid_query = "SELECT * FROM User WHERE username = \'{0}\'".format(
                        ' '.join(args[1:]))
                    uid_stmt = db2.exec_immediate(connection, uid_query)
                    uid_res = db2.fetch_assoc(uid_stmt)
                    if (uid_res != False):
                        friend_id = uid_res['OWNID']
                    else:
                        print(
                            "That user doesn't have an account on SocialNet..."
                        )
                        break

                    owner_query = "SELECT privacy, description FROM PageOwner WHERE ownID = {0}".format(
                        friend_id)
                    owner_stmt = db2.exec_immediate(connection, owner_query)
                    owner_res = db2.fetch_assoc(owner_stmt)
                    if (owner_res != False):
                        owner_prv = owner_res['PRIVACY']
コード例 #52
0
       "UID={5};"
       "PWD={6};").format(dsn_driver, dsn_database, dsn_hostname, dsn_port,
                          dsn_protocol, dsn_uid, dsn_pwd)

try:
    conn = ibm_db.connect(dsn, "", "")
    print("Connected to database: ", dsn_database, "as user: "******"on host: ", dsn_hostname)

    #Deleting only the rows which meet some criteria
    ##    DeleteQuery = "delete from "+TABLENAME+" WHERE id="+'EXAMPLE'
    ##    DeleteStmt = ibm_db.exec_immediate(conn, DeleteQuery)

    # Or you may just want to delete everything:
    DeleteQuery = "delete from " + TABLENAME
    DeleteStmt = ibm_db.exec_immediate(conn, DeleteQuery)

    #Checking the result
    selectQuery = "select * from " + TABLENAME
    selectStmt = ibm_db.exec_immediate(conn, selectQuery)

    #Fetch the Dictionary, if the only row is enough for you
    #ibm_db.fetch_both(selectStmt)

    #Fetch all the rows and print the first([0]) and the second ("USERNAME") columns for those rows
    while ibm_db.fetch_row(selectStmt) != False:
        print(" ID:", ibm_db.result(selectStmt, 0), " @username:"******"USERNAME"))

except:
    print("something went wrong, but I don't know what exactly ",
コード例 #53
0
def GetListOfComponentsRecentlyModified(modified_since,
                                        xmeta_user='******',
                                        xmeta_password='******'):
    """
    modified_since should be a datetime  ( utc) ( or None).

    What's the best way of providing db credentials to this?

    Initial MVP  - just doing jobdefns
    
    """

    if modified_since is None:
        logMessage.info('Getting list of all DataStage components')
    else:
        logMessage.info(
            'Getting list of DataStage components modified since :' +
            str(modified_since))

    # https://www.ibm.com/docs/en/db2/9.7?topic=db-fetching-rows-columns-from-result-sets
    #https://stackoverflow.com/questions/6044326/how-to-connect-python-to-db2

    ## might need rh-python36-python-devel.x86_64 to be able to run this - nope that didn't help/
    ## maybe need some optional repos? yum install python3-devel.x86_64 --enablerepo=rhel-7-server-optional-rpms
    ## Yep, that sorted it. yum install python3-devel.x86_64 --enablerepo=rhel-7-server-optional-rpms

    #from ibm_db import connect, exec_immediate

    database = 'XMETA'
    database_schema = 'XMETA'

    ## Setup input args

    import ibm_db
    # Careful with the punctuation here - we have 3 arguments.
    # The first is a big string with semicolons in it.
    # (Strings separated by only whitespace, newlines included,
    #  are automatically joined together, in case you didn't know.)
    # The last two are emptry strings.
    conn = ibm_db.connect(
        'DATABASE=' + database + ';'
        'HOSTNAME=localhost;'  # 127.0.0.1 or localhost works if it's local
        'PORT=50000;'
        'PROTOCOL=TCPIP;'
        'UID=' + xmeta_user + ';'
        'PWD=' + xmeta_password + ';',
        '',
        '')

    ## Example ..get all tables
    #from ibm_db import tables
    #t = db2_results(tables(connection))
    #print(t)

    # This SQL would show you the different types of class and users that modify things.
    #db2 "select CLASSNAME_XMETA, XMETA_MODIFIED_BY_USER_XMETA, count(*) from DATASTAGEX_DSITEM group by  CLASSNAME_XMETA, XMETA_MODIFIED_BY_USER_XMETA "
    ## Maybe we should not include things last modified by DataStageSystemUser in the backup - I assume they are default components.

    #SQL="select NAME_XMETA, XMETA_MODIFICATION_TIMESTAMP_XMETA \
    # from ISMETA01.DATASTAGEX_DSJOBDEF \
    # order by XMETA_MODIFICATION_TIMESTAMP_XMETA desc \
    # fetch first 6 rows only"

    ##ob I created has modification date 2020-10-08-08.29.14.000000
    #That's shown as 1602142154825 in db2
    #
    #So, need to divide the number by 1000 and add as seconds to 1970-01-01

    # Convert the timestamp input

    from datetime import datetime
    dt = datetime.today()  # Get timezone naive now

    seconds = dt.timestamp()

    import time
    import datetime

    where_clause = """
       t2.XMETA_MODIFIED_BY_USER_XMETA <> 'DataStageSystemUser' 
       and t2.XMETA_MODIFIED_BY_USER_XMETA <> 'admin01' 
    """

    if modified_since is not None:
        where_clause += ' and t2.XMETA_MODIFICATION_TIMESTAMP_XMETA >= ' + str(
            modified_since.timestamp() * 1000)

    #sql="""
    # select XMETA_MODIFIED_BY_USER_XMETA, PROJECTNAMESPACE_XMETA, CLASSNAME_XMETA, NAME_XMETA, XMETA_MODIFICATION_TIMESTAMP_XMETA
    # from  XMETA.DATASTAGEX_DSITEM
    # where """ + where_clause + """
    # ;
    # """

    sql = """
    select t1.CATEGORY_XMETA, t2.NAME_XMETA, t2.XMETA_MODIFICATION_TIMESTAMP_XMETA, t2.CLASSNAME_XMETA, t2.PROJECTNAMESPACE_XMETA
    from """ + database_schema + """.DATASTAGEX_DSJOBDEF t1
    inner join """ + database_schema + """.DATASTAGEX_DSITEM t2 on ( t1.XMETA_REPOS_OBJECT_ID_XMETA  = t2.REPOSID_XMETA  ) 
    where """ + where_clause + """
    ;
    """

    ## Need to include component path in here too.
    ## I guess need to join to these or tables like these
    """"                    DATASTAGEX_DSIMSVIEWSET                                                                                                          CATEGORY_XMETA
                            DATASTAGEX_DSJCLTEMPLATE                                                                                                         CATEGORY_XMETA
                            DATASTAGEX_DSJOBDEF                                                                                                              CATEGORY_XMETA
                            DATASTAGEX_DSMACHINEPROFILE                                                                                                      CATEGORY_XMETA
                            DATASTAGEX_DSPARAMETERSET                                                                                                        CATEGORY_XMETA
                            DATASTAGEX_DSROUTINE                                                                                                             CATEGORY_XMETA
                            DATASTAGEX_DSSHAREDCONTAINERDEF                                                                                                  CATEGORY_XMETA
                            DATASTAGEX_DSSTAGETYPE                                                                                                           CATEGORY_XMETA
                            DATASTAGEX_DSTABLEDEFINITION                                                                                                     CATEGORY_XMETA
                            DATASTAGEX_DSTRANSFORM                                                                                                           CATEGORY_XMETA
                            VWDATASTAGEX_DSIMSROOTOBJECT                                                                                                     CATEGORY_XMETA
                            VWMMI_ABSTRACTPERSISTENTADMINPROPERTY                                                                                            CATEGORY_XMETA


    maybe better to have something liek.
    select t1.CATEGORY_XMETA , t2.*
from DATASTAGEX_DSJOBDEF t1
inner join DATASTAGEX_DSITEM t2 on ( t1.XMETA_REPOS_OBJECT_ID_XMETA  = t2.REPOSID_XMETA  ) 
    """
    ## build dictionary of
    #( engine, project) [ (component_type, component_name, component_last_modified_timestamp) , .. , ..  ]
    objectsList = {}  #

    #sql = "SELECT * FROM XMETA.DATASTAGEX_DSITEM "
    stmt = ibm_db.exec_immediate(conn, sql)
    #counter=0
    while ibm_db.fetch_row(stmt) != False:
        #counter+=1
        #mod_timestamp=datetime.datetime.fromtimestamp(mod_timestamp_unix,tz=datetime.timezone.utc) # Let's just stick with utc.
        #formatted_modification_timestamp=mod_timestamp.strftime("%A, %B %d, %Y %I:%M:%S")
        project_namespace = ibm_db.result(stmt, "PROJECTNAMESPACE_XMETA")
        project_namespace_tuple = tuple(project_namespace.split(':'))
        component_type = ibm_db.result(stmt, "CLASSNAME_XMETA")
        component_name = ibm_db.result(stmt, "NAME_XMETA")
        mod_timestamp_unix = int(
            ibm_db.result(stmt, "XMETA_MODIFICATION_TIMESTAMP_XMETA")) / 1000
        component_last_modified_ts = datetime.datetime.fromtimestamp(
            mod_timestamp_unix, tz=datetime.timezone.utc)
        category = ibm_db.result(stmt, "CATEGORY_XMETA")

        try:
            objectsList[project_namespace_tuple] += [
                (component_type, component_name, component_last_modified_ts,
                 category)
            ]
        except KeyError:
            objectsList[project_namespace_tuple] = [
                (component_type, component_name, component_last_modified_ts,
                 category)
            ]

    return objectsList
コード例 #54
0
    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]

            with open(
                    os.path.dirname(os.path.abspath(__file__)) + '/' +
                    animal[1], 'rb') as fileHandle:
                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
        if ('zos' in sys.platform):
            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))
        else:
            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]
                with open(
                        os.path.dirname(os.path.abspath(__file__)) + '/' +
                        photo[2], 'rb') as fileHandler:
                    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 = (('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'))

        if ('zos' in sys.platform):
            project = (('AD3100', 'ADMIN SERVICES', 'D01', '000010', 6.5,
                        '1982-01-01', '1983-02-01', None), ) + project
        else:
            project = (('AD3100', 'ADMIN SERVICES', 'D01', '000010', 6.5,
                        '1982-01-01', '1983-02-01', ''), ) + project

        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")
コード例 #55
0
 def run_test_200(self):
   conn = ibm_db.connect(config.database, config.user, config.password)
   serverinfo = ibm_db.server_info( conn )
   server = serverinfo.DBMS_NAME[0:3]
   if (server == 'IDS'):
      procedure = """
       CREATE FUNCTION multiResults()
        RETURNING CHAR(16), INT;
               
        DEFINE p_name CHAR(16);
        DEFINE p_id INT;
              
        FOREACH c1 FOR
            SELECT name, id
             INTO p_name, p_id
              FROM animals
              ORDER BY name
             RETURN p_name, p_id WITH RESUME;
        END FOREACH;
               
      END FUNCTION;
      """
   else:
      procedure = """
       CREATE PROCEDURE multiResults ()
       RESULT SETS 3
       LANGUAGE SQL
       BEGIN
        DECLARE c1 CURSOR WITH RETURN FOR
         SELECT name, id
         FROM animals
         ORDER BY name;
   
        DECLARE c2 CURSOR WITH RETURN FOR
         SELECT name, id
         FROM animals
         WHERE id < 4
         ORDER BY name DESC;
   
        DECLARE c3 CURSOR WITH RETURN FOR
         SELECT name, id
         FROM animals
         WHERE weight < 5.0
         ORDER BY name;
   
        OPEN c1;
        OPEN c2;
        OPEN c3;
       END
      """
   
   if conn:
    try:
      ibm_db.exec_immediate(conn, 'DROP PROCEDURE multiResults')
    except:
      pass
    ibm_db.exec_immediate(conn, procedure)
    stmt = ibm_db.exec_immediate(conn, 'CALL multiResults()')
   
    print "Fetching first result set"
    row = ibm_db.fetch_tuple(stmt)
    while ( row ):
      for i in row:
        print i
      row = ibm_db.fetch_tuple(stmt)
   
    if (server == 'IDS'):
      print "Fetching second result set (should fail -- IDS does not support multiple result sets)"
    else:
      print "Fetching second result set"
    res = ibm_db.next_result (stmt)
    if res:
      row = ibm_db.fetch_tuple(res)
      while ( row ):
        for i in row:
          print i
        row = ibm_db.fetch_tuple(res)
   
    if (server == 'IDS'):
      print "Fetching third result set (should fail -- IDS does not support multiple result sets)"
    else:
      print "Fetching third result set"
    res2 = ibm_db.next_result(stmt)
    if res2:
      row = ibm_db.fetch_tuple(res2)
      while ( row ):
        for i in row:
          print i
        row = ibm_db.fetch_tuple(res2)
   
    print "Fetching fourth result set (should fail)"
    res3 = ibm_db.next_result(stmt)
    if res3:
      row = ibm_db.fetch_tuple(res3)
      while ( row ):
        for i in row:
          print i
        row = ibm_db.fetch_tuple(res3)
    
    ibm_db.close(conn)
   else:
     print "Connection failed."
コード例 #56
0
                        if len(ret) > 0:
                            #We have to think about event order when processing
                            cVal = ret[len(ret) - 1][ref]
                            for e in ret:
                                self.generate(e)
                    except Exception,e:
                        logger.error("Error building the event: %s" %(str(e)))
                        time.sleep(tSleep)
                else:
                    self.error ("Couldn't connect to database, maximum retries exceeded")
                    return
            else:
                sql = rules['query']['query']
                sql = sql.replace("$1", str(cVal))
                logger.debug(sql)
                result = ibm_db.exec_immediate(self.__objDBConn, sql)
                row  = ibm_db.fetch_tuple(result)
                ret = []
                while row:
                    logger.info(str(row))
                    ret.append(row)
                    row = ibm_db.fetch_tuple(result)
                logger.info("len ret %s y ref %s" % (len(ret),ref))
                if len(ret) > 0:
                    cVal = ret[len(ret) - 1][ref]
                    for e in ret:
                        logger.info("-.-->", e)
                        self.generate(e)

            time.sleep(tSleep)
コード例 #57
0
def index():
    if request.method == "POST":
        if 'signupBVN' in request.form:
            sql = "SELECT * FROM CUSTOMER WHERE BVN = ?"
            stmt = ibm_db.prepare(conn, sql)
            param = request.form["signupBVN"],
            ibm_db.execute(stmt, param)
            if ibm_db.fetch_row(stmt) == False:
                sql = "SELECT * FROM CUSTOMER ORDER BY ID DESC fetch first 1 row only"
                stmt = ibm_db.exec_immediate(conn, sql)
                customer = 1
                while ibm_db.fetch_row(stmt) != False:
                    customer = customer + int(ibm_db.result(stmt, "ID"))
                # Save new customer info
                sql = "INSERT INTO CUSTOMER (ID, BVN, FIRSTNAME, LASTNAME, DATEOFBIRTH, PHONENUMBER, EMAIL, PASSWORD, CITY, DOC) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
                stmt = ibm_db.prepare(conn, sql)
                param = customer, request.form["signupBVN"], request.form["inputFName"], request.form["inputLName"], request.form["inputDOB"], request.form["inputPhone"], request.form["inputEmail"], request.form["inputPassword"], request.form["inputLocation"], datetime.date.today(),
                ibm_db.execute(stmt, param)
                # Save security info for new customer
                sql = "INSERT INTO SECURITY (ID, BVN, SQ1, SA1, SQ2, SA2, SQ3, SA3) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
                stmt = ibm_db.prepare(conn, sql)
                param = customer, request.form["signupBVN"], request.form["inputSQ1"], request.form["inputSA1"], request.form["inputSQ2"], request.form["inputSA2"], request.form["inputSQ3"], request.form["inputSA3"],
                ibm_db.execute(stmt, param)
                msg = Message('Welcome to CASBA', sender = '*****@*****.**', recipients = [str(request.form["inputEmail"])])
                msg.body = "Thank you for registering to use of Cognitive Banking (CogniBank) service. Enjoy the experience of using artifical intelligence to organise your finances."
                mail.send(msg)
                flash('You were successfully registered!')
                return redirect(url_for('index'))
            else:
                flash('You were already registered!')
                return redirect(url_for('index'))
        elif 'loginBVN' in request.form:
            sql = "SELECT * FROM CUSTOMER WHERE BVN = ?"
            stmt = ibm_db.prepare(conn, sql)
            param = request.form["loginBVN"],
            ibm_db.execute(stmt, param)
            if ibm_db.fetch_row(stmt) != False:
                # check if user credentials match
                if request.form["inputPassword"] == ibm_db.result(stmt, "PASSWORD"):
                    session['logged_in'] = True
                    user.update({'bvn': str(request.form["loginBVN"]), 'fName': str(ibm_db.result(stmt, "FIRSTNAME")), 'lName': str(ibm_db.result(stmt, "LASTNAME")), 'email': str(ibm_db.result(stmt, "EMAIL")) })
                    # sql = "SELECT * FROM ACCOUNT WHERE BVN = ?"
                    # stmt = ibm_db.prepare(conn, sql)
                    # param = request.form["loginBVN"],
                    # ibm_db.execute(stmt, param)
                    sql1 = "".join(["SELECT * FROM ACCOUNT WHERE BVN = ", str(request.form["loginBVN"])])
                    stmt1 = ibm_db.exec_immediate(conn, sql1)
                    userAccountDF = pandas.read_sql(sql1 , pconn)
                    sql2 = "".join(["SELECT * FROM CARD WHERE BVN = ", str(user['bvn'])])
                    stmt2 = ibm_db.exec_immediate(conn, sql2)
                    userCardDF = pandas.read_sql(sql2 , pconn)
                    if userAccountDF.empty:
                        user.update({'accountIn': 0})
                        if userCardDF.empty:
                            user.update({'#Card': 0})
                        return redirect(url_for('chat'))
                    else:
                        user.update({'accountIn': len(userAccountDF.index)})
                        userAccount = {}
                        for i in range(0, user["accountIn"]):
                            userAccount[i] = userAccountDF.iloc[i].tolist()
                        user.update({'Account': userAccount})
                        #print user["Account"][0][0]
                        if userCardDF.empty:
                            user.update({'#Card': 0})
                            return redirect(url_for('chat'))
                        else:
                            user.update({'#Card': len(userCardDF.index)})
                            userCard = {}
                            for i in range(0, user["#Card"]):
                                userCard[i] = userCardDF.iloc[i].tolist()
                            user.update({'Card': userCard})
                            #print user["Account"][0][0]
                            return redirect(url_for('chat'))
                else:
                    flash('Wrong credentials, please try again or create an account!')
                    return redirect(url_for('index'))
            else:
                flash('Wrong credentials, please try again or create an account!')
                return redirect(url_for('index'))
        elif 'passwordBVN' in request.form:
            sql = "SELECT * FROM SECURITY WHERE BVN = ?"
            stmt = ibm_db.prepare(conn, sql)
            param = request.form["passwordBVN"],
            ibm_db.execute(stmt, param)
            # check if security questions match
            if ibm_db.fetch_row(stmt) != False:
                if str(ibm_db.result(stmt, "SQ1")) == str(request.form["inputSQ1"]) and str(ibm_db.result(stmt, "SA1")) == str(request.form["inputSA1"]):
                    if str(ibm_db.result(stmt, "SQ2")) == str(request.form["inputSQ2"]) and str(ibm_db.result(stmt, "SA2")) == str(request.form["inputSA2"]):
                        if str(ibm_db.result(stmt, "SQ3")) == str(request.form["inputSQ3"]) and str(ibm_db.result(stmt, "SA3")) == str(request.form["inputSA3"]):
                            new_password = gen_random_string(password_charset, 6)
                            sql = "UPDATE CUSTOMER SET PASSWORD = ? WHERE BVN = ?"
                            stmt = ibm_db.prepare(conn, sql)
                            param = new_password, request.form["passwordBVN"],
                            ibm_db.execute(stmt, param)
                            sql = "SELECT * FROM CUSTOMER WHERE BVN = ?"
                            stmt = ibm_db.prepare(conn, sql)
                            param = request.form["passwordBVN"],
                            ibm_db.execute(stmt, param)
                            if ibm_db.fetch_row(stmt) != False:
                                msg = Message('Password Reset', sender = '*****@*****.**', recipients = [str(ibm_db.result(stmt, "EMAIL"))])
                                msg.body = ' '.join(["Here is your new password", new_password])
                                mail.send(msg)
                                flash('Your password has been reset! Please check your email.')
                                return redirect(url_for('index'))
                            else:
                                flash('You are not registered! Please try again or create an account.')
                                return redirect(url_for('index'))
                        else:
                            flash('Incorrect Security Question & Answer')
                            return redirect(url_for('index'))
                    else:
                        flash('Incorrect Security Question & Answer')
                        return redirect(url_for('index'))
                else:
                    flash('Incorrect Security Question & Answer')
                    return redirect(url_for('index'))
            else:
                flash('You are not a registered user! Please create an account.')
                return redirect(url_for('index'))
    else:
        return render_template('index.html')
コード例 #58
0
#conn = ibm_db.connect("DATABASE=TLSAAS;HOSTNAME=10.122.107.243;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=db2inst1;", "", "")
#prodqa
#conn = ibm_db.connect("DATABASE=TLSAAS;HOSTNAME=10.108.240.66;PORT=50000;PROTOCOL=TCPIP;UID=tlsystem;PWD=B!rd33B!rd;", "", "")
#wdc
#conn = ibm_db.connect("DATABASE=TLSAAS;HOSTNAME=10.108.216.4;PORT=50000;PROTOCOL=TCPIP;UID=tlsystem;PWD=B!rd33B!rd;", "", "")
#dal
#conn = ibm_db.connect("DATABASE=TLSAAS;HOSTNAME=10.142.234.144;PORT=50000;PROTOCOL=TCPIP;UID=tlsystem;PWD=B!rd33B!rd;", "", "")
#SYD
conn = ibm_db.connect(
    "DATABASE=TLSAAS;HOSTNAME=10.138.55.105;PORT=50000;PROTOCOL=TCPIP;UID=tlsystem;PWD=B!rd33B!rd;",
    "", "")
#FRA
#conn = ibm_db.connect("DATABASE=TLSAAS;HOSTNAME=10.134.194.133;PORT=50000;PROTOCOL=TCPIP;UID=tlsystem;PWD=B!rd33B!rd;", "", "")
#sqlorgs = "select schemaname from syscat.schemata where schemaname like 'ORG%'"
sqlorgs = "select schemaname from syscat.schemata"
stmtorgs = ibm_db.exec_immediate(conn, sqlorgs)
resultorgs = ibm_db.fetch_both(stmtorgs)
while resultorgs != False:
    orgname = resultorgs["SCHEMANAME"]

    try:
        sqlQryEvents = "select sharetype, COUNT(*) from " + orgname + ".workspace group by sharetype order by sharetype"
        stmtQryEvents = ibm_db.exec_immediate(conn, sqlQryEvents)
        eventCount = ibm_db.fetch_tuple(stmtQryEvents)
        count0 = 0
        count1 = 0
        count2 = 0
        while eventCount != False:
            if eventCount[0] == 0: count0 = eventCount[1]
            if eventCount[0] == 1: count1 = eventCount[1]
            if eventCount[0] == 2: count2 = eventCount[1]
コード例 #59
0
def delete():
    mode = request.args.get('dmode1') if request.args.get(
        'dmode1') else request.args.get('dmode2')
    mode = mode.split(' ')[-1]
    start = time.time()

    etime = request.args.get('time') if request.args.get('time') else 'None'
    lat = request.args.get('lat') if request.args.get('lat') else -1
    lon = request.args.get('lon') if request.args.get('lon') else -1
    dep = request.args.get('dep') if request.args.get('dep') else -1
    mag = request.args.get('mag') if request.args.get('mag') else -1
    magtype = request.args.get('magtype') if request.args.get(
        'magtype') else 'None'
    nst = request.args.get('nst') if request.args.get('nst') else -1
    gap = request.args.get('gap') if request.args.get('gap') else -1
    dmin = request.args.get('dmin') if request.args.get('dmin') else -1
    rms = request.args.get('rms') if request.args.get('rms') else -1
    net = request.args.get('net') if request.args.get('net') else 'None'
    eid = request.args.get('id') if request.args.get('id') else 'None'
    updated = request.args.get('updated') if request.args.get(
        'updated') else 'None'
    place = request.args.get('place') if request.args.get('place') else 'None'
    etype = request.args.get('type') if request.args.get('type') else 'None'
    he = request.args.get('he') if request.args.get('he') else -1
    de = request.args.get('de') if request.args.get('de') else -1
    me = request.args.get('me') if request.args.get('me') else -1
    mn = request.args.get('mn') if request.args.get('mn') else -1
    status = request.args.get('status') if request.args.get(
        'status') else 'None'
    locsource = request.args.get('locsource') if request.args.get(
        'locsource') else 'None'
    magsource = request.args.get('magsource') if request.args.get(
        'magsource') else 'None'

    params = {
        "TIME": etime,
        "Latitude": lat,
        "LONGTITUDE": lon,
        "DEPTH": dep,
        "MAG": mag,
        "MAGTYPE": magtype,
        "NST": nst,
        "GAP": gap,
        "DMIN": dmin,
        "RMS": rms,
        "NET": net,
        "ID": eid,
        "UPDATED": updated,
        "PLACE": place,
        "TYPE": etype,
        "HOTIZONTALERROR": he,
        "DEPTHERROR": de,
        "MAGERROR": me,
        "MAGNST": mn,
        "STATUS": status,
        "LOCATIONSOURCE": locsource,
        "MAGSOURCE": magsource,
    }

    params = [(k, v) for k, v in params.items() if v not in [-1, 'None']]
    sub_sql = ', '.join([p[0] + '=' + str(p[1]) for p in params])
    if mode == 'RDB':
        # connect to DB2
        db2conn = ibm_db.connect(db2cred['ssldsn'], "", "")
        if db2conn:
            sql = """DELETE FROM EARTHQUAKE WHERE {}""".format(sub_sql)
            ibm_db.exec_immediate(db2conn, sql)
    elif mode == 'Memcache':
        for p in params:
            global cache_csv
            cache_csv = cache_csv[cache_csv[p[0]] != p[1]]

    end = time.time()
    elapse = end - start
    return render_template('index.html', app=appenv, delete_elp=elapse)
コード例 #60
0
ファイル: loader.py プロジェクト: psustr/db2histmon
def main():
  # Parse the input arguments
  parser = argparse.ArgumentParser()
  parser.add_argument("-d","-dbname", dest="dbname", required=True, help = "Target database to load the data into")
  parser.add_argument("-sourcePath", dest="sourcePath", required=True, help = "Path containing output from Historical Monitorinig. E.g. /home/db2inst1/sqllib/db2dump/IBMHIST_SAMPLE")
  parser.add_argument("-dataCollectionName", dest="dataCollectionName", help = "Data collection name, as defined in the task_details.json file")
  parser.add_argument("-startDate", dest="startDate", help = "Start timestamp (Default: localtime) (Must be of format YYYY-MM-DD-hh.mm.ss)")
  parser.add_argument("-endDate", dest="endDate", help = "End timestamp (Default: localtime) (Must be of format YYYY-MM-DD-hh.mm.ss)")
  args = parser.parse_args()
  sourcePath = Path(args.sourcePath)
  # Check if the raw data path exists
  if not sourcePath.exists():
    print("Raw data folder is not found: ", sourcePath)
    exit(1)
 
  print("Connecting to database:", args.dbname)
  conn = ibm_db.connect(args.dbname, '', '')

  # Set up preconditions
  createDBObjects(conn)
  
  # When -startDate and -endDate are specified, filter the qualified raw files 
  startDate, endDate = args.startDate, args.endDate
  if startDate and endDate:
    startTs = time.mktime(time.strptime(startDate, '%Y-%m-%d-%H.%M.%S'))
    startHour = time.mktime(time.strptime(startDate[:startDate.find('.')], '%Y-%m-%d-%H'))
    endTs = time.mktime(time.strptime(endDate, '%Y-%m-%d-%H.%M.%S'))
    endHour = time.mktime(time.strptime(endDate[:endDate.find('.')], '%Y-%m-%d-%H'))
    hourDirList = [hourDir for hourDir in sourcePath.glob('*_??????????') if startHour <= time.mktime(time.strptime(hourDir.name[-10:], '%Y%m%d%H')) <= endTs ]
  else:
    hourDirList = [hourDir for hourDir in sourcePath.glob('*_??????????')]

  if not hourDirList:
    print("No hourly directory found in {}".format(sourcePath))
    exit(1)

  # Load task details from json file
  searchTaskDetailFile = [file for file in hourDirList[0].glob(taskDetailFileName)]
  if searchTaskDetailFile:
    taskDetailFile = searchTaskDetailFile.pop()
  else:
    print("Task details file {} is not found under {}".format(taskDetailFileName, hourDirList[0]))
    exit(1)
  print("Loading tasks from task_details.json ...")
  with open(taskDetailFile) as file:
    tasks = json.load(file)

  # For each SQL task, import the data and delta data into tables if needed
  for task in tasks:
    collectionName = task['collection_name']
    if args.dataCollectionName and args.dataCollectionName != collectionName:
      continue
    if task['collection_class'] == "SQL":
      # Find raw data files
      fileList = []
      if startDate and endDate:
        files = list(sourcePath.rglob(collectionName + "*.del"))
        for file in files:
          tsPattern = re.search("[0-9]{12}", file.name)
          if tsPattern:
            fileTs = time.mktime(time.strptime(tsPattern.group(0), "%Y%m%d%H%M"))
            if startTs <= fileTs <= endTs:
              fileList.append(file)
        if not fileList:
          print("No raw data file found for {} with the time range from {} to {}".format(collectionName, startDate, endDate))
          continue
      else:
        fileList = list(sourcePath.rglob(collectionName + "*.del"))
      if not fileList:
        print("No raw data file found for {}".format(collectionName))
        continue
      tableName = schemaName + '.' + collectionName
      deltaTableName = tableName + delExt

      # Create the data table
      print("Creating the data table:",tableName) 
      createTable = "create table {} as ( {} ) WITH NO DATA NOT LOGGED INITIALLY IN {}".format(tableName, task['collection_command'], monTSName)
      stmt = ibm_db.exec_immediate(conn, createTable)
      
      # Load data into table
      print("Loading data into", tableName)
      for file in fileList:
        importCmd = "CALL SYSPROC.ADMIN_CMD('import from {} of del insert into {}' )".format(file, tableName)
        stmt = ibm_db.exec_immediate(conn, importCmd)

      # Check whether we need to create and load the delta table
      if task['loader_diff_exempt_columns'] != "ALL":
        print("Creating the delta data table:",deltaTableName)
        createDeltaTable = "create table {} as ( {} ) WITH NO DATA NOT LOGGED INITIALLY IN {}".format(deltaTableName, task['collection_command'], monTSName)
        stmt = ibm_db.exec_immediate(conn, createDeltaTable)
 
        # Read column names and types from the describe command
        desCmd = "CALL SYSPROC.ADMIN_CMD('describe table {} show detail' )".format(deltaTableName)
        tabDes = ibm_db.exec_immediate(conn, desCmd)
        loadDeltaStmt = "insert into {} (".format(deltaTableName)
        alterColList, colList = [], ""
        exemptionColList = [col.strip() for col in task['loader_diff_exempt_columns'].split(',')]
        joinColumns = [col.strip() for col in task['loader_join_columns'].split(",") ]
        # The array should never be empty unless somebody forgot to specify
        # "loader_join_columns" in the JSON file, but never hurts to check
        if joinColumns:
          # Despite not empty, the array may still contain only one element, and
          # that could be a null string ("loader_join_columns": "")
          if joinColumns[0]:
            orderByList = ','.join(joinColumns + [collectionTimeColName])
          else:
            orderByList = collectionTimeColName
        else:
         orderByList = collectionTimeColName
        tuple = ibm_db.fetch_tuple(tabDes)
        while tuple != False:
          colName, colType = tuple[0], tuple[2]
          loadDeltaStmt = "{} {},".format(loadDeltaStmt, colName)
          if colType in ["TIMESTAMP", "BIGINT"] and colName not in exemptionColList and colName != collectionTimeColName:
            if colType == "TIMESTAMP":
              alterColList.append(colName)
              colList="{} COALESCE(TIMESTAMPDIFF(2, current.{} - previous.{}), 0),".format(colList, colName, colName)
            else:
              colList="{} COALESCE(current.{} - previous.{}, 0),".format(colList, colName, colName)
          else:
            colList = "{} current.{},".format(colList, colName)
          tuple = ibm_db.fetch_tuple(tabDes)

        # Alter the column data type from TIMESTAMP to BIGINT
        for col in alterColList:
          stmt = ibm_db.exec_immediate(conn,"alter table {} alter column {} set data type bigint".format(deltaTableName, col))
          stmt = ibm_db.exec_immediate(conn, "CALL SYSPROC.ADMIN_CMD('reorg table {}' )".format(deltaTableName))
          stmt = ibm_db.exec_immediate(conn, "commit")

        # Remove the last comma and append the closing bracket
        loadDeltaStmt = loadDeltaStmt.rstrip(',') + ")"
        colList = colList.rstrip(',')
        loadDeltaStmt = "{} with current as ( SELECT ( row_number() over ( order by {} ) ) rowId, \
                         {}.* from {} order by {} ) select ".format(loadDeltaStmt, orderByList, tableName, tableName, orderByList)
        # Append the column list
        loadDeltaStmt += colList
        # Append the join clause
        loadDeltaStmt = "{} FROM current AS previous RIGHT JOIN current ON previous.rowId + 1 = current.rowId ".format(loadDeltaStmt)
        for col in joinColumns:
          # col can still be an empty string ("loader_join_columns": "")
          if col:
            loadDeltaStmt = "{} and previous.{} = current.{} ".format(loadDeltaStmt, col, col)

        # Load the delta data into table
        print("Loading data into", deltaTableName)
        stmt = ibm_db.exec_immediate(conn, loadDeltaStmt)

  print("Closing connection ...")
  ibm_db.close(conn)