Python 操作 MySQL 数据库

一、安装依赖

推荐使用 pymysql 库，它是纯 Python 实现的 MySQL 客户端，安装方便。

pip install pymysql

二、连接数据库

import pymysql

# 建立连接
conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='your_password',
    database='test_db',
    charset='utf8mb4'
)

# 创建游标对象
cursor = conn.cursor()
print("连接成功！")

三、创建表

create_table_sql = '''
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    email VARCHAR(100)
)
'''

cursor.execute(create_table_sql)
print("创建表成功！")

四、插入数据

insert_sql = "INSERT INTO users (name, age, email) VALUES (%s, %s, %s)"
data = ("Alice", 25, "alice@example.com")

cursor.execute(insert_sql, data)
conn.commit()
print("插入数据成功！")

批量插入：

users = [
    ("Bob", 30, "bob@example.com"),
    ("Charlie", 22, "charlie@example.com")
]

cursor.executemany(insert_sql, users)
conn.commit()

五、查询数据

查询所有数据：

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

for row in rows:
    print(row)

条件查询：

cursor.execute("SELECT * FROM users WHERE age > %s", (23,))
results = cursor.fetchall()
for user in results:
    print(user)

六、更新数据

update_sql = "UPDATE users SET age = %s WHERE name = %s"
cursor.execute(update_sql, (28, "Alice"))
conn.commit()
print("更新成功！")

七、删除数据

delete_sql = "DELETE FROM users WHERE name = %s"
cursor.execute(delete_sql, ("Charlie",))
conn.commit()
print("删除成功！")

八、关闭连接

cursor.close()
conn.close()
print("连接已关闭")

九、完整示例代码

import pymysql

conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='your_password',
    database='test_db',
    charset='utf8mb4'
)

cursor = conn.cursor()

# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    email VARCHAR(100)
)
''')

# 插入数据
cursor.execute("INSERT INTO users (name, age, email) VALUES (%s, %s, %s)", ("Alice", 25, "alice@example.com"))
conn.commit()

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

# 更新数据
cursor.execute("UPDATE users SET age = %s WHERE name = %s", (26, "Alice"))
conn.commit()

# 删除数据
cursor.execute("DELETE FROM users WHERE name = %s", ("Alice",))
conn.commit()

cursor.close()
conn.close()

📌 常见问题

• 连接失败？

  • 检查数据库服务是否运行。
  • 检查用户名/密码/端口。

• 中文乱码？

  • 使用 charset='utf8mb4'。

• SQL 报错？

  • 打印 conn.errorhandler() 里的信息定位错误。