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

# 数据库连接配置 - 请根据实际情况修改
db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',  # 修改为你的数据库密码
    'database': 'book',
    'port': 3306,
    'charset': 'utf8mb4'
}

# CSV文件路径 - 请修改为实际路径
csv_file = 'book_info.csv'

def import_csv_to_mysql():
    try:
        # 读取CSV文件
        df = pd.read_csv(csv_file)
        
        # 建立数据库连接
        connection = mysql.connector.connect(**db_config)
        if connection.is_connected():
            cursor = connection.cursor()
            
            # 获取CSV列名作为表字段
            columns = ', '.join(df.columns)
            placeholders = ', '.join(['%s'] * len(df.columns))
            
            # 检查表是否存在，不存在则创建
            table_name = 'book_info'  # 修改为你的表名
            create_table_query = f"""
            CREATE TABLE IF NOT EXISTS {table_name} (
                id INT AUTO_INCREMENT PRIMARY KEY,
                {', '.join([f'`{col}` TEXT' for col in df.columns])}
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            """
            cursor.execute(create_table_query)
            
            # 构建插入语句
            insert_query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
            
            # 批量插入数据
            for _, row in df.iterrows():
                cursor.execute(insert_query, tuple(row))
            
            connection.commit()
            print(f"成功导入 {cursor.rowcount} 条记录到 {table_name} 表")
            
    except Error as e:
        print(f"数据库错误: {e}")
    except Exception as e:
        print(f"发生异常: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("数据库连接已关闭")

if __name__ == "__main__":
    import_csv_to_mysql()