目录
MySQL运程控制
MySQL是最流行的关系型数据库之一，Python通过多种方式可以与MySQL进行交互。下面我将详细介绍Python操作MySQL的常用方法和最佳实践。
1. 准备工作
在开始之前，你需要：
- 安装MySQL服务器
- 安装Python的MySQL连接库
推荐使用mysql-connector-python或PyMySQL库：
pip install mysql-connector-python
# 或
pip install pymysql 2. 连接MySQL数据库
使用mysql-connector
import mysql.connector
# 创建连接
conn = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password",
database="your_database"
)
# 创建游标
cursor = conn.cursor()
# 执行SQL查询
cursor.execute("SELECT * FROM your_table")
# 获取结果
results = cursor.fetchall()
for row in results:
print(row)
# 关闭连接
cursor.close()
conn.close() 使用PyMySQL
import pymysql
# 创建连接
conn = pymysql.connect(
host='localhost',
user='your_username',
password='your_password',
db='your_database',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
# 使用上下文管理器自动管理连接
with conn:
with conn.cursor() as cursor:
# 执行SQL查询
sql = "SELECT * FROM your_table"
cursor.execute(sql)
# 获取结果
results = cursor.fetchall()
for row in results:
print(row) 3. 基本CRUD操作
创建表
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
""") 插入数据
# 单条插入
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
val = ("John Doe", "john@example.com")
cursor.execute(sql, val)
# 多条插入
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
val = [
("Jane Smith", "jane@example.com"),
("Bob Johnson", "bob@example.com")
]
cursor.executemany(sql, val)
# 提交事务
conn.commit() 查询数据
# 查询所有记录
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
# 查询单条记录
cursor.execute("SELECT * FROM users WHERE id = %s", (1,))
row = cursor.fetchone()
# 带条件的查询
cursor.execute("SELECT name, email FROM users WHERE name LIKE %s", ("%John%",))
rows = cursor.fetchall() 更新数据
sql = "UPDATE users SET name = %s WHERE id = %s"
val = ("John Smith", 1)
cursor.execute(sql, val)
conn.commit() 删除数据
sql = "DELETE FROM users WHERE id = %s"
val = (1,)
cursor.execute(sql, val)
conn.commit() 4. 高级操作
事务处理
try:
# 开始事务
conn.start_transaction()
# 执行多个SQL操作
cursor.execute(sql1, val1)
cursor.execute(sql2, val2)
# 提交事务 conn.commit()
except Exception as e:
# 发生错误时回滚
conn.rollback()
print(f"Transaction failed: {e}") 使用ORM框架 - SQLAlchemy
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# 创建引擎
engine = create_engine('mysql+pymysql://user:password@localhost/dbname')
# 声明基类
Base = declarative_base()
# 定义模型
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(255))
email = Column(String(255), unique=True)
# 创建表
Base.metadata.create_all(engine)
# 创建会话
Session = sessionmaker(bind=engine)
session = Session()
# 添加新用户
new_user = User(name='Alice', email='alice@example.com')
session.add(new_user)
session.commit()
# 查询用户
users = session.query(User).filter_by(name='Alice').all()
for user in users:
print(user.name, user.email) 5. 最佳实践
- 使用参数化查询：防止SQL注入攻击
- 使用上下文管理器：确保连接和游标正确关闭
- 处理异常：捕获并处理数据库操作中的异常
- 连接池：在高并发应用中使用连接池
- 索引优化：为常用查询字段添加索引
6. 常见错误处理
try:
conn = mysql.connector.connect(**config)
cursor = conn.cursor()
cursor.execute("SELECT * FROM non_existent_table")
except mysql.connector.Error as err:
print(f"Error: {err}")
finally: if 'conn' in locals() and conn.is_connected():
cursor.close()
conn.close()连接池
一、连接池的作用
数据库连接池是一种预先创建并管理数据库连接的技术，主要解决频繁创建/销毁连接的性能损耗问题。其核心思想是连接复用，应用程序从池中获取连接，使用后归还而非直接关闭。
二、优势与劣势
优势：
-
性能提升：减少连接创建/销毁的TCP三次握手和认证开销，降低延迟
-
资源控制：通过max_connections限制最大连接数，防止数据库过载
-
响应加速：初始化时预建连接，业务请求可直接使用
-
泄漏防护：超时回收机制避免连接长期占用
劣势：
-
需要合理配置参数（如最大/最小连接数）
-
连接状态维护增加复杂度
-
不适用于超短生命周期应用
三、部署与使用
1. 常用库及安装
# SQLAlchemy（支持多种数据库）
pip install sqlalchemy
# DBUtils（通用连接池）
pip install dbutils
# Psycopg2（PostgreSQL专用）
pip install psycopg2-binary2. 基础使用示例
SQLAlchemy连接池配置：
from sqlalchemy import create_engine
# 带连接池的配置（连接池大小5-10）
engine = create_engine(
"mysql+pymysql://user:pass@host/db",
pool_size=5,
max_overflow=5,
pool_recycle=3600
)DBUtils连接池示例：
from dbutils.pooled_db import PooledDB
import pymysql
pool = PooledDB(
creator=pymysql,
maxconnections=10,
host='localhost',
user='root',
database='test'
)
conn = pool.connection() # 获取连接3. 生产环境建议
-
根据QPS设置
pool_size（建议=平均并发量×1.2） -
启用
pool_pre_ping自动检测失效连接 -
使用
with语句确保连接归还 -
监控连接池使用率（如SQLAlchemy的
pool.status()）
四、性能优化技巧
-
不同业务使用独立连接池隔离资源
-
动态调整连接数（如SQLAlchemy的
pool_events） -
配合连接池使用ORM的Session缓存机制
事务管理
一、事务核心概念
-
ACID特性
- 原子性(Atomicity)：事务是不可分割的工作单元
- 一致性(Consistency)：事务前后数据库状态保持一致
- 隔离性(Isolation)：并发事务互不干扰
- 持久性(Durability)：事务提交后结果永久生效
-
隔离级别
- READ_UNCOMMITTED（可能读取未提交数据）
- READ_COMMITTED（避免脏读）
- REPEATABLE_READ（避免不可重复读）
- SERIALIZABLE（完全串行化）
四大隔离级别对比
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 锁机制特点 |
|---|---|---|---|---|
| READ UNCOMMITTED | ✓ | ✓ | ✓ | 无读锁，仅写锁冲突 |
| READ COMMITTED | ✗ | ✓ | ✓ | 读后立即释放共享锁 |
| REPEATABLE READ | ✗ | ✗ | ✓* | 持有读锁至事务结束 |
| SERIALIZABLE | ✗ | ✗ | ✗ | 范围锁防止幻读 |
*注：MySQL的InnoDB通过MVCC机制在REPEATABLE READ下可避免幻读
二、典型问题场景
- 脏读：事务A读取事务B未提交的修改，B回滚导致A获得无效数据
- 不可重复读：事务A两次读取同记录，因事务B提交修改导致结果不一致
- 幻读：事务A按条件查询，事务B新增符合条件记录导致A两次结果集不同
三、选型建议
- 实时分析系统：READ UNCOMMITTED（容忍脏读换取性能）
- 支付系统：REPEATABLE READ（保证金额一致性）
- 票务系统：SERIALIZABLE（杜绝超卖风险）
- 常规OLTP：READ COMMITTED（平衡性能与一致性）
四、Python配置示例
# PostgreSQL设置隔离级别
import psycopg2
conn = psycopg2.connect(dsn)
conn.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_REPEATABLE_READ ) 不同数据库对隔离级别的实现存在差异，如Oracle默认READ COMMITTED而MySQL默认REPEATABLE READ38，实际开发需结合具体数据库特性调整
扫一扫
3760
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
