python Mysql增删改查

import mysql.connector
from mysql.connector import Error

def update_data(connection):
    try:
        cursor = connection.cursor()
       
        update_query = "UPDATE t_student SET name = %s WHERE id = %s"
        data = ("陈六", "1")
        cursor.execute(update_query, data)
        connection.commit()
        print("更新数据成功")
       
    except Error as e:
        print(f"更新数据失败: {e}")
        connection.rollback()
    finally:
        if cursor:
            cursor.close()

def insert_data(connection):
    try:
        cursor = connection.cursor()
       
        # 插入单条数据
        insert_query = "INSERT INTO t_student (name, no) VALUES (%s, %s)"
        data = ("张三", "222")
        cursor.execute(insert_query, data)
        connection.commit()
        print("插入数据成功")
       
        # 插入多条数据
        multiple_data = [
            ("李四", "333"),
            ("王五", "444")
        ]
        cursor.executemany(insert_query, multiple_data)
        connection.commit()
        print("批量插入数据成功")
       
    except Error as e:
        print(f"插入数据失败: {e}")
        connection.rollback()
    finally:
        if cursor:
            cursor.close()

def connect_to_mysql():
    """连接MySQL数据库"""
    try:
        # 创建数据库连接
        connection = mysql.connector.connect(
            host='xxx',      # 数据库服务器地址
            user='xxx',  # 数据库用户名
            password='xxx',  # 数据库密码
            database='xxx'   # 数据库名称
        )
       
        if connection.is_connected():
            db_info = connection.get_server_info()
            print(f"成功连接到MySQL服务器，版本: {db_info}")
           
            return connection
           
    except Error as e:
        print(f"连接MySQL数据库失败: {e}")
        return None

def query_data(connection):
    """查询数据并遍历结果"""
    try:
        cursor = connection.cursor()
       
        # 执行SQL查询
        cursor.execute("SELECT * FROM t_student")  # 替换为你的表名
       
        # 获取所有列名
        columns = [column[0] for column in cursor.description]
        print("列名:", columns)
       
        print("\n查询结果:")
        # 遍历结果集
        for row in cursor:
            print(row)
           
        # 或者获取所有结果
        # results = cursor.fetchall()
        # for row in results:
        #     print(row)
           
    except Error as e:
        print(f"查询数据失败: {e}")
    finally:
        if cursor:
            cursor.close()

def main():
    # 连接数据库
    connection = connect_to_mysql()
   
    if connection:
        try:
            # 插入数据
            # insert_data(connection)

            # 更新数据
            update_data(connection)

            # 查询数据
            query_data(connection)
           
        finally:
            # 关闭数据库连接
            connection.close()
            print("\nMySQL连接已关闭")

if __name__ == "__main__":
    main()