基于字母排序的Sqlite3伪索引列

Question

在Sqlite3中，我该如何创建一个伪索引列，用于根据同一表中另一个列的字母顺序生成连续的行号呢？

到目前为止，我一直在为在一个Python网页应用中解决简单问题而写复杂的SQL查询。

我只是想在记录之间循环（比如前后翻页），而不想去处理索引和不连续的记录编号。

简单来说，我有一个表，里面有一个“标题”列和一个“记录ID”列。（总共有40列）

这个表很小，只有大约250条记录。标题很长，而且有些记录ID是缺失的，因为有记录被删除了。我大约每个月才会添加或删除一条记录（使用DB Browser for SQLite），而且我不插入记录，只是在最后添加，记录ID会自动递增。

我在想，为什么不在表中添加一个“标题排序顺序”（整数）列，基于标题列的字母顺序，然后在我很少添加或删除记录时重新编号呢？

我知道怎么做这种事情，使用f-string（在这里是为了“前一个”）：

f"SELECT record_id, title FROM { table } 
INDEXED BY { titles_index } 
WHERE title < '{ current_title_text }' 
ORDER BY title DESC LIMIT 1;"

这真的很麻烦。

在StackOverflow上有很多复杂的解决方案来处理“前后”问题（比如我找到上面的内容），但添加一个新列会简单得多。

这个表已经有一个列：title_sort(int)

我想到了写一个小的Python脚本来读取这个表，像这样写伪代码：

table_data = SELECT rowid, title FROM { table } 
INDEXED BY { titles_index } 
ORDER BY title ASC;

order = 1

for each row in table_data

    UPDATE { table }

    SET row.title_sort = order

    order = order + 1

但是我在代码上遇到了困难。

任何帮助都会很感激。谢谢。

编辑：如果你们不知道怎么做，就直接说吧。如果你们只是想随便评论我或者Sqlite，那就没什么价值。我已经很清楚地解释了原因和背景，这已经回答了你们的问题。也许在评论之前先读一下，或者干脆别评论。

Answer 1

这是我实现的方法。作为一个对sqlite还不太熟悉的新手，我知道这些步骤可能效率不高，但确实能用。这里的伪索引字段叫做“title_sort”。我创建了一个flask路由和一个测试页面。访问这个路由时，会更新表格，页面上会显示结果。

在我的应用程序中，当我显示一条记录时，顶部有一个小部件，显示当前的记录ID、标题和排序位置，还有“上一条”和“下一条”的记录ID和标题。这里的“上一条”和“下一条”是根据字母顺序来排序的，而不是根据记录ID。

import sqlite3
from flask import render_template

@app.route("/sort")
def sort():
    index = "ix_title"

    # Get the rows in alphabetical order of "title" column. 
    # Selecting "title" is used for diagnostics
    statement_1 = f"SELECT rowid, title FROM { table } INDEXED BY { index } ORDER BY title ASC"

    conn = sqlite3.connect(PRACTICE)
    cursor = conn.cursor()
    cursor.execute(statement_1)
    row_data = cursor.fetchall()
    conn.close()

    # Now use "row_data" (indexed by title) from above to cycle through
    # the table a row at a time, populating the title_sort column
    # "row_data" type is a list of tuples.  

    order = 1
    for row in row_data:
        # inside the tuple, row[0] will be the rowid from the query above
        statement2 = f"UPDATE { table } SET title_sort = { order } WHERE rowid = { row[0] }"
        conn = sqlite3.connect(PRACTICE)
        cursor = conn.cursor()
        cursor.execute(statement_2)
        conn.commit()
        conn.close()
    
# to test whether it worked correctly, grab the updated rows and send to a page

    statement_3 = f"SELECT title_sort, rowid, title FROM { table } ORDER BY title_sort ASC"
    conn = sqlite3.connect(PRACTICE)
    cursor = conn.cursor()
    cursor.execute(statement_3)
    sort_test = cursor.fetchall()
    conn.close()

    return render_template("/sort.html", 
        row_data=row_data, 
        sort_test=sort_test)