Databases
StackOverflow2013
Postsdbo
POUsing sqlite prepared statements with SELECT
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing sqlite prepared statements with SELECT
    text
    copied!<p>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:</p> <pre><code>#!/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 </code></pre> <p>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? </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload