python查询db2数据库数据_【Python】从DB2数据库中取出字段信息并根据字段类型生成SQL...

1.需求描述

现在有以下需求，表结构如下：

需要将DATE类型的字段转化为字符串输出，如：to_char(HIREDATE,'YYYY-MM-DD')

最终需要得到的结果如下：

select ID,NAME,ADDR,to_char(HIREDATE,'YYYY-MM-DD') AS HIREDATE from CUST_INFO;

2.实现代码

# Author:Logan

# Date:2020/5/28 11:31

# IDE:PyCharm

import ibm_db

# 连接数据库获取数据

def conn_db2():

tab_name = 'CUST_INFO'

conn = ibm_db.connect("DATABASE=dm;HOSTNAME=10.0.0.24;PORT=60000;PROTOCOL=TCPIP;UID=app;PWD=app;", "", "")

sql = "select COLNAME,TYPENAME from syscat.columns where tabschema='APP' and tabname='" + tab_name + "' ORDER BY COLNO"

stmt = ibm_db.exec_immediate(conn,sql)

result = ibm_db.fetch_both(stmt)

res = "select "

while(result):

if result[1] == 'DATE':

str = "to_char("+ result[0] +",'YYYY-MM-DD') AS " + result[0] + ' from ' + tab_name

else:

str = result[0]

res = res + str + ','

result = ibm_db.fetch_both(stmt)

res = res[:-1] + ";"

print(res)

def main():

# 1.获取数据

conn_db2()

if __name__ == '__main__':

main()

代码执行结果：