Python常用脚本-查询Oracle数据库，嵌套循环查询数据库记录，输出到多个Excel到指定目录

# coding=utf-8
# 证照模板下载，查询中心库，调用网站接口，下载文件保存到本地
# 查询Oracle数据库，循环导出多个Excel到目录
import requests
import json
import datetime
import xlwt
import os
import cx_Oracle as oracle


def download(url,fname):
    headers = {
        'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.125 Safari/537.36',
        'Referer':'http://5*.203.1*3.182:8800/zzwk/catalog/certificateStyleView.do?id=ff80808175b737d50175e37151e6171a',
        'Host':'59.203.153.182:8800',
        'method':'GET',
        'Origin':'http://5*.203.153.182:8800',
        'Content-Type': 'application/x-www-form-urlencoded',
        'Cookie':'JSESSIONID=480B303CC5E1BAD338442CAEBF3C1659.tomcat8080; loginType=normal; cookie=20111181'
    }
    response = requests.get(url=url, headers=headers)
    filename =fname+".zip"
    print(filename)
    with open('d:\\dc\\'+filename, "wb") as code:
        code.write(response.content)


# 查询数据库，获取结果集
def get_site_id_by_name():
    db = oracle.connect('u_z**/U_*ZW_2018@1**.23.*.48:1521/**zz')
    cursor = db.cursor()
    qsite_sql = "select * from LSB0819"
    cursor.execute(qsite_sql)
    data = cursor.fetchall()
    print(data)
    return data


def get_yuan_info(id):
    db = oracle.connect('u_**w/U_ZZW_2018@**2.23.*.48:1521/**zz')
    cursor = db.cursor()
    qsite_sql = "SELECT ROWNUM AS 序号，W.* FROM (select metadata 字段名称,name 字段编码,decode(type, 'varchar2', '文本', 'clob', '图片')  字段类型, decode(is_require, 'Y', '是', 'N', '否')  是否必填"   \
                "  from zzwk_business_metadata"  \
                " where delete_state = '0'" \
                "   and style_id in"  \
                "  (select id from zzwk_style s where s.code = '%s')"  \
                "union  all"   \
                "  select  字段名称, '','文本' 类型,    decode(是否必填, 'Y', '是', 'N', '否')  from zzwk_business_metadata_y)W"%id;
    print(qsite_sql)
    cursor.execute(qsite_sql)
    data = cursor.fetchall()
    print(data)
    return data


def export(id,name):
    results=get_yuan_info(id)
    print(results)
    workbook = xlwt.Workbook(encoding='utf-8')
    # 创建一个worksheet
    worksheet = workbook.add_sheet(name+"-照面信息")
     # 写入excel
    # 参数对应 行, 列, 值

    columnName=['序号','字段名称','字段编码','字段类型','是否必填'];

    # 获取行数
    rows = len(results)
    # 获取列数
    columns = len(columnName)

    tstyle = xlwt.XFStyle()
    borders = xlwt.Borders()
    borders.left = xlwt.Borders.THIN
    borders.right = xlwt.Borders.THIN
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN

    borders.left_colour = 0
    borders.right_colour = 0
    borders.top_colour = 0
    borders.bottom_colour = 0

    tstyle.borders = borders

    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_CENTER  # 设置水平位置，0是左对齐，1是居中，2是右对齐
    # 设置自动换行
    alignment.wrap = 1
    tstyle.alignment = alignment

    cstyle=tstyle;

    font1 = xlwt.Font()
    #font1.name = 'Times New Roman'
   # 字体加粗
    #font1.bold = True
    cstyle.font=font1


    #for i in range(columns):
        # 设置列的宽度
        #worksheet.col(i).width = 5000
    worksheet.col(0).width = 2000
    worksheet.col(1).width = 8000
    worksheet.col(2).width = 5000
    worksheet.col(3).width = 5000
    worksheet.col(4).width = 5000



    # 插入列名
    for i in range(columns):
        worksheet.write(0, i, columnName[i],tstyle)

    # 将数据插入表格
    for i in range(0, rows):
        for j in range(columns):
            worksheet.write(i+1, j, results[i][j],cstyle)
    workbook.save("d:\\dcx\\"+name+".xls")

if __name__ == '__main__':
   zzdata=get_site_id_by_name()
   print(zzdata)
   for i in zzdata:
            id=i[1]
            name=i[0]
            print('id:'+id)
            print('name:' + name)
            # 下载
            export(id,name)

　　

5*