Python从数据库拉取数据并导出成表格
- 数据库连接SSH(mysqlconn.py)
- 变量配置(timeUtil.py)
- SQL执行及结果获取(mysqlconn.py)
- 导出多个sheet的excel(exportExo.py)
又是提高工作效率的一个东西!!俗话说:磨刀不误砍柴工。
可我砍一次柴,磨一次刀🙂磨刀1小时,砍柴1分钟。
数据库连接SSH(mysqlconn.py)
话不多说,上代码!
def get_ssh_tunnel():
server = SSHTunnelForwarder(
ssh_address_or_host=('SSH HOSTNAME', 22),
ssh_username='SSH USERNAME',
ssh_pkey="/Users/aaa/KEY", # 密钥文件的路径
remote_bind_address=('MYSQL HOSTNAME', 3306),
# local_bind_address=('0.0.0.0', 3306)
)
server.start()
return server
connection = pymysql.connect(host = '127.0.0.1(固定这个,别改,别问,我不造)',port=get_ssh_tunnel().local_bind_port,user = '用户名',password = '密码',db = '指定库名')
if connection:
#对打印字体加了样式,对这个无聊的小东西有兴趣的可以自己搜一下相关样式
print('\033[1;37;42m连接数据库成功!\033[0m')#测试是否连接上
############此处加入SQL执行############
# 关闭数据库连接
connection.close()
# cur.close()
print('\033[1;37;41m关闭数据库成功!\033[0m')#测试是否连接上
执行一下!请看↓
至此我们是已经打通了数据库的大门了~
变量配置(timeUtil.py)
然后我SQL中会用到一些变量,比如自动获取这个月的第一天,最后一天啦吧啦吧啦,你们自己看需要
import datetime
from datetime import timedelta
import dateutil.relativedelta
def first_day_of_month(date_value):
return date_value.replace(day = 1).strftime('%Y-%m-%d 00:00:00')
def first_day_of_next_month(date_value):
date_value = date_value + dateutil.relativedelta.relativedelta(months=1)
return date_value.replace(day = 1).strftime('%Y-%m-%d 00:00:00')
缺什么加什么,这个世上我能随便加的除了奶茶里的糖就是代码行
SQL执行及结果获取(mysqlconn.py)
准备好你要的SQL,每一个SQL会有占一个sheet,我就展示一个给你们瞅瞅!以下代码就是插入到刚刚第一个代码段里面的!
sql1 = "select t1.id as '编号'," \
" t2.name as '名字'," \
" t2.create_time as '时间'" \
" from table1 t1 " \
" left join table2 t2 on t1.t2_id=t2.id " \
" where t2_id is not null " \
" and t2.create_time >= '" + first_day_of_month +"'" \
" and t2.create_time < '" + first_day_of_next_month + "'"
print("【SQL】sql1:",sql1)
然后拿着你的SQL们去数据库要人!这里我也只展示要一个人怎么要的!
# 执行第一个sql
cur.execute(sql1)
desc1 = cur.description
# for field in desc1:
# print(field[0])
print("\033[1;36mdemo:desc1:",desc1,'\033[0m')
#通过cur.fetchall()获取查询所有结果
result1 = cur.fetchall()
# for d in result1:
# print(d)
print("result1:",result1)
这人我给打码了哈
其中DESC呢是你每个结果的字段名详情是个list,你自己试试就知道了,我不方便透露太多🙂
导出多个sheet的excel(exportExo.py)
好了,人已经要到了,我们应该>>>>>你懂的!
from calendar import monthrange
import pandas as pd
# from mysqlconn import result1
# from mysqlconn import result2
# from mysqlconn import result3
# from mysqlconn import result4
# from mysqlconn import desc1
# from mysqlconn import desc2
# from mysqlconn import desc3
# from mysqlconn import desc4
# from mysqlconn import today
def excelName():
strftimeS = today.replace(day = 1).strftime('%m%d')
strftimeE = today.replace(day = monthrange(today.year, today.month)[1]).strftime('%m%d')
return strftimeS+'-'+strftimeE+'正经数据拉取'
result_list = [result1, result2,result3,result4]
sheet_name_list = ['sheet1的名字','玩一玩啊','别走啊','新年好']
column_list = [desc1,desc2,desc3,desc4]
writer = pd.ExcelWriter(excelName() + '.xlsx')
for i in range(len(result_list)):
colums_name = [i[0] for i in column_list[i]] # 读取数据表的字段名称
pd_data_frame = pd.DataFrame(result_list[i], columns=colums_name)
pd_data_frame.to_excel(writer, sheet_name=sheet_name_list[i], startrow=0, header=colums_name, index=False)
worksheet = writer.sheets[sheet_name_list[i]]
print("玩好了一个sheet:",sheet_name_list[i])
writer.close()
执行完之后,你的project
就会多一个你玩过的证据