因为系统运维需要执行的sql语句较多，也为了方便同事使用写了个执行sql的小工具，打包成exe后只需修改同目录下的excel即可管理
sql语句
配置
输出的结果每个sql对应excel一个页签
Python代码（Oracle）
# -*- coding: utf-8 -*-
"""
Created on Thu Sep 10 09:27:26 2020
@author: Nilz
"""
import pandas as pd
import cx_Oracle
import datetime
from dateutil.relativedelta import relativedelta
def sqlboy(df_sql,df_conf,charset='utf8'):
try:
connection = cx_Oracle.connect(df_conf.iloc[[0],[0]].values[0][0],
df_conf.iloc[[0],[1]].values[0][0],
df_conf.iloc[[0],[2]].values[0][0])
except Exception as e:
print('连接数据库出错!! ',e)
return "ok"
print(connection)
today = datetime.date.today() # 1. 获取「今天」
df = pd.read_excel('sql.xlsx')
cursor = connection.cursor() #建立游标
try:
with pd.ExcelWriter('.out输出日志.xlsx') as writer:
for i in range(len(df_sql)):
sql = df_sql['sql'][i]
cursor.execute(sql) #执行sql
title = [j[0] for j in cursor.description] #获取oracle表头
new_df = pd.DataFrame(cursor.fetchall())
try:
new_df.to_excel(writer,sheet_name = df_sql['需求描述（简短）'][i],header=title, index=0)
except:
print(df_sql['需求描述（简短）'][i],'执行结果为空')
continue
print('已完成',df_sql['需求描述（简短）'][i],'的输出。')
except Exception as e:
print('执行输出过程出错!! ',e)
finally:
cursor.close()
connection.close()
return "ok"
#调用自定义函数sqlboy.py
if __name__ == "__main__":
print('开始执行。。。')
df_xlsx = input("请输入读取文件名(如sql.xlsx,则输入sql)：")
df_sql = pd.read_excel('.\'+df_xlsx+'.xlsx',sheet_name=0) #sql
df_conf = pd.read_excel('.\'+df_xlsx+'.xlsx',sheet_name=1) #数据库连接配置
sqlboy(df_sql,df_conf)
out = input("执行完毕！！回车退出。。。")
Python代码（MySQL）
# -*- coding: utf-8 -*-
"""
Created on Thu Sep 10 09:27:26 2020
@author: Nilz
"""
import pandas as pd
import pymysql
def sqlboy(df,port=3306,charset='utf8'):
try:
connection = pymysql.connect(host='localhost',user='root',password='123456',
database='mysql50',charset='utf8')
print("连接数据库成功")
except:
print("连接数据库失败")
return "ok"
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor) #建立游标
with pd.ExcelWriter('D:桌面111.xlsx') as writer:
for i in range(len(df)):
sql = df['sql'][i]
# print(sql)
cursor.execute(sql)#执行sql
new_df = pd.DataFrame(cursor.fetchall())
new_df.to_excel(writer,sheet_name = df['需求'][i],header=1, index=0)
return "ok"
if __name__ == '__main__':
df = pd.read_excel('D:桌面sql.xlsx')
sqlboy(df)
print("程序执行完毕")
