python通过excel连接mysql进行查询

前言

我们需要根据excel表的一些关键数据，拼接sql语句进行查询

代码

import pandas as pd
import pymysql

excel = pd.read_excel(r"xxxxxxxxx", engine="openpyxl")
biao = excel["表"]
ziduan = excel["字段"]
shuoming = excel["说明"]
conn = pymysql.connect(
    host='xxx',
    user='xx',
    password='xxxxxx',
    port=xxx,
    db="xxx",
    charset='utf8',
)
for i in range(218, 230):
    create_sql1 = "SELECT COUNT(*) FROM `{}` WHERE `{}` IS NULL INTO @meiyou;".format(biao[i], ziduan[i])
    create_sql2 = "SELECT COUNT(*) FROM `{}`  INTO @total;".format(biao[i])
    create_sql3 = "SELECT @meiyou/@total AS 缺失值;"

    cur = conn.cursor()
    cur.execute(create_sql1)
    cur.execute(create_sql2)
    result = cur.execute(create_sql3)
    print(shuoming[i], cur.fetchone())

总结

• 遇到的小坑就是pandas.read_excel()读取错误，因为它默认依赖的xlrd库更新后，就不支持对.xlsx文件的读取。要么将xlrd版本回滚，要么手动选择引擎，此次我们选择openpyxl，一个能读能写的python库。