尝试在SQLi中按组合主键的长列表逐行选择

Question

这是我的查询，使用的是在浏览此网站时发现的代码：

query="""SELECT Family
           FROM Table2
     INNER JOIN Table1 ON Table1.idSequence=Table2.idSequence
     WHERE (Table1.Chromosome, Table1.hg19_coordinate) IN ({seq})
""".format(seq=','.join(['?']*len(matchIds_list)))

matchIds_list是（？）？,?) 格式。你知道吗

如果我只要求一个条件（即与Chromosome和hg_coordinate相反的Table1.Chromosome），并且matchIds_list只是一个简单的单值列表，那么它就可以工作，但是我不知道如何使它与一个复合键或两个列一起工作。你知道吗

Answer 1

既然您运行的是SQLite3.7.17，我建议您只使用一个临时表。你知道吗

创建并填充临时表。你知道吗

cursor.executescript("""
    CREATE TEMP TABLE control_list (
        Chromosome TEXT NOT NULL,
        hg19_coordinate TEXT NOT NULL
    );

    CREATE INDEX control_list_idx ON control_list (Chromosome, hg19_coordinate);
""")

cursor.executemany("""
    INSERT INTO control_list (Chromosome, hg19_coordinate)
    VALUES (?, ?)
""", matchIds_list)

只需将查询约束到控制列表临时表。你知道吗

SELECT Family
FROM Table2
INNER JOIN Table1
    ON Table1.idSequence = Table2.idSequence

  Constrain to control_list.
WHERE EXISTS (
        SELECT *
        FROM control_list
        WHERE control_list.Chromosome = Table1.Chromosome
            AND control_list.hg19_coordinate = Table1.hg19_coordinate
    )

最后执行查询（无需格式化此查询）。你知道吗

cursor.execute(query)

# Remove the temporary table since we're done with it.
cursor.execute("""
    DROP TABLE control_list;
""")

---

短查询（需要SQLite3.15）：实际上，您几乎已经拥有了它。您需要使IN ({seq})成为子查询 表情。你知道吗

SELECT Family
FROM Table2
INNER JOIN Table1
    ON Table1.idSequence = Table2.idSequence
WHERE (Table1.Chromosome, Table1.hg19_coordinate) IN (VALUES {seq});

长查询（需要SQLite3.8.3）：它看起来有点复杂，但非常简单。把你的 控件列表中的子选择，然后通过控件约束该主选择 列表。你知道吗

SELECT Family
FROM Table2
INNER JOIN Table1
    ON Table1.idSequence = Table2.idSequence

  Constrain to control_list.
WHERE EXISTS (
        SELECT *
        FROM (
            SELECT
                  Name the columns (must match order in tuples).
                "" AS Chromosome,
                ":1" AS hg19_coordinate
            FROM (
                  Get control list.
                VALUES {seq}
            ) AS control_values
        ) AS control_list
          Constrain Table1 to control_list.
        WHERE control_list.Chromosome = Table1.Chromosome
            AND control_list.hg19_coordinate = Table1.hg19_coordinate
    )

无论使用哪种查询，格式化SQL时，为每个compsite将{seq}替换为(?,?) 而不仅仅是?。你知道吗

query = " ... ".format(seq=','.join(['(?,?)']*len(matchIds_list)))

最后在执行查询时将matchIds_list展平，因为它是一个元组列表。你知道吗

import itertools
cursor.execute(query, list(itertools.chain.from_iterable(matchIds_list)))