3、Python数据库编程基础：DB-API 2.0规范、Connection对象、Cursor对象、事务管理、异常处理

好，咱们进入正题。Python 操作数据库，说白了就是靠一套统一的接口规范——DB-API 2.0。你可能会问，为什么要有这个规范？

我刚开始写 Python 连数据库的时候，用的是 MySQLdb，后来换成 psycopg2 连 PostgreSQL，再后来用 sqlite3。每个库的用法都不一样，换个数据库就得重学一遍 API，那叫一个痛苦。DB-API 2.0 就是来解决这个问题的。

3.1 DB-API 2.0 规范是什么？

DB-API 2.0 是 Python 社区制定的一套标准接口。所有主流的数据库驱动，比如 sqlite3、pymysql、psycopg2、cx_Oracle，都遵循这个规范。你只要学会一套写法，换数据库就是改个连接字符串的事。

核心就两个对象：Connection 和 Cursor。再加上几个关键方法：execute()、fetchone()、fetchall()、commit()、rollback()。

记住这个口诀：连接用 Connection，操作用 Cursor，改完记得 commit，出错就 rollback。

3.2 Connection 对象——你的数据库通道

Connection 对象代表与数据库的一次连接。说白了，就是你和数据库之间的一条专线。

创建连接时，你需要提供数据库的地址、端口、用户名、密码、数据库名这些信息。以 sqlite3 为例，它最简单，连用户名密码都省了：

import sqlite3

# 创建连接
conn = sqlite3.connect('mydb.db')
# 或者用内存数据库
# conn = sqlite3.connect(':memory:')

Connection 对象有几个重要方法：

  • close()：关闭连接。用完一定要关，不然会泄漏连接。
  • commit()：提交事务。你做的修改，只有 commit 了才会真正写入数据库。
  • rollback()：回滚事务。出错了就撤销所有未提交的修改。
  • cursor()：创建游标对象。这是你执行 SQL 的“手”。

我的习惯：用 with 语句管理连接，这样能自动关闭，省心。

with sqlite3.connect('mydb.db') as conn:
    # 在这里操作数据库
    pass  # 连接会自动关闭

3.3 Cursor 对象——你的执行之手

Cursor 对象才是真正干活的。你通过它执行 SQL 语句，获取查询结果。

创建游标很简单：

cursor = conn.cursor()

Cursor 的核心方法：

  • execute(sql, parameters)：执行单条 SQL 语句。参数用占位符 ? 或 %s，避免 SQL 注入。
  • executemany(sql, seq_of_parameters)：批量执行，比如一次插入多条数据。
  • fetchone()：取一条结果。
  • fetchall()：取所有结果。
  • fetchmany(size)：取指定数量的结果。

来看个完整的例子：

import sqlite3

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

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

# 插入数据
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 25))
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Bob', 30))

# 提交事务
conn.commit()

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

# 关闭
cursor.close()
conn.close()

这里要注意，execute() 的参数用 ? 占位，而不是用 f-string 拼接字符串。为什么？因为拼接字符串容易引发 SQL 注入攻击。我在项目中见过有人用 f-string 拼接用户输入，结果被注入了删表语句，数据全没了。嗯，那场面...你懂的。

3.4 事务管理——要么全做，要么全不做

事务是数据库的基石。一个事务里的操作，要么全部成功，要么全部失败回滚。

在 DB-API 2.0 中，事务是自动开启的。你执行 execute() 后，修改不会立即生效，需要调用 conn.commit() 才会真正写入。如果出错了，调用 conn.rollback() 撤销所有修改。

典型的转账场景：

def transfer(from_id, to_id, amount):
    conn = sqlite3.connect('bank.db')
    cursor = conn.cursor()
    try:
        # 扣钱
        cursor.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', (amount, from_id))
        # 加钱
        cursor.execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', (amount, to_id))
        # 提交
        conn.commit()
        print('转账成功')
    except Exception as e:
        # 出错就回滚
        conn.rollback()
        print(f'转账失败，已回滚：{e}')
    finally:
        cursor.close()
        conn.close()

我曾经踩过的坑：忘记调用 commit()，数据没写进去，查了半天。还有一次是异常处理里忘了 rollback()，导致后续操作都基于一个脏连接。记住：commit 和 rollback 要成对出现，就像打开和关闭文件一样。

3.5 异常处理——别让程序崩了

数据库操作最容易出异常。网络断了、权限不够、SQL 语法错误、唯一约束冲突...各种情况。

DB-API 2.0 定义了一套异常层次结构：

异常类 说明
Warning 警告，比如数据截断
Error 所有错误的基类
InterfaceError 数据库接口错误，比如连接断开
DatabaseError 数据库层面的错误
DataError 数据问题，比如除零、数值越界
OperationalError 操作错误，比如表不存在、连接超时
IntegrityError 完整性约束违反，比如主键重复
InternalError 数据库内部错误
ProgrammingError 编程错误，比如 SQL 语法错误
NotSupportedError 调用了不支持的 API

实际开发中，我一般这样处理：

import sqlite3
from sqlite3 import Error

try:
    conn = sqlite3.connect('mydb.db')
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM non_existent_table')
except sqlite3.OperationalError as e:
    print(f'表不存在？检查一下：{e}')
except sqlite3.IntegrityError as e:
    print(f'数据冲突了：{e}')
except Error as e:
    print(f'其他数据库错误：{e}')
finally:
    if conn:
        conn.close()

我的建议：不要只捕获通用的 Exception，尽量捕获具体的异常类。这样你能知道到底出了什么问题，调试起来快得多。

3.6 完整的实践模板

最后，给你一个我常用的模板。每次写数据库操作，我都按这个结构来：

import sqlite3
from contextlib import closing

def safe_db_operation():
    conn = None
    try:
        conn = sqlite3.connect('mydb.db')
        with closing(conn.cursor()) as cursor:
            # 执行操作
            cursor.execute('...')
            conn.commit()
    except sqlite3.Error as e:
        if conn:
            conn.rollback()
        print(f'数据库操作失败：{e}')
        raise  # 或者返回错误信息
    finally:
        if conn:
            conn.close()

这个模板的好处是：连接和游标都能正确关闭，异常能回滚，错误能记录。你直接套用就行。

好了，DB-API 2.0 的核心就这些。说白了就是：连接、游标、执行、提交、回滚、异常处理。把这六个点吃透，Python 连任何数据库都不在话下。

← 上一章 📖 返回目录 下一章 →