Python MySQL 数据库访问：基于标准 DB-API 的完整指南

Python 数据库接口标准是 Python DB-API，大多数 Python 数据库接口遵循该标准。

可根据应用选择合适数据库。Python Database API 支持多种数据库服务器：

• GadFly
• mSQL
• MySQL
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• Interbase
• Oracle
• Sybase

可用 Python 数据库接口列表见：Python Database Interfaces and APIs。每种数据库需单独下载对应 DB-API 模块。例如同时访问 Oracle 和 MySQL，需分别下载两者模块。

DB-API 提供最小标准，尽可能使用 Python 结构和语法操作数据库，包含：

• 导入 API 模块。
• 获取数据库连接。
• 执行 SQL 语句和存储过程。
• 关闭连接。

本教程以 MySQL 为例，介绍 MySQLdb 模块。

什么是 MySQLdb？

MySQLdb 是从 Python 连接 MySQL 数据库服务器的接口，实现了 Python Database API v2.0，基于 MySQL C API 构建。

如何安装 MySQLdb？

运行前确保已安装。在 Python 脚本中执行：

#!/usr/bin/python
import MySQLdb

若报错 ImportError: No module named MySQLdb 则未安装。

安装命令：

# Ubuntu
$ sudo apt-get install python-pip python-dev libmysqlclient-dev
# Fedora
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
# Python 命令行
pip install MySQL-python

注意：需 root 权限安装。

数据库连接

连接 MySQL 前确保：

• 已创建数据库 TESTDB。
• 已在 TESTDB 中创建表 EMPLOYEE。
• 表包含字段 FIRST_NAME, LAST_NAME, AGE, SEX, INCOME。
• 用户 "testuser" 密码 "test123" 有权访问 TESTDB。
• Python 模块 MySQLdb 安装正常。
• 已了解 MySQL 基础。

示例：连接 TESTDB

#!/usr/bin/python
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")
# 获取游标对象
cursor = db.cursor()
# 执行 SQL 查询
cursor.execute("SELECT VERSION()")
# 获取单行数据
data = cursor.fetchone()
print("Database version : %s " % data)
# 断开连接
db.close()

运行结果（Linux 环境）：

Database version : 5.0.45

连接成功返回 Connection Object 赋值给 db，失败则 db 为 None。用 db 创建 cursor 执行 SQL，最后确保关闭连接释放资源。

创建数据库表

连接建立后，使用游标的 execute 方法创建表或插入记录。

示例：创建 EMPLOYEE 表

#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")
cursor = db.cursor()
# 表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 创建表
sql = """CREATE TABLE EMPLOYEE (
    FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME CHAR(20),
    AGE INT,
    SEX CHAR(1),
    INCOME FLOAT )"""
cursor.execute(sql)
db.close()

INSERT 操作

向表中插入记录。

示例 1：静态 SQL

#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")
cursor = db.cursor()
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

示例 2：动态拼接 SQL

#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")
cursor = db.cursor()
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d')" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

示例 3：直接传参

user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' % (user_id, password))

READ 操作

从数据库获取有用信息。连接建立后可查询。用 fetchone() 获取单条，fetchall() 获取多条。

• fetchone()：获取查询结果集的下一行。
• fetchall()：获取结果集所有行，若已提取部分则获取剩余行。
• rowcount：只读属性，返回 execute() 影响的行数。

示例：查询收入 > 1000 的记录

#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")
cursor = db.cursor()
sql = "SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (1000)
try:
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        fname, lname, age, sex, income = row[0], row[1], row[2], row[3], row[4]
        print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % (fname, lname, age, sex, income))
except:
    print("Error: unable to fetch data")
db.close()

输出：

fname=Mac, lname=Mohan, age=20, sex=M, income=2000

UPDATE 操作

更新数据库中已有记录。示例：将所有男性年龄加 1。

示例

#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")
cursor = db.cursor()
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

DELETE 操作

删除数据库记录。示例：删除年龄 > 20 的记录。

示例

#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect("localhost","testuser","test123","TESTDB")
cursor = db.cursor()
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

事务处理

事务保证数据一致性，具有四大特性（ACID）：

• 原子性：要么全做，要么全不做。
• 一致性：事务前后系统保持一致状态。
• 隔离性：事务中间结果对外不可见。
• 持久性：提交后效果永久保留，即使系统故障。

Python DB API 2.0 提供 commit() 和 rollback() 两个方法控制事务。

示例

sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

COMMIT 操作

提交更改，不可回滚。

db.commit()

ROLLBACK 操作

不满意更改时完全回滚。

db.rollback()

断开数据库连接

使用 close() 方法。

db.close()

用户调用 close() 关闭连接时，未完成事务会被数据库回滚。但建议应用显式调用 commit 或 rollback，不依赖底层实现细节。

错误处理

错误来源多样：SQL 语法错误、连接失败、对已取消语句调用 fetch 等。DB-API 定义每个数据库模块必须包含的异常：

脚本应处理这些错误，使用前确认 MySQLdb 支持该异常。详情参阅 DB-API 2.0 规范。