Python系列之MySQL查询数据导出Excel

最近接到需求,需要通过一条SQL查询出来的id,去过滤另外一条SQL的数据,听起来并不难,但是因为业务原因需要查询很多个环境,而且需要经常查询,所以想到通过python程序来实现,先查出一个SQL数据,然后通过id拼装in查询出数据。

开发环境

  • MySQL 10.1.38-MariaDB-1~bionic
  • Python3.7.8

开发工具

  • PyCharm2018.1
  • SmartGit18.1
  • Navicat15.0.28

先引入一些依赖,主要有​​pandas​​​和​​pymysql​

  • Pandas 是基于 BSD 许可的开源支持库,为 Python 提供了高性能、易使用的数据结构与数据分析工具。
  • Pymysql:PyMySQL是从Python连接到MySQL数据库服务器的接口。 它实现了Python数据库API v2.0,并包含一个纯Python的MySQL客户端库


import pandas as pd
import warnings

import pymysql

先进行数据库配置,需要引入​​pymysql​​,封装一个连接数据库的函数

# 数据库配置
MYSQL_HOST_ITEM = '127.0.0.1'
MYSQL_PORT_ITEM = 3306
MYSQL_USER_ITEM = 'root'
MYSQL_PASSWORD_ITEM = '11'
MYSQL_DATABASE_ITEM = 'test'
MYSQL_CHARSET_ITEM = 'utf8'

# 定义连接mysql函数
def connetmysql(host, port, user, password, database, sql, charset='utf8'):
conn = pymysql.connect(host=host,
port=port,
user=user,
password=password,
database=database,
charset=charset)
cursor = conn.cursor()
sql = """%s""" % sql
# 遇到时间格式自动解析
df_mysql = pd.read_sql(sql, conn, parse_dates=True)
df_mysql.columns = [x.lower() for x in df_mysql.columns]
cursor.close()
return df_mysql

先查询出ids

def readPlatformDept():
sql_script = '''
SELECT id from t1
'''
sql_data = connetmysql(host=MYSQL_HOST_PLATFORM, port=MYSQL_PORT_PLATFORM,
user=MYSQL_USER_PLATFORM,
password=MYSQL_PASSWORD_PLATFORM, sql=sql_script,
database=MYSQL_DATABASE_PLATFORM)
return sql_data

通过ids 使用in查询

def readItemDeptNum(codes):
sql_script = '''SELECT
*
FROM
t2
code in ({codes})
'''.format(codes=', '.join("'" + item + "'" for item in codes))
# sql_script = sql_script % ','.join(['%s']*len(tongyicodes))
sql_data = connetmysql(host=MYSQL_HOST_ITEM, port=MYSQL_PORT_ITEM,
user=MYSQL_USER_ITEM,
password=MYSQL_PASSWORD_ITEM, sql=sql_script,
database=MYSQL_DATABASE_ITEM)

return sql_data

main函数进行调用

if __name__ == "__main__":
platform_data = readPlatformDept()
item_data = readItemDepNum(platform_data['id'])
# 导出Excel
item_data.to_excel('item_query_excel.xlsx')
import pandas as pd
import warnings

import pymysql

warnings.filterwarnings("ignore")

# Platform配置
MYSQL_HOST_PLATFORM = '127.0.0.1'
MYSQL_PORT_PLATFORM = 3306
MYSQL_USER_PLATFORM = 'root'
MYSQL_PASSWORD_PLATFORM = '111'
MYSQL_DATABASE_PLATFORM = 'test'
MYSQL_CHARSET_PLATFORM = 'utf8'
# ITEM配置
MYSQL_HOST_ITEM = '127.0.0.1'
MYSQL_PORT_ITEM = 33306
MYSQL_USER_ITEM = 'root'
MYSQL_PASSWORD_ITEM = '111'
MYSQL_DATABASE_ITEM = 'item'
MYSQL_CHARSET_ITEM = 'utf8'

# 定义连接mysql函数
def connetmysql(host, port, user, password, database, sql, charset='utf8'):
conn = pymysql.connect(host=host,
port=port,
user=user,
password=password,
database=database,
charset=charset)
cursor = conn.cursor()
sql = """%s""" % sql
# 遇到时间格式自动解析
df_mysql = pd.read_sql(sql, conn, parse_dates=True)
df_mysql.columns = [x.lower() for x in df_mysql.columns]
cursor.close()
return df_mysql


def readPlatformDept():
sql_script = '''
SELECT id from t1
'''
sql_data = connetmysql(host=MYSQL_HOST_PLATFORM, port=MYSQL_PORT_PLATFORM,
user=MYSQL_USER_PLATFORM,
password=MYSQL_PASSWORD_PLATFORM, sql=sql_script,
database=MYSQL_DATABASE_PLATFORM)
return sql_data

def readItemDeptNum(codes):
sql_script = '''SELECT
*
FROM
t2
code in ({codes})
'''.format(codes=', '.join("'" + item + "'" for item in codes))
# sql_script = sql_script % ','.join(['%s']*len(tongyicodes))
sql_data = connetmysql(host=MYSQL_HOST_ITEM, port=MYSQL_PORT_ITEM,
user=MYSQL_USER_ITEM,
password=MYSQL_PASSWORD_ITEM, sql=sql_script,
database=MYSQL_DATABASE_ITEM)

return sql_data

if __name__ == "__main__":
platform_data = readPlatformDept()
item_data = readItemDepNum(platform_data['id'])
item_data.to_excel('item_query_excel.xlsx')

然后程序就写好了,需要写一个shell脚本启动一些python

python3 start.py

因为是java开发,python并没有学过,所以通过自己摸索,还是可以写出来,对比一下java,觉得python语法有时候确实比较简便,比如要导出Excel,一行代码就可以,然后到linux上部署也比较容易,所以觉得后端程序员掌握一门脚本语言还是有需要的