python - Pandas + SQLite "cannot use index"错误

Question

我正在使用 pandas、sqlite 和 sqlalchemy 来搜索一堆字符串中的子字符串。该项目的灵感来自this tutorial.

首先，我创建一个包含一列字符串的 SQLite 数据库。然后我迭代一个单独的字符串文件并在数据库中搜索这些字符串。

我发现这个过程很慢，所以我做了一些研究，发现我需要在我的列上建立一个索引。当我按照here提供的说明进行操作时在 sqlite shell 中，一切似乎都工作得很好。

但是，当我尝试在 python 脚本中创建索引时，出现“无法使用索引”错误。

import pandas as pd
from sqlalchemy import create_engine # database connection
import datetime as dt



def load_kmer_db(disk_engine, chunk_size, encoding='utf-8'):
    start = dt.datetime.now()
    j = 0
    index_start = 1
    for df in pd.read_csv('fake.kmers.csv', chunksize=chunk_size, iterator=True, encoding=encoding):
        df.index += index_start
        j += 1
        df.to_sql('data', disk_engine.raw_connection(), if_exists='append', index=True, index_label='kmer_index')
        index_start = df.index[-1] + 1


def search_db_for_subsequence(disk_engine, sequence):
    """

    :param disk_engine: Disk engine for database containing query sequences
    :param sequence: Sequence for finding subsequences in the database
    :return: A data frame with the subsequences of sequence
    """
return pd.read_sql_query("SELECT kmer FROM data INDEXED BY kmer_index WHERE '" + sequence + "' LIKE '%' || kmer || '%'", disk_engine)

if __name__ == "__main__":
    import argparse

    parser = argparse.ArgumentParser()
    parser.add_argument('kmers', type=str, metavar='<kmer_file.txt>', help='text file with kmers')
    parser.add_argument('reads', type=str, metavar='<reads.fastq>', help='Reads to filter by input kmers')

    # Get the command line arguments.
    args = parser.parse_args()
    kmer_file = args.kmers
    reads_file = args.reads

    # Initialize database with filename 311_8M.db
    disk_engine = create_engine('sqlite:///311_8M.db') # This requires ipython to be installed

    load_kmer_db(disk_engine, 200)

    #****** Try explicitly calling the create index command
    #****** using the sqlite module.
    import sqlite3
    conn = sqlite3.connect('311_8M.db')
    c = conn.cursor()
    c.execute("CREATE INDEX kmer_index ON data(kmer);")

    reads = SeqReader(reads_file)
    for read in reads.parse_fastq():
        count += 1
        sequence = read[1]
        df = search_db_for_subsequence(
            disk_engine,
            sequence
        )

可以看到，我首先尝试通过将正确的关键字参数传递给 to_sql 方法来创建索引。当我单独执行此操作时，出现错误，指出找不到索引。然后我通过 sqlite3 模块显式地创建了索引，这产生了“无法使用索引”错误。

现在看来我已经创建了索引，但由于某种原因，我无法使用它。为什么会这样呢？如何使用 pandas api 创建索引而不必使用 sqlite3 模块？

Answer 1

该错误消息“无法使用索引”似乎与 pd.read_sql_query() 调用相关，而不是直接使用 sqlite3 模块创建索引的部分。

使用 some_col LIKE '%[some term]%' 的查询无法使用 some_col 上的索引。另一方面，使用 some_col LIKE '[some_term]%' 的查询可以使用 some_col 上的索引。