Using sqlite prepared statements with SELECT

Question

I'm working on a little pet project that organizes people's comic books and stores metadata about them in an SQL database. For the sake of correctness, I'm using prepared statements instead of Python's built in string operators, but I can't quite get it to work right. This is a brief snippet of code I've made to illustrate the problems I'm having:

#!/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

My problem is in the search function. The query doesn't return anything unless I replace the two lines where I execute cmd using the ? operator with the two (commented out) lines where I execute cmd using Python's built in string operators. Can anybody help me figure out what I'm doing wrong?

Answer 1

You can't use placeholders for column names - they are for values only. So this would work:

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