标题就是需求,使用语言是python,用到了pymysql库和pandas库(没有的话自行pip install),代码如下:
import pymysql
import pandas as pd
# 连数据库
conn = pymysql.connect(host='localhost', user='root', password='0000', db='p1')
# 创建列表
excel_file = pd.ExcelFile('数组.xlsx')
df1 = pd.read_excel(excel_file, sheet_name='Sheet1')
df2 = pd.read_excel(excel_file, sheet_name='Sheet2')
# 将df1和df2转换为列表
list1 = df1.values.tolist()
list2 = df2.values.tolist()
# 进行数据对比并更新列表
i = 0
j = 0
while i < len(list1):
print('第' + str(i) + f'行,进度{round(i / 24956 * 100, 1)}%')
while j < len(list2):
if str(list1[i][0]) == str(list2[j][0]) and str(list1[i][1]) == str(list2[j][1]) and (str(list1[i][5]) == str(list2[j][3]) or str(list1[i][6]) == str(list2[j][5])) and str(list1[i][8]) == str(list2[j][9]):
list1[i][13] = list2[j][15]
list1[i][14] = list2[j][16]
list1[i][15] = list2[j][17]
list1[i][16] = list2[j][18]
list1[i][17] = list2[j][19]
list1[i][18] = list2[j][20]
j += 1
j = 0
i += 1
# 创建列表的idx用来给sql提供idx
idxs = []
columns_sql = ""
for idx in df1.columns:
idxs.append(str(idx))
print(idx)
columns_sql += f"{str(idx)} VARCHAR(255) NOT NULL, "
print(len(idxs))
# 创建一个游标对象
cursor = conn.cursor()
# 定义创建表的SQL语句
sql = f'''CREATE TABLE StudyTable (
{columns_sql[:-2]},
PRIMARY KEY ({idxs[0]})
)'''
# 生成数据表
cursor.execute(sql)
# 将二维列表插入到MySQL表中
placeholders = ', '.join(['%s'] * len(idxs))
insert_sql = f"INSERT INTO StudyTable VALUES ({placeholders})"
# 执行SQL语句
cursor.executemany(insert_sql, list1)
# 提交事务
conn.commit()
# 关闭连接
cursor.close()
conn.close()