python - 使用带有 SELECT 的 sqlite 准备好的语句

Question

我正在从事一个小项目，该项目组织人们的漫画书并将有关它们的元数据存储在 SQL 数据库中。为了正确起见，我使用准备好的语句而不是 Python 的内置字符串运算符，但我不能让它正常工作。这是我用来说明我遇到的问题的一小段代码:

#!/usr/bin/env python

import sqlite3

connection = sqlite3.connect("MyComicBox.db")

curs = connection.cursor()

curs.execute("CREATE TABLE IF NOT EXISTS comic_collection (id INTEGER PRIMARY KEY, series TEXT, publisher TEXT, issue TEXT, tags TEXT)")

connection.commit()

def addComic(series = "", publisher = "", issue = "", tags = ""):
    curs.execute("INSERT INTO comic_collection (series, publisher, issue, tags) VALUES(?, ?, ?, ?)", (series, publisher, issue, tags))
    connection.commit()

def search(attribute, val):
    """
    Do an SQL query for all comics where attribute LIKE val and return a list of them.
    """

#   cmd = "SELECT * from comic_collection WHERE %s LIKE '%s'" % (attribute, val)
#   curs.execute(cmd)

    cmd = "SELECT * from comic_collection WHERE ? LIKE ?"
    curs.execute(cmd, (attribute, val))

    results = []

    for comic in curs:
        results.append(comic)

    return results

addComic(series = "Invincible Iron Man", issue = "500", publisher = "Marvel Comics", tags = "Iron Man; Spider-Man; Mandarin")


searchResults = search("issue", "500")

for item in searchResults:
    print item

我的问题出在搜索功能上。除非我使用 ? 替换执行 cmd 的两行，否则查询不会返回任何内容。我使用 Python 的内置字符串运算符执行 cmd 的两行(已注释掉)运算符。谁能帮我弄清楚我做错了什么？

Answer 1

您不能为列名使用占位符 - 它们仅用于值。所以这会起作用:

cmd = "SELECT * from comic_collection WHERE %s LIKE ?" % attribute
curs.execute(cmd, (val,))