Python数据库操作实战：从入门到性能调优的完整指南

作者： /

刚开始写Python操作数据库时，我以为就一句cursor.execute()的事儿，结果第一个项目就被生产环境教做人了：连接超时、SQL注入、性能慢得像乌龟爬。今天把这些坑全扒开，从基础到实战，再给几个能直接用的性能优化技巧。

（开篇：一张Python操作数据库的流程图，显示连接、执行、关闭的循环，以及连接池的交互）

先搞定基础连接（别踩我的坑）

1. SQLite：本地测试最省心

“
import sqlite3

连接数据库（不存在会自动创建）

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

创建表

cursor.execute('''CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER
)''')

插入数据（别用字符串拼接，这是安全红线）

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("张三", 25))
conn.commit() # 忘了这步数据不会存盘
`
一开始我老忘记commit()，查了半天数据哪里去了。还有，参数化查询是必须的：别写f”INSERT INTO users VALUES (‘{name}’)”，会被人用‘ OR 1=1–轻松拖库。

2. MySQL：生产环境的标配

`
import pymysql

config = {
'host': 'localhost',
'user': 'root',
'password': 'your_password',
'database': 'test_db',
'charset': 'utf8mb4',
'cursorclass': pymysql.cursors.DictCursor # 返回字典格式，方便取字段
}

try:
conn = pymysql.connect(**config)
with conn.cursor()

<

p>as cursor:
cursor.execute("SELECT * FROM users WHERE age > %s", (20,))
result = cursor.fetchall()
print(f"查询到{len(result)}条记录")
except pymysql.Error as e:
print(f"数据库错误: {e}")
finally:
conn.close()
`
这个设计真的反人类：每次都要手动close()，忘了就连接泄漏。直到我用了with语句和连接池才解脱。

连接池：自动管理连接的救星

（核心：连接池架构图，展示多个连接从池中获取和归还，加上超时重试机制）

单连接在并发场景下必死。比如一个Web接口同时处理100个请求，每个请求都connect()->close()，数据库服务器CPU直接满载。用连接池，固定10个连接来回用，性能从3.2秒降到0.8秒。

`
from dbutils.pooled_db import PooledDB
import pymysql

创建连接池

pool = PooledDB(
creator=pymysql,
maxconnect

ions=10, # 最大连接数
mincached=2, # 初始化时创建的空闲连接
maxcached=5, # 最大空闲连接
blocking=True, # 连接用完时等待
host='localhost',
user='root',
password='your_password',
database='test_db',
charset='utf8mb4'
)

def query_users_by_age(min_age):
"""从连接池获取连接查询"""
conn = pool.connection()
try:
with conn.cursor() as cursor:
cursor.execute("SELECT * FROM users WHERE age > %s", (min_age,))
return cursor.fetchall()
finally:
conn.close() # 这里不是真关闭，是归还到连接池
`
官方文档这段文档不够清晰：conn.close()在连接池模式里不是销毁，而是还回池子。刚开始我以为是bug，调试半天才发现。

批量操作：从10秒到0.5秒的技巧

另一个坑：别逐条插入数据。我见过有人用for循环一条条insert，100万条数据跑了3小时。用executemany()批量提交，10万条只需要1.2秒。

`
def batch_insert_users(user_list):
"""
批量插入用户数据
user_list: [(name, age), ...]
"""
conn = pool.connection()
try:
with conn.cursor() as cursor:
sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
cursor.executemany(sql, user_list)
conn.commit()
print(f"成功插入{len(user_list)}条数据")
except Exception as e:
conn.rollback()
print(f"插入失败，已回滚: {e}")
finally:
conn.close()

测试：生成10万条数据

test_data = [(f"用户{i}", 20 + i % 50) for i in range(100000)]
batch_insert_users(test_data)
`
还有个技巧：如果数据量更大（百万级以上），用LOAD DATA LOCAL INFILE，直接从文件导入，速度能再快10倍。但要注意MySQL配置需要开启local-infile=1。

事务管理：别让数据半死不活

有次做订单系统，更新库存成功了，但插入订单记录时挂了，结果库存少了、订单没生成——这TM叫数据不一致。事务就是干这个的：要么全成功，要么全回滚。

`
def transfer_money(from_user, to_user, amount):
"""转账操作：扣钱+加钱，必须原子执行"""
conn = pool.connection()
try:
conn.begin() # 开启事务

with conn.cursor() as cursor:
# 扣钱
cursor.execute("UPDATE account SET balance = balance - %s WHERE id = %s", (amount, from_user))
if cursor.rowcount == 0:
raise Exception("转出用户不存在")

# 模拟网络波动（测试用）
# if True: raise Exception("模拟失败")

# 加钱
cursor.execute("UPDATE account SET balance = balance + %s WHERE id = %s", (amount, to_user))
if cursor.rowcount == 0:
raise Exception("转入用户不存在")

conn.commit()
print("转账成功")
except Exception as e:
conn.rollback()
print(f"转账失败，已回滚: {e}")
finally:
conn.close()
`
事务的关键：begin()后所有操作在内存中，只有commit()才写入。万一中间出错，rollback()能让数据完全复原。别省略这一步，我亲眼见过线上数据库被半事务搞崩。

性能优化的三个绝招

1. 索引不是越多越好

`
-- 给常用查询加索引
CREATE INDEX idx_age ON users(age);
-- 但别给每个字段都加，写入时会变慢
`
官方文档说索引能加速查询，但没说索引也损耗写入性能。我试过给10个字段加索引，插入速度从0.2秒降到1.5秒。只给WHERE、JOIN、ORDER BY用的字段加。

2. 用explain分析慢查询

`
cursor.execute("EXPLAIN SELECT * FROM users WHERE age > 20")
explain_result = cursor.fetchall()
print(explain_result) # 看type字段：ALL是全表扫描，ref是索引查询
`
发现慢查询别瞎猜，EXPLAIN告诉你用了哪个索引、扫描了多少行。ALL的查询一定要优化。

3. 连接复用代替短连接

`

坏习惯：每个函数都声明连接

def query_user(id):
conn = pymysql.connect(...) # 每次都新建
# ...
conn.close()

好习惯：全局连接池

pool = PooledDB(...)
def query_user(id):
conn = pool.connection() # 复用连接
# ...
conn.close() # 归还
`
我监控过，短连接模式每秒最多处理50个请求，连接池模式能到800个。差距就在这里。

总结一下，你可以立刻用的三个点

  • 连接池必须上：单连接是玩具，连接池是生产。用PooledDB，设置maxconnections=10，性能翻5倍以上。
  • 参数化查询+事务：永远别拼接SQL，用%s占位符。所有写操作放事务里，commit()和rollback()配套使用。
  • 批量操作+索引：插入用executemany，查询加必要索引，配合EXPLAIN`分析。10万条数据插入从分钟级降到秒级。

    • 数据库操作说难不难，但这些细节不踩一遍坑真不知道。现在写代码前先想清楚连接管理和事务边界，少加多少班。

    滚动至顶部