Python实现Excel与MySQL数据库导入导出数据

Excel导入数据到MySQL

import pymysql
import openpyxl

wb = openpyxl.load_workbook('员工表.xlsx')
ws = wb.active
params = []
for row_idx in range(2, ws.max_row + 1):
    values = []
    for col_idx in range(1, ws.max_column):
        values.append(ws.cell(row_idx, col_idx).value)
    params.append(values)

conn = pymysql.connect(host='', port=3306,
                       user='', passwd='',
                       database='', charset='utf8mb4')
try:
    with conn.cursor() as cursor:
        # 批量插入操作
        cursor.executemany(
            'inser into tb (no, name) values (%s, %s)',
            params
        )
    conn.commit()
except pymysql.MySQLError as err:
    print(err)
    conn.rollback()
finally:
    conn.close()

MySQL导出数据到Excel

import pymysql
import openpyxl

conn = pymysql.connect(host='', port=3306,
                       user='', passwd='',
                       database='', charset='utf8mb4')
try:
    with conn.cursor() as cursor:
        cursor.execute(
            'select no from tb'
        )
        wb = openpyxl.Workbook()
        ws = wb.active
        title = ('工号', '姓名')
        for col_idx, col_name in enumerate(title):
            ws.cell(1, col_idx + 1, col_name)
        for row_idx, row in enumerate(cursor.fetchall()):
            for col_idx, col_value in enumerate(row):
                ws.cell(row_idx + 2, col_idx + 1, col_value)
        wb.save('员工表.xlsx')
except pymysql.MySQLError as err:
    print(err)
finally:
    conn.close()