【Python】从DB2数据库中取出字段信息并根据字段类型生成SQL

目录

• 1.需求描述
• 2.实现代码

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()

代码执行结果：