11_mysql在python的连接应用

1、python连接数据库

　　① 先在终端界面安装数据库连接组件 pip install mysql-connector-python

　　

　　② 连接数据库函数

　　def connect_to_mysql(host, user, password, database):

    """
    创建一个到MySQL数据库的连接
    """
    conn = None
    try:
        conn = mysql.connector.connect(
            host=host,
            port=3306,
            user=user,
            password=password,
            database=database
        )
        if conn.is_connected():
            # 使用 server_info 属性替代弃用的方法
            db_info = conn.server_info
            print(f"成功连接到MySQL服务器: {db_info}")
            cursor = conn.cursor()
            cursor.execute("SELECT DATABASE();")
            record = cursor.fetchone()
            print(f"当前连接数据库：{record[0] if record else 'None'}")
            return conn
    except Error as e:
        print(f"无法连接到MySQL数据库: {e}")
        return None
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()
            print("已关闭MySQL连接")

 　　③执行函数测试

　　connection = connect_to_mysql('localhost', 'root', 'zbl1118', 'zbl_database')

　　

2、查询数据

　　①  全表查询函数

　　def query_data_all():　　   

 　　"""

    查询数据库中的数据
    """
    try:
        conn = mysql.connector.connect(
            host = 'localhost',
            user = 'root',
            password = 'zbl1118',
            database = 'zbl_database')
        cursor = conn.cursor()
        query = "SELECT * FROM stu_tb"
        cursor.execute(query)
        records = cursor.fetchall()
        print(f"查询到{cursor.rowcount}行数据：")
        for row in records:
            print(row)
            print("-" * 50)
    except Error as e:
        print(f"查询数据时出错: {e}")
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()
　　② 执行输出
　　query_data_all()

　　

　　①条件查询数据函数

def query_data_by_condition(condition_field, condition_value):
    """
    根据指定条件查询数据库中的数据
    """
    try:
        conn = mysql.connector.connect(
            host='localhost',
            user='root',
            password='zbl1118',
            database='zbl_database')
        cursor = conn.cursor()

        # 使用参数化查询防止SQL注入
        query = f"SELECT * FROM stu_tb WHERE {condition_field} = %s"
        cursor.execute(query, (condition_value,))
        records = cursor.fetchall()

        print(f"根据 {condition_field} = {condition_value} 查询到 {len(records)} 行数据：")
        for row in records:
            print(row)
            print("-" * 50)

    except Error as e:
        print(f"条件查询数据时出错: {e}")
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()
　　② 执行输出
　　query_data_by_condition('class', 'mid')

　　

3、插入数据

　　①、插入数据函数

def insert_data(id, name, cla, age):
    """
    插入数据到数据库
    """
    try:
        conn = mysql.connector.connect(
            host = 'localhost',
            user = 'root',
            password = 'zbl1118',
            database = 'zbl_database')
        cursor = conn.cursor()
        insert_query = "INSERT INTO stu_tb (id, name, class, age) VALUES (%s, %s, %s, %s)"
        values = (id, name, cla, age)
        cursor.execute(insert_query, values)
        conn.commit()
        print(f"成功插入 {cursor.rowcount} 行数据 {values}")

    except Error as e:
        print(f"插入数据时出错: {e}")
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()

　　②、执行输出

　　insert_data(7, 'chenYi', 'mid', 6)

　　

 4、删除数据

　　①、删除数据函数

def delete_data(id):
    """
    根据ID删除数据库中的数据
    """
    try:
        conn = mysql.connector.connect(
            host = 'localhost',
            user = 'root',
            password = 'zbl1118',
            database = 'zbl_database')
        cursor = conn.cursor()
        # 使用参数化查询防止SQL注入
        delete_query = "DELETE FROM stu_tb WHERE id = %s"
        values = (id,)
        cursor.execute(delete_query, values)
        conn.commit()
        print(f"成功删除 {cursor.rowcount} 行数据，ID: {id}")
    except Error as e:
        print(f"删除数据时出错: {e}")
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()

　　②、执行输出

　　delete_data(7)

　　

5、更新数据

　　①、更新数据函数

def update_data(id, name=None, cla=None, age=None):
    """
    根据ID更新数据库中的数据
    可以选择性地更新某些字段
    """
    try:
        conn = mysql.connector.connect(
            host='localhost',
            user='root',
            password='zbl1118',
            database='zbl_database')
        cursor = conn.cursor()

        # 构建动态更新语句
        updates = []
        values = []

        if name is not None:
            updates.append("name = %s")
            values.append(name)
        if cla is not None:
            updates.append("class = %s")
            values.append(cla)
        if age is not None:
            updates.append("age = %s")
            values.append(age)

        if not updates:
            print("没有提供需要更新的字段")
            return

        # 组合更新语句
        update_query = f"UPDATE stu_tb SET {', '.join(updates)} WHERE id = %s"
        values.append(id)  # ID作为WHERE条件

        cursor.execute(update_query, tuple(values))
        conn.commit()

        print(f"成功更新 {cursor.rowcount} 行数据，ID: {id}")

    except Error as e:
        print(f"更新数据时出错: {e}")
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()

　　②、执行输出　

　　update_data(6,age=6)