Python高级技术之：`Python`的`DB-API 2.0`：统一数据库访问接口的设计。

观众朋友们，晚上好！欢迎来到“Python高级技术之夜”。今天，咱们要聊的是Python中一个隐藏的英雄——DB-API 2.0，一个让你用一套代码就能耍遍天下数据库的“统一战线”。

开场白：数据库的“七国集团”与统一接口的必要性

想象一下，你要开发一个网站，需要存储用户数据、商品信息、订单详情。数据库的选择就像选妃，MySQL、PostgreSQL、SQLite、Oracle、SQL Server…各有千秋。问题来了，如果你直接针对每种数据库写一套代码，那你的项目瞬间变成了一场噩梦，到处充斥着“如果…否则…”的判断，维护起来比养娃还累。

这就是DB-API 2.0诞生的意义。它就像一个“联合国”，为各种数据库定义了一套统一的接口，让你的代码只需关注业务逻辑，而不用操心底层数据库的具体实现。就像你只需要告诉服务员“来份宫保鸡丁”，而不用管后厨用的是哪个牌子的酱油。

DB-API 2.0的核心概念：连接、游标与SQL

DB-API 2.0的核心围绕三个概念展开：

• Connection (连接): 就像你打开一扇通往数据库的大门，是所有操作的起点。
• Cursor (游标): 想象一下你拿着一根指挥棒，在数据库的表格中穿梭，执行SQL语句，获取结果。
• SQL (结构化查询语言): 这是你和数据库交流的语言，告诉它你要做什么。

1. 连接 (Connection)

要使用DB-API，首先你需要建立一个连接。不同的数据库连接方式略有不同，但基本流程是：

1. 导入数据库驱动模块。
2. 调用connect()函数，传入连接参数。

# 导入SQLite驱动
import sqlite3

# 建立连接 (如果文件不存在，会自动创建)
conn = sqlite3.connect('mydatabase.db')

# 对于MySQL，可能需要这样：
# import mysql.connector
# conn = mysql.connector.connect(user='your_user', password='your_password', host='your_host', database='your_database')

# 对于PostgreSQL：
# import psycopg2
# conn = psycopg2.connect(user='your_user', password='your_password', host='your_host', database='your_database')

注意，连接参数因数据库而异，务必查阅相应数据库驱动的文档。

2. 游标 (Cursor)

有了连接，接下来你需要一个游标来执行SQL语句。

# 创建游标
cursor = conn.cursor()

游标就像一个执行SQL语句的工具，你可以用它来创建表格、插入数据、查询数据等等。

3. 执行SQL (Executing SQL)

接下来，你可以用游标来执行SQL语句了。

# 创建表格
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 ('Alice', 30)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 25)")

# 查询数据
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()  # 获取所有结果

for row in rows:
    print(f"ID: {row[0]}, Name: {row[1]}, Age: {row[2]}")

4. 提交与关闭 (Commit and Close)

记住，在执行完修改操作后，一定要提交事务，才能将更改保存到数据库。

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

DB-API 2.0的精髓：参数化查询 (Parameterized Queries)

直接拼接SQL字符串很容易受到SQL注入攻击，就像给黑客留了一扇敞开的大门。DB-API 2.0提倡使用参数化查询，将SQL语句和参数分开处理，可以有效防止SQL注入。

# 不要这样做 (容易受到SQL注入攻击！)
# name = "Robert'); DROP TABLE users; --"
# cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")

# 应该这样做 (参数化查询)
name = "Robert'); DROP TABLE users; --"  # 即使是恶意字符串也没关系
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))  # 注意这里用的是元组
rows = cursor.fetchall()

for row in rows:
    print(f"ID: {row[0]}, Name: {row[1]}, Age: {row[2]}")

不同的数据库驱动可能使用不同的占位符，常见的有：

• ?: SQLite, MySQL (mysql.connector)
• %s: MySQL (旧版本驱动), PostgreSQL
• :name: Oracle

异常处理 (Exception Handling)

数据库操作难免会遇到各种异常，比如连接失败、SQL语法错误等等。DB-API 2.0定义了一系列异常类，你可以根据需要捕获并处理它们。

import sqlite3

try:
    conn = sqlite3.connect('mydatabase.db')
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM non_existent_table") # 故意制造一个错误

    conn.commit()
    cursor.close()
    conn.close()

except sqlite3.Error as e:  # 捕获所有sqlite3相关的异常
    print(f"数据库操作出错啦: {e}")
    if conn: # 确保连接存在
        conn.rollback()  # 回滚事务，撤销之前的操作
        cursor.close()
        conn.close()

上下文管理器 (Context Managers)

为了更优雅地管理连接和游标，可以使用with语句，也就是上下文管理器。它可以自动关闭连接和游标，即使发生异常也能保证资源被释放。

import sqlite3

try:
    with sqlite3.connect('mydatabase.db') as conn:  # 自动关闭连接
        cursor = conn.cursor()

        cursor.execute("CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, name TEXT, price REAL)")
        cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Laptop", 1200.00))
        cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", ("Mouse", 25.00))

        conn.commit() # 提交事务

        cursor.execute("SELECT * FROM products")
        for row in cursor.fetchall():
            print(row)  # 打印结果
except sqlite3.Error as e:
    print(f"数据库操作出错: {e}")
    if conn: # 确保连接存在
        conn.rollback() # 回滚事务，撤销之前的操作

高级用法：使用ORM (Object-Relational Mapping)

DB-API 2.0是底层接口，直接使用它需要编写大量的SQL语句。为了提高开发效率，可以使用ORM框架，比如SQLAlchemy。ORM可以将数据库表映射为Python对象，让你像操作对象一样操作数据库，而不用写SQL。

# 这只是一个简单的示例，SQLAlchemy的功能远不止这些
# 需要安装：pip install sqlalchemy

from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 定义数据库连接
engine = create_engine('sqlite:///mydatabase.db', echo=True) # echo=True 可以看到SQL语句

# 定义基类
Base = declarative_base()

# 定义模型
class Product(Base):
    __tablename__ = 'products'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    price = Column(Float)

    def __repr__(self):
        return "<Product(name='%s', price='%s')>" % (self.name, self.price)

# 创建表格 (如果不存在)
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 插入数据
new_product1 = Product(name='Keyboard', price=75.00)
new_product2 = Product(name='Monitor', price=300.00)

session.add_all([new_product1, new_product2])
session.commit()

# 查询数据
products = session.query(Product).all()

for product in products:
    print(product)

# 关闭会话
session.close()

ORM将数据库操作提升到了更高的抽象层次，减少了重复代码，提高了可维护性。

DB-API 2.0的优势与局限

优势：

• 统一接口: 一套代码，多种数据库。
• 防止SQL注入: 参数化查询提高安全性。
• 标准化: DB-API 2.0是Python官方标准，广泛支持。

局限：

• 底层接口: 需要编写SQL语句，较为繁琐。
• 功能有限: 只提供最基本的操作，高级功能需要自行实现或使用ORM。

总结：DB-API 2.0：数据库访问的基石

DB-API 2.0是Python数据库编程的基石，它提供了一套统一的接口，让你的代码可以轻松地切换数据库。虽然直接使用DB-API 2.0可能比较繁琐，但理解它的原理对于深入学习数据库编程至关重要。

无论你是直接使用DB-API 2.0，还是使用ORM框架，掌握DB-API 2.0的概念和用法都是必不可少的。 它可以让你更好地理解ORM的工作原理，并在遇到问题时能够快速定位和解决。

DB-API 2.0 核心属性和方法概览

为了更清晰地了解DB-API 2.0的细节，下面提供一个表格，总结了其核心属性和方法：

对象	属性/方法	描述
Connection	cursor()	创建一个游标对象。
	commit()	提交当前事务。
	rollback()	回滚当前事务。
	close()	关闭数据库连接。
Cursor	execute(sql, [params])	执行SQL语句。可选的params参数用于参数化查询。
	executemany(sql, seq_of_params)	执行SQL语句多次，每次使用不同的参数序列。
	fetchone()	获取查询结果的下一行，返回一个元组。如果已经没有更多行，则返回None。
	fetchmany(size)	获取查询结果的下size行，返回一个列表，其中每个元素都是一个元组。如果剩余的行数少于size，则返回所有剩余的行。如果没有更多行，则返回一个空列表。
	fetchall()	获取查询结果的所有行，返回一个列表，其中每个元素都是一个元组。
	close()	关闭游标。
	rowcount	返回受上一个execute()影响的行数。请注意，这个属性的含义可能因数据库而异。
	description	返回查询结果的列信息的元组列表。每个元组包含列名、类型代码、显示大小、内部大小、精度、比例和是否允许空值。
模块级别	paramstyle	一个字符串，指示模块使用的参数化查询的样式。常见的值有qmark（?）、numeric（:1）、named（:name）、format（%s）和pyformat（%(name)s）。
异常类	Error	所有DB-API异常的基类。
	DatabaseError	与数据库相关的错误的基类。
	IntegrityError	违反数据库完整性约束（例如，唯一性约束）的错误。
	ProgrammingError	由于编程错误（例如，SQL语法错误或使用了不支持的操作）导致的错误。
	NotSupportedError	数据库不支持请求的操作的错误。

结束语：开启你的数据库之旅

希望今天的讲座能让你对Python的DB-API 2.0有一个更清晰的认识。 记住，实践是检验真理的唯一标准，赶快动手写一些代码，连接你的数据库，开始你的数据库之旅吧！

感谢大家的收听，我们下期再见！