Python 连接 MySQL 数据库的增删改查操作-优快云博客

环境准备确保已安装 `mysql-connector-python` 或 `pymysql` 库，可通过以下命令安装：

pip install mysql-connector-python
# 或
pip install pymysql

连接 MySQL 数据库

使用 pymysql 建立连接：

import pymysql

conn = pymysql.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)
cursor = conn.cursor()

创建数据表（示例）

create_table_sql = """
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    grade FLOAT
)
"""
cursor.execute(create_table_sql)
conn.commit()

插入数据（增）

单条插入：

insert_sql = "INSERT INTO students (name, age, grade) VALUES (%s, %s, %s)"
values = ("Alice", 20, 85.5)
cursor.execute(insert_sql, values)
conn.commit()

批量插入：

values = [("Bob", 21, 78.0), ("Charlie", 19, 92.5)]
cursor.executemany(insert_sql, values)
conn.commit()

查询数据（查）

查询所有记录：

cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
for row in rows:
    print(row)

条件查询：

cursor.execute("SELECT name, grade FROM students WHERE age > %s", (20,))
results = cursor.fetchall()
for name, grade in results:
    print(f"{name}: {grade}")

更新数据（改）

update_sql = "UPDATE students SET grade = %s WHERE name = %s"
cursor.execute(update_sql, (90.0, "Alice"))
conn.commit()

删除数据（删）

delete_sql = "DELETE FROM students WHERE id = %s"
cursor.execute(delete_sql, (1,))
conn.commit()

关闭连接

操作完成后需关闭连接：

cursor.close()
conn.close()

完整示例代码

import mysql.connector

def main():
    # 连接数据库
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="password",
        database="test_db"
    )
    cursor = conn.cursor()

    # 创建表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS products (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(50),
            price DECIMAL(10,2)
        )
    """)

    # 插入数据
    cursor.execute("INSERT INTO products (name, price) VALUES (%s, %s)", ("Laptop", 999.99))
    cursor.executemany(
        "INSERT INTO products (name, price) VALUES (%s, %s)",
        [("Phone", 599.99), ("Tablet", 399.99)]
    )
    conn.commit()

    # 查询数据
    cursor.execute("SELECT * FROM products")
    print("All products:")
    for row in cursor:
        print(row)

    # 更新数据
    cursor.execute("UPDATE products SET price = %s WHERE name = %s", (1099.99, "Laptop"))
    conn.commit()

    # 删除数据
    cursor.execute("DELETE FROM products WHERE price < %s", (500,))
    conn.commit()

    # 关闭连接
    cursor.close()
    conn.close()

if __name__ == "__main__":
    main()