自动化办公！Python 高效读取 Excel 并写入 Mysql

# 数据库配置信息

PageCount=10

dbhost="10.10.1.248"
dbport=3306
dbuser="root"
dbpasswd="123456"
dbname="testdb"

# 数据库操作

import pymysql
from configs import dbhost,dbpasswd,dbport,dbuser,dbname

def get_cur():
    conn = pymysql.connect(host=dbhost, port=dbport, user=dbuser, passwd=dbpasswd, database=dbname)
    cur = conn.cursor()
    return cur,conn

def get_count(sql):
    cur,conn = get_cur()
    cur.execute(sql)
    count = cur.fetchall()
    cur.close()
    conn.close()
    return count[0][0]


def get_table_colum():
    cur,conn = get_cur()
    cur.execute("select * from xxljob_info")
    col_name_list = [tuple[0] for tuple in cur.description]
    cur.close()
    conn.close()
    return col_name_list

def get_data(sql1):
    cur,conn = get_cur()
    cur.execute(sql1)
    results = cur.fetchall()
    cloumn = get_table_colum()
    res = {}
    reslist = []
    for r in range(len(list(results))):
        for m in range(len(list(cloumn))):
            res[str(list(cloumn)[m])] = str(list(results)[r][m])
        reslist.append(res)
        res = {}
    cur.close()
    conn.close()
    return reslist

def execute_sql(sql):
    cur, conn = get_cur()
    cur.execute(sql)
    cur.close()
    conn.commit()
    conn.close()

if __name__ == '__main__':
    get_table_colum()
    sql = "insert into xxljob_info (executer,describes,router,block,cron,is_repeater,leader,status,mark,create_time) VALUES ('testabc','testabc','第一个','单机串行','testabc','否','testabc','testabc','testabc','2022-06-16 13:14:19')"

    for i in range(55):
        execute_sql(sql)
      # get_count("select count(*) from xxljob_info")

# 读取excel表数据,并执行sql

import xlrd
from mysqldb import execute_sql
file = xlrd.open_workbook("testdata.xls")
table1 = file.sheet_by_name("数据表")
sql = "insert into testdata values "

for row in range(table1.nrows - 1):
    list1 = table1.row_values(row + 1)
    list1 = [row + 1] + list1
    execute_sql(sql + str(tuple(list1)))