[python]操作mysql(实战)

作为Python开发者，操作MySQL数据库主要使用PyMySQL或mysql-connector-python库.

任选其中一个即可.

以下是详细操作指南：

---

1. 安装驱动库

# 任选其一安装
pip install pymysql                 # 纯Python实现
pip install mysql-connector-python  # Oracle官方驱动

---

2. 连接数据库

import pymysql

# 建立连接
conn = pymysql.connect(
    host='localhost',      # 数据库IP
    user='your_username',  # 用户名
    password='your_pwd',   # 密码
    database='test_db',    # 数据库名（可选）
    port=3306,             # 端口（默认3306）
    charset='utf8mb4'      # 编码
)

# 创建游标对象（用于执行SQL）
cursor = conn.cursor()

---

3. 数据库操作

创建新数据库

cursor.execute("CREATE DATABASE IF NOT EXISTS mydb")

切换数据库

conn.select_db("mydb")  # 或连接时指定database参数

---

4. 数据表操作

创建表

create_table_sql = """
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
"""
cursor.execute(create_table_sql)

修改表结构

cursor.execute("ALTER TABLE users ADD COLUMN age INT AFTER name")

删除表

cursor.execute("DROP TABLE IF EXISTS old_users")

---

5. 数据增删改查 (CRUD)

插入数据

sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
val = ("张三", "zhangsan@example.com", 25)

# 单条插入
cursor.execute(sql, val)

# 批量插入
vals = [("李四", "lisi@example.com", 30), ("王五", "wangwu@example.com", 28)]
cursor.executemany(sql, vals)

conn.commit()  # 提交事务！

查询数据

cursor.execute("SELECT id, name, email FROM users WHERE age > %s", (26,))

# 获取单条结果
row = cursor.fetchone()
print(row)

# 获取所有结果
rows = cursor.fetchall()
for row in rows:
    print(row)

# 字典游标（返回字典形式）
dict_cursor = conn.cursor(pymysql.cursors.DictCursor)
dict_cursor.execute("SELECT * FROM users")
print(dict_cursor.fetchall())

更新数据

update_sql = "UPDATE users SET age = %s WHERE name = %s"
cursor.execute(update_sql, (27, "张三"))
conn.commit()  # 提交事务

删除数据

delete_sql = "DELETE FROM users WHERE email IS NULL"
cursor.execute(delete_sql)
conn.commit()

---

6. 事务管理

try:
    # 执行多个操作
    cursor.execute(sql1)
    cursor.execute(sql2)
    conn.commit()  # 提交事务
except Exception as e:
    conn.rollback()  # 回滚事务
    print("操作失败:", e)

---

7. 关闭连接

# 最后务必释放资源
cursor.close()
conn.close()

---

最佳实践建议

1. 使用上下文管理器（自动关闭连接）

   with pymysql.connect(...) as conn:
       with conn.cursor() as cursor:
           cursor.execute(...)
           conn.commit()
   

2. 防止SQL注入

   • 永远用参数化查询 (%s占位符)，避免直接拼接SQL字符串

3. 连接池优化

   • 高并发场景使用DBUtils或SQLAlchemy连接池

4. ORM框架推荐

   • 复杂项目建议使用ORM（如SQLAlchemy、Django ORM）

---

完整示例

import pymysql

try:
    # 连接数据库
    conn = pymysql.connect(
        host='localhost',
        user='root',
        password='secret',
        database='mydb'
    )
    
    # 执行查询
    with conn.cursor() as cursor:
        cursor.execute("SELECT version()")
        print("MySQL版本:", cursor.fetchone()[0])
        
finally:
    if conn:  # 确保连接关闭
        conn.close()