Python中sqlite3自定义函数内执行SQL命令报错原因排查

问题描述 #

我正在学习使用Python创建sqlite3自定义函数，编写了如下代码：

import sqlite3

conn = sqlite3.connect("data.db")
c = conn.cursor()


query = "CREATE TABLE IF NOT EXISTS names (ID INTEGER UNIQUE PRIMARY KEY NOT NULL, name TEXT)"
c.execute(query)


query = "INSERT INTO names (name) VALUES ('foo')"
c.execute(query)

query = "INSERT INTO names (name) VALUES ('boo')"
c.execute(query)

conn.commit()


def get_ID(name):
    
    query = "SELECT ID FROM names WHERE name = '{}'".format(name)
    c.execute(query)
    id = c.fetchall()
    print(id)
    return id

print(get_ID("foo"))

conn.create_function("GETID", 1, get_ID)

query = "SELECT GETID(?)"
c.execute(query, ("foo",))
print(c.fetchall())

c.close()
conn.close()

执行后输出如下：

[(1,)]
[(1,)]
Traceback (most recent call last):
  File "D:\Sync1\Code\Python3\test.py", line 33, in <module>
    c.execute(query, ("foo",))
sqlite3.OperationalError: user-defined function raised exception

直接调用get_ID函数可正常返回结果，但通过SQL语句调用注册后的GETID函数时触发错误，请问该报错的原因是什么？

报错原因 #

1. 游标复用冲突：当SQLite执行SELECT GETID(?)时，已经占用了游标c，而get_ID函数内部又尝试用同一个游标执行另一条查询，导致游标处于忙碌状态，触发异常。
2. 返回值类型不兼容：SQLite自定义函数要求返回值为SQLite原生支持的类型（如整数、字符串、None等），但get_ID返回的是fetchall()得到的列表（如[(1,)]），这种复合结构无法被SQLite解析，这是核心报错原因。

修复方案 #

针对上述问题，修改代码如下：

import sqlite3

conn = sqlite3.connect("data.db")
c = conn.cursor()

# 表创建与数据插入逻辑保持不变
query = "CREATE TABLE IF NOT EXISTS names (ID INTEGER UNIQUE PRIMARY KEY NOT NULL, name TEXT)"
c.execute(query)
query = "INSERT INTO names (name) VALUES ('foo')"
c.execute(query)
query = "INSERT INTO names (name) VALUES ('boo')"
c.execute(query)
conn.commit()

def get_ID(name):
    # 内部创建独立游标，避免与外部游标冲突
    inner_cursor = conn.cursor()
    # 使用参数化查询，杜绝SQL注入风险
    query = "SELECT ID FROM names WHERE name = ?"
    inner_cursor.execute(query, (name,))
    result = inner_cursor.fetchone()
    inner_cursor.close()
    # 返回单个值，无匹配时返回None
    return result[0] if result else None

print(get_ID("foo"))

conn.create_function("GETID", 1, get_ID)

query = "SELECT GETID(?)"
c.execute(query, ("foo",))
print(c.fetchall())

c.close()
conn.close()

额外注意事项 #

• 必须使用参数化查询替代字符串格式化，避免SQL注入漏洞；
• 自定义函数的返回值只能是SQLite兼容的基础类型，不能返回列表、字典等复杂结构。

内容的提问来源于stack exchange，提问作者Abel Gutiérrez