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()