python - python sqlite3中的递归(级联)选择

Question

我有一个 sqlite 表，其中包含 3 列，名为 ID(整数)、N(整数)和 V(实数)。该对 (ID, N) 是唯一的。

使用python模块sqlite3，我想使用以下形式执行递归选择

select ID from TABLE where N = 0 and V between ? and ? and ID in 
    (select ID from TABLE where N = 7 and V between ? and ? and ID in
        (select ID from TABLE where N = 8 and V between ? and ? and ID in  
            (...)
        )
    )

我收到以下错误，可能是因为超出了最大递归深度(？)。我需要大约 20 到 50 个回避级别

sqlite3.OperationalError: parser stack overflow

我也尝试加入子选择，例如

select ID from
         (select ID from TABLE where N = 0 and V between ? and ?) 
    join (select ID from TABLE where N = 7 and V between ? and ?) using (ID)
    join (select ID from TABLE where N = 8 and V between ? and ?) using (ID)
    join ...

但是这种方法速度出奇的慢，即使只有很少的子选择

是否有更好的方法来执行相同的选择？
注意:该表的索引为 (N, V)

下面是一个示例，展示了选择的工作原理

ID  N   V  
0   0   0,1  
0   1   0,2  
0   2   0,3  
1   0   0,5  
1   1   0,6  
1   2   0,7  
2   0   0,8  
2   1   0,9  
2   2   1,2

步骤0

select ID from TABLE where N = 0 and V between 0 and 0,6

ID 位于 (0,1)
步骤1

select ID from TABLE where N = 1 and V between 0 and 1  and ID in (0, 1)

ID仍在(0,1)
步骤2

select ID from TABLE where N = 2 and V between 0,5 and 1 and ID in (0, 1)

ID为1

Answer 1

解开递归，以相反的顺序执行并在 Python 中执行。为此，我创建了一个包含 100 条记录的表，每条记录的 Id 介于 0 到 99 之间，N=3，V=5。我随意选择了整个记录集合作为最里面的记录。

您需要想象有一个 N 和 V 索引的值列表，以便为最后一个 SQL SELECT 选择列表开头的值。循环所做的只是获取内部 SELECT 产生的 ID 列表，将其作为 IN 子句的一部分提供给下一个 SELECT。

没有任何索引，这一切都在 augenblick 中结束。

>>> import sqlite3
>>> conn = sqlite3.connect('recur.db')
>>> c = conn.cursor()
>>> previous_ids = str(tuple(range(0,100)))
>>> for it in range(50):
...     rows = c.execute('''SELECT ID FROM the_table WHERE N=3 AND V BETWEEN 2 AND 7 AND ID IN %s''' % previous_ids)
...     previous_ids = str(tuple([int(_[0]) for _ in rows.fetchall()]))
...     
>>> previous_ids
'(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99)'

编辑:这避免了使用长字符串，比 augenblick 花费更长的时间。这本质上与使用表格实现的想法相同。

>>> import sqlite3
>>> conn = sqlite3.connect('recur.db')
>>> c = conn.cursor()
>>> N_V = [
... (0, (0,6)),
... (0, (0, 1)),
... (1, (0, 2)),
... (2, (0, 3)),
... (0, (0, 5)),
... (1, (0, 6)),
... (2, (0, 7)),
... (0, (0, 8)),
... (1, (0, 9)),
... (2, (1, 2))
... ]
>>> r = c.execute('''CREATE TABLE essentials AS SELECT ID, N, V FROM the_table WHERE N=0 AND V BETWEEN 0 AND 6''')
>>> for n_v in N_V[1:]:
...     r = c.execute('''CREATE TABLE next AS SELECT * FROM essentials WHERE essentials.ID IN (SELECT ID FROM the_table WHERE N=%s AND V BETWEEN %s AND %s)''' % (n_v[0], n_v[1][0], n_v[1][1]))
...     r = c.execute('''DROP TABLE essentials''')
...     r = c.execute('''ALTER TABLE next RENAME TO essentials''')
...