python处理excel数据去除a列中含b列的内容

1 首先将a列的值存入mysql,给予状态值status=1

import openpyxl
import pymysql

# 打开工作簿
workbook = openpyxl.load_workbook('D:\test.xlsx')
# 获取表单
sheet = workbook['Sheet1']
colA = sheet['A']
colB = sheet['B']
mysql_config={
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "123456",
    "charset": "utf8mb4"
}
mysql_config['db'] = 'testdb'
connection = pymysql.connect(**mysql_config)

for each_cell in colA:
    print(each_cell.value)
    cursor = connection.cursor()
    try:
        sql = """INSERT INTO `table1`(`id`, `status`) VALUES ({val}, 1)""".format(val=each_cell.value)
        cursor.execute(sql)
        connection.commit()
    except Exception as e:
        connection.rollback()

2 循环读取b列值在数据库中查询给予状态值2

for each_cell in colB:
    print(each_cell.value)
    cursor = connection.cursor()
    try:
        sql = """UPDATE `table1` SET `status` = 2 WHERE `id` = {val} AND `status` = 1""".format(val=each_cell.value)
        cursor.execute(sql)
        connection.commit()
    except Exception as e:
        connection.rollback()

3 此时可以导出mysql数据到excel，筛选status=1的行即可。

PS: 需要将id列设为主键保证唯一性，否则b列数据几十万时速度会非常慢，excel本身支持单列去重