python处理excel数据去除a列中含b列的内容
2022/5/4 17:13:18
本文主要是介绍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本身支持单列去重
这篇关于python处理excel数据去除a列中含b列的内容的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-08有遇到过吗?同样的规则 Excel 中 比Python 结果大
- 2024-03-30开始python成长之路
- 2024-03-29python optparse
- 2024-03-29python map 函数
- 2024-03-20invalid format specifier python
- 2024-03-18pool.map python
- 2024-03-18threads in python
- 2024-03-14python Ai 应用开发基础训练,字符串,字典,文件
- 2024-03-13id3 algorithm python
- 2024-03-13sum array elements python