Python sqlite3.Connection.cursor 方法

上次修改时间：2025 年 4 月 15 日

本全面指南探讨了 Python 的 sqlite3.Connection.cursor 方法，这是在 SQLite 数据库中执行 SQL 语句的主要方式。我们将介绍基本用法、执行方法以及带有正确资源管理的实际示例。

基本定义

cursor 方法创建一个与数据库连接关联的游标对象。游标用于执行 SQL 命令并获取结果。

主要特点：每个游标都维护自己的状态，可以执行多个语句，并管理结果集。游标是轻量级的，但不使用时应正确关闭。

基本游标用法

这是使用 cursor 创建游标并执行基本 SQL 语句的最简单用法。

import sqlite3

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    
    # Create table
    cursor.execute('''CREATE TABLE IF NOT EXISTS products
                     (id INTEGER PRIMARY KEY, name TEXT, price REAL)''')
    
    # Insert data
    cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", 
                  ('Laptop', 999.99))
    
    # Query data
    cursor.execute("SELECT * FROM products")
    print(cursor.fetchall())

此示例显示了基本工作流程：创建游标、执行 SQL 语句并获取结果。with 语句确保正确的资源清理。

当连接上下文退出时，游标会自动关闭。参数化查询（使用 ? 占位符）用于安全的值插入。

执行多个语句

单个游标可以按顺序执行多个 SQL 语句。此示例演示了批量操作。

import sqlite3

with sqlite3.connect('inventory.db') as conn:
    cursor = conn.cursor()
    
    # Execute multiple statements
    cursor.executescript('''
        CREATE TABLE IF NOT EXISTS categories (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL
        );
        
        CREATE TABLE IF NOT EXISTS items (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            category_id INTEGER,
            FOREIGN KEY (category_id) REFERENCES categories(id)
        );
        
        INSERT INTO categories (name) VALUES ('Electronics');
        INSERT INTO categories (name) VALUES ('Clothing');
    ''')
    
    # Verify inserts
    cursor.execute("SELECT * FROM categories")
    print("Categories:", cursor.fetchall())

executescript 方法一次执行多个 SQL 语句。 这对于模式设置或批量操作非常有用。 每个语句必须用分号正确终止。

请注意，executescript 在执行之前会提交任何挂起的事务。在生产代码中谨慎使用。

带有游标的参数化查询

游标支持各种参数替换方法，用于安全地构建 SQL。 此示例显示了不同的参数样式。

import sqlite3

with sqlite3.connect('sales.db') as conn:
    cursor = conn.cursor()
    
    # qmark style (default)
    cursor.execute("INSERT INTO orders VALUES (?, ?, ?)", 
                  (1, '2023-01-15', 149.99))
    
    # named style
    cursor.execute("INSERT INTO orders VALUES (:id, :date, :amount)", 
                  {'id': 2, 'date': '2023-01-16', 'amount': 299.99})
    
    # format style (not recommended for security)
    cursor.execute("INSERT INTO orders VALUES (%s, %s, %s)" % 
                  (3, "'2023-01-17'", 199.99))
    
    # Verify inserts
    cursor.execute("SELECT * FROM orders")
    print("Orders:", cursor.fetchall())

此示例演示了三种参数样式：qmark (?)、命名 (:name) 和格式化 (%s)。 建议使用 qmark 和命名样式以确保安全性。

始终首选参数化查询而不是字符串格式化，以防止 SQL 注入攻击。 显示格式样式用于比较，但应避免使用。

带有自定义行工厂的游标

游标可以使用行工厂以不同的格式返回行。 此示例显示了如何自定义行输出。

import sqlite3
from collections import namedtuple

with sqlite3.connect('employees.db') as conn:
    # Set row factory for the connection
    conn.row_factory = sqlite3.Row
    
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS employees
                     (id INTEGER PRIMARY KEY, name TEXT, department TEXT)''')
    cursor.execute("INSERT INTO employees (name, department) VALUES (?, ?)",
                  ('Alice', 'Engineering'))
    
    # Fetch as dictionary-like Row object
    cursor.execute("SELECT * FROM employees")
    row = cursor.fetchone()
    print("Row as dict:", row['name'], row['department'])
    
    # Alternative: namedtuple factory
    def namedtuple_factory(cursor, row):
        fields = [col[0] for col in cursor.description]
        Employee = namedtuple('Employee', fields)
        return Employee(*row)
    
    cursor.row_factory = namedtuple_factory
    cursor.execute("SELECT * FROM employees")
    employee = cursor.fetchone()
    print("Row as namedtuple:", employee.name, employee.department)

此示例显示了两种行工厂方法：SQLite 的内置 Row 工厂和自定义 namedtuple 工厂。两者都允许对列进行命名访问。

可以在连接或游标级别设置行工厂。 它们可以大大提高处理结果集时的代码可读性。

带有事务的游标

游标在事务管理中起着关键作用。 此示例演示了手动事务控制。

import sqlite3

with sqlite3.connect('bank.db', isolation_level=None) as conn:
    cursor = conn.cursor()
    
    try:
        # Begin transaction explicitly
        cursor.execute("BEGIN")
        
        # Transfer funds
        cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
        
        # Simulate error
        # raise ValueError("Simulated error")
        
        # Commit if successful
        cursor.execute("COMMIT")
        print("Transaction completed successfully")
        
    except Exception as e:
        # Rollback on error
        cursor.execute("ROLLBACK")
        print("Transaction failed:", e)

此示例显示了使用 BEGIN、COMMIT 和 ROLLBACK 进行手动事务控制。 isolation_level=None 设置允许显式事务管理。

正确的事务处理对于数据完整性至关重要。 即使发生错误，with 语句也确保游标关闭。

带有上下文管理器的游标

游标可以用作上下文管理器以进行自动清理。 此示例显示了推荐的模式。

import sqlite3
from contextlib import closing

with sqlite3.connect('library.db') as conn:
    with closing(conn.cursor()) as cursor:
        cursor.execute('''CREATE TABLE IF NOT EXISTS books
                        (id INTEGER PRIMARY KEY, title TEXT, author TEXT)''')
        
        # Batch insert with executemany
        books = [
            (1, 'Python Basics', 'John Doe'),
            (2, 'Advanced SQL', 'Jane Smith'),
            (3, 'Web Development', 'Alice Johnson')
        ]
        cursor.executemany("INSERT INTO books VALUES (?, ?, ?)", books)
        
        # Iterate through results
        cursor.execute("SELECT * FROM books")
        for row in cursor:
            print(row)

此示例使用 contextlib.closing 确保游标正确关闭。 executemany 方法有效地插入多个行。

游标迭代 (for row in cursor) 对于大型结果集来说是内存高效的。 双 with 语句管理连接和游标资源。

带有自定义类型的游标

游标可以通过适配器和转换器处理自定义 Python 类型。 此示例演示了类型自定义。

import sqlite3
import json
from datetime import datetime

# Custom type adapter
def adapt_dict(d):
    return json.dumps(d)

# Custom type converter
def convert_dict(s):
    return json.loads(s)

# Register the adapter and converter
sqlite3.register_adapter(dict, adapt_dict)
sqlite3.register_converter("JSON", convert_dict)

with sqlite3.connect('data.db', detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    cursor = conn.cursor()
    
    # Create table with custom type
    cursor.execute('''CREATE TABLE IF NOT EXISTS events
                     (id INTEGER PRIMARY KEY, 
                     timestamp TIMESTAMP,
                     data JSON)''')
    
    # Insert custom types
    event = {
        'type': 'login',
        'user': 'alice',
        'ip': '192.168.1.1'
    }
    cursor.execute("INSERT INTO events (timestamp, data) VALUES (?, ?)",
                  (datetime.now(), event))
    
    # Retrieve custom types
    cursor.execute("SELECT * FROM events")
    row = cursor.fetchone()
    print("Timestamp type:", type(row[1]))  # datetime
    print("Data type:", type(row[2]))      # dict

此示例注册 Python 字典和 datetime 对象的自定义适配器和转换器。 PARSE_DECLTYPES 标志启用类型检测。

自定义类型处理允许无缝存储和检索复杂的 Python 对象，同时保持数据库兼容性。

最佳实践

• 始终关闭游标： 使用上下文管理器或显式 close()
• 首选参数化查询： 避免 SQL 注入漏洞
• 正确管理事务： 根据需要提交或回滚
• 使用适当的提取方法： fetchone()、fetchmany() 或 fetchall()
• 考虑游标 arraysize： 针对批量操作进行优化

资料来源

• Python sqlite3 文档
• SQLite 游标文档
• Python DB API 2.0 规范

作者

我叫 Jan Bodnar，是一位充满热情的程序员，拥有丰富的编程经验。 自 2007 年以来，我一直在撰写编程文章。 迄今为止，我已撰写了 1,400 多篇文章和 8 本电子书。 我拥有超过十年的编程教学经验。

列出所有 Python 教程。