import pandas as pd
import mysql.connector
from mysql.connector import errorcode

# 資料庫連線資訊
DB_HOST = "mysql.theaken.com"
DB_PORT = 33306
DB_NAME = "db_A018"
DB_USER = "A018"
DB_PASSWORD = "4MQYkJRYtyLE"

# Excel 檔案路徑
EXCEL_FILE = "c:\\AI_Program\\C0908\\Data\\pizza.xlsx"

def insert_data_to_db():
    try:
        # 讀取 Excel 檔案
        data = pd.read_excel(EXCEL_FILE)

        # 檢查欄位名稱是否正確
        required_columns = ['name', 'size', 'price']
        for column in required_columns:
            if column not in data.columns:
                raise ValueError(f"Excel 檔案缺少必要欄位: {column}")

        # 建立資料庫連線
        conn = mysql.connector.connect(
            host=DB_HOST,
            port=DB_PORT,
            user=DB_USER,
            password=DB_PASSWORD,
            database=DB_NAME
        )
        cursor = conn.cursor()

        # 插入資料到 pizza 資料表，ID 自動生成
        for index, row in data.iterrows():
            insert_query = (
                "INSERT INTO pizza (name, size, price) "
                "VALUES (%s, %s, %s)"
            )
            cursor.execute(insert_query, (row['name'], row['size'], row['price']))

        conn.commit()
        print("資料插入成功！")

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("使用者名稱或密碼錯誤")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("資料庫不存在")
        else:
            print(err)
    except Exception as e:
        print(f"處理資料時發生錯誤: {e}")
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()

if __name__ == "__main__":
    insert_data_to_db()