python把excel表格数据传入sql数据库，并删除一部分数据。

​
import  pymysql
import openpyxl


database = pymysql.connect(host='192.168.99.90',user='root',password='xxxx123456@',
                            port=3306,database='company',charset='utf8')

cur = database.cursor()


excel_path = r'D:\xxx\Python\homework\20230904\excel数据读取练习题\testdata03.xlsx'


wb = openpyxl.load_workbook(excel_path)
sheet1 = wb.worksheets[0]
rows = sheet1.iter_rows()

for row in rows:
    cell_list = []
    for cell in row:
        cell_list.append(cell.value)
    print(cell_list)
    cell_tup = tuple(cell_list)
    print(cell_tup)
    sql = 'INSERT INTO xxx VALUES {};'.format(cell_tup)
    cur.execute(sql)
# 因为sql语句values后面语法是小括号
# insert into Student(name,gender,age) values(‘张三’, ’男’, 18)
# 所以要转换列表数据类型为元组数据类型

sql1 = r"INSERT INTO xxx VALUES(%s,%s,%s,%s);"
datas = [('SCH26','C++','30','jnc'),
        ('SCH24','C++','30','jnc'),
        ('SCH25','C++','30','jnc')]


for i in range(1,50):
    sql = f"delete from xxx where CID='SCH{i}';"
    cur.execute(sql)


database.commit()

cur.close()
database.close()

​