import pymysql

# 連接到MySQL數(shù)據(jù)庫(kù)
mydb = pymysql.connect(
    host="localhost",
    user="root",
    passwd="123456",
    db="test"
)

from openpyxl import load_workbook

# 打開(kāi)Excel文件
wb = load_workbook(filename=r'E:\重新開(kāi)始\Python操作MySQL數(shù)據(jù)庫(kù)\sheet1.xlsx')
sheet = wb.active

# 獲取表頭
header = [cell.value for cell in sheet[1]]

# 遍歷每一行數(shù)據(jù)，并將其插入到數(shù)據(jù)庫(kù)中
cursor = mydb.cursor()
count = 0
for row in sheet.iter_rows(min_row=2, values_only=True):
    sql = f"INSERT INTO yonghu ({', '.join(header)}) VALUES ({', '.join(['%s'] * len(header))})"
    cursor.execute(sql, row)
    count += 1
    print(f"正在插入{count}條數(shù)據(jù)")

# 提交更改并關(guān)閉數(shù)據(jù)庫(kù)連接
mydb.commit()
cursor.close()
mydb.close()

from openpyxl import load_workbook  # 導(dǎo)入load_workbook函數(shù)，用于加載Excel文件
import pymysql  # 導(dǎo)入pymysql庫(kù)，用于連接和操作MySQL數(shù)據(jù)庫(kù)

# 連接到MySQL數(shù)據(jù)庫(kù)
mydb = pymysql.connect(
    host="localhost",
    user="root",
    passwd="123456",
    db="test"
)

# 打開(kāi)Excel文件
wb = load_workbook(filename=r'E:\重新開(kāi)始\Python操作MySQL數(shù)據(jù)庫(kù)\sheet1.xlsx')  # 加載Excel文件
sheet = wb.active  # 獲取活動(dòng)工作表

# 獲取表頭
header = [cell.value for cell in sheet[1]]  # 獲取第一行的單元格值，作為表頭

# 遍歷每一行數(shù)據(jù)，并將其插入到數(shù)據(jù)庫(kù)中
cursor = mydb.cursor()  # 創(chuàng)建游標(biāo)對(duì)象，用于執(zhí)行SQL語(yǔ)句
count = 0  # 計(jì)數(shù)器，用于記錄插入的數(shù)據(jù)條數(shù)
for row in sheet.iter_rows(min_row=2, values_only=True):  # 從第二行開(kāi)始遍歷每一行數(shù)據(jù)
    sql = f"INSERT INTO yonghu ({', '.join(header)}) VALUES ({', '.join(['%s'] * len(header))})"
    # 構(gòu)建插入數(shù)據(jù)的SQL語(yǔ)句，使用占位符%s
    cursor.execute(sql, row)  # 執(zhí)行SQL語(yǔ)句，將行數(shù)據(jù)插入到數(shù)據(jù)庫(kù)中
    count += 1  # 每插入一條數(shù)據(jù)，計(jì)數(shù)器加1
    print(f"正在插入{count}條數(shù)據(jù)")  # 輸出插入的數(shù)據(jù)條數(shù)

# 提交更改并關(guān)閉數(shù)據(jù)庫(kù)連接
mydb.commit()  # 提交對(duì)數(shù)據(jù)庫(kù)的更改
cursor.close()  # 關(guān)閉游標(biāo)對(duì)象
mydb.close()  # 關(guān)閉與數(shù)據(jù)庫(kù)的連接