import mysql.connector
from mysql.connector import errorcode

# 你的数据库连接配置
config = {
    'user': 'root',
    'password': '123456',
    'host': '127.0.0.1',
    'database': 'card_score_database'
}
TABLES = {}
TABLES['employees'] = (
    "CREATE TABLE `employees` ("
    "  `id` int(11) NOT NULL AUTO_INCREMENT,"
    "  `name` varchar(50) NOT NULL,"
    "  `position` varchar(50) NOT NULL,"
    "  `hire_date` date NOT NULL,"
    "  PRIMARY KEY (`id`)"
    ") ENGINE=InnoDB")

connection = None
cursor = None
database_name = "card_score_database"


def connect_mysql():
    # 尝试连接
    connection = mysql.connector.connect(**config)
    print("成功连接到 MySQL 服务器！")

    # 获取一个游标对象
    cursor = connection.cursor()

    # 打印服务器版本信息
    cursor.execute("SELECT VERSION()")
    version = cursor.fetchone()
    print(f"数据库版本: {version[0]}")
    return connection, cursor


def creat_database(cursor, database_name):
    cursor.execute(f"CREATE DATABASE IF NOT EXISTS {database_name} DEFAULT CHARACTER SET 'utf8mb4'")


if __name__ == '__main__':
    connection, cursor = connect_mysql()

    # creat_database(cursor, database_name)
    # 创建 employees 表
    # cursor.execute(TABLES['employees'])

    # add_employee_sql = ("INSERT INTO employees "
    #                     "(name, position, hire_date) "
    #                     "VALUES (%s, %s, %s)")
    # employee_data = ('张三', '软件工程师', '2023-01-15')
    # cursor.execute(add_employee_sql, employee_data)
    # employee_id = cursor.lastrowid  # 获取刚插入行的ID
    # print(f"成功插入一条记录, ID: {employee_id}")
    #
    # connection.commit()

    # 1. 查询所有员工
    query_all = "SELECT id, name, position, hire_date FROM employees"
    cursor.execute(query_all)

    print("\n--- 所有员工信息 ---")
    for row in cursor.fetchall():
        print(f"ID: {row['id']}, 姓名: {row['name']}, 职位: {row['position']}, 入职日期: {row['hire_date']}")