利用Python自动化执行本地的多个sql脚本，导出excel数据

利用Python自动化执行本地的多个sql脚本，导出excel数据

代码如下

import cx_Oracle
import openpyxl
import os
import printFile

def export_excel(sql,fileName):
    rr = curs.execute(sql)
    rows = curs.fetchall()

    #获取字段名
    title = [ i[0] for i in curs.description ]

    #创建excel表
    wb = openpyxl.Workbook()
    ws = wb.active

    #插入字段名到第一行
    for c in range(len(title)):
        ws.cell(1,c+1,value = title[c])

    #写入查询数据
    for r in range(len(rows)):
        for c in range(len(rows[r])):
            if rows[r][c]: #值不为空时写入，空值不写入
                ws.cell(r+2,c+1,value=str(rows[r][c])) #str()防止用科学计数法写入造成信息丢失

    #保存sql脚本
    ws1 = wb.create_sheet('sql')
    ws1.cell(1,1,value=sql)

    wb.save(fileName)
    wb.close()
    curs.close()

# 定义函数
def getDBInfo(DIRECTORY,FILENAME):
    # 打开sql文件获取sql语句
    with open(DIRECTORY + "\\" + FILENAME, 'r', encoding='UTF-8') as sql_0:
        sql = sql_0.read()
        print(sql)
        export_excel(sql, 'book_' + FILENAME + '.xlsx')
    conn.close()

if __name__ == '__main__':
    conn = cx_Oracle.connect('test/123456789@192.168.1.131/orcl',encoding = 'utf-8') #utf-8显示中文
    curs= conn.cursor()
    # 调用函数
    DIRECTORY = 'F:\\sqlExecute'
    FILENAME = 'query2.sql'
    #getDBInfo(DIRECTORY,FILENAME)
    # 文件路径
    file_path = r'F:\\sqlExecute'
    # 提取文件中的所有文件生成一个列表
    folders = os.listdir(file_path)
    for file in folders:
        # 打印所有文件名
        print(file)
        getDBInfo(DIRECTORY,file)

运行结果及报错内容

query1.sql
SELECT
        a.email "电子邮件"
FROM
        test1 a
query2.sql
SELECT
        id AS "ID编号",
        name AS "名字",
        a.HIRE_DATE "日期",
        a.email "电子邮件"
FROM
        test1 a
Traceback (most recent call last):
  File ".\exportData2.py", line 59, in <module>
    getDBInfo(DIRECTORY,file)
  File ".\exportData2.py", line 42, in getDBInfo
    export_excel(sql, 'book_' + FILENAME + '.xlsx')
  File ".\exportData2.py", line 8, in export_excel
    rr = curs.execute(sql)
cx_Oracle.InterfaceError: not open

其他文件

--- query1.sql 
SELECT
    a.email "电子邮件"
FROM
    test1 a

--- query2.sql 
SELECT
    id AS "ID编号",
    name AS "名字",
    a.HIRE_DATE "日期",
    a.email "电子邮件"
FROM
    test1 a

我想要达到的结果

例如指定文件夹 F:\sqlExecute 有多个（n个）脚本 query1.sql 、query2.sql
导出结果就是本地生成两个（或多个）excel文件， query1.xlsx query2.xlsx

因为没有Oracle数据库，没法测试，目测你的问题是没有把curs参数传递到export_excel函数中，简单的解决方案就是在getDBInfo和export_excel函数都加个参数curs，调用的时候把curs传递进去

import cx_Oracle
import openpyxl
import os
import printFile
 
def export_excel(sql,fileName,curs):
    rr = curs.execute(sql)
    rows = curs.fetchall()
 
    #获取字段名
    title = [ i[0] for i in curs.description ]
 
    #创建excel表
    wb = openpyxl.Workbook()
    ws = wb.active
 
    #插入字段名到第一行
    for c in range(len(title)):
        ws.cell(1,c+1,value = title[c])
 
    #写入查询数据
    for r in range(len(rows)):
        for c in range(len(rows[r])):
            if rows[r][c]: #值不为空时写入，空值不写入
                ws.cell(r+2,c+1,value=str(rows[r][c])) #str()防止用科学计数法写入造成信息丢失
 
    #保存sql脚本
    ws1 = wb.create_sheet('sql')
    ws1.cell(1,1,value=sql)
 
    wb.save(fileName)
    wb.close()
    curs.close()
 
# 定义函数
def getDBInfo(DIRECTORY,FILENAME,curs):
    # 打开sql文件获取sql语句
    with open(DIRECTORY + "\\" + FILENAME, 'r', encoding='UTF-8') as sql_0:
        sql = sql_0.read()
        print(sql)
        export_excel(sql, 'book_' + FILENAME + '.xlsx',curs)
    conn.close()
 
if __name__ == '__main__':
    conn = cx_Oracle.connect('test/123456789@192.168.1.131/orcl',encoding = 'utf-8') #utf-8显示中文
    curs= conn.cursor()
    # 调用函数
    DIRECTORY = 'F:\\sqlExecute'
    FILENAME = 'query2.sql'
    #getDBInfo(DIRECTORY,FILENAME)
    # 文件路径
    file_path = r'F:\\sqlExecute'
    # 提取文件中的所有文件生成一个列表
    folders = os.listdir(file_path)
    for file in folders:
        # 打印所有文件名
        print(file)
        getDBInfo(DIRECTORY,file,curs)

我也经常有此类需求，建议：

1. 执行SQL，改用pymysql的read_sql方法；
2. 输出excel，改用to_excel方法；

import pymysql.cursors

# 数据库配置
config = {
    'host': '',
    'port': 3306,
    'user': '',
    'password': '',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor,
}

conn = pymysql.connect(**config)
sql_data = pd.read_sql(sql, conn) # SQL是本地读入的SQL文本
conn.close()

# 输出为excel文件
out_path = path + file_name # path是文件路径，file_name是输出文件名
writer = pd.ExcelWriter(out_path, engine='xlsxwriter')
result.to_excel(writer, index=None)
writer.save()

数据库关闭或没有响应！端口是否为1521，192.168.1.131:1521