Python 连接db2 以及读取Excel 操作

来源:互联网 发布:mac傻瓜剪辑视频软件 编辑:程序博客网 时间:2024/03/29 17:48
import ibm_dbimport xlrdconn = ibm_db.connect("catalogdb",'username','passwd')#Preparing and executing a single SQL statement in Python#https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.swg.im.dbclient.python.doc/doc/t0054695.htmlif conn:    sql = 'select * from DWDM2.tablename with ur'    stmt=ibm_db.exec_immediate(conn,sql)    print "number of affected rows: ",ibm_db.num_rows(stmt)   #number of affected rows:  -1    result = ibm_db.fetch_both(stmt)    #description : fetch data from table ,result means one column    #result from table:  3603 0 POPULATE 2017-07-04 22:24:05 2017-07-05 00:48:58 COMPLETED    while(result):        print "result from table: ",result[0],result[1],result[2],result[3],result[4],result[5]        result = ibm_db.fetch_both(stmt)    #Preparing and executing SQL statements with variable input in Python    #https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.swg.im.dbclient.python.doc/doc/t0054696.html    sqlwithvariable = "select * from DWDM2.SNS_PROCESS_STATUS where  row_id > ? and row_id < ? "    statprepare = ibm_db.prepare(conn,sql)    maxs = 3000    mins = 2456    # Explicitly bind parameters    #ibm_db.bind_param(statprepare, 1, mins)   # not works    #ibm_db.bind_param(statprepare, 2, maxs)    param = maxs, mins,    results = ibm_db.execute(statprepare,param)  #works    if results:        print "results from table with prepare: ", results    #Fetching rows or columns from result sets in Python    #https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.swg.im.dbclient.python.doc/doc/t0054388.html    # description    '''    Fetch data from a result set by calling one of the fetch functions.    Function           Description    ibm_db.fetch_tuple Returns a tuple, which is indexed by column position, representing a row in a result set. The columns are 0-indexed.    ibm_db.fetch_assoc Returns a dictionary, which is indexed by column name, representing a row in a result set.    ibm_db.fetch_both Returns a dictionary, which is indexed by both column name and position, representing a row in a result set.    ibm_db.fetch_row Sets the result set pointer to the next row or requested row. Use this function to iterate through a result set.    we already have give an example about function fetch_both ,so will not give one in the blow example ,thanks    '''    print "hello new world"    stmtfetch_assoc = ibm_db.exec_immediate(conn, sql) # can not uses the first stam defined above ,create a new one    dictionary = ibm_db.fetch_assoc(stmtfetch_assoc)    print dictionary    while dictionary != False:        print "the row id of table sns is :" ,dictionary["ROW_ID"]        print "the action code of table sns is :",dictionary["ACTION_CODE"]        print "the comment of table sns is :",dictionary["COMMENT"]   # get line by column nane ,not by the index of each line        dictionary = ibm_db.fetch_assoc(stmtfetch_assoc)    stmtfetch_tuple = ibm_db.exec_immediate(conn, sql)    tuple = ibm_db.fetch_tuple(stmtfetch_tuple)    while tuple :        print "tuple the row id of table sns is :", tuple[0]        print "tuple the action code of table sns is :", tuple[1]        print "tuple the comment of table sns is :", tuple[2]        tuple = ibm_db.fetch_tuple(stmtfetch_tuple)    stmtfetch_row = ibm_db.exec_immediate(conn, sql)    resullllt = ibm_db.fetch_row(stmtfetch_row)    while ibm_db.fetch_row(stmtfetch_row) !=False:  # this place can not use resullllt to replace        print "The Employee number is : ", ibm_db.result(stmtfetch_row, 0)        print "The last name is : ", ibm_db.result(stmtfetch_row, "COMMENT")data = xlrd.open_workbook("subidacq4.7.17.xls")table = data.sheet_by_index(0)table1 = data.sheets()[0]#table2 =data.sheet_by_name("sheetname")#get n rows and n colusnrows= table.nrowsncols =table.ncolsprint table.row_values(12) ,ncols,nrows  #[u'ALFP0006', u'ALGO', u'2008-01-01', u'', u'', u'KMROSA', u'', u'JSPINELLI'] 8 23245for rownum in range(nrows):    print table.row_values(rownum)#cell functionprint table.cell(0,0).valueprint table.cell(2,4).valueprint table.row(0)[0].valueprint table.col(1)[0].value

原创粉丝点击