!/usr/bin/env python

* coding:utf-8 *

import pandas as pd
import ibm_db_dbi
import time
import os

dbinfo = ['1', 'DB2', '192.168.1.131', '60000', 'SAMPLE', 'db2inst', 'password']

host = dbinfo[2]
port = dbinfo[3]
db = dbinfo[4]
user = dbinfo[5]
password = dbinfo[6]
stmt = 'SELECT tabschema,tabname from syscat.tables'
today = time.strftime("%Y%m%d")
iterm = 'TEST1'
writer = pd.ExcelWriter('./temp/{hostname}_{dbname}report{rdate}.xlsx'.format(hostname=host,dbname=db,rdate=today), engine='xlsxwriter')

try:
conn = (ibm_db_dbi.connect(
"DATABASE=" + db + ";"
"HOSTNAME=" + host + ";"
"PORT=" + port + ";"
"PROTOCOL=TCPIP;"
"UID=" + user + ";"
"PWD=" + password + ";"
"","", ""))
df = pd.read_sql(stmt, con=conn)

df.to_excel(writer,sheet_name='{sheetname}'.format(sheetname=iterm),index=False)
print df.to_json(orient='index')

conn.close()

except ibm_db_dbi.Error :
print '语句执行错误：', ibm_db_dbi.Error
conn.close()
'''文件写入保存'''
writer.save()