Python sqlite3.PARSE_COLNAMES 常量

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

本综合指南探讨 Python 的 sqlite3.PARSE_COLNAMES 常量，该常量允许在 SQLite 查询中进行高级列名解析。我们将介绍其目的、使用模式和实际示例。

基本定义

sqlite3.PARSE_COLNAMES 是一个标志，与 SQLite 连接一起使用，以启用 SQL 查询中列名的解析。 设置后，它允许在列名中使用特殊语法进行类型转换。

主要特征：它与 sqlite3.connect 一起使用，通过列名注释启用类型转换，并提供对查询结果的更多控制。 它与 detect_types 参数一起使用。

基本的 PARSE_COLNAMES 用法

这是一个简单的例子，展示了如何使用 PARSE_COLNAMES 启用列名解析以进行基本的类型转换。

import sqlite3

with sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_COLNAMES) as conn:
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE test (id INTEGER, data TEXT)''')
    cursor.execute("INSERT INTO test VALUES (1, 'Sample data')")
    
    # Query with column name annotation for type conversion
    cursor.execute('''SELECT data AS "data [str]" FROM test''')
    row = cursor.fetchone()
    print(type(row[0]))  # <class 'str'>

此示例创建一个启用了 PARSE_COLNAMES 的内存数据库。 查询使用 AS "column [type]" 语法来指定结果列的类型转换。

括号中的类型（如 [str]）告诉 SQLite 如何转换列值。 这对于自定义类型或您需要特定的 Python 类型时特别有用。

日期类型转换

此示例演示了使用 PARSE_COLNAMES 自动将日期字符串转换为 Python datetime.date 对象。

import sqlite3
import datetime

def adapt_date(date):
    return date.isoformat()

def convert_date(value):
    return datetime.date.fromisoformat(value.decode())

# Register type adapters
sqlite3.register_adapter(datetime.date, adapt_date)
sqlite3.register_converter("date", convert_date)

with sqlite3.connect(':memory:', 
                    detect_types=sqlite3.PARSE_COLNAMES) as conn:
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE events 
                     (id INTEGER, event_date TEXT)''')
    
    today = datetime.date.today()
    cursor.execute("INSERT INTO events VALUES (1, ?)", (today,))
    
    # Query with date type conversion
    cursor.execute('''SELECT event_date AS "event_date [date]" 
                     FROM events''')
    row = cursor.fetchone()
    print(type(row[0]), row[0])  # <class 'datetime.date'> YYYY-MM-DD

我们首先注册 datetime.date 的适配器和转换器函数。 然后，查询在列别名中使用 [date] 来触发转换。

此模式对于在数据库中使用日期，同时在应用程序逻辑中维护正确的 Python 日期对象非常有用。

自定义类型转换

此示例展示了如何在更高级的场景中使用带有自定义 Python 类型的 PARSE_COLNAMES。

import sqlite3

class Point:
    def __init__(self, x, y):
        self.x = x
        self.y = y
    
    def __repr__(self):
        return f"Point({self.x}, {self.y})"

def adapt_point(point):
    return f"{point.x};{point.y}".encode()

def convert_point(value):
    x, y = value.decode().split(';')
    return Point(float(x), float(y))

# Register custom type
sqlite3.register_adapter(Point, adapt_point)
sqlite3.register_converter("point", convert_point)

with sqlite3.connect(':memory:', 
                    detect_types=sqlite3.PARSE_COLNAMES) as conn:
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE shapes 
                     (id INTEGER, coordinates TEXT)''')
    
    p = Point(3.5, 4.2)
    cursor.execute("INSERT INTO shapes VALUES (1, ?)", (p,))
    
    # Query with custom type conversion
    cursor.execute('''SELECT coordinates AS "coordinates [point]" 
                     FROM shapes''')
    row = cursor.fetchone()
    print(type(row[0]), row[0])  # <class '__main__.Point'> Point(3.5, 4.2)

我们定义了一个自定义 Point 类并注册适配器/转换器函数。 然后，查询使用 [point] 将存储的字符串转换回 Point 对象。

这种技术非常强大，可以将复杂的 Python 对象存储在 SQLite 中，同时在检索时保持其原始类型。

多列转换

此示例演示了使用 PARSE_COLNAMES 以及不同的类型在单个查询中转换多个列。

import sqlite3
import datetime

# Register date converters
sqlite3.register_adapter(datetime.date, lambda d: d.isoformat())
sqlite3.register_converter("date", lambda b: datetime.date.fromisoformat(b.decode()))

with sqlite3.connect(':memory:', 
                    detect_types=sqlite3.PARSE_COLNAMES) as conn:
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE records 
                     (id INTEGER, record_date TEXT, value REAL)''')
    
    cursor.execute("INSERT INTO records VALUES (1, ?, 42.5)", 
                  (datetime.date.today(),))
    
    # Convert multiple columns with different types
    cursor.execute('''SELECT 
                        id AS "id [int]",
                        record_date AS "record_date [date]",
                        value AS "value [float]"
                     FROM records''')
    row = cursor.fetchone()
    print(type(row[0]), type(row[1]), type(row[2]))
    # <class 'int'> <class 'datetime.date'> <class 'float'>

查询将三个列转换为不同的 Python 类型：整数、日期和浮点数。 每个列都使用带有相应类型的 AS "column [type]" 语法。

这种方法提供了对每个返回列的类型的细粒度控制，这可以简化处理查询结果的应用程序代码。

与 PARSE_DECLTYPES 结合

此示例展示了 PARSE_COLNAMES 如何与 PARSE_DECLTYPES 协同工作以进行全面的类型处理。

import sqlite3
import datetime

# Register type handlers
sqlite3.register_adapter(datetime.date, lambda d: d.isoformat())
sqlite3.register_converter("date", lambda b: datetime.date.fromisoformat(b.decode()))

# Use both PARSE_DECLTYPES and PARSE_COLNAMES
with sqlite3.connect(':memory:', 
                    detect_types=sqlite3.PARSE_DECLTYPES | 
                               sqlite3.PARSE_COLNAMES) as conn:
    cursor = conn.cursor()
    # Declare column type in table definition
    cursor.execute('''CREATE TABLE events 
                     (id INTEGER, event_date DATE)''')
    
    today = datetime.date.today()
    cursor.execute("INSERT INTO events VALUES (1, ?)", (today,))
    
    # Override declared type with column name annotation
    cursor.execute('''SELECT 
                        event_date AS "event_date [str]"
                     FROM events''')
    row = cursor.fetchone()
    print(type(row[0]))  # <class 'str'> (overridden by column name)

我们使用按位 OR (|) 组合这两个标志。 该表将 event_date 声明为 DATE 类型，但查询使用列别名中的 [str] 覆盖了它。

这种组合提供了最大的灵活性：声明的类型用于正常操作，并且能够在需要时通过列名覆盖。

处理 NULL 值

此示例演示了 PARSE_COLNAMES 在类型转换期间如何处理 NULL 值。

import sqlite3
import datetime

# Register date converter
sqlite3.register_converter("date", lambda b: datetime.date.fromisoformat(b.decode()))

with sqlite3.connect(':memory:', 
                    detect_types=sqlite3.PARSE_COLNAMES) as conn:
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE tasks 
                     (id INTEGER, due_date TEXT)''')
    
    # Insert both date and NULL values
    cursor.execute("INSERT INTO tasks VALUES (1, ?)", 
                  (datetime.date.today().isoformat(),))
    cursor.execute("INSERT INTO tasks VALUES (2, NULL)")
    
    # Query with type conversion
    cursor.execute('''SELECT due_date AS "due_date [date]" FROM tasks''')
    rows = cursor.fetchall()
    for row in rows:
        print(type(row[0]), row[0])
    # <class 'datetime.date'> YYYY-MM-DD
    # <class 'NoneType'> None

该示例表明，即使指定了类型转换，数据库中的 NULL 值在 Python 中仍然保留为 None。 不会为 NULL 值调用转换器函数。

在编写处理数据库中可能为 NULL 的可选字段的查询结果的代码时，记住此行为非常重要。

复杂类型转换

这个高级示例演示了将带有 JSON 数据的 PARSE_COLNAMES 用于复杂类型转换场景。

import sqlite3
import json

def adapt_json(data):
    return json.dumps(data).encode()

def convert_json(value):
    return json.loads(value.decode())

# Register JSON converter
sqlite3.register_adapter(dict, adapt_json)
sqlite3.register_converter("json", convert_json)

with sqlite3.connect(':memory:', 
                    detect_types=sqlite3.PARSE_COLNAMES) as conn:
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE configs 
                     (id INTEGER, settings TEXT)''')
    
    config = {'theme': 'dark', 'notifications': True}
    cursor.execute("INSERT INTO configs VALUES (1, ?)", (config,))
    
    # Query with JSON conversion
    cursor.execute('''SELECT settings AS "settings [json]" 
                     FROM configs''')
    row = cursor.fetchone()
    print(type(row[0]), row[0]['theme'])  # <class 'dict'> dark

我们为 Python 字典到 JSON 字符串以及反向注册转换器。 查询使用 [json] 自动将存储的 JSON 字符串转换回 Python 字典。

这种模式对于在 SQLite 中存储结构化配置数据或其他复杂对象，同时保持对原始 Python 数据结构的轻松访问非常有用。

最佳实践

• 使用描述性类型名称：在方括号中选择清晰的类型名称
• 仔细注册转换器：确保转换器处理所有边缘情况
• 与 PARSE_DECLTYPES 结合：用于全面的类型处理
• 记录类型转换：明确列类型期望
• 处理 NULL 值：确保您的代码处理潜在的 NULL 值

资料来源

• Python sqlite3 文档
• SQLite 数据类型
• SQLite CAST 表达式

作者

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

列出所有 Python 教程。